SSRS 2008 Sum IIf and Variables

0
votes

What I'm trying to do is summarize several fields from a query at the variable level due to multiple calculations in my report (some additions, some subtractions).

For example, I'm trying to sum a financial value which has 3 conditions I need to check. I tried creating a variable and assigning it an expression of:

=Sum(IIf((Fields!Year.Value, "Query_Name") = Parameters!Year AND (Fields!Period.Value, "Query_Name") = Parameters!Period AND (Fields!Type.Value, "Query_Name") = Parameters!Type, (Fields!Amount.Value, "Query_Name"), 0))

I'm checking to see if the year, period and type (budget or actual) are correct and then I want to sum the values. It isn't working so I'm wondering what I'm doing wrong.

I do have multiple datasets in the report, however I'm referencing the correct one each time. Is this not possible or am I doing this incorrectly?

1
ssrs-2008
Maybe it's just a typo, but where you're mentioning Parameters!Year above - this should be Parameters!Year.Value. It looks like you're referencing the Field values correctly, just not the Parameter ones. – Ian Preston
Here's the actual statement: =Sum(IIF((Fields!FSCSP.Value, "GLIncomeStatement") = Parameters!intFiscalPeriod AND (Fields!FSCSDSG.Value, "GLIncomeStatement") = "A" AND (Fields!ISGRPCODE1.Value, "GLIncomeStatement") = 1 AND (Fields!ISGRPCODE2.Value, "GLIncomeStatement") = 1, (Fields!NETPERD.Value, "GLIncomeStatement"), 0)) The error is: The Variable(ContractRevenue) expression for the report 'body' uses an aggreggate expression without scope. A scope is required for all aggregates used outside of a data regioon unless the report contains exactly one dataset. – Ian

1 Answers

0
votes

Hey this may be because you are using 2 datasets in your report And you need to mention dataset before each value Dataset1(Fields!FSCSP.Value) instead of just Fields!FSCSP.Value it would do the trick. Tell me exactly when this error is occurring