Grouping in SSRS?

Question

i am new to the SSRS and Have data as below

RCnt Jobnumber NJCT          JD                 pmt    flag cnt1 cnt2
2   001008      0   PRESSURE SEAL               PSI Y   0   0   2
2   001008      0   PRESSURE SEAL               PSI Y   0   0   10
2   001008      0   PRESSURE SEAL               PSI Y   0   0   1
1   001009      0   Single Sheet Duplex Legal   PSI Y   0   0   1
1   001009      0   Single Sheet Duplex Legal   PSI Y   0   0   4
6   001010      0   Single Sheet Duplex Legal   PSI Y   0   0   6
5   001011      0   Single Sheet Duplex Legal   PSI Y   0   0   5
5   001011      0   Single Sheet Duplex Legal   PSI Y   0   0   3
13  001012      13  Single Sheet Duplex Legal   PSI Y   0   13  0
13  001012      17  Single Sheet Duplex Legal   PSI Y   0   17  0
13  001012      8   Single Sheet Duplex Legal   PSI Y   0   8   0

And i want to display it as below ,

jobNumber  NJCT   JD                          cnt2   Rcnt
001008    0     PRESSURE SEAL                13        2
001009    0     Single Sheet Duplex Legal    5         1
001010    0     Single Sheet Duplex Legal    6         6
001011    0     Single Sheet Duplex Legal    8         5
001012    38    Single Sheet Duplex Legal    0         13  

TOTAL    38                                 32         27

I know this grouping can be done at the server level in query but i don't have access to query and have to format this data as below in report using SSRS may be this is simple grouping i don't know when i googled about grouping it given me column level and row level grouping which left me very confused so any help related to this will be very helpful.Any links are also welcome to get grouping like this in SSRS report.

Answer 1

You have the following data in your Dataset:

Drag a table into the report and set its Dataset in the properties. Right click on the left of the table with the three lines and click Add Group -> Parent Group.

Group on the Jobnumber field, and check Include group header:

It will look like this:

Right click on the first column and choose Delete Columns.

Right click on the detail row and choose Delete Rows:

Click OK when prompted with Delete rows and associated groups?

Finally we need a table footer row - click on the bottom row and click Insert Row -> Outside Group - Below:

It should look like this:

Now add your data! Add new columns as required. When adding numeric data to these group and header fields, it will aggregate as a Sum by default:

Now the table is basically looking like the requirements; just format to taste:

Also, some links as requested:

Books Online, Adding Grouping and Totals (Reporting Services).

SQL Server Central, Tablix Tutorial.

These should be a good intro and are part of larger series of articles.