Search code examples
c#reporting-servicesssrs-2008rdlc

How to show group by columns on top of the table header instead of on the left in rdlc

I have a report with columns EmpType(Full Time/Part Time etc),EmpLocation(Redmond/Seatle/Washington etc),EmpBand(S/T/E etc),EmpId,EmpName,EmpFullAddress,EmpContactNo,EmpSal,EmpGender,EmpDob.

We need to group the data by columns EmpType,EmpLocation,EmpBand and then display rest of the columns as data.

I could use the group by feature of the rdlc report to first group by EmpType then by EmpLocation as its child and EmpBand as child group of EmpLocation but these three values appear on the left as a tree.

But what I want is those three fields to appear on top of every table as something like header on top of header of data table instead of tree on left.

Is that possible to show them like below?

EmpType: Full Time EmpLocation: Seatle EmpBand: S

EmpId | Emp Name | EmpFullAddress  | EmpContactNo | EmpSal |EmpGender |EmpDob    
100     Kris       40 Drealand ave   7417894561    74000    M      15/4/1985

EmpType: Full Time EmpLocation: Seatle EmpBand: T

EmpId | Emp Name | EmpFullAddress  | EmpContactNo | EmpSal |EmpGender |EmpDob   
100     Kris       40 Drealand ave   7417894561    74000      M      15/4/1985

EmpType: Full Time EmpLocation: Redmond EmpBand: S

EmpId | Emp Name | EmpFullAddress  | EmpContactNo | EmpSal |EmpGender |EmpDob   
100     Kris       40 Drealand ave   7417894561    74000      M      15/4/1985

EmpType: Full Time EmpLocation: Seatle EmpBand: S

EmpId | Emp Name | EmpFullAddress  | EmpContactNo | EmpSal |EmpGender |EmpDob   
100     Kris       40 Drealand ave   7417894561    74000      M      15/4/1985

EmpType: Part Time EmpLocation: Seatle EmpBand: S

EmpId | Emp Name | EmpFullAddress  | EmpContactNo | EmpSal |EmpGender |EmpDob   
100     Kris       40 Drealand ave   7417894561    74000      M      15/4/1985
Solution

  • Yes, this is definitely possible. However, you do not want it to group on these three columns in a hierarchy (at least, as I'm conceiving of it that doesn't seem right).

    What you actually want to do is group on the combination of the three variables (see screenshot below). Alternatively, you could create a single expression which creates a string to group on:

    =Fields!Field1.Value & "-" & Fields!Field2.Value & "-" & Fields!Field3.Value

    Either way you do it, you will want to set the sorting to put the groups in the desired order.

    It should also be noted that whenever you add a parent row group, a column is added. You can delete the column that was added and, when prompted, select "Delete Columns Only". This will leave the group in its place in the hierarchy but remove the extra column.

    Once that is done, you can do the following to achieve your desired result:

    1. Right-click on your Detail Row and choose Insert Row => Outside Group - Above. Move your row headers to this newly created row.

    2. Right-click on the row you just created and choose Insert Row => Inside Group - Above.

    3. Merge all the cells in the row created in step 2.

    4. Add a placeholder to the single cell in the row created in step 2. Set the expression for the placeholder to create the desired group header:

      ="EmpType: " & Fields!EmpType.Value & " EmpLocation: " & Fields!EmpLocation.Value & " EmpBand: " & Fields!EmpBand.Value

    Voila! You should have your desired result. If you have questions or issues, leave a comment and I'll do my best to help.

    enter image description here

    Additional Details based on Comment

    Can you repeat the header on a new page when a tablix spans pages? Most definitely. To do this, access the advanced mode to view the row details. Click on the row in the advanced mode that corresponds to the row in your tablix that contains the header.

    enter image description here

    Under the tablix properties, change Keep with Group to "After" in the case of a header and change the Repeat on New Page to True.

    enter image description here

    Can you show the sum of Salaries for the entire group? Yes, you can. You want to add a new row to the bottom and sum the desired field for the default scope.

    1. Right-click on your Detail Row and choose Insert Row => Outside Group - Below.

    2. Create your totals in this row using the following expression:

      =SUM(Fields!Salary.Value)

    Notice that I did not specify a scope or dataset with the aggregate. So long as the expression lies inside a group (be it a detail group or otherwise), if no scope/dataset is provided, it will default to the current scope. This should give you the totals you desire.

    When you say "if I want only the table with its header to repeat under the same group header for different values of EmpBand S/T/E, can I do that ?", I have not idea what you mean.

    Happy coding!