-1

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

Thom A
  • 88,727
  • 11
  • 45
  • 75
jhon_123
  • 31
  • 1
  • 7
  • Is there any reason you've tagged an End of life version of SSRS, and another that has only 18 months of extended support left? Which are you really using? – Thom A Jan 08 '21 at 17:10
  • I am using latest version. I changed it. – jhon_123 Jan 08 '21 at 17:17
  • The latest version of SSRS is 2019, not 2012 (which as I mentioned has 18 months of extended support left). – Thom A Jan 08 '21 at 17:25
  • I am using SSRS 2019. I have just put this to get more people to see this. Can you please help me with the problem. – jhon_123 Jan 08 '21 at 17:42
  • *" have just put this to get more people to see this. "* that is not th point of tags. Tags are there to tell people the related technoy, not try and get more people to look at the question. – Thom A Jan 08 '21 at 17:59
  • Thanks @Larun. I got you. I will be careful next time. Can you please give me any hint? – jhon_123 Jan 08 '21 at 18:08

1 Answers1

0

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.

enter image description here

Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • Thanks for your help. It helped me a lot. But i still having some issues. Here's the output https://drive.google.com/file/d/1Fgw-iLJiyPbZSnJBoQBvBO4EmfYCjUyq/view?usp=sharing Can we make it like the ssrs output i shown in question. Right know you can see some extra columns between companyName invoice number and PurcheseNumber. And here is my Grouping picture, https://drive.google.com/file/d/1MFZ95zg9T55rPwIAMcIj_VMWXM7Z8MOM/view?usp=sharing – jhon_123 Jan 09 '21 at 08:06
  • I bet the VendorPO Row group is messing things up. If you are pushing multiple buckets out per invoice then you do not need VenbderPO on the row group as it is a many to one with invoice. Vendor PO should be a column inside the column group. You probally could get away with one column group based on PO. Row groups on Invoice and column groups on PO. – Ross Bush Jan 11 '21 at 13:48
  • I would select the last column with the "CATTEL001" value, right-click, and select "Split Cells". Next, Pull the "Ace Hardware" value up one row and delete the vendor PO column. You need to delete the last two rows so that all the date field values are aligned with the cost fields, you only need one header row and one data row. – Ross Bush Jan 11 '21 at 13:48