Sum Function in Expression with two Grouping fields in SSRS 2008
I have data values like below and I am going to find the sum between them based on two grouping. Date------------City -------- Amount 1/7/2012 Omaha 4000 1/7/2012 Denver 5000 2/7/2012 Denver 1000 I need to have Sum(Amount) with two groups on Date as parent and City as child. it seems that Sum function handle just one group like below. Sum(Amount,"Date"). How can I add "City" to the sum as well? Thanks
July 26th, 2012 12:28am

For each group add a sum expression: =Sum(Amount,"Date") =Sum(Amount,"City")
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2012 2:30am

For each group add a sum expression: =Sum(Amount,"Date") =Sum(Amount,"City")
July 26th, 2012 2:30am

Well i need to have one Sum with two groups like: Sum(Amount,"Date","City"). "Date" is parent of "City". Like in SQL: SELECT SUM(Amount) From Table Group by Date,City
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2012 12:52pm

Well i need to have one Sum with two groups like: Sum(Amount,"Date","City"). "Date" is parent of "City". Like in SQL: SELECT SUM(Amount) From Table Group by Date,City
July 26th, 2012 12:52pm

HI Ali ! You need to create 2 row groups first on "Date" Field and Second on "City" Field. Now Add a column "Amount" and set expression like this; =SUM(Fields!Amount.Value) Please let me know if this helps. Hopefully i have answered you correctly. Thanks, Hasham
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2012 12:57pm

HI Ali ! You need to create 2 row groups first on "Date" Field and Second on "City" Field. Now Add a column "Amount" and set expression like this; =SUM(Fields!Amount.Value) Please let me know if this helps. Hopefully i have answered you correctly. Thanks, Hasham
July 26th, 2012 12:57pm

Thanks Hasham, Actually I don't have any table. I want to use the Sum in the expression in Chart. I have a category("Date" Field) and Series("City" Field). I want to scale the interval of the axis base on the max of the data grouping by Date and City. Which is gonna be: Max(Sum(Amount,"Date" and "City")) In another word: Can more that 1 group be used in th SUM() function?
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2012 11:55pm

You can calculate the sum in SQL server and use it in your chart. SELECT Date,City, SUM(Amount) as Amount From Table Group by Date,City
July 27th, 2012 2:04am

You can calculate the sum in SQL server and use it in your chart. SELECT Date,City, SUM(Amount) as Amount From Table Group by Date,City
Free Windows Admin Tool Kit Click here and download it now
July 27th, 2012 2:05am

I use a store procedure in my report. This chart is part of the report. Can I use SQL quary in expression in SSRS?
July 30th, 2012 2:59pm

I use a store procedure in my report. This chart is part of the report. Can I use SQL quary in expression in SSRS?
Free Windows Admin Tool Kit Click here and download it now
July 30th, 2012 2:59pm

You cannot use SQL in SSRS, because SSRS has its own expression language which is similar to VB.Net. Are you allowed to modify your stored procedure?
July 31st, 2012 9:16am

No I couldn't do that. Can I make a new dataset in SSRS using the store procedure and make a query like below? SELECT Date,City, SUM(Amount) as Amount From Table Group by Date,City
Free Windows Admin Tool Kit Click here and download it now
July 31st, 2012 9:18am

You cannot use SQL in SSRS, because SSRS has its own expression language which is similar to VB.Net. Are you allowed to modify your stored procedure?
July 31st, 2012 9:18am

No I couldn't do that. Can I make a new dataset in SSRS using the store procedure and make a query like below? SELECT Date,City, SUM(Amount) as Amount From Table Group by Date,City
Free Windows Admin Tool Kit Click here and download it now
July 31st, 2012 9:19am

Was there ever a method to do this please? I have a similar problem; I want to show the sum of some values but only for the maximum date. I too have a stored procedure that sends me all the data that can't be changed so just want to have two aggregates.Sue Neilson Sovereign Business Integration plc
October 25th, 2012 4:19am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics