Report Columns not summing correctly.

Question

Report Columns not summing correctly.

jman76

Hall of Fame

Points: 3388
More actions
August 7, 2018 at 1:13 am

#407645

Hello,
I am using report builder 3 to build a report. The report is based on three tables and two datasets.
The top two tables are designed as follows:
                        YearandMonth (Group Column)
No of Clients [Sum(ClientNo)]
Placements    [Sum(Placements)]
Stays              [Sum(Stays)]
                         YearandMonth (Group Column)
No of Clients    [Sum(ClientNo)]
Placements    [Sum(Placements)]
Stays             [Sum(Stays)]
The third Table is my totals table which sums the figures from the top two table columns/rows
My totals are based on an expression
No of Clients    =Sum(Fields!ClientNo.Value,"CityOne") + Sum(Fields!ClientNo.Value,"CityTwo")
Placements    =Sum(Fields!Placements.Value,"CityOne") + Sum(Fields!Placements.Value,"CityTwo")
Stays        =Sum(Fields!Stays.Value,"CityOne") + Sum(Fields!Stays.Value,"CityTwo")
When i run the report rather than summing column by column the report seems to be summing row by row and then giving me the same number in each column. Does anyone know where I am going wrong?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Sounds like the groupings you've set up are wrong. Completely impossible to work this out without any sample data, as well as current and expected results.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jman76 Hall of Fame Points: 3388 More actions · Answer 2

Hi
Thanks for your reply. Please find the sample attached. I attached an excel spreadsheet.
The top results include data from the three datasets and then my total.

The bottom results are the expected results.

drew.allen SSC Guru Points: 76944 More actions · Answer 3

jman76 - Tuesday, August 7, 2018 7:49 AM
Hi
Thanks for your reply. Please find the sample attached. I attached an excel spreadsheet.
The top results include data from the three datasets and then my total.
The bottom results are the expected results.

Most people won't open files posted by random strangers on the internet. The correct way to post data is outlined in the first link in my signature.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sgmunson SSC Guru Points: 110639 More actions · Answer 4

jman76 - Tuesday, August 7, 2018 7:49 AM
Hi
Thanks for your reply. Please find the sample attached. I attached an excel spreadsheet.
The top results include data from the three datasets and then my total.
The bottom results are the expected results.

With only the report itself to go by, I have to ask why you need two datasets to create the report. There's more than enough in common to get all that data into a single query or stored procedure. If you can post both existing queries or stored procedures for your datasets we should be able to provide you with a single query or stored procedure that can do the job. That way the normal grouping formulas should work fine, and you should be able to handle this entirely within a single matrix in SSRS.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)