SSRS 2005 - problem with IIF/switch runningvalue formula
I created 2 code functions that calculate number of days between actions. There four actions: 1.Rcvd, 2.Sent out for info, 3.Back to process and 4.completed dat. The days need to be calculated only between 1 and 2 and between 3 and 4, if the document is not sent out for more info, the days are calculated between action 1 and 4. The expression that I created below calculates those values with no problem for each document. Now, I need to calculate an average of days for each coordinator, for that I created another expression that will run a running value for each coordinator, the expression 2 that I pasted it below is the one that does not work, it will do the running sum for a few records then when the criteria moves to the false part of the IIF or SWITCH then will start from 0 again. PLEASE HELP ME! Expression 1: Calculate number of days for each document =IIF((RUNNINGVALUE(code.SentTo(Fields!DateReceived.Value, Fields!ActionTypeID.Value, Fields!ActionDate.Value), SUM,"table1_Coordinator_Group2")) = NOTHING, DATEDIFF("d",Fields!DateReceived.Value,Fields!DateOfResponse.Value),(RUNNINGVALUE(code.SentTo(Fields!DateReceived.Value, Fields!ActionTypeID.Value, Fields!ActionDate.Value),SUM,"table1_Coordinator_Group2") + RUNNINGVALUE(CODE.BackFrom( Fields!ActionDate.Value, Fields!DateOfResponse.Value,Fields!ActionTypeID.Value),SUM,"table1_Coordinator_Group2"))) Expression 2: =switch((RUNNINGVALUE(code.SentTo(Fields!DateReceived.Value, Fields!ActionTypeID.Value, Fields!ActionDate.Value), SUM,"table1")) = nothing, (runningvalue(code. ComplRfrd( Fields!DateReceived.Value, Fields!DateOfResponse.Value,Fields!ActionTypeID.Value), SUM,"table1")), (RUNNINGVALUE(code.SentTo(Fields!DateReceived.Value, Fields!ActionTypeID.Value, Fields!ActionDate.Value), SUM,"table1")) > 0,((RUNNINGVALUE(code.SentTo(Fields!DateReceived.Value, Fields!ActionTypeID.Value, Fields!ActionDate.Value),SUM,"table1")) + (RUNNINGVALUE(CODE.BackFrom( Fields!ActionDate.Value, Fields!DateOfResponse.Value,Fields!ActionTypeID.Value), SUM,"table1")))) Angie Rivera
December 16th, 2010 2:30pm

Hi Angie, Your description and expression examples are complex, and not good for our understanding or repro the issue if necessary. To better support, i would suggest you give us example data here and tell us what output do you want. Maybe, some expression is better and unreadable to achieve your purpose. thanks, Jerry
Free Windows Admin Tool Kit Click here and download it now
December 17th, 2010 4:30am

Below is a sample of the data and the type of report that I need to duplicate. The days between item 2 and 3 should not be counted. With the Expression 1 that I show above I am able to calculate the total days per document, but I am not able to sum that column (Total days per document) /number of documents to come up with the average? I tried to refer to that textbox to capture the value but I get a message that it should be use only on page header or footer, I was putting it in the group header but it did not do it.... PLEASE HELP ME!!!! Item Date # days per action TOTAL days per document JANE DOE Doc1 1 Received 10/6/2010 6 dayS 2 Sent out 10/6/2010 0 3 Back 10/30/2010 4 Completed 11/5/2010 6 Doc2 1 Received 10/7/2010 3 days 2 Sent out 10/10/2010 3 3 Back 11/05/2010 4 Completed 11/05/2010 0 Doc3 1 Received 10/7/2010 38 days 4 Completed 11/15/2010 38 Doc4 1 Received 12/15/2010 9 days 4 Completed 12/24/2010 38 JANE DOE TOTAL Average Days To Complete 14 DAYS Number Documents 4 JOHN DOE Doc1 1 Received 10/6/2010 6 days 2 Sent out 10/6/2010 0 3 Back 10/30/2010 4 Completed 11/5/2010 6 Doc2 1 Received 11/4/2010 20 days 4 Completed 11/24/2010 20 Doc3 1 Received 10/7/2010 3 days 2 Sent out 10/10/2010 3 3 Back 11/05/2010 4 Completed 11/05/2010 0 Doc4 1 Received 10/7/2010 38 days 4 Completed 11/15/2010 38 Doc5 1 Received 12/15/2010 9 days 4 Completed 12/24/2010 38 JOHN DOE TOTAL Average Days To Complete 15.2 DAYS Number Documents 5 Angie Rivera
December 17th, 2010 9:14am

