December 9, 2007 at 7:00 pm
Hello, I've had a look around but can't seem to find a definitive way to do this. I have solved it by creating a separate dataset and a table to hold the result of an SQL statement that meets my totalling requirements but surely there must be an easier way to do this...
Assume I have factories that make, well, widgets. I want to know the total area of all my widget factories that are making my widgets. I have a group for each widget factory that shows the totals all all widgets made in the factory.
Obviously when I simply SUM over the dataset I get the widget factory area repeated however many times there are detail rows.
Here is some sample data. In the example below I would expect to get 6,000 sqm and of course I get 17,000 sqm if I just use a SUM.
Widget factory 1
Widget type 1 50
Widget type 2 75
Widget factory 1 total widgets125
Widget factory 1 area 1000 sqm
Widget factory 2
Widget type 3 45
Widget type 4 55
Widget type 5 35
Widget factory 2 total widgets135
Widget factory 2 area 5000 sqm
Total Widget factory area 6000 (17000)
Question: what is the standard way to accomplish this and if so can someone supply a URL that shows an example ?
thanks
December 10, 2007 at 2:25 am
I think I understand your issue but without being able to see the code behind your Dataset and Also how you have laid out your data in your RDL this make s things difficult.
But from what I have read, I understand that you are using a table to hold the data, would using a pivot table (Matrix) help you more if you are just using this to create a high level summary of your underlying data?
December 10, 2007 at 5:27 am
It looks like you are summing the sqm total over each widget type, not over the entire dataset.
(See underlined)
Widget factory 1
Widget type 1 50 - 1000 sqm
Widget type 2 75 - 1000 sqm
Widget factory 1 total widgets 125
Widget factory 1 area 1000 sqm
Widget factory 2
Widget type 3 45 - 5000 sqm
Widget type 4 55 - 5000 sqm
Widget type 5 35 - 5000 sqm
Widget factory 2 total widgets 135
Widget factory 2 area 5000 sqm
This gives your 17000 sqm grand total. You may have to change the SP if you are calculating a running sum in each detail record.
Try placinig a scope parameter in your Grand Total Summation. You can take the text from your subtotal and paste it into the grand total, then add the dataset scope.
=Sum(Fields!WidgetSQM.Value,"DatasetName")
December 10, 2007 at 4:38 pm
"I think I understand your issue but without being able to see the code behind your Dataset and Also how you have laid out your data in your RDL this make s things difficult."
I see the confusion, the sample data is actually both the report layout and the dataset.
Widget factory 1- factory group header
Widget type 1 50
Widget type 2 75
Widget factory 1 total widgets 125- factory group footer
Widget factory 1 area 1000 sqm- factory group footer
Widget factory 2
Widget type 3 45
Widget type 4 55
Widget type 5 35
Widget factory 2 total widgets 135
Widget factory 2 area 5000 sqm
Total Widget factory area 6000 (17000)- total factory area showing both the desired figure and the figure I currently get (in brackets)
So, widget factory 1 produces 2 types of widget giving two rows (detail or sub group it doesn't really matter) for widget factory 1 so for factory 1 we multiply the factory area (1000sqm) by two to get 2000sqm.
Widget factory 2 produces three types of widget giving three rows so we multiply the factory area (5000sqm) by three to get 15000sqm. Then we add 15000 to 2000sqm to get a grand total of 17,000sqm for widget factory 2.
What is required is for me to know the total area of all my widget factories that are making my widgets. I have two factories (widget factory 1 and widget factory 2), one with an area of 1000sqm and one with an area of 5000sqm therefore the total I am after is 6000sqm so what I really need is to sum the factory area once for each occurrence of the factory group rather than once for each detail/sub group row, i.e. I need to sum my grouped data.
After some further hunting around I see that this has been asked before...
http://www.sqlservercentral.com/Forums/Topic256848-150-1.aspx
http://www.sqlservercentral.com/Forums/Topic287119-150-1.aspx
thanks for putting forward some ideas - I think I'll just put this to bed
🙂
December 10, 2007 at 4:39 pm
"Try placinig a scope parameter in your Grand Total Summation. You can take the text from your subtotal and paste it into the grand total, then add the dataset scope"
Yes, nice try, unfortunately a scope parameter won't work when used in this manner.
Final word from the MSDN forums 🙂
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266724&SiteID=1
"Aggregate of aggregates" is a feature we don't currently support. It's a high priority item on our wishlist for a future release.
I think I'll stick with what I've got, it works.
thanks for playing guys, have a good one
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply