5 Useful Areas to Customize GP SSRS Reports

There are over 260 SSRS reports that come with GP installation. After they are deployed to SSRS servers, they are available for customization through Report Builder. Report Builder is a very Robust tool that can be used for customizations ranging from easy to advanced levels.

In this blog,  I have described some areas where customizations can take place. After reading this blog you should be able to identify places where Report Customizations can be used in your Business to enhance user experience. This blog will cover 5 such areas and they are listed out as follows:

1.    Calculated Fields

2.    Conditional Formatting

3.    Interactive Sorting

4.    Keep Header Rows visible

5.    Number of Records Per Page

1)    Calculated Fields

Sometimes we need to show data on the report that is not directly saved in the Database. It can be calculated from the existing data.

For example, suppose we have a custom column that saves how many items were Approved by Quality Analysis, we don’t particularly have a column to save what’s left. But we may want to see how much is left on a Report. This can be done by a calculated field.

#No. of items not Approved = Total items – #No. of Items Approved.

To create a Calculated field, right click the Dataset and click ‘Add Calculated Field’, give it a name and click the Expression.

SQL Server Reporting Services allows us to use powerful Expressions to create Calculated Fields in SSRS. So, we can either use existing fields or create a custom expression using the expression window.

2)     Conditional Formatting

Reports are a great way to visualize data in a format that makes sense to the user. Report layout makes it very easy for users to read and understand the information that is presented. Sometimes adding little cues to the report makes it even better to visualize in a glance.

For example, text can be made Bold or highlighted for certain data that needs attention. Or background of Text can be colored Red to indicate high priority data. Graphs can show different colors based on certain conditions.

Conditional formatting makes use of Expressions to evaluate Values that will be assigned to Properties.

Here, we are going to change the color of Text depending on input data. Right click the textbox and go to Text Box Properties

=Switch(Fields!Total.Value<100,”Green”,Fields!Total.Value>100,”Red”)

3)    Interactive Sorting

Sorting can be important when viewing the data. For viewing the Customer List, it makes sense if the list is sorted by Customer name. Sorting can be applied to the data when it is fetched using SQL, but for that we need to know the order in which data needs to be sorted at that time. But sometimes, it is hard to determine what the sorting order should be as it means something different to each user. One user may wish to have data sorted by Customer First name, other users may feel data should be sorted by Last name. Interactive sorting becomes useful in such scenarios. To enable interactive sorting:

1. Right-click the text box in the column header to which you want to add an interactive sort button, and then click Text Box Properties.
2. Click Interactive Sorting.
3. Select Enable interactive sorting on this text box.
4. In Choose what to sort, click Detail rows.
5. In Sort by, specify a sort expression. From the drop-down list, select the field that corresponds to the column for which you are defining a sort action (for example, for a column heading named “Title”, choose [Title]). Specifying a sort expression is required.
6. Click OK.
7. Repeat steps 1-6 for every column to which you want to add an interactive sort button.

When you view the report, columns that support interactive sorting have arrow icons that change to indicate the sort order. The first time you click an interactive sort button, items are sorted in ascending order. Subsequent clicks toggle the sort order between ascending and descending order.

4)    Keep Header Rows visible

Reports can render a lot of data and in case of a table or matrix, scroll bars help to navigate the records. If column names are not visible then it can lead to confusion and the user will have to keep going back to the top of the report just to see the Header names. Luckily, SSRS provides ways to lock the Header Row.

In case of Matrix, here is how to make Header Rows visible

1. Right-click the row, column, or corner handle of a tablix data region, and then click Tablix Properties.
2. On the Generaltab, under Row Headers or Column Headers, select Header should remain visible while scrolling.
3. Click OK.

In case of a Table, this is how it is done

1. On the design surface, click anywhere in the table to display static members, as well as groups, in the grouping pane.

The Row Groups pane displays the hierarchical static and dynamic members for the row groups hierarchy, and the Column groups pane shows a similar display for the column groups hierarchy.

2. On the right side of the grouping pane, click the down arrow, and then click Advanced Mode.
3. Click the static member (row or column) that you want to remain visible while scrolling. The Properties pane displays the Tablix Member properties. 
4. In the Properties pane, set FixedDatato True.
5. Repeat this for as many adjacent members as you want to keep visible while scrolling. Preview the report.

5)    Number of records per page

Long reports stretch across multiple pages, and it is very useful for the user to know what the page number is. Page Number property is stored as fields in the Built-in Fields folder in the Report Data pane. For a page number, you may want to add the word “Page” before the number. You may also want to show the total number of pages.

To add a page number

1. In the Report Data pane, expand the Built-in Fields folder. If you don’t see the Report Data pane, on the View tab, check Report Data.
2. Drag the Page Numberfield from the Report Data pane to the report header or footer.

 Note: The page footer is added to the report automatically. To add a page header, on the Insert tab, click Header, and then click Add Header.

A text box that contains the simple expression [&PageNumber] is added.

To add the word “Page” before the page number

1. Right-click the text box that contains [&PageNumber] and click Expressions.

Then Set Expression for: Value text box contains the expression =Globals!PageNumber.

2. Place the cursor after the = sign and type “Page ” &.

The expression is now =”Page “&Globals!PageNumber. Click OK.

To add total number of pages after the page number

1. Right click the text box with the expression and click Expressions.
2. Type &” of “&at the end of the expression.
3. In the Category pane, expand Built-in Fieldsand double-click TotalPages.

The expression is now =”Page “&Globals!PageNumber &” of “&Globals!TotalPages. Click OK.

To Reset page number after the group ends

1. ResetPageNumber property can be used to Reset page numbers with each new page break (e.g. on a group)

If you need continued training or help customizing your GP SSRS reports, get in touch with KTL today. We offer in-house and remote training, as well as customization for any Microsoft product. learn more about our services.