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