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