SSRS - SUM of SUMs
Hello Experts,
I have a SSRS report column named sales value. And it aggregates total sales values for each store. I want to get sales value percentages of each store in a separate column named Mix%.
I have tried to use:
=Fields!Sales_Value.Value / SUM (Fields!Sales_Value.Value)
But it always shows 1 as result.
How can I solve this problem? Any idea?
Regards
October 17th, 2010 3:46am
Perhaps you get 1 because it rounds INTEGER to 1, try converting it to DECIMAL and see how was it goingBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Free Windows Admin Tool Kit Click here and download it now
October 17th, 2010 11:03am
Thank you for your reply. But I think it is not about the data type of the column.
My sales value column expression is like this.
=Sum(Fields!Sale_Unit.Value)
Suppose that I have three stores a,b,c.
And my report layout is like:
A 10
B 4
C 6
so, my mix column should be:
A 50%
B 20%
C 30%
October 17th, 2010 8:22pm
maybe:
=Sum(Fields!Sale_Unit.Value) / Sum(Fields!Sale_Unit.Value,"DataSetName")
as otherwise you will always get contextual aggregation - you are getting the 1 as the answer is 100% each time with the way you have it currently
Rgds Geoff
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2010 5:32am
thank you very much!
October 18th, 2010 10:42am