Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SSRS Sum in table group

I am working on SSRS reporting services. I have a table on which I applied group. Originally I had this data:

enter image description here

I changed something in my tablix and created a tablix and added a parent group of Age i.e. left column and then in the right column, I applied an expression: =SUM(Fields!AgeTotal.Value, "Group1") which made the result like this: enter image description here

Now I want 3,3,3 and 2,2 not to repeat and make them 1 row of each group. Like this: enter image description here

UPDATE: enter image description here

like image 272
asma Avatar asked Jun 06 '11 13:06

asma


People also ask

How do I sum a group in SSRS?

In the tablix data region row group area, right-click a cell in the row group area for which you want totals, point to Add Total, and then click Before or After. A new row outside the current group is added to the data region, and then a default total is added for each numeric field in the row.

How do you find total in SSRS?

Switch to the Design view. Right-click the data region cell that contains the [LineTotal] expression, and select Add Total. Report Designer adds a row with a sum of the dollar amount for each order. Right-click the cell that contains the field [Qty] , and select Add Total.

How do I use IIF in SSRS expression?

Using IIF Function in SSRS We are going to add a new field to the report data set to determine if the Order Year is the Max or Current Year. As shown below, the dataset properties window is opened, and the Fields tab is selected. After clicking Add, at the bottom of the list a new field is added.

How do I add a total column in report builder?

To add totals for a column group In the tablix data region row group area, right-click a cell in the column group area for which you want totals, then point to Add Total, and click Before or After.


1 Answers

Right Click on the details cell and select add Total, then right click on the entire details rows and change it's visibility to Hide. This should do the trick!

EDIT

Setp by step tutorial with Image:

First image example: on the left the result you want, on the right the result you get from a simple grouping.
I'm getting the sum(but you could use a count or whatever aggregation you want) of the field lam_larghezza (which, for your information, is a width) group by different lam_spessore values (which, is a thickness)

I'm sorry but mi IDE is in Italian so here a simple translation:

Aggiungi -> Add
Gruppo -> Group
Righe -> Rows
Raggruppa per -> Group By
Totale -> Total
Dividi Celle -> Split Cell
Visibilità Righe -> Row visibility

enter image description here

Let's start!

Create the dataset for the report and clean the report

Add a table to the report

enter image description here

Add the detail field in the data row

enter image description here

Right click on the full row
Add group -> Row group -> father group -> group by grouping field (should be age)

enter image description here

enter image description here

Click on the detail cell (only the cell not the entire row, and remember that the cell had to be highlighted not the text inside the cell, because two different contex menu appear) -> Add Total

enter image description here

You now have 1 grouping cell and two detail cell

split the grouping field cell

enter image description here

set the visibility of the detail row to hidden

enter image description here

And you are done!

enter image description here

like image 75
il_guru Avatar answered Sep 19 '22 16:09

il_guru