Search code examples
reporting-servicespivotreport

SSRS Pivot Creating Extra Rows

I have a report and need to pivot on SLOC (storage location). It all works great except it creates extra rows due to the storage bins being different even though I'm not grouping on them. The first image shows what I have. The second image shows what I want. How do I 'push up' the rows?

Rows Grouped on "Part Kit" then "Material"
Columns Grouped on "SLOC"

Currently Looks Like this

I want it to look like this

Solution

  • You can achieve the desired format by adding a row number to group on. This will tell the report where to position things. In this case I added a row number like this:

    ROW_NUMBER()OVER(PARTITION BY SLOK, PartMaterial ORDER BY StorageBin) as RN

    Here is a sample of how it looked before:

    enter image description here

    And here it is after I grouped the rows by the row number:

    enter image description here

    Of course, you can hide the row number, but this shows why and how it works. In this case, I added a child row group after the part material and grouped and sorted it by the row number.