4

I am new-ish to SSRS (2012) coming from a loooong background using BusinessObjects, so it is sometimes hard for me to know how to search for the correct terminology.

I have a report like this, where the sections are in on tablix and the Grand Total in another. This layout is required (or this would be a bit simpler!)

Section 1
                Date 1  Date 2  Date 3  % of Grand Total
Product1        value   value   Value   Value3/GT3 (12%)
Product2        value   value   Value   Value3/GT3 (14%)
Section Total   Total1  Total   Total3  Value3/GT3 (7%)

Section 2
                Date 1  Date 2  Date 3
Product1        value   value   Value   Value3/GT3 (30%)
Product2        value   value   Value   Value3/GT3 (22%)
Section Total   Total1  Total 2 Total3  Value3/GT3 (22%)

Grand Total     GT1     GT2     GT3     GT3/GT3 (100%)

I am having difficulty calculating % of Grand Total, as the numerator and denominator are not in the same scope. I thought maybe a Report Variable, but I can't figure out how to create the equivalent of this kind of formula from BusinessObjects - 

=Sum(Value) where (Date = "Date 3")

I want to assign the context or scope for my report variable but I don't know how to do that, or if it will even work.

I know I can create the scope of the whole report by including the Dataset name in quotes, but how to limit to a subset of that?

Thanks, nod.

user2812742
  • 43
  • 1
  • 3

2 Answers2

2

The aggregate functions in SSRS will take a scope as the second parameter. That scope parameter is a string that is the "name" of whatever your scope is. If you want the scope to be the table group you would use the name of your table group. If you want the scope to be your entire dataset then you would use the name of your dataset. You can always see the name of an element in the properties window (press F4).

Depending on how you've done your filtering you could use different scopes. Using the dataset name would be the most straightforward, but that assumes that the dataset has already been filtered to the correct records in the SQL. Otherwise you would probably want to use the tablix as your scope. (Something like "Tablix1" instead of "DataSetName" in the example below.)

Once you know which scope you're using you can use a conditional in the aggregate to only look at the correct records. E.g. to rewrite your Business Objects expression in SSRS you would do:

=SUM(IIF(Date = "Date 3",Value,0), "DataSetName")

This expression works because if the Date field does not equal "Date 3" it will return zero and not add anything to your SUM. Only rows where Date = "Date 3" will have their Value included in the SUM() function.

Some good resources on SSRS scope and aggregate functions:

Mike D.
  • 4,034
  • 2
  • 26
  • 41
1

The mike-d is a great explanation; I'd like to add just a special hint for the record: when you code that onto a report variable, watch out all the field types; my SSRS 2012 throws a generic "cannot generate the report" (*) when I use:

=Sum(IIf(Fields!ROWTYPE.Value = 9, Fields!TAXES.Value, 0), "DataSet1")

It's a stupid detail for which I lost eons: the TAXES field is decimal, the 0 is... integer! Simply substitute the latter with CDec(0)! Ciao

(*) The real error is only in the ssrs system logs. Something like (freely translated sorry): "The expression for the variable myVAR uses an aggregation function on different data type"

pgfiore
  • 41
  • 2