Column Grouping In SSRS

Question

I am new to SSRS. I am working on a report. and the data that, I have in my table in SQL SERVER is this:

**Region**     | **CompanyName**| **CompanyCode** | **VendorCode** |  **PurchaseNumber** | **Cost** |  **InvoiceNumber**
 SouthWest     |  Company1      |  Cpr132         |  GOP1776       |  UI199              | $30.67   | 1123 
 SouthWest     |  Company1      |  Cpr132         |  GOP1776       |  UI451              | $49.19   | 1123            

This is how i want to display it in SSRS:

**Region**     | **CompanyName**| **CompanyCode** | **VendorCode** |  **PurchaseNumber** | **Cost** | **VendorCode** | **PurcheseNumber** | **Cost** | **InvoiceNumber**
                                                                              
SouthWest      | Company1       | Cpr132          |  GOP1776       |    UI199            |   $30.67 | GOP1776         |UI451               | $49.19   | 1123

I want VendorCode, PurcheseNumber And cost to be grouped as many times if teh data is different. I am confused how to do it. Any hint? Thanks in advance

Answer 1

Here is one way to do it.

1. Create a matrix
2. Set the DataSetName to your data
3. Right-click the first RowGroup in the lower-left Row Groups List Box and Select "Group Properties".
4. Add a "Group Expression" on Company Name.
5. Right-click on the group you just edited and select "Add Group|Add Child Group"
6. Open the new Row group and set its group expression to Invoice Number
7. Select the first column group and add a column group on Purchase Number
8. Add Vendor Code (Inside ColumnGroup)
9. Add Purchase Number (Inside Column Group)
10. Add Cost (Inside Column Group)
11. Right-click on the column group in the lower right column group list view and select "Add Total | After"
12. In the last field add a column for Invoice NOTE: You do not have to sum, however, SSRS will add sum because of the groupings. You can just make it a detailed field as your data seems to be laid out that way anyway.