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 '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


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 =

LEFT JOIN inv_investments p ON t.prprojectid =

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     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.