SSRS Expression - Aggregating variables

2
votes

I'm building a report where I'm trying to write an expression for a variable (not parameter). I'm planning on creating multiple variables, each referencing the SUM(SUM()) of amounts from different datasets. I'm then planning on referencing each of these variables in a single textbox to do a calculation. This is to circumvent SSRS' limitation of using multiple datasets in a single tablix/matrix.

I need some assistance with the syntax. When I use SUM by itself, the report runs fine and I can perform calculations on these values without issues. For example, this works fine:

Variable1:

=SUM(Fields!Amount.Value, "DataSet1")

Variable2:

=SUM(Fields!Amount.Value, "DataSet2")

Textbox1:

=Variable1.Value - Variable2.Value

But I get an error when trying to amend it to the following (which is what I actually need):

Variable1:

=SUM(SUM(Fields!Amount.Value, "DataSet1"))

I get an error saying "The variable expression for the report 'body' uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one dataset." I have a hunch that there's a problem with my syntax/parantheses placement. I tried adding ",DataSet1" again, at the end (SUM(SUM(Fields!Amount.Value, "DataSet1"),"DataSet1") but receive an error about recursive nesting problems. Any suggestions?

1
reporting-servicessumssrs-2008ssrs-expression
What does SUM(SUM()) mean? I don't understand the need for the additional SUM. Unless you're trying to do SUM() * COUNT()?? – lc.
Basically, the datasets pull "amounts" from a table. The amounts (data of the tablix) belong to different accounts (rows of the tablix), as well as different locations (columns of the tablix). The sum of the amounts lumps some locations together, and the second sum provides a total vertically, at the bottom of the tablix. The expression I'm using for this bottom horizontal totals line is SUM(SUM(Fields!Amount.Value)). I need to replicate this value for another textbox. – dp3
Sorry, I wasn't thinking in nested row/col groups... – lc.

1 Answers

4
votes

I'm guessing where you assign the variable, it doesn't have the concept of these column groups, so the only option is to sum the entire dataset.

I think you can try one of the following:

  1. Assign the value from within the tablix (if it lets you, you might need custom code which aggregates and then sets the variable)
  2. Specify the grouping condition again in a conditional sum, like SUM(IIF(Fields!Location.Value = TheLocationValueForTheColumnYouNeed, Fields!Amount.Value, 0))