[Webinar] Streamline your web hosting managementRegister Today


SSRS Column Summing IIssue

Posted on 2010-04-02
Medium Priority
Last Modified: 2012-06-27
Date                              Amt
03/20/2010                    100.00
03/22/2010                    125.00
03/22/2010                    200.00
03/23/2010                      50.00
03/23/2010                    250.00

In the above example of a ssrs report grouped on date and amt, is there a way on the footer line that i can sum up the Amt column but only sum  the amts with todays date.
Lets say 03/23/2010 is todays date. I only want the sum total on the footer line to be 300.00.

Problem is that user can create report with only todays  records or todays records  and all history records with various dates. If user selects only to show records with todays date then the sum of that column is correct. But if they choose todays and history records then I stll want the final amt (for amt column) to only show a total of todays  records only. I dont want history record amts included in final total.  

Question by:thayduck
  • 4
  • 3
LVL 16

Expert Comment

by:Megan Brooks
ID: 29509050
You can calculate anything you want in the SQL (or MDX) query of the dataset, and include it in the group or page footer. For example, you can add a column to the dataset output that is a subquery that sums only the current day's amounts. This value would be the same in every record, and you would ignore all but the first or last value. I have included a simple example below.
You would either need to set a query parameter, @currentdate, with the current date or you would need to replace @currentdate in the query with a suitable SQL expression (I like "DATEADD(day, DATEDIFF(day, 0, getdate()), 0)" -- it returns the current date with no time).
If you add a group footer row to the table then you can access the sum column as "=First(Fields!dailysum.Value)". You need to use a First() or Last() aggregate since the dailysum field is repeated with the same value for every row of data, and all you want is one of them.
I verified this in SSRS 2008; I don't have SSRS 2005.

select transdate, transamt, 
(select sum(transamt) from transtable where transdate = @currentdate) as dailysum
from transtable
order by transdate

Open in new window


Author Comment

ID: 29552882
The problem is that the report has 22 various amt columns that I would need to do this for.
I wanted to do this all in ssrs and not add more columns to the report query.
There is no way of doing this within the report itself with the data at hand?
Maybe create a function you can access within the report that would do what you are suggesting but within the report with data at hand.
LVL 16

Expert Comment

by:Megan Brooks
ID: 29589990
I did eventually convince SSRS 2008 to do this, and I think SSRS 2005 can do it too. Just use the expression
=Sum(IIf(Fields!transdate.Value = <currentdate>, Fields!transamt.Value, 0))
Use whatever you prefer for <currentdate>, as long as it is something that evaluates to the right date.
I placed the expression in a textbox in a row just below the details group row, and outside of the details group. It does need to be inside the table, within the scope of the dataset, unless you specify the dataset name in the function. The latter form would be
=Sum(IIf(Fields!transdate.Value = <currentdate>, Fields!transamt.Value, 0), "datasetname")
I was able to put this latter expression in the page footer using SSRS 2008, even though the expression editor reported an error.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.


Author Comment

ID: 29678913
These totals need to be in the table footer line.

I tried putting your code there but get a error.
LVL 16

Accepted Solution

Megan Brooks earned 500 total points
ID: 29688653
I don't have SSRS 2005 to test with. I know that it allows the Sum aggregate function in a group footer, and I don't see why it wouldn't allow the IIf in the expression.

If it doesn't work in the table footer then create a group that groups on a constant (e.g. group on "=1"). When you do that, there is only one group of results. The only reason for creating the group would be to provide you with a group footer in which to do the summing.

What was the error?
LVL 27

Assisted Solution

planocz earned 500 total points
ID: 29863644
One way to get around this is to have your sum fields up in your data area and hide them.
Then Create your textboxes in your footer and use this as the value.....
=ReportItems!Field1.Value . The Field1 is the name of your textboxes  or fields that you have hidden in the above code.

You can also use a table footer and use the info that you have already made from your description above.

Author Comment

ID: 30223892
Neither of these possible solutions will work for me in this report. I have decided to go in another direction, but thanks for your suggestions.

Author Closing Comment

ID: 31710391
Both of these solutions will work just not in my report.
Thanks for your suggestions though.

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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

607 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