SSRS Variable Expression - Sum, Sum, Scope?

Question

I'm trying to write an expression for a variable (not parameter), so that I can use/reference it to do a calculation in another textbox. I have multiple datasets, and I need the SUM(SUM(Fields!amount.Value)) for each of these datasets. I am then going to use these numbers in another textbox, adding them with each other. I need some assistance with the syntax. I am able to use SUM by itself, without an issue. For example, this works fine:

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

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

=SUM(SUM(Fields!amt.Value, "Acctrange_90300_90399_InterestExpenses"))

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. Any suggestions?

score 1 · Answer 1 · answered Sep 06 '12 at 02:33

1

Outer SUM needs a scope too. Try this:

=SUM(SUM(Fields!amt.Value,"Acctrange_90300_90399_InterestExpenses"),"Acctrange_90300_90399_InterestExpenses")

I expect this will also fail--in some new & different way--but that error will get you closer to a solution.

answered Sep 06 '12 at 02:33

Peter Radocchia

10,710
2
34
56

Thanks for the recommendation, unfortunately SSRS doesn't cooperate. The error returned says "The Variable expression for the report 'body' has an inner aggregate in an outer aggregate that specifies a dataset scope. An aggregate that specifies a dataset scope cannot contain other aggregates." Funny because all these variable is doing is trying to replicate the data that is displays in another textbox, is it really too much to ask? – dp3 Sep 06 '12 at 13:41

s1lntz · Accepted Answer · 2012-09-07T16:35:59.033

could you please provide a detailed example with maybe sample data and an expected solution?

Because I do not understand why you want to SUM(SUM()). The inner SUM() would result in a single integer value, why would you want to do another SUM on just a single value. even if it worked, it would just be the same value. I apologize if I understood the question wrongly, i can't comment, so i am answering, but I want to know clearly what you are looking for. I can understand if you are trying to do SUM( SUM(), SUM(), SUM()...). As in, sum of sums.

Sorry again for answering just to inquire more info, but I can't see a comment option.

UPDATE:

ok now i think you have something like

      | a  | b  | c  | d  |
---------------------------
1     | *  | *  | *  | *  |
---------------------------
2     | *  | *  | *  | *  |
---------------------------
3     | *  | *  | *  | *  |
---------------------------
total | w  | x  | y  | z  |

so SUM(Fields!a.Value) would give you w, and SUM(Fields!b.Value) would give you x, and so on. and you want w+x+y+z? If that is so, then you can do this:

add a calculated field to your dataset to calculate the row totals like

{ (a1+b1+c1+d1),(a2+b2+....), .... },

and then from your variable, call

SUM(Fields!CalculatedField.Value).

for above example, you can give the calculated field an expression as:

= CInt(Fields!a.Value)+CInt(Fields!b.Value)+CInt(Fields!c.Value)+CInt(Fields!d.Value)

this would make each entry in the calculated field as sum of each entry in all fields. So sum of calculated field would give you your answer.

Hope thats what you wanted. Otherwise, well I tried understanding the problem. :)

thank you s1lntz. the datasets pull "amounts" from a table. The amounts (data of the tablix) belong to different accounts (rows of the tablix), as well as regional locations (columns of the tablix). The sum of the amounts lumps small locations together, and the second sum provides a total vertically, at the bottom of the tablix. The expression I'm using for the cells is SUM(Fields!Amount.Value) but the expression for the bottom horizontal totals line is SUM(SUM(Fields!Amount.Value)). For some reason I cannot create a variable reproducing this effect — dp3, Sep 06 '12 at 16:50

score 0 · Answer 3 · answered Feb 27 '17 at 19:01

When SSRS wants to give me headaches like this, I cheat. I put the inner calculation in a hidden text box or name the box that displays it if I want it to show, then refer to it by name. So if I had the sum of the first column in a text box called txtFirstColumnSum, and the second column sum in the text box called txtSecondColumnSum, I'd use:

=cdec(ReportItems!txtFirstColumnSum.Value)+ cdec(ReportItems!txtSecondColumnSum.Value)...

Another way to do it is using the built-in scopes, but writing custom code to handle the math between the fields. For example if I want the percentage of the current YTD sales over the same period the prior year, and also want to check for divide by zero, I can do it in an expression, but I use it on several levels, so instead I made custom code:

Public Function DeltaPercent(ByVal currentAmount as decimal,ByVal pastAmount as Decimal) as Decimal
    Dim difference as decimal
    Dim result as decimal
    if pastAmount<>0 then
        difference=currentAmount-pastAmount
        result=difference/pastamount
    else
        result=0
    end if
return result

End Function

Then to get the % change, I call it:

=Code.DeltaPercent(Sum(Fields!SalesYTD.Value, "SalesTerritory"),Sum(Fields!SalesPY1TD.Value, "SalesTerritory"))

One tip I wish I'd known sooner: If you use the syntax

ReportItems("txtFirstColumnSum").Value

you have to make sure you type it right. If you use the bang syntax (!) above, you get intellisense.

SSRS Variable Expression - Sum, Sum, Scope?

3 Answers3