?
Solved

how to sum a formula in SQL Reporting Services 2008

Posted on 2011-05-02
11
Medium Priority
?
620 Views
Last Modified: 2012-05-11
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
Comment
Question by:apitech
11 Comments
 

Expert Comment

by:Kretonia
ID: 35505991
Are you trying to Sum Sums?
0
 
LVL 1

Author Comment

by:apitech
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

by:Kretonia
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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
LVL 1

Author Comment

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

Expert Comment

by:Alpesh Patel
ID: 35511599
Put Sum in variable and use at last.
0
 
LVL 1

Author Comment

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

Expert Comment

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

Author Comment

by:apitech
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

by:
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

by:apitech
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

by:apitech
ID: 35689481
Nicobo is awesome!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question