CODEWITHSUNDEEP
sqlsql-serverreporting-servicessql-server-2012ssrs-2012
Saoirse
Saoirse

Reputation: 247

SSRS report. I need to total non aggregate data

I'm quite new to ssrs, so please bear with me.

To simplify - I have a sql server table with 3 columns - ProductGroup, ProductName, ProductWeight.

My query simply selects everything, ordering by productGroup.

In my ssrs report, I need all individual rows displayed. Straightforward enough. However, I need to insert a total ProductWeight for each ProductGroup. i.e. I need a rows with these totals between each changing of the groduct groups. I can't simply aggregate the ProductGroups as I need all the individual records displayed.

I hope I'm making sense.

Many thanks.

Upvotes: 0

Views: 1109

Answers (1)

R. Richards
R. Richards

Reputation: 25151

This is actually very easy to do, but not obvious if you are new to SSRS.

When you are is design mode in the report development tool (assuming you are using Visual Studio (or BIDS)), look for the Row Groups section down at the bottom of the window. You should see a row in that list that has the text (Details) in it. Right click on that row, hover you mouse pointer over Add Group, and then choose Parent Group… from the context menu. Choose the column to group by; ProductGroup in this case. Click OK.

You will see a new column at the front of the tablix for the ProductGroup. Right click on the detail row under the new column, go to Add Total, and then choose After. This will create a new row below the detail row where you can add aggregates for columns where is makes sense. When you hover over this row for the ProductWeight column, you will see a little field chooser button, click that and choose ProductWeight from the dropdown. By default, that adds a Sum aggregate into the new row for that column. You can change the aggregate (to an average, perhaps) by right clicking on the cell, and choosing Expression from the context menu. Preview the report to see if that is what you need.

At this point, you can remove the original Product Group column since it is no longer needed.

Upvotes: 1

Related Questions