asked on

SSRS Column Summing IIssue

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.

Megan Brooks

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

thayduck

ASKER

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.

Megan Brooks

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.

thayduck

ASKER

These totals need to be in the table footer line.

I tried putting your code there but get a error.

ASKER CERTIFIED SOLUTION

Megan Brooks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION