March 4, 2014 at 8:34 am
I have a Stored Procedure which returns three fields: Name, Sector and TotalTurnover. Any given client (Name) can have multiple rows, the only difference being the sector. The TotalTurnover is what is says, total across all sectors.
I have a filter on the tablix in SSRS which allows the user to select one or more sectors, and the data is grouped to display only the name and TotalTurnover. So far so good!
I now need to add a SUM(TotalTurnover) figure: this being the sum of the turnover(s) displayed. However, a simple SUM() returns a value much higher than actual as it sums all the TotalTurnover values for each client when I only want it to count each value once.
Any suggestions gratefully received!
March 4, 2014 at 12:06 pm
Sounds like you may have an issue with the scope. So you probably need something like SUM(TotalTurnover.Value, [Group Name]). Check out BOL, http://technet.microsoft.com/en-us/library/dd283120(v=sql.105).aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2014 at 1:44 am
Thanks Jack but I've already explored that one. I'm hoping that someone might have an idea along the lines of 'sum of displayed figures'!
March 5, 2014 at 8:00 am
David,
I can't duplicate your issue, can you post the query and rdl? I'm attaching an example I put together that I think matches your scenario, but I'm not seeing the issue with what I have setup.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2014 at 9:29 am
I've written this quickly as an example, I'll try to put a proper example together shortly.
select
'Fred Bloggs' as Name,
'Sky Hooks' as Sector,
8005.23 as TotalTurnover
UNION
Select
'Fred Bloggs',
'Pith Helmets',
8005.23
UNION
Select
'Fred Bloggs',
'Unicycles',
8005.23
UNION
Select
'Brenda Bloggs',
'Sky Hooks',
11005.56
UNION
Select
'Brenda Bloggs',
'Unicycles',
11005.56
UNION
Select
'Brenda Bloggs',
'Bable Fish',
11005.56
The parameter selected by the report user is Sector
Display Name and ToalTurnover, grouped by Name, filter by Sector.
So, Select Sky Hooks and Unicycles, the report looks like this:
Fred Bloggs 8005.23
Brenda Bloggs11005.56
But the total will be 38021.58
March 6, 2014 at 1:41 am
I hope this works!
March 6, 2014 at 12:06 pm
Working through the example you posted now. The issue is around using the Multivalue parameter. I'm pretty sure you can't just use the "IN" in the filter. I'm working on a solution now.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 6, 2014 at 12:56 pm
Jack Corbett (3/6/2014)
Working through the example you posted now. The issue is around using the Multivalue parameter. I'm pretty sure you can't just use the "IN" in the filter. I'm working on a solution now.
Okay. To use your example I had to change the data source and the query because I don't have access to the server or the database. I changed the data source to point to the local server and tempdb and then used the query you provided earlier as the dataset. Using that setup I can't duplicate the issue you are seeing. If you attach the create table and insert statement for the table you reference in the example report I'll create that table with that data in a local database and see if I can duplicate the issue. I'm attaching the project with the changes I made and it is working as desired.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 7, 2014 at 2:08 am
Hi Jack,
Many thanks for that but unfortunately it's not what the user wants!
The total turnover figure represents the total turnover across all sectors, not the specific sector. So if someone has bought sky hooks, the user don't want to know how much they spent on sky hooks but how much they spent overall. So what they are looking for is who bought items from sectors x and y, how much their total spend was, and how much the grand total of the total spend was.
So regardless of how many of the chosen sectors the customer has purchased products in the customer only appears once (hence the grouping) and the totalturnover figure only appears once. And the grand total should be the sum of the figures displayed.
Yes, I know it doesn't make a lot of sense but that is what the user wants!
The code to mimic the output from the SP appears earlier in the thread?
March 7, 2014 at 5:15 am
I think I've found a way to achieve what I want.
I've added a page header with a text box / placeholder, and use the formula '=Sum(ReportItems("TotalTurnover").Value)'
March 7, 2014 at 7:14 am
David,
Sorry I misunderstood your problem, I thought you were getting the total of all rows and wanted just the total for the filtered rows. Yes, what you are doing should work, but I would recommend pushing that down to the query by adding a column doing a sum(totalturnover) over() which returns the sum of all the rows in the results. Then in the total row on the report you'd use first(totalturnover) instead of sum. I'd do that because I find it easier to understand and maintain than hiding textbox and then referencing it in anther object in the report. Both work so it really comes down to personal preference.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 7, 2014 at 7:24 am
No problem, it doesn't help when it's not a 'logical' request!
It's a work around up to a point, but by definition it's per page. I've made the pages as large as possible but if the report continues onto a second then the values are not the true totals. As it stands it's unlikely to be a multi-page report but should that arise I'll revisit the problem.
Once again thank you for your input.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply