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

group totals don't equal sum of group items Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 1:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 2:58 PM
Points: 3, Visits: 23
OK - I've tried everything I can think of to fix this but nothing is working. Here's the issue; I have created a report that is sums building square footage grouped by market. the client wants the excel export in whole numbers so I put in a round function, with and w/o midpointrounding.away fromzero and then tried (format N0) each returns the whole number. So far so good. However when I total the group the value is sometimes spot on and sometimes off because of the rounding. I tried rounding the group total, etc., but no combination of rounding/formattting seems to work. I thought of going into the underlying sql and create a new dataset for the totals, but not sure that wouldn't have the same issue, besides it is a pain given all the colums subtotals and totals I am dealing with. Anyone else encountered this? How did you solve it?

Thanks, John
Post #1432879
Posted Tuesday, March 19, 2013 1:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:03 PM
Points: 13,289, Visits: 12,136
jgluszak (3/19/2013)
OK - I've tried everything I can think of to fix this but nothing is working. Here's the issue; I have created a report that is sums building square footage grouped by market. the client wants the excel export in whole numbers so I put in a round function, with and w/o midpointrounding.away fromzero and then tried (format N0) each returns the whole number. So far so good. However when I total the group the value is sometimes spot on and sometimes off because of the rounding. I tried rounding the group total, etc., but no combination of rounding/formattting seems to work. I thought of going into the underlying sql and create a new dataset for the totals, but not sure that wouldn't have the same issue, besides it is a pain given all the colums subtotals and totals I am dealing with. Anyone else encountered this? How did you solve it?

Thanks, John


The problem is not in the sql but with your client. They want numeric calculations presented as integers and then don't understand why the totals are off.

Maybe you need to use SUM(CEILING(SquareFootage)). In other words force your calculations to be incorrect so the totals will match. It probably comes down to accurate the report needs to be. If close is ok then something like that should work.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1432885
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse