SQL Server Reporting Services Part II: Basic Grouping

This is the second post in a series about SQL Server Reporting Services (SSRS). I will be picking up where we left off in the project that was created in Part 1.

In this post we will be taking our list of Sales People and seeing how we can group and sort them in order to make the report more useful. We will also jump into expressions in order to add conditional formatting. The goal is to group the sales people by Country, show the YTD and last year’s sales totals and add color formatting to quickly show how this year’s sales compare to last year.

Sorting

Let’s refresh, this is what our report looks like now.

It was a good start, but the data is not displayed in a very useful way. To improve the readability of this report we could group the data by country.

The first step is to not display the Sales Managers and pull in the missing fields. To hide the Managers we can simply add a WHERE clause to our query in the SalesPersonData Dataset to only pull data WHERE TerritoryName IS NOT NULL. The SalesLastYear and CountryRegionNames are already being pulled in so we just need to add a new column for Sales Last Year (don’t forget to add the Currency formatting) and replace the Territory Name with Country (We will bring the Territory back in future posts for more detailed grouping). If you preview and the Managers still show up, hit the Refresh button in the menu bar (highlighted below).

The simplest way we can view the data by Country is to sort it. Right click on the grey box around the tablix and select Tablix Properties. In the navigation go to Sorting. Right now the data is relying on SQL Server’s default sorting. Click Add and in the Sort by dropdown select [CountryRegionName]. You also have the option to change the order to descending. You can add more to sort the data within a sort – the top line is always the top level sort. Use the up and down arrows to rearrange them.

Now our report is being sorted by [CountryRegionName] and [LastName].

Grouping

While the sorting does help with seeing how the data is structured, it isn’t quite what we wanted. One way we can layout the data is with Country name as a group header and the related Sales Reps listed underneath. Similar to the quick outline below:

Country Name
First Name | Last Name | Email | Sales YTD | Sales Last Year
First Name | Last Name | Email | Sales YTD | Sales Last Year

Country Name
First Name | Last Name | Email | Sales YTD | Sales Last Year

To get this layout head down to the Row Groups section under the Report Design stage.

Right now you see one row in there called (Details) and the same three lines that is next to the details row in the report. Clicking on the drop down on this row you see the options to add a group, delete this group, add totals and view this group’s properties (even a detail row is treated as group).

For this report we want to group our details by country so select Add Group > Parent Group. In the Group by dropdown select [CountryRegionName]. Also select the option for Add group header. You have the option to include a group footer which can be used for totals or additional group specific information. The group header and footer will each repeat once per group.

After hitting OK you will see that the report has changed a bit. There is a new initial column for the Country and an additional row (the group header). In the tablix box you will also see that there is now a bracket encasing the new row and the detail row. With the detail row inside this bracket, the row will repeat for every item within the group. The row above it does not have an extra icon in the tablix box so it will only repeat once in each group.

Here is the report preview so you can compare and see how the rows are displayed based on their location and tablix icon.

For the first bit of formatting, I want to merge all of the table cells in the row that contains the country group name (far left column). Select the cell with the group name, shift-click the columns you want to merge with and open the right mouse click menu. There should be an option that says Merge Cells, but it isn’t (test it out on the empty cells below the First Name and Last Name header to verify that it exists). The reason is because of the thicker right border on the country grouping column – you can’t merge cells across these groups. Don’t worry though, we can get around it.

First, go ahead and delete the column with the column grouping display (you should be fine to do this, but if you get a pop up asking whether to delete the columns and the group or just the columns, select delete columns only).

Next, add a column to the left of the First Name. In the middle row add the CountryRegionName and then try the steps mentioned earlier to merge the columns in this row. (Note some subtle formatting changes in the below screenshot: bold country group name, removed Country Region Name header, removed Country column)

Group Totals

We are making good progress here. Next we need to add some totals – by country and overall.
First, an overall total. In the Row Groups right-click on the CountryRegionName group and expand the Add Totals menu. Since we are at the CountryRegionName group level it will add a total (or sum) of all of the groups within the level. You can add totals before or after the group – let’s go with after.

SSRS adds another row (notice it is outside of the grouping). It even recognizes when there are columns that have fields that can be summed and includes the field values for you. This is nice when working with a simple report but you will need to write the sum expression for the textbox yourself when doing more complicated calculations.

Now that you have added this one, try to add the totals that will show up for each country group (hint: after each group’s details). In the Design view take a note of where within the group this new row is placed.

(Right-click the Details in the Row Groups and Add Total > After)

Let’s add a couple more formatting changes before moving on.

• Add Group Total to the first column of the group sums
• Add Grand Total to the first column of the overall sum
• Add an empty row under the Group Total inside the group that will add space between countries
• Bold text in the Total rows

Conditional Formatting Through Expressions

The last thing we are going to look at is changing the font color.
Right-click on the cell for SalesYTD and select Text Box Properties from the menu. This dialog form has many options to format the selected text box. We are interested in the Font section where we can change the font styling (font, size, color, etc). Everything has the option for you to add a custom expression (fx). Select the expression button next to Color.

There could be multiple posts on writing expressions but right now I’ll give you some tips to get us started.

• Expressions should start with an equal sign (=) unless it is a SSRS keyword (eg. where we left off you should see only the word Black with no equal sign)
• To access the values for a field use Field!FieldName.Value (Remember the .Value or you will get errors when running your report)
• The Category section in the bottom left has some useful functions that are available and short descriptions on parameters and overloads. You can also read through the documentation (https://msdn.microsoft.com/en-us/library/ms157328.aspx).

The function we will be working with is the condition function, IIF (In the Category menu you can find it under Common Functions > Program Flow). The function takes three parameters: an expression that calculates to true or false, the object to return if it is true and the object to return if it is false

(IIF(<expression>, <do this if true>, <do this if false>).

Keeping these things in mind, our goal is to change the text green if the YTD Sales are greater than Last Year and red if it is lower. So our function will look like this:

= IIF(Fields!SalesYTD.Value > Fields!SalesLastYear.Value, "Green", "Red")

We are returning the color as part of a function so they have to be marked as strings within quotes. To apply this formatting to the Sum fields you will also need to update the function to compare the summed fields:

= IIF(Sum(Fields!SalesYTD.Value) > Sum(Fields!SalesLastYear.Value), "Green", "Red")

These concepts should get you on the right track to start building basic reports and will be the starting points for any more complicated reports you will need to create. I’ve tried to include all of the little gotchas I have run into while building reports.

Let me know in the comments if there are any other areas you would like to see a post on or if there is anything I have gone over at top level that you would like a more advanced look at!

Take a look at Part III: Parameters