July 1, 2008 at 9:37 am
I have a column that returns the Qty changed from last week :
=IIF(Fields!DCs.Value-Previous(Fields!DCs.Value)= Fields!DCs.Value,"",Fields!DCs.Value-Previous(Fields!DCs.Value))
I now need to add an accumulation column based on the calculated column.
I tried and refernce the textbox and use the previous function, but it did not work.
Any help would be great,
Thanks,
Seajoker
July 1, 2008 at 9:51 am
Hi
This is a problem with sub-totalling in SSRS when using conditions/expressions. The trick is to ensure that both results of the expression are the same data type.
Your expression:
=IIF(Fields!DCs.Value-Previous(Fields!DCs.Value)= Fields!DCs.Value,"",Fields!DCs.Value-Previous(Fields!DCs.Value))
Should be written in the following way for the sub-total:
=SUM(IIF(Fields!DCs.Value-Previous(Fields!DCs.Value)= Fields!DCs.Value,cdbl(0),cdbl(Fields!DCs.Value-Previous(Fields!DCs.Value)))
You might also want to do the same for the detail cell, but this shouldn't be necessary.
Good luck,
Nigel West
UK
July 1, 2008 at 1:01 pm
Nigel,
I'm getting this error when using your code:
[rsAggregateofAggregate] The Value expression for the textbox 'textbox73' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.
seajoker
July 1, 2008 at 2:20 pm
Sorry, didn't think about the use of Previous along side SUM, I'll have a bit of a re-think.
If I understand it correctly you have something like this
Desc WK Val Diff
A 1 5 0
B 2 3 -2
C 3 6 3
D 4 11 5
And what you want is to do a SUM of diff, and this should show a value of 6.
Is this correct?
Nigel West
UK
July 1, 2008 at 2:26 pm
nigel.c.west (7/1/2008)
If I understand it correctly you have something like this
Desc WK Val Diff Accum
A 1 5 0 0
B 2 3 -2 -2
C 3 6 3 1
D 4 11 5 6
And what you want is to do a SUM of diff, and this should show a value of 6.
Is this correct?
Yes. I would like a running total if possible, on each line, but I would take a total of 6.
July 1, 2008 at 3:45 pm
OK, I did some work on this and found that, as difficult to believe as it is, this is not possible directly in SSRS.
My advice is to do the calculation of difference in the dataset, this should be possible by creating a temporary table and then performing an update on it to calculate the difference. This way, the difference is added to your report table simply as a value from the dataset.
Best of luck,
Nigel West
UK
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply