Solved

# how to sum a formula in SQL Reporting Services 2008

Posted on 2011-05-02
Medium Priority
620 Views
Hello:

Attached is an rdl.  I'm trying to produce a Grand Total for the "Sales Amt" column of this report.  (That column is the final column on the right.)

In that Sales Amt column, I already have a group sum.  And, the "Sales Amt" field itself in that column is a formula .

I need a grand total.  This will amount to a third field in this column.

How do I create this?

Apitech

Summary-Sales-By-Product2.txt
0
Question by:apitech

Expert Comment

ID: 35505991
Are you trying to Sum Sums?
0

LVL 1

Author Comment

ID: 35506027
Exactly.  I know that there is not a way to do that.  But, that's how I was trying to explain it here in this posting.

The following is the group formula in SQL Reporting Services 2008 that sums the extended price column and specifically for each item class:

=Sum(iif(Fields!SOPTYPE.Value=4,
-Fields!XTNDPRCE.Value,
Fields!XTNDPRCE.Value))

Now, I want to do a āgrand totalā for this formula (on this column).  But, I cannot find the means to do this in SSRS.  I have tried doing a sum of that sum and I have tried the RunningValue function based on the textbox that this formula is in.  No dice.

Thoughts would be very, very much appreciated!

Apitech
0

Expert Comment

ID: 35506308
Api,

I know in BO I have to make separate variables for each Sum I had to cast the Sum ToNumber then Sum the Cast Results. I believe this is how I did it

First Step: As Variable
ToNumber ({@Sum FlxFstDisp})

Second Step: As Variable
Sum ({@FlexFstDispAmt})

0

LVL 1

Author Comment

ID: 35506378
Do I place these into the Expression "area" of the field?  Are these formulas?
0

LVL 21

Expert Comment

ID: 35511599
Put Sum in variable and use at last.
0

LVL 1

Author Comment

ID: 35511902
Again, I ask "Do I place these into the Expression "area" of the field?  Are these formulas?"
0

Expert Comment

ID: 35512870
Yes these would be formula's in expression format using a variable in the variable editor.
0

LVL 1

Author Comment

ID: 35515174
I'm still not seeing how this works.  Where is the "variable editor"?  Where do I place these formulas at?  How many variables do I creat?  How do I glean a "Grand Total" field (my ultimate goal) from definining variables?

I'm a total novice on how variables work in SQL Reporting Services 2008 (and 2005).  Could someone please methodically point out how I can get this accomplished?

I do appreciate the responses that I have received, thus far.  But, I do need step by step directions on this issue.

Thanks!  Again, it's very appreciated!

Apitech
0

LVL 22

Accepted Solution

Nico Bontenbal earned 2000 total points
ID: 35688420
At the bottom of your screen in the Row Groups section right click the ItemClass row and select Add Total|After. This will give you a new total row. Right click the cell in the Sales Amt column on the Total row and choose Expression. Here you enter:
=sum(iif(Fields!SOPTYPE.Value=4,  -Fields!XTNDPRCE.Value,  Fields!XTNDPRCE.Value))
This should give you the total.
0

LVL 1

Author Comment

ID: 35689478
Hi Nicobo!

That's exactly what I needed!  Thank you!!!!!!!!!!!!

That worked!!!

I was looking for something really simple like that!

Whew!  I was hoping that you would chime in on this!  Thank you, again, for doing so!

You're the best!

Apitech
0

LVL 1

Author Closing Comment

ID: 35689481
Nicobo is awesome!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The dataā¦
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installatiā¦
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. ā¦
Screencast - Getting to Know the Pipeline
###### Suggested Courses
Course of the Month14 days, 13 hours left to enroll