AnsweredAssumed Answered

multidimensional data error's on custom portlets

Question asked by Kery on Oct 11, 2010
Latest reply on Oct 11, 2010 by Dave
Can anyone explain the logic behind adding attributes to the ID in the Query for a portlet?
I have 13 new queries that I created for custom portlets and while I can get them all to work I keep getting a multidimensional data error when certain combinations of parameters are used. Here is a specific example I am struggling with:

I have a portlet that is returning timesheet data. I have it coded so that data for only one resource can be returned at any given time. A resource can log time to a task for the same time period more than once but as a different role. So what is happening in this portlet is that only one line of data is being returned for a unique role. So while I have 8 lines of data I am expecting to be returned, I am only getting 3 - with unique roles returned and the multidimensional data error.

I have included the NSQL Query so you can see what I did. I am hoping someone can explain the logic behind the id field and why certain syntax is necessary I can troubleshoot and fix all the portlets that have this issue.


SELECT
@SELECT:DIM:USER_DEF:IMPLIED:Q:cast(q.[ResourceID] AS Varchar)
+cast(q.[ProjectID] AS Varchar)+cast(q.[Start Date] AS Varchar)+cast(q.[Finish Date] AS Varchar)
+isnull(q.[Role], ' ')+isnull(q.[Status], ' ')
+isnull(q.[Input Type Code], ' ')+isnull(q.[Charge Code], ' '):ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[ResourceID]:filterResource@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[Resource]:resourcename@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[Input Type Code]:inputtypecode@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[Role]:role@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[ProjectID]:projectid@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[Project Name]:projectname@,
@SELECT:DIM_PROP:USER_DEF:BOOLEAN:Q:q.[Project Hours]:projecthours@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[Task]:task@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[Charge Code]:chargecode@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[Start Date]:startdate@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[Finish Date]:finishdate@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[Status]:status@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[Entry Note(s)]:entrynote@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:Q:q.[Sheet Note(s)]:sheetnote@,
@SELECT:METRIC:USER_DEF:IMPLIED:SUM(q.[Hours]):Hours:AGG@
FROM
(
select
sres.id as [ResourceID],
sres.last_name + ', ' + sres.first_name + ' (' + sres.unique_name + ')' as [Resource],
inputtype.prname as [Input Type Code],
role.full_name as [Role],
inv.code as [ProjectID],
inv.name as [Project Name],
CASE WHEN inv.code LIKE 'M-0%' THEN 0 ELSE 1 END AS [Project Hours],
task.prname as [Task],
charge.prname as [Charge Code],
tperiod.prstart as [Start Date],
tperiod.prfinish-1 as [Finish Date],
tentry.practsum/3600 AS [Hours],
CASE WHEN tsheet.prstatus = 0 THEN 'Saved'
WHEN tsheet.prstatus = 1 THEN 'Submitted'
WHEN tsheet.prstatus = 2 THEN 'Returned'
WHEN tsheet.prstatus = 3 THEN 'Approved'
WHEN tsheet.prstatus = 4 THEN 'Posted'
WHEN tsheet.prstatus = 5 THEN 'Adjusted'
END AS [Status]
,
niku.TF_TS_NOTES_FCT(tentry.prid) as [Entry Note(s)],
niku.Z_TS_NOTES_FCT(tsheet.prid) as [Sheet Note(s)]
from
niku.prtimeentry as tentry
inner join niku.prtimesheet as tsheet on tentry.prtimesheetid = tsheet.prid
inner join niku.prtimeperiod as tperiod on tsheet.prtimeperiodid = tperiod.prid
inner join niku.prassignment as assign on tentry.prassignmentid = assign.prid
inner join niku.prtask as task on assign.prtaskid = task.prid
inner join niku.inv_investments as inv on task.prprojectid = inv.id
inner join niku.srm_resources as sres on tsheet.prresourceid = sres.id
left join niku.srm_resources as role on tentry.role_id = role.id
left join niku.prchargecode as charge on tentry.prchargecodeid = charge.prid
left join niku.prtypecode as inputtype on tentry.prtypecodeid = inputtype.prid
where

tperiod.PRSTART >= @where:param:user_def:date:param_startdate@
and tperiod.PRFINISH <= @where:param:user_def:date:param_enddate@
and tentry.practsum <> 0


) q
WHERE @FILTER@
GROUP BY
cast(q.[ResourceID] AS Varchar)
+cast(q.[ProjectID] AS Varchar)+cast(q.[Start Date] AS Varchar)+cast(q.[Finish Date] AS Varchar)
+isnull(q.[Role], ' ')+isnull(q.[Status], ' ')
+isnull(q.[Input Type Code], ' ')+isnull(q.[Charge Code], ' '),
q.[ResourceID],
q.[Resource],
q.[Input Type Code],
q.[Role],
q.[ProjectID],
q.[Project Name],
q.[Project Hours],
q.[Task],
q.[Charge Code],
q.[Start Date],
q.[Finish Date],
q.[Status],
q.[Entry Note(s)],
q.[Sheet Note(s)],
q.[Hours]
HAVING @HAVING_FILTER@

Outcomes