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.  

thayduckProgrammer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Megan BrooksSQL Server ConsultantCommented:
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

thayduckProgrammer AnalystAuthor Commented:
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 BrooksSQL Server ConsultantCommented:
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.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

thayduckProgrammer AnalystAuthor Commented:
These totals need to be in the table footer line.

I tried putting your code there but get a error.
Megan BrooksSQL Server ConsultantCommented:
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
thayduckProgrammer AnalystAuthor Commented:
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.
thayduckProgrammer AnalystAuthor Commented:
Both of these solutions will work just not in my report.
Thanks for your suggestions though.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.