Using Sum(IIF()) with multiple datasets

1
votes

Using the following statement to calculate percentages of multi-category revenue, based on a specific field value (Fields!CSU.Value="CEG")

=sum(iif(Fields!CSU.Value="CEG",cdbl(Sum(Fields!Multi_Cat__Rev.Value)/Sum(Fields!Est_Revenue_000_s.Value)/1000),0))

This works fine in the current data set that I am using. I also am attempting to calculate the difference between 2 data sets (same equation) but when I specify the 2nd data set in the function, I get the nested aggregate error (see below)

The expression used for the calculated field =Lookup(Fields!Opp__.Value,Fields!Opp__.Value,sum(iif(Fields!CSU.Value="CEG",cdbl(Sum(Fields!Multi_Cat__Rev.Value)/Sum(Fields!Est_Revenue_000_s.Value)/1000),0),"dsPriorWeek"))' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber.

I've searched through and cannot find an answer for my issue, please help...

Here is the current way I am attempting to do this: =sum(iif(Fields!CSU.Value="CEG",cdbl(Sum(Fields!Multi_Cat__Rev.Value)/Sum(Fields!Est_Revenue_000_s.Value)/1000),0))-sum(iif(Fields!CSU.Value="CEG",cdbl(Sum(Fields!Multi_Cat__Rev.Value)/Sum(Fields!Est_Revenue_000_s.Value)/1000),0),"dsPriorWeek")

1
reporting-services

1 Answers

0
votes

First, you'll need to place this expression in a textbox on the report, not in a calculated field in the dataset.

Second, you'll need to bring the aggregate functions outside the Lookup function. The lookup will bring back the column you need, then you aggregate it.

EDIT: Nesting aggregates doesn't generally make sense in SSRS. Also, if you're going down the path of specifying a scope in the Sum function, you can only specify one group. So you'll quickly run into issues if you are grouping on multiple items in your table.

Based on your clarification, I would suggest that you rewrite the query in such a way that you bring in all the values you need in one dataset. There is limited functionality in SSRS to merge or aggregate across datasets.