Hi Angie, Sorry for this late reply. The average purpose can't be achieved in reporting services 2005, because the caculations have aggregates functions nested while nested aggregates functions can't be supported in SSRS 2005. Even in SSRS 2008 R2, only part of aggregates functions can do nested aggregation. See http://msdn.microsoft.com/en-us/library/dd255275.aspx on Restrictions on Nested Aggregates in SSRS 2008 R2. To get the issue around, i would suggest you write T-SQL script to get the #DaysPerAction and TotalDaysPerDocument. Below is my code sample for your reference: create table Documents (People varchar(20), doc varchar(20), Item varchar(25), Dates datetime) go insert into Documents select 'JANE DOE','Doc1','1Received','2010-10-6' union all select 'JANE DOE','Doc1','2Send out','2010-10-6' union all select 'JANE DOE','Doc1','3Back','2010-10-30' union all select 'JANE DOE','Doc1','4Completed','2010-11-5' union all select 'JANE DOE','Doc2','1Received','2010-10-7' union all select 'JANE DOE','Doc2','2Send out','2010-10-10' union all select 'JANE DOE','Doc2','3Back','2010-11-5' union all select 'JANE DOE','Doc2','4Completed','2010-11-5' union all select 'JANE DOE','Doc3','1Received','2010-10-7' union all select 'JANE DOE','Doc3','4Completed','2010-11-15' union all select 'JANE DOE','Doc4','1Received','2010-12-15' union all select 'JANE DOE','Doc4','4Completed','2010-12-24' union all select 'JOHN DOE','Doc1','1Received','2010-10-6' union all select 'JOHN DOE','Doc1','2Send out','2010-10-6' union all select 'JOHN DOE','Doc1','3Back','2010-10-30' union all select 'JOHN DOE','Doc1','4Completed','2010-11-5' union all select 'JOHN DOE','Doc2','1Received','2010-11-4' union all select 'JOHN DOE','Doc2','4Completed','2010-11-24' union all select 'JOHN DOE','Doc3','1Received','2010-10-7' union all select 'JOHN DOE','Doc3','2Send out','2010-10-10' union all select 'JOHN DOE','Doc3','3Back','2010-11-5' union all select 'JOHN DOE','Doc3','4Completed','2010-11-5' union all select 'JOHN DOE','Doc4','1Received','2010-10-7' union all select 'JOHN DOE','Doc4','4Completed','2010-11-15' union all select 'JOHN DOE','Doc5','1Received','2010-12-15' union all select 'JOHN DOE','Doc5','4Completed','2010-12-24' go select People,doc,Item,Dates, case when Item = '1Received' then 1 when Item = '2Send out' then 2 when Item = '3Back' then 3 when Item = '4Completed' then 4 end as ItemNumber from Documents go with CTE1(People,Doc,Item,ItemNumber,Dates) AS ( select People,doc,Item, ROW_NUMBER() over(partition by People,doc order by item) as ItemNumber, Dates from Documents ), CTE2(People,Doc,Item,ItemNumber,Dates,DaysPerAction) as( select c1.People,c1.Doc,c1.Item,c1.ItemNumber,c1.Dates,case when c1.ItemNumber % 2 =0 then DateDiff(DAY,c2.Dates,c1.Dates) else 0 end from CTE1 c1 left join CTE1 c2 on c1.People = c2.People and c1.Doc = c2.Doc and c1.ItemNumber = c2.ItemNumber + 1 ) , CTE3(People,Doc,DocDaysPerDoc) as ( select People,Doc,SUM(DaysPerAction) from CTE2 group by People,Doc ) , CTE4(People,Doc,Item,ItemNumber,Dates,DaysPerAction,DaysPerDoc) as (select c2.People,c2.Doc,c2.Item,c2.ItemNumber,c2.Dates,c2.DaysPerAction, case when c2.Item = '4Completed' then c3.DocDaysPerDoc else 0 end from CTE2 c2 left join CTE3 c3 on c2.People = c3.People and c2.Doc = c3.Doc) select * from CTE4 order by People,Doc,ItemNumber the above code calls CTE, see http://msdn.microsoft.com/en-us/library/ms175972.aspx for CTE syntax and sample code. The highlighted part code is used in the report dataset query. thanks, Jerry
Free Windows Admin Tool Kit Click here and download it now
December 23rd, 2010 1:29am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics