ssrs report : Facing issues with Master detail kin

Question

I need to generate a report of below format. The header repeats only per page.

ItemNr | ItemGenDesc | FulfilmentBin | onHand 
-----------------------------------------------
CAP100   Capacitor     FulFil1              5

     binPriority | binBackup | binBackupQty | binMin | binMax
     -----------------------------------------------------------------
               1   bkBUSLOT2            100        1        5
               2   bkBUSLOT3            150        2        6

CAP400   Transistor    FulFil12             20
CAP500   Transistor    FulFil14             30

     binPriority | binBackup | binBackupQty | binMin | binMax
     -----------------------------------------------------------------
               1   bkBUSLOT5            250        5        9

My SQL output is as below

I tried to work with 2 tablix and somehow I got the output coming up in preview mode but when I export to PDF, I get blank lines between each item. I am guessing that's because the binPriority grid (detail grid) is hidden when the itemnr count <=1. Also the tablix1 row header appears for every grouping but I want row header to appear once per page.

Can anyone suggest what are the ways to generate this type of report?

Thanks.

Update 1 : Sorry CAP500 in the report should be Radiator. My typo mistake. The report should be grouped by ItemNumber and it is sorted by binPriority. So it will print binpriority=0 in the master grid and then in the detail grid it will print in the order of binPriority. The OnHand that is shown in the master grid is relevant to binPriority= 0.

Answer 1

You can achieve your specified layout with a single Tablix. This is fairly simple if you don't mind Columns 2-4 in your Group heading lining up with Columns 1-3 in your Detail. Since horizontal page real estate isn't really an issue (only 6 columns counting the blank column to the left of your Detail columns), I would take this route.

I'm not sure how much you know about SSRS, so forgive me if this explanation is too rudimentary. Others with less experience than you might find it helpful as well.

1. Right Click the Detail Row on your Tablix.
2. Select Add Group => Row Group => Parent Group.
3. Set the Group By value to ItemNumber and check the "Add Group Header" box.
4. This creates a Group By Column, which I would delete for your specified layout. Make sure to only delete the Column, not the Row Group.
5. In the Grouping Window (down at the bottom by default), right click the ItemNumber Row Group and select Group Properties.
6. On the Sorting tab/menu, set the Sort By value to binPriority.
7. Right Click your new Row Group Row on the Tablix and select Insert Row => Inside Group Above. You'll need to do this twice so you have 3 Row headings in the Row Group.
8. Insert 3 more columns for a total of 6 columns in your Tablix.
9. Put your ItemNr, ItemGenDesc, FulfilmentBin, onHand Labels and Values in the first two Rows, first 4 Columns of your Row Group.
10. Put your binPriority, binBackup, binBackupQty, binMin, binMax Labels in the Last Row, Last 5 columns of your Row Group.
11. Put your binPriority, binBackup, binBackupQty, binMin, binMax Values in the Last 5 columns of your Detail Row.