CODEWITHSUNDEEP
ssrs-2008-r2
FredTheDog
FredTheDog

Reputation: 63

SSRS Report Grouping and Sums

Am struggling getting to terms with SSRS and have a report to write that I cannot seem figure out how to do. Basically I have I hierarchy of Parent Groups that are related Bldg > Floor > Cost Code and fields outside of these groups for Sum(area), sum(seats) that calculate the area and seats on each floor belonging to each cost code...simple so far. The problem I have is I need to calculate the percentage of area and seats each cost code takes up from the total on a floor and in the building.

By putting calculations in the group section for building total and floor total I can refer to the value in the text box and perform calculations from it.....great, but I cannot hide the columns as they are within the group section! Also going to the column properties I can hide the column but then get white space in the report where the column was......not very useful!

How can I sum a total area and seats for a floor and a building so I can use it to calculate a percentage for each cost code while still showing the totals spilt by cost code?

Upvotes: 0

Views: 104

Answers (1)

FredTheDog
FredTheDog

Reputation: 63

I figured it out in the end using Row Group Variables. Click on the arrow next to the group in the Row Groups window and select Group Properties. I created variables on each particular group to sum the values I needed, so for example the floor group has a variable that sums the area of all rooms, as its on the floor group it sums all rooms on the floor. The same expression in the Building group variable sums all the rooms in the building. I can then refer to the variable in the detail row to get the percentage a particular cost code takes up on the floor. Did similar to the Building Group.

A couple of things threw me, the Variables do not show up in the Expression dialogue box, would have thought they would be displayed in the Variables Category list. Also when I refer to them in the expression using Variables!name.Value I get the red underline suggesting an error. However when running the report all is well.

Upvotes: 0

Related Questions