AnsweredAssumed Answered

Exporting a custom nsql portlet to excel

Question asked by Keri Taylor on Jan 19, 2010
Latest reply on Jan 21, 2010 by Keri Taylor
I am having trouble in knowing exactly how i am suppopsed to code numbers as well as text that should come over as numbers for use with the export to excel  feature. Here is an example of a portlet.   SELECT


@SELECT:DIM:USER_DEF:IMPLIED:ACT:NEWID():UNIQUEROW@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACT:SOURCE_DATA_ID:SOURCE_DATA_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACT:SUMMARY_LEVEL:SUMMARY_LEVEL@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACT:ACTUALS_DATE:ACTUALS_DATE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACT:ACTUALS_RESOURCE_ID:ACTUALS_RESOURCE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACT:ACTUALS_PROJECT_ID:ACTUALS_PROJECT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACT:ACTUALS_TASK_ID:ACTUALS_TASK_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACT:ACTUALS_TASK_NAME:ACTUALS_TASK_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACT:ACTUALS_BILL_TYPE:ACTUALS_BILL_TYPE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACT:ACTUALS:ACTUALS@

FROM
(

select '1' AS source_data_id

, '1' AS summary_level

, CONVERT(VARCHAR(10),s.slice_date, 111) AS actuals_date

, r.unique_name actuals_resource_id

, p.code actuals_project_id

, t.prid actuals_task_id

, t.prname actuals_task_name

, case when cc.prname like 'Non-Chargeable' Then 'Non-Billable' Else cc.prname End AS actuals_bill_type

, Sum(ISNULL(s.slice,0)) actuals


from PRJ_BLB_SLICES s

INNER JOIN PRTIMEENTRY te ON s.prj_object_id = te.prid

INNER JOIN PRTIMESHEET ts ON te.prtimesheetid = ts.prid

LEFT JOIN PRASSIGNMENT a ON te.prassignmentid = a.prid

LEFT JOIN PRCHARGECODE cc ON te.prchargecodeid = cc.prid

LEFT JOIN PRTASK t ON a.prtaskid = t.prid

INNER JOIN SRM_RESOURCES r ON ts.prresourceid = r.id

LEFT JOIN inv_investments p ON t.prprojectid = p.id

INNER JOIN prj_obs_associations g ON p.ID = g.RECORD_ID

INNER JOIN nbi_dim_obs h ON g.unit_id = h.obs_unit_id


where (1=1)

and s.slice_request_id = 55555

and s.SLICE_DATE >= @WHERE:PARAM:USER_DEF:DATE:StartDate@

and s.SLICE_DATE     the source_data_id and summar_level i want to come over as numeric, should i just use convert/cast? as well as the actuals_task_id, i want to come over as numeric, but only want it formatted without the commas and decimal, (ie comes over as 5,123,123.00). Been trying to understand this for a while now, Thanks for any help.

Outcomes