2

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

enter image description here

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.

Anirudh
  • 581
  • 5
  • 14
  • 32
  • The report layout is difficult to understand. Is the report supposed to group by the first 3 columns in the dataset? How does one know which OnHand value to display on the header? Also, the layout has an ITEMNMBR of 'CAP500' and an ITEMGenDesc of 'Transistor', but I don't see that in the SQL output. – Ron Smith Feb 22 '14 at 02:58
  • possible duplicate of [ssrs 2008 : Do not want group header to appear for every group](http://stackoverflow.com/questions/21944568/ssrs-2008-do-not-want-group-header-to-appear-for-every-group) – kyzen Feb 22 '14 at 18:28
  • @RonSmith : 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 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. Please let me know for any questions. Thanks – Anirudh Feb 23 '14 at 17:07
  • @kyzen: The other question that you have linked was more complex and that's why I made it simpler here.. Thanks for understanding. – Anirudh Feb 23 '14 at 17:18
  • Anyone has any suggestions? Thanks in advance. – Anirudh Feb 24 '14 at 14:01
  • I'm still not sure how you know which OnHand value to put in the heading since it varies within the each group. In Group 1 (CAP100), the OnHand value corresponding to the Minimum binPriority value is displayed (OnHand: 5, binPriority: 0), while in Group 3 (CAP500), the OnHand value corresponding to the Maximum binPriority value is displayed (OnHand: 30, binPriority: 1). I will go ahead and answer this question assuming that you want to display the OnHand value corresponding to the Minimum binPriority, which in the case of CAP500, should be 25. If there is some other logic, you can add it. – Ron Smith Feb 24 '14 at 17:33

1 Answers1

2

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.
Ron Smith
  • 3,241
  • 1
  • 13
  • 16
  • Thanks for the detailed steps. I still have blank rows coming between each line and also the PDF export throws blank pages. I checked all report settings etc. I will look into it separately and will not club with this. – Anirudh Feb 25 '14 at 15:08
  • You are quite welcome! Sometimes blank pages on PDF export are due to the width of the report exceeding the width of the page. Make sure your Report Body width + Left and Right Margins = your Page width. – Ron Smith Feb 25 '14 at 18:03