Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Trouble with SUM() Expand / Collapse
Author
Message
Posted Tuesday, March 4, 2014 8:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 7:04 AM
Points: 43, Visits: 233
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!
Post #1547375
Posted Tuesday, March 4, 2014 12:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:36 PM
Points: 11,140, Visits: 12,881
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1547483
Posted Wednesday, March 5, 2014 1:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 7:04 AM
Points: 43, Visits: 233
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'!
Post #1547667
Posted Wednesday, March 5, 2014 8:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:36 PM
Points: 11,140, Visits: 12,881
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2


  Post Attachments 
Test Report Project.zip (1 view, 10.95 KB)
Post #1547811
Posted Wednesday, March 5, 2014 9:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 7:04 AM
Points: 43, Visits: 233
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 Bloggs 11005.56

But the total will be 38021.58
Post #1547886
Posted Thursday, March 6, 2014 1:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 7:04 AM
Points: 43, Visits: 233
I hope this works!

  Post Attachments 
Test Group.zip (3 views, 11.61 KB)
Post #1548105
Posted Thursday, March 6, 2014 12:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:36 PM
Points: 11,140, Visits: 12,881
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1548423
Posted Thursday, March 6, 2014 12:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:36 PM
Points: 11,140, Visits: 12,881
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2


  Post Attachments 
Test Group.zip (1 view, 12.62 KB)
Post #1548440
Posted Friday, March 7, 2014 2:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 7:04 AM
Points: 43, Visits: 233
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?
Post #1548614
Posted Friday, March 7, 2014 5:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 7:04 AM
Points: 43, Visits: 233
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)'
Post #1548681
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse