Chad's Tech

SQL Reporting with custom pagination and Excel named worksheets

<

div class=”ExternalClass16EB91745BFA419384C0E3307E72D388″>

With some guidance from Microsoft TechNet, Edward at StackOverflow.com, and Adam at RedThree,  I managed to enhance the Compliance 5 – Computers in a Collection report by separating each computer onto its own page.  When exported to Excel, each “page” becomes a worksheet or tab.  This could also be separated by any other field in the report such as Computer Domain, SCCM Site, etc.

Start by opening the Compliance 5 – Computers in a Collection report in Report Builder then follow the steps below:

  • Click the data row ( point 1)
  • right-click on the Row Group name (= (Table0_Details_Group)
  • select Add Group
  • select Parent Group…

image

In the Tablix group

  • On the Group by drop down, select the column you want to group by.  In this example [UpdateClassification] is used
  • Select / enable Add group header

image

In the Row Groups panel

  • right-click the column you want to group by.  In this example [UpdateClassification] is used
  • Select Group Properties…

image

In the Group Properties

  • Select Page Breaks
  • Select Between each instance of a group for the Page break options

image

In the Report Builder main window

  • Select the cell that was added for the Group by function
  • Enable the Properties View
  • Expand Group –> Group –> PageBreak –> PageName
  • Select the drop down arrow and <Expression…>

image

In the Expression window

  • Select the Fields (DataSet0) Category
  • Double-Click the column you want to group by.  In this example [UpdateClassification] is used

image

continue reading on CatapultSystems.com
%d bloggers like this: