sql server reporting services grouping data

01 October 2019

33829 views

Printer friendly version

  • Reporting Services Basics: Adding Groups to Reports

Most SSRS reports are organized into grouping levels where it’s possible to add totals for each group. In this article, Kathi Kellenberger continues her SSRS series by walking through how to create both a Matrix and Table report.

The series so far:

  • Reporting Services Basics: Overview and Installation
  • Reporting Services Basics: Creating Your First Report
  • Reporting Services Basics: Data Sources and Datasets
  • Reporting Services Basics: Parameters

So far in this series, I’ve shown you how to create a basic report with parameters. The next necessary skill for report developers is to add grouping levels to the report. Managers often want to see subtotals, for example, at various levels, and adding groups is the way to do this. The report might be divided into categories and subcategories, maybe by locations and departments, or possibly by year and month. Whenever there is a hierarchical relationship in the data, it might make sense to add groups based on those relationships.

There are several ways to add groups, but I’m going to show you the ones that have worked the best for me.

The Groups section

When you look at a report in Visual Studio in design view, you will see a section under the report canvas with Row Groups and Column Groups as shown in Figure 1.

sql server reporting services grouping data

Figure 1: The groups section

If you don’t see the section, click on the report and then select Report View Grouping from the menu as shown in Figure 2 to make it visible.

sql server reporting services grouping data

Figure 2: Where to turn on the Grouping section

You may be wondering what the difference is between column and row groups, and they are quite different. Row groups are used to organize the report into horizontal sections in a typical report. Figure 3 shows one page of a report with row groups.

sql server reporting services grouping data

Figure 3: A report with row groups

Column groups are used in matrix reports, which might also be described as “pivot” reports. A matrix report displays data from a column across the top of the report as headings. You can also add row groups to matrix reports. For example, you might want to display several years across a report along with row groups for category and subcategory as shown in Figure 4.

sql server reporting services grouping data

Figure 4: A matrix report

The SSRS Toolbox contains Table and Matrix items. When you add either one to a report, you’ll notice that each is called a Tablix instead of the original name. It’s also possible to turn a Table into a Matrix by adding column groups or turn a Matrix into a Table by removing the column groups. In my experience, it is better to start with the one you need.

For years, I used the SSRS wizard to create matrix reports, but eventually I realized that simple matrix reports were not that difficult.

Creating a matrix report

In this example, I’ll show you how to create the report shown in Figure 4. If you need help with setting up an SSRS project or creating data sources or datasets, be sure to go back to the earlier articles in this series to learn more.

To get started, you’ll need an SSRS project with a shared data source pointing to the AdventureWorks2017 database. Create a new report by right-clicking Reports and selecting Add New Item…. In the Add New Item dialog, select Report . Name the report Sales by year and category and click Add . The dialog will look similar to Figure 5.

sql server reporting services grouping data

Figure 5: Creating a new report

Inside the Report Data window, create a Data Source that points to the project’s Shared Data Source . Create a Dataset that is embedded in the report named ProductSales with the query below:

After creating the dataset, the Report Data window should look like Figure 6.

sql server reporting services grouping data

Figure 6: The Report Data window

The next step is to drag a Matrix object to the report canvas from the Toolbox window. You can also right click the canvas and select Insert Matrix . The empty matrix object will look like Figure 7.

sql server reporting services grouping data

Figure 7: The empty Matrix

When creating a matrix report, the trick is figuring out what goes where. The Columns cell will contain the column you want displayed as headings across the report. The Data cell will contain the value you want to aggregate. The Rows cell will be the remaining columns. In this case, the report must display the years across the report and add up the sales amount.

Drag OrderYear to the Columns cell, SalesAmt to the Data cell, and Category to the Rows cell as shown in Figure 8.

sql server reporting services grouping data

Figure 8: Report after adding main items

Notice that you can see the grouping levels in the grouping section. When you run the report, it will look something like Figure 9.

sql server reporting services grouping data

Figure 9: The report so far

The report’s not pretty at this point, but it does display as expected with the years going across the top as headings. The original report also includes subcategories. Go back to design view and right click on the Category group in the Row Groups section. Click Add Group Child Group… as shown in Figure 10.

sql server reporting services grouping data

Figure 10: Adding a Child Group

This brings up the Tablix group dialog. Select SubCategory as shown in Figure 11.

sql server reporting services grouping data

Figure 11: The Tablix group dialog

After clicking OK, the report should now look like Figure 12.

sql server reporting services grouping data

Figure 12: After adding SubCategory

When previewing the report, the top of it should look like Figure 13.

sql server reporting services grouping data

Figure 13: The report

You can format your report or not as desired since this article is about grouping, but at this point, I have to add some formatting for my own sanity! In this case, I have bolded the top row and the first two columns. Everything should end up bolded except for the Data cell as shown in Figure 14.

sql server reporting services grouping data

Figure 14: Bold everything except for the total

I also formatted the SalesAmt in the Data cell to be Currency with a thousands separator and no decimal places and added a heading with the report name. The default margins are too wide for this report, so I also changed them in the Report Properties and made sure that everything in the report was pulled to the left. (To learn more about formatting, see the previous articles.)

After the formatting is complete, the report looks like Figure 15.

sql server reporting services grouping data

Figure 15: The formatted report

The next step is to add some totals to the report. It is so easy to do when working with matrix reports! Right-click the Data cell that contains the summed SalesAmt and select Add Total Row . Repeat for Add Total Column . Figure 16 shows you where to find these.

sql server reporting services grouping data

Figure 16: Adding totals

Adding these two totals will leave your report canvas looking like Figure 17.

sql server reporting services grouping data

Figure 17: After adding the row and column totals

You might just guess the next step: adding a grand total in the empty cell. Hover over the cell until the column list appears and click it as shown in Figure 18. Select SalesAmt which will automatically sum.

sql server reporting services grouping data

Figure 18: Adding SaleAmt to the empty cell for a grand total

I also had to format that last cell since it didn’t pick up the previous formatting. After running the report, the first page in Print Layout mode looks like Figure 19.

sql server reporting services grouping data

Figure 19: The matrix report with totals in Print Layout mode

Obviously, you can add colors, a footer, and more to make this report look nicer, but just getting the data displayed correctly was quite simple! There’s one more thing that might be useful here. If you are in Print Layout mode and scroll, you’ll see that the top headings do not carry over from page to page. (If you are seeing alternating blank pages, go back adjust the page margins and make sure that the report edge has been pulled to the left.) To get the headings to appear on the second and subsequent pages, go to the Tablix properties and change the RepeatColumnHeaders and RepeatRowHeaders to True as shown in Figure 20. Probably the easiest way to see the Tablix properties is by selecting it from the list in the Properties window.

(Note that this method to repeat headings doesn’t work on regular table reports, and I’ll explain how to do that later in the article.)

sql server reporting services grouping data

Figure 20: The properties to get the matrix report headings to repeat

Now that you have seen how to create a matrix report, it’s time to learn how to create a regular row-grouped report.

Creating a table report

You saw a glimpse of row groups in the last section, but this time you will learn even more about row groups in reports as you create the report shown in Figure 1. To get started, create a new report in the project with the name Product Sales . It will also point to the project’s shared data source. Create a dataset using the same query that was used in the matrix report. Add a Table control to the report canvas and fill in the Data row, also known as the detail group, as shown in Figure 21. The headings should fill in by themselves.

sql server reporting services grouping data

Figure 21: The table with the detail row fields

The report is grouped by Category Sub Category OrderYear , so OrderYear is the first “parent” above the detail. In this type of report, I think it is easier to start at the detail and build out, but you can also go the other way. To add the group, right click the Details group in the Row Groups section. Select Add Group Parent Group… as shown in Figure 22.

sql server reporting services grouping data

Figure 22: Adding a parent group

This will bring up the Tablix group dialog where you will select the group’s field. Select OrderYear since it is the direct parent of the detail row. Also check Add group footer . This is where the subtotals will go. The dialog should look like Figure 23.

sql server reporting services grouping data

Figure 23: The Tablix group dialog

After you click OK to add the group, you’ll see quite a few changes to the report canvas. The Order Year group has been added to the report and to the Row Groups section. Figure 24 shows the report canvas at this point.

sql server reporting services grouping data

The next parent level is SubCategory . This time, I’ll show you another way to add a group. Right-click on the OrderYear cell and select Add Group Parent Group… as shown in Figure 25.

sql server reporting services grouping data

Figure 25: Another way to add a parent group

Add SubCategory as the new group and be sure to select Add group footer . Repeat the process to add Category using either method. Make sure to add the new group as a parent to SubCategory . Once all the groups are added, the report canvas should look like Figure 26.

sql server reporting services grouping data

Figure 26: All the groups added to the report

There are a lot of empty cells, and you can add totals to some of them. To figure out which grouping level a particular cell belongs to just select it. You’ll see the grouping level light up in orange as shown in Figure 27.

sql server reporting services grouping data

Figure 27: The grouping level lights up in orange

To add subtotal for each level, fill in SalesAmt for each cell underneath the SalesAmt cell. You can also add subtotals for Quantity . They will automatically sum as shown in Figure 28.

sql server reporting services grouping data

Figure 28: Adding subtotals

You can also add a grand total to the report by right-clicking the bottommost SalesAmt cell and selecting Add Total as shown in Figure 29.

sql server reporting services grouping data

Figure 29: Adding a grand total

When adding a total in this way, it is automatically added to the next parent grouping level. In this case, Category is at the top, so the total is added to the report level. You can add a grand total for Quantity . In this case, just add Quantity to the cell. The report design should look like Figure 30.

sql server reporting services grouping data

Figure 30: Adding grand totals

Preview the report. The report should look something like Figure 31.

sql server reporting services grouping data

Figure 31: The unformatted report

There is quite a bit of formatting that should be done, but I’ll leave it up to you to decide what to do except to be sure to modify the report’s margins so that all the fields fit on one page. After formatting, my report looks like Figure 32.

sql server reporting services grouping data

Figure 32: Report with some formatting

If you take a close look, it’s hard to tell what levels the total amounts refer to. To fix that, follow these steps. In the cell under ProductName , add this expression to the Value property:

Holding down the Shift key, select the two cells in row 4 and columns 4 and 5. Right-click and select Merge Cells as shown in Figure 33.

sql server reporting services grouping data

Figure 33: Merging two cells

Add this expression to the new larger cell:

Remove the word Total from the cell to the left. Merge the cells in row 5 and columns 4 and 5. Add this formula:

Finally, merge the cells in the bottom row and columns 4 and 5. The cell should just say Grand Total . Delete the original word Total from several cells. The report design should look like Figure 34.

sql server reporting services grouping data

Figure 34: The subtotals labeled

I would like to move some of the labels more to the left, but I found that the Merge Cells option was not always available. If it is for you, then modify accordingly.

The last thing to do to make this report more usable is to have the headings repeat on new pages. If you go into Print Layout mode and scroll to page 2, you will see that the top headings do not repeat. The RepeatRowHeaders property doesn’t seem to do anything on regular table reports. There is a way to do this, though.

On the grouping section at the bottom of the page, select Advanced Mode as shown in Figure 35.

sql server reporting services grouping data

Figure 35: Turning on Advanced Mode

Make sure this is checked which will add Static sections to the row and column groups as shown in Figure 36.

sql server reporting services grouping data

Figure 36: The Static groups

Working only on the row groups, select the first Static group and open the Properties window. Change the RepeatOnNewPage property to True as shown in Figure 37.

sql server reporting services grouping data

Figure 37: The RepeatOnNewPage property

Repeat the process for the next two Static groups. Now when you run the report and scroll to page 2, you’ll see the top headings repeated. Figure 39 shows you page 2 of my report.

sql server reporting services grouping data

Figure 39: The report with repeating row headings

This article introduced adding grouping by walking you through the creation of two simple reports. This article covered a lot of ground, but there is still so much to learn. In the next article, you’ll learn more about adding expressions to reports to add even more functionality.

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

Rate this article

sql server reporting services grouping data

Kathi Kellenberger

Kathi Kellenberger is a Customer Success Engineer at Redgate and a Microsoft Data Platform MVP. She has worked with SQL Server for over 20 years and has authored, co-authored, or tech edited more than 20 technical books. Kathi is a volunteer at LaunchCode, the St. Louis based organization providing free training and paid apprenticeships in technology. When Kathi isn’t working she enjoys spending time with family and friends, cycling, singing, and climbing the stairs of tall buildings. Be sure to check out her courses on Pluralsight .

Follow Kathi Kellenberger via

View all articles by Kathi Kellenberger

Load comments

Related articles

sql server reporting services grouping data

Top-Down and Bottom-Up Approaches in Microsoft Fabric

SQLServerCentral Article

Dynamic Grouping in SSRS Reports

Andy Owl , 2015-07-31 (first published: 2012-10-11 )

This article shows how you can create a dynamic report in SQL Server Reporting Services (SSRS). When you run this type of report normally, without selecting any particular grouping, you get a simple table. For example, Figure 1 shows a report with a table that contains information about films. Because the data is not being grouped, the information is very straightforward.

sql server reporting services grouping data

Figure 1: Displaying a table without grouping the information

In the figure above, the user can specifically choose not to group data. However, the user can also choose to group the data by one of the listed values, in which case we would expect to see very different results, as shown in Figure 2.

sql server reporting services grouping data

Figure 2: Grouping the data by country

As you can see, you can group the report data by a specific field. In this case, I’ve selected Country , which means the report will display the film information grouped by specific countries, which are the values contained in the Country field.

As basic as this might seem, there’s a lot to do to get this all working! So let’s look at how to do that. For now, we’ll ignore the Don’t group option and assume that we have to group by some field. When we get this working, we’ll then allow users to choose not to group the data.

Step 1 – Creating the Dataset

The report’s dataset must include the fields that we want to group by, along with any other fields whose data we want to include in the report. Figure 3 shows the dsFilms dataset, as it appears in the Report Data window. Notice that the dataset is expanded to show the list of fields that the dataset retrieves.

sql server reporting services grouping data

Figure 3: The fields in the dsFilms dataset

In this case, the dataset is retrieving data from the database shown in Figure 4. Notice that the fields in our dataset are checked in the tables within that database.

sql server reporting services grouping data

Figure 4: The database on which our dataset is based

As you can see, it’s a fairly simple database: each film is related to a country, film studio, director and language. (Now’s probably a good time to apologise to all American readers for calling them films, rather than movies!)

Step 2 – Creating the Table

Now we’ve got a dataset, we can create a simple grouped table. The easiest way to do this is to assume that we’re grouping by a fixed field. (We’ll assume we’re grouping by country.) Figure 5 shows what your grouped table should look like after you’ve added it to your design surface. Notice that I’ve added three fields from the dataset: FilmName , FilmRunTimeMinu t es , and FilmOscarWins , renamed as shown in the figure.

sql server reporting services grouping data

Figure 5: Adding a grouped table to our report

I also added a bit of formatting to make the report look a bit neater. When you view the report, it should look similar to what’s shown in Figure 6. As you can see, we have a basic report, grouped by country.

sql server reporting services grouping data

Figure 6: Viewing a report with the data grouped by country

So far, this is a fairly ordinary report. The next step is to add a drop-down list so viewers can choose how to group the data. This is what will make the report dynamic.

Step 3 – Creating the Parameter

To add a drop-down list to the report, you must start by adding a parameter. To do so, right click the Parameters node in the Report Data window, and then click Add Parameter , as shown in Figure 7.

sql server reporting services grouping data

Figure 7: ?

When the Report Parameter Properties dialog box appears, provide a name for your parameter. I used GroupField , as shown in Figure 8.

sql server reporting services grouping data

Figure 8: Providing a name for the report parameter

We can now list out the values available to our parameter. These values are the ones that will appear in our drop-down list.

sql server reporting services grouping data

Figure 9: Adding values to your report parameter

sql server reporting services grouping data

Figure 10: Specifying a default value for your report’s parameter

When you run the report, you should now see a drop-down list similar to the one shown in Figure 11.

sql server reporting services grouping data

Figure 11: Viewing the drop-down list in your report

However, selecting an option from the drop-down list does not affect the data that’s displayed in your report—yet.

Step 4 – Making the Grouping Variable

This is the clever bit (and the reason you’re probably reading this article). We need to make the grouping dynamic. First edit the group’s properties. SSRS supports several ways to access the table’s group properties. One of the easiest ways is to right-click the group in the Row Groups window and then click Group Properties , as shown in Figure 12.

sql server reporting services grouping data

Figure 12: Accessing the table’s group properties

When you click this option, the Group Properties dialog box appears, as shown in Figure 13.

sql server reporting services grouping data

Figure 13: Viewing the CountryName group properties

What you should now do is change the name of the group. On my system, I changed the name from the specific CountryName to the more general GroupName , as shown in Figure 14.

sql server reporting services grouping data

Figure 14: Changing the group name

Notice in the Group on text box shows the CountryName field as its value. That’s because the table’s group is based on this field. However, we’re going to instead define an expression. To create the expression, click the expression builder button next to the Group on text box. This opens the Expression dialog box, where you’ll modify the default expression to the one shown in Figure 15.

sql server reporting services grouping data

Figure 15: Modifying the expression association with the Group on text box

The expression tells SSRS to group the data based on the returned value. That value is based on the option selected from the drop-down list, concatenated with the word “Name.” We do this to return a value that matches the actual field name, as it’s listed in the dataset. For example, if the user selects Studio from the drop-down list, the expression will group the data based on the field called StudioName . This approach works because all of our group fields follow the same naming structure; if this were not the case, we’d have to use more complicated logic in our expression.

Step 5 - Tidying up the Report

Although the steps we’ve taken so far will get the grouping working, there are a number of other expressions we need to change to make the report tidier.

For example, report title shouldn’t be specific to the field selected from the drop-down list.

sql server reporting services grouping data

So if, for example, a user chooses to group by Studio, the title will read List of films by Studio.

sql server reporting services grouping data

This complicated expression will display something like Studio: MGM.

Finally, entries in the group should be sorted by the right field. (For example, if the user chooses to group by studio, then the group should be sorted by the value of the StudioName field.) To achieve this, return to the G roup P roperties dialog box and ???

sql server reporting services grouping data

Figure 18: Sorting your grouped data

The expression tells SSRS to sort the data based on the returned field name. That name is based on the option selected from the drop-down list, concatenated with the word “Name.”

Step 6 – Making the Grouping Optional

I promised earlier that we would—eventually—make the grouping optional. Now is the time to do this! First, we’ll add another parameter value, as shown in Figure 19.

sql server reporting services grouping data

Figure 19: Adding another value to the report parameter

In this case, we’re adding a value whose label is Don’t group and value is Nothing . This change alone does nothing but add another value to our drop-down list, as shown in Figure 20.

sql server reporting services grouping data

Figure 20: The new value as it appears in the drop-down list

We can’t remove grouping from the report, but what we can do is ensure that if the user chooses the Don’t group option, two things happen:

The grouping expression evaluates to a constant value so that all records belong to the same group.

The grouping header and footer are hidden.

Taken together, these two changes will make the report appear as if no data is being grouped. Let’s make those changes one at a time.

Step 7 – Group Expression Allowing for No Grouping

To set the grouping expression to accommodate a possible Don’t group choice, once again access the group properties. To do so, right-click the group in the Row Groups window, and then click Group Properties , as shown in Figure 21.

sql server reporting services grouping data

Figure 21: Accessing the tables group properties

When the Group Properties dialog box appears, click on the expression builder button to the right of the Group on text box, as shown in Figure 22.

sql server reporting services grouping data

Figure 22: Accessing the expression associated with the Group on text box

In the Expression dialog box, modify the expression so to what is shown in Figure 23. (The expression is explained in more detail below.)

sql server reporting services grouping data

Figure 23: Modifying the expression associated with the Group on text box

The expression uses an IIF function in order to incorporate conditional logic. (The syntax is the same as the IF function in Excel: if the condition is true, do one thing; otherwise, do another.)

The first part of the function states that if the user has chosen not to group data (that is, if the GroupField parameter value is Nothing ), then we set the value of the group expression to be an arbitrary constant value, in this case, 1 . This way, all rows will belong to the same group.

If, on the other hand, the user has chosen to group data, we set the group expression to be as follows:

Fields(iif(Parameters!GroupField.Value="Nothing","FilmName",

Parameters!GroupField.Value & "Name")).Value

This represents the third argument in our initial IIF function. Notice that it includes another instance of the IIF function, which defines how to group the data if the user chooses a value other than Don’t group .

The reason this third argument is so cumbersome is because SSRS evaluates all parts of the expression, even if the first part of the IIF condition is true. This means that even if the user chooses Don’t group as an option, the last part of the expression will still be evaluated, and will crash the report because SSRS is trying to refer to a field that doesn’t exist. To avoid this happening, we specify the FilmName field, even though we know that this part of the expression can never be used!

While we’re at it, we also need to tidy up our titles. First, access the expression you defined on the report title and modify it as shown in Figure 24.

sql server reporting services grouping data

Figure 24: Modifying the expression used to define the title

As you can see, we’re again using the IIF function to add conditional logical to our expression. This way, the title is appended only if the user choses a field to group by.

Next, we need to modify the group title. Once again, access the expression you created for the group title and modify that expression as is shown in Figure 25.

sql server reporting services grouping data

Figure 25: Modifying the expression used to define the group title

If the user doesn’t chose a grouping field, it doesn’t matter what we set this title to, as it will be hidden; otherwise, we set the title so that it reads something similar to Studio: MGM . (See the section above about the grouping expression for details about why this expression needs to be so complex.)

Step 8 – Hiding the Group Header Conditionally

Finally, we need to ensure the group header doesn’t appear when the user has chosen not to group. To do this, you have to change the properties of the group header rows.

sql server reporting services grouping data

Figure 26: Accessing the Row Visibility dialog box

(Irritatingly, you have to do this one row at a time if you’re using the Properties dialog box). You can then set the row’s Hidden property to be an expression like this, as shown in Figure 27.

sql server reporting services grouping data

Figure 27: Creating an expression that defines a row’s visibility.

Thus if the user has chosen Don’t group as an option, the Hidden property will be set to True.

Hope people have enjoyed this article. If you like the idea of creating dynamic reports like this, you might like to look at an example of how to make the data source of a report dynamic , or a blog on making matrix dimensions dynamic .

4.88 ( 76 )

Log in or register to rate

You rated this post out of 5. Change rating

  • Reporting Services (SSRS)

Join the discussion and add your comment

Related content

Create first ssrs report with sql server analysis services olap cube.

  • by Additional Articles
  • CodeProject.com

Mubin M. Shaikh outlines how to create SSRS Report on SSAS OLAP Cube. If you don't have much prior knowledge of SSRS, just follow the steps and to have your first report ready on OLAP Cube.

12,428 reads

Fix SSRS Subscription Owners

  • by aksjd234
  • SQLServerCentral.com

Stored procedure to update SSRS Subscription owners to avoid email errors.

2012-03-13 (first published: 2012-02-28 )

1,313 reads

External Article

Using Report Parameters in SQL Server Reporting Services

  • SQL Server Performance

Report parameters assist in narrowing down a report for better analysis.

2,614 reads

Navigation Options in Reports

  • by Vasant Raj

You can build some very complex reports in Reporting Services and longtime author Raj Vasant brings us a short tutorial on how you can implement internal navigation structures in your reports.

5,148 reads

Gantt charting for the holidays

  • by David Leibowitz
  • SQLServerCentral

Gantt charts in SSRS aren’t included in the 2005 release. You can purchase a third-party add-on or, if you can wait, these powerful project tools might make it in the 2008 release. Alternatively, you can do this now with the native RS Chart control by using the methods David Leibowitz provides.

4.96 ( 23 )

13,198 reads

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Professional SSRS 2016 Preview: Chapter 6–Grouping and Totals

This post is part of the series: “ Professional SSRS 2016 Book Preview Posts ” which are excerpts for my Wrox Press book: Professional SQL Server 2016 Reporting Services and Mobile Reports .  Each of the posts is a condensed version of the material covered in a corresponding chapter from the book.  The goal for this posts is to provide useful and meaningful information you can use.  For more comprehensive details, I refer readers to the rather lengthy book itself.  Note that large portions are copied directly from the book manuscript that may refer to figures and screen capture images that have been removed for brevity.

  • Introducing SQL Server Data Tools for Visual Studio
  • Sample reports projects and exercises
  • Using the graphical Query Designer
  • Understanding query groups and table joins
  • Understanding report data flow
  • Understanding report groups
  • Grasping expression basics
  • Utilizing group sorting and visibility

This chapter introduces and explains one of the most fundamental and essential concepts in SSRS report design. All of the data regions—tables, matrices, lists, and visual controls like charts—rely on groups. You will get started with the sample report project in SSDT that contains several completed examples. You will learn the differences between grouping and aggregating rows in queries and performing grouping on datasets within a report region. We explain the flow of data through the report along with the opportunities to filter, group, and aggregate values at multiple points along this path. You will add groups and related header tiles and footer totals to groups in a table and then a matrix. Then you’ll see how multiple groups for hierarchies of grouped results make for effective reporting.

Another important topic, essential to effective report design, is the use of expressions to define groups and properties. We introduce aggregate functions and aggregate scope, which will be expanded in Chapter 7. Finally, you will learn to design a table report with multi-level drill- down navigation so users can explore details within summary groups as needed.

SQL SERVER DATA TOOLS

So far, you have been working with Report Builder, the simplest of the two report design tools. Before moving into the next report design topic, you should become familiar with the other report designer: SQL Server Data Tools (SSDT) for Visual Studio. This report tool was created primarily with the IT professional in mind. An earlier version of the Visual Studio report design tool was once called Business Intelligence Development Studio (BIDS).

Honestly, most of the report design features in both Report Builder and SSDT are the same. Conversely, subtle differences and some capabilities in SSDT don’t exist in Report Builder. The tool you choose to use to accomplish your day-to-day report design work will likely depend on your organizational role and the complexity of the report design solutions. Before making a final decision, you should learn to use both tools, and then decide which one is right for you and your project.

NOTE A f t e r several years of experience, I have changed my approach to teaching people to use both design tools. Even so, it is continually a quandary for instructors and authors to develop the best instructional method. In classes, and in previous editions of this book, I gave instructions using one tool, and then

c a l l – o u t some of the differences along the way. Because there are so many subtle differences, this approach can confuse new users. Consequently, if you really want to learn serious report design, educate yourself on both tools, one at a time; and then make your choice.

Getting Started

Before you get started using SSDT, decide where you prefer to store the project files. As you’ll see when you create a new project, the default project file path is in your Windows Documents library. If you are new to Visual Studio, I recommend you use the default project path for this quick introductory walkthrough. I like to keep mine in a folder named Projects in my OneDrive, or in a folder named Projects right off the root of the main storage drive. Whatever your choice, decide now, and create a folder named Projects in your preferred storage location.

The following numbered steps are not a complete exercise, but rather a few simple instructions to help you create a new project. The project and report names you use in this little practice run are not critical so just use some names that make sense to you. You will quickly move on and not use this project and report in future exercises.

1. The first thing is to open SQL Server Data Tools 2015. Depending on your version of Windows, either use the SQL Server 2016 program group, or just type the name of the program until you see it in the search results.

TIP B e c a u se S Q L S e r v e r Data Tools now installs as a separate setup package from the link provided in the SQL Server Installation Center, it may not appear under the SQL Server 2016 program group. This may change over time with sub- sequent version upgrades, or if you had upgraded from a previous version of SQL Server. On my Windows 2012 development server, which has a fresh install, SQL Server Data Tools 2015 shows up in the Apps list under the letter “S.”

2. Run SQL Server Data Tools 2015.

NOTE R u n ni n g Visual Studio 2015 is exactly the same as running SQL Server Data Tools 2015. Keep in mind that Visual Studio 2015 was the current version of Visual Studio at the time of publication. Newer versions of Visual Studio will work with appropriate updates to the SSDT add-in.

3. After the Visual Studio shell opens, from the File menu, choose New ➪ Project….

4. The New Project dialog opens. From the Templates pane on the left, expand Business

Intelligence and choose Reporting Services, as shown in Figure 6-1.

clip_image005

FIGURE 6-1: Choosing Reporting Services from the New Project dialog.

5. Within the project templates list in the middle of the dialog, select Report Server Project.

6. At the bottom of the window, Enter the name for the project as My first report project

NOTE T h i s should be a short name including spaces and descriptive text, which will create a folder and file names. I usually use a name that briefly describes

t h e purpose of the project and that I will recognize later. The project name I just recommended is only to get you started and give you some experience with new project setup. As I mentioned earlier, you will soon leave this project behind and then open an existing project I have prepared for you.

7. Note the default project folder path. If you prefer, use the Browse button to navigate to a

Projects folder you created earlier to complete the path in the Location box.

The Solution name will be the same as the Project name unless you change it. In larger,

8. If the project won’t be managed as part of a larger solution, just leave the names the same.

When you’re finished making changes, click the OK button.

9. With a report project open and the Solution Explorer visible on the right-side of the SSDT/ Visual Studio main window, right-click the Reports folder and choose Add ➪ Existing Item….

10. From the Add New dialog, select Report and then give the report file a name. This should normally be a friendly name with spaces and mixed case. When you’re ready, click the Add button.

When the Report Designer opens, notice there are different window panes containing controls and properties docked to both sides of the main window. These are labeled in Figure 6-2, and the key that follows explains the labels. The four tool windows used for reports design include, on the left, the Report Data and Toolbox windows arranged as tabs, and, on the right, the Solution Explorer and Properties windows arranged as docked windows, one above the other with a sliding separation bar. You will see additional windows that you can ignore or hide if you prefer.

1. Report Formatting toolbars

Set properties for the currently selected object or objects in the Report Designer when in Design view. Standard properties include Font Name, Font Size, Weight, Italic, Underline, Foreground color, Background color, Alignment, List styles, and Indentation.

FIGURE 6-2: Window panes in Report Designer.

2. Report Designer view selection tabs

Switch between Design and Preview.

3. Report Data window

The Toolbox window is also displayed in this area. Switch windows using the selection tabs at the bottom of the window pane.

4. Solution Explorer window

Navigate the solution, projects, and project files. Right-click to set project properties, add new items, and perform actions.

5. Report Designer window

This is the main design canvas used to design reports and set properties for data regions and report items.

6. Grouping window

Add, remove, and set properties for row and column groups.

7. Properties windo w

Displays and manages all properties for the selected object or group of selected objects.

8. Tabbed doc uments selection

In the default view, windows in this pane are selected using these tabs. The Report Data and Toolbox are used for report design, and other windows are available. Windows can be added with the View menu. Use the window pane toolbar to hide, move, pin, and auto-hide windows.

9. Sliding window separator

In the default view, windows in this pane are docked with a movable separator bar. The Solution Explorer and Properties windows are used for report design and solution management. Use the window pane toolbar to hide, move, pin, and auto-hide windows.

The best way to get started with Visual Studio is to create a test project and spend some time familiarizing yourself with the interface. You can dock, hide, and show windows so that you have what you need at your fingertips, as well as remove the ones you don’t need. If you are new to Visual Studio, don’t go crazy moving things around and closing windows until you are familiar with these tools enough to set things back.

Each window has a familiar pushpin icon that will either “pin” the window in place, or allow it to auto-hide when the mouse moves away from it. Use the auto-hide feature and resize tool windows rather than closing windows you’ll need again. If you close or hide a window using the right-click menu or drop-down arrow in the window header, you can get it back using the View menu. Some

of these windows are only available on the View menu when certain items are selected in the design interface. For example, if you were to close the Report Data window, the option to display it again is listed on the View menu only when you set focus to the report in the designer.

In the next section you will be using a sample solution that contains a set of existing projects pro- vided for you. If prompted to save your changes to this new project, answer “Yes,” but you’re not actually going to use that project any more.

Getting Started with Sample Reports Projects

As mentioned in Chapter 3, copies of all the reports used throughout the book are provided on this book’s website at www.wrox.co . These are organized into two projects contained in a Visual Studio solution in the folders you extract with the book download sample files.

1. On the File menu, select Open ➪ Project / Solution….

2. Use the Open File dialog to navigate to the location where you extracted the book sample files. Locate and open the folder named Wrox SSRS 2016 Report Solution

3. Open the file named Wrox SSRS 2016 Report Solution.sln. SSDT opens with the solution and related projects listed in the Object Explorer on the right side of the report design interface. Figure 6-3 shows SSDT open with a report open in the Design window.

The solution contains multiple projects. Use the Solution Explorer window on the right side of the main window to expand and collapse each project in order to access the files it contains. Reports and shared data sources are also added in Solution Explorer.

Generally, you should use shared data sources as a best practice. The only reason typically not to use them is when you want a report to be completely self-contained. But otherwise, having one place to manage data source connection information makes a lot of sense when these objects all get shipped off to the report server and are making important things happen out there in Business User Land.

FIGURE 6-3: A report in the Design window.

Shared datasets have advantages under very specific circumstances and are required when designing mobile reports. Therefore, you will use them in chapters 17, 18, 19, and 20. As a default choice, for most paginated reports, I generally recommend using embedded datasets.

The Wrox SSRS 2016 Exercises project contains finished copies of all the exercises found at the end of each chapter. The Wrox SSRS 2016 Samples project includes many topic-specific examples, each prefixed with the chapter number. For the chapter samples, it is important to realize that explicit instructions are not provided for every single click and menu selection. Because you have already mastered the essentials getting started creating a new report, data source, and dataset, there is no need to repeat those steps. Some unfinished reports are provided with steps completed up to

the task or topic that you can finish. You will be instructed as to which report to open. In some of cases, you’ll examine a completed report to see how it works.

To follow along, open a copy of the sample report, rather than making changes to the original, so you can go back and start over if necessary. The best way to do that is to copy and paste the report in the Solution Explorer.

TIP W h e n e ver y o u use the copy/paste method to back up a report, ensure that you save your changes first, because this technique actually makes a copy of the last saved file, rather than the version of the report sitting in memory that may have unsaved changes.

This step is pretty simple, but not entirely intuitive at first. My preferred method is to select the report in Solution Explorer (not the report file name as if renaming the file, but just click once to select the report), and then use Ctrl+C (copy) and Ctrl+V (paste). If you right-click to Copy, then

you must right-click the project to Paste. I typically remove the Copy of file prefix and then describe the backup state in parentheses at the end of the filename. You can see this pattern in the sample project.

TIP M ak i n g regular backups provides peace of mind by ensuring that you don’t lose your work. There are three common backup methods for reports: using a version control system such as TFS, making routine copies of the report files in the project folder to a different storage location, and keeping a secondary report project in the solution. The latter allows you to use the earlier-mentioned copy- and-paste backup method.

If you are disciplined about using integrated version control, then it can provide a safety net to prevent catastrophic loss. However, it doesn’t help with the typical cadence of report design. When working on a challenging report, I often create and keep one or more backup copies of the report I’m working on. This provides a way to experiment with different techniques without messing up a

working design. If something goes wrong, rather than trying to untangle it, you can just revert to an earlier copy. After testing to make sure the design is working, you can delete the backup copies.

Graphical Query Designer

The reason there are different query tools is that report developers have varied levels of experience with T-SQL. Therefore, individuals develop strong preferences. When I began my career with databases, at first I relied on graphical query design tools. Now I find it easier to hand-write queries rather than using the graphical Query Designer. These helper tools exist for good reason, and they work well. If you are not a T-SQL aficionado, you should learn how to use the graphical query design tool, but it shouldn’t always be your central experience. With a little practice, many find it easier to hand-code simple queries, rather than relying on the Query Designer.

When using SQL Server as a data source, the dataset in Query Designer is more elaborate than in Report Builder. This graphical Query Designer is actually borrowed from the SQL Server client tools, and is also available in SQL Server Management Studio (SSMS).

Open the Sample Report

If you don’t already have the Wrox SSRS 2016 Report Solution open in SSDT, open it now.

1. In the Object Explorer, expand the Wrox SSRS 2016 Samples project.

2. Expand the Reports folder.

3. In the Object Explorer, click once to select the Ch06 – Sales Summary (query completed report.

TIP T h e r e is a subtle difference between selecting an object and selecting the name of the object. For example, when you select a report in Object Explorer, the entire line is highlighted. If you click once and then click again, the name

of the report is selected. The same behavior applies to textboxes in the designer where clicking twice will select the text within the object, rather than the object itself. If this happens, click outside the object and then click it once.

4. Press Ctrl+C and then Ctrl+V to make a copy of the report. The new report should appear in the Object Explorer preceded by the text Copy of in the filename.

5. Double-click the new report copy to open it in the designer.

6. Expand the Datasets node in the Report Data window.

7. Right-click the SalesSummaryMonthProductRegion dataset and select Query… to open the graphical Query Designer.

8. Use this finished query to familiarize yourself with the query design interface as you read the following description about designing a query with this tool. When you’re finished, cancel out of the Query Designer and don’t save changes.

9. Optionally, create a new dataset in this report and follow these steps to duplicate this query.

Save your changes, and then switch back to the original query to check the results.

To design a query in SSDT, create a dataset just like you did previously using Report Builder. Click the Query Designer… button to open the graphical Query Designer. You add tables to the query by using the right-most icon on the toolbar. In the example shown in Figure 6-4 is the end result after completing the following steps. Please note that the following six steps are for reference and are explained in greater detailer in the subsequent steps.

clip_image015

FIGURE 6-4: Tables added to Query Designer.

1. Add the Date Sales, and Product tables.

2. Verify that the join lines were added by the query designer.

3. Use the check boxes to select the fields you see in the example: MonthNumber, MonthName ProductCategory ProductSubcategory ProductName, OrderQuantity, and SalesAmount.

4. Click the Use Group By button on the toolbar.

5. For the OrderQuantity and SalesAmount fields, use the drop-down list to change the Group

By column selection to Sum

6. For the OrderQuantity and SalesAmount fields, copy and paste the field names (in the

“Column” column) to the Alias column.

The designer has four panes separated vertically. The top pane shows the tables that were added to the query, joins, and selected columns. The second pane lets you rename columns, group, aggregate, filter the results, and apply parameters. The query text pane shows the T-SQL script generated by

the designer, and the results pane shows the rows returned by the executed query. Test and run the query by clicking the red exclamation mark icon on the toolbar.

Grouping Query Results

Figure 6-4 shows the Group By button on the toolbar (second from the right). This option adds every column to the GROUP BY clause in the query, and displays the text “Group By” in the seventh column of the fields list pane.

Any numeric columns that should be aggregated (rather than grouped in the query) must have the Group By option changed. Figure 6-5 shows the OrderQuantity and SalesAmount columns changed from Group By to Sum. When you do this, a column alias is created for each aggregated column using placeholder names like Expr1. These should be changed to something that makes more sense. You can actually use the original column name for the alias. To differentiate the alias

from the original column name, the original names have been appended with the word Total, so the alias names are OrderQuantityTotal and SalesAmountTotal

clip_image017

FIGURE 6-5: Column names modified.

Despite the desire to filter data by the Year, that column doesn’t need to be returned in the query results. You can see that the Year column has been included from the Date table and the Output checkbox has been unchecked. To add a parameter, type the parameter name you want to use in the Filter cell preceded by an equal sign (=) and an ampersand (@). In the graphical query designer, a column that is used only for filtering is excluded from the GROUP BY list by changing the selection to “Where.” In this case, the designer references the Year column in a HAVING clause to implement filtering.

Query Joins and Join Types

Because these tables have relationships defined in the database, the tables are automatically joined

on the key columns. In the example, the Sales table is joined to the Date table using the key column in the Date table named TheDate, and the OrderDate column in the Sales table. Likewise, Sales and Product tables are joined on the respective ProductKey columns.

The designer applies inner joins by default, meaning that there must be matching records on both sides of the join. The Date table contains one record for every day in the years 2005 through 2014, but there are not orders for every date, so the inner join will only return date information for exist- ing orders. An outer join returns all records from a specified table on one side of the join, and then all the matching records from the table on the other side.

Changing the join type is simple. Right-click the diamond on the line between the two tables and select all rows from the table on the outer side. In this case, you right-click the join between Date and Sales tables, and then choose “Select All rows from Date.” The join will be designated either LEFT OUTER RIGHT OUTER, depending on the order they were added in the Query Designer.

Execute the query by clicking the exclamation mark icon in the toolbar. When prompted for the

Year parameter, type 2013 , and then click OK. Notice in the results pane, a row for December,

2013, for which there are no existing orders. This is the effect of the outer join.

Report Data Flow

You will find that most objects within the Report Designer have many properties and features, and many of them are only used when necessary. Standard objects you will use in the reports all the time have properties that you probably won’t use in most reports, but they provide tremendous flexibility and opportunities to address unique business requirements using creative design techniques.

Some of these properties are depicted in Figure 6-6. You can see (moving left to right) that after query results are processed in a dataset query, an optional filter can be applied after the results are produced. Likewise, conditional filters can be applied as the data enters any data region. After that, the results can be sorted before they are presented to the first group expression. Each data region handles grouping a little differently, but the core concepts are the same. Some data regions support multiple levels of grouping.

clip_image019

FIGURE 6-6: Using Report Designer properties and features.

REPORT GROUPS

R e port groups are one of the most important concepts in fundamental report design. Whether your reports will visualize data in a table, matrix, or any type of chart, you will define group expressions either implicitly by dragging and dropping fields in the designer, or explicitly by writing

the expression. Table and list reports do not require a group to be defined, but have limited utility without any groups. Beginning with a table, let’s a take a look at how groups work.

1. In SSDT, if you have any unsaved work you want to keep, save that report.

2. From the Object Explorer on the right, open the report named Ch06 – Sales Summary with Groups

3. You can see that the table is bound to the dataset named

SalesSummaryMonthProductRegion

4. In the Row Groups list below the designer window, use the down arrow button next to the ProductCategory group to choose Group Properties… to open the properties dialog for that group, as shown in Figure 6-7.

It’s probably no surprise that the “Group expression” refers to the ProductCategory field (see Figure 6-8), but there are several optional properties and features associated with groups. For example, page breaks can be managed for each group, so a new page is inserted before or after the grouped field value changes.

5. Use the page list on the left to switch to the Page Breaks page in the Group Properties dialog shown in Figure 6-9.

clip_image023

FIGURE 6-8: ProductCategory field as a group expression.

clip_image025

FIGURE 6-9: Managing page breaks.

6. Check the first box to set a page break between each instance of a group.

7. Leave the other checkboxes unchecked.

8. Click the OK button to accept the group changes and close the Group Properties dialog.

There’s no need to inspect the ProductSubcategory group because it’s set to group by the ProductSubcategory field. But, what about the Details) group? This group isn’t normally set to group records for a field by default, so let’s find out if someone might have changed it.

9. Edit the group properties for the Details) group and open the Group Properties dialog.

Hey, look at that! The (Details group has been changed and set up to group by the ProductName field. Why would this make a difference when that field is already being grouped within the detail rows returned by the query? It seems senseless to explicitly group by the lowest-level value that is being returned in the query results.

10. Now check the behavior in the Table Designer. Hover the mouse pointer over and

re-select the two numeric fields for the detail cells in the Order Quantity and Sales Amount columns. You see that the designer applies the SUM function. This is because of the group definition in that Detail group.

The first two groups are set up for drill-down navigation by hiding levels that are expanded with toggle items. With any level collapsed, it is important that the value displayed on this line be an aggregated total representing all the hidden details. This will only happen if an aggregate function (such as SUM) has been applied in each expression on the detail row.

Adding Totals to a Table or Matrix Report

The table, matrix, and list data regions are all based on a common object called the Tablix, and many design techniques are similar for each of these data regions. Each of these objects provides dif- ferent layout options, but the fundamental concept of grouping is the same.

In a table, adding a total to a row group adds a new row that applies an aggregate function (for example, SUM) to all the members of t h a t group. The same applies to a total added to a matrix col- umn group. By adding a total row or column, you’re actually adding a total that applies to the p a r – ent of the group. Consider this example. Suppose columns are grouped by quarter and then by year. If you were to add a total to the Quarter column group, the total would be for all the quarters add- ing into the year. This means that a total applied to the topmost group will always return the grand total for all records in the data region. This is evident in the example shown in Figure 6-10 where the table data region is grouped and totaled by Subcategory.

clip_image027

FIGURE 6-10: Grouped table data region with totals.

Defining a total for a group at a lower level would create a subtotal break. Totals can be placed b e f o r e after group values. Adding totals b e f o r e a row group shows the total a b o ve the group in a heading row, and adding the total a f t e r shows the total b el o w the group in a footer row.

Subsequently, for a column group in a matrix data region, inserting a total b e f o r e the group places totals t o the left of the group. Adding totals a f t e r the group inserts a total column t o the right of the group.

Groups, headers, footers, and totals are all related design elements that can take a simple report to the next level and provide significant value. Groups are an essential design concept, and a number of more advanced capabilities have been added as Reporting Services has evolved through newer versions. At the group level, you can now conditionally control things like page breaks and page numbers.

As you continue to review the completed Ch06 – Sales Summary (query completed report, use the following steps to see how the report was be designed.

1. Switch back to Design view.

2. Take a look at the SalesAmount column. The heading label Sales Total was changed from the original field name to make it more readable.

3. The last column was added by right-clicking the header of the column to its left and choosing the option to add a new, blank column. The column label was changed to Avg Sales .

4. In the detail row, right-click the Avg Sales textbox and choose Expression….

5. Review the expression that was added: =AVG(Fields!SalesAmount.Value . Before you click OK to complete the expression, select and copy this text to the clipboard.

6. Right-click the textbox in the Total row of the new column and choose Expression….

7. Review this expression that was added by pasting the expression you copied from the detail cell. Click the OK button.

8. Preview and test the report.

Expression Basics

Looking at the last few examples, when you create a field reference in the designer by dragging

and dropping or selecting from the field list, you will see a field or expression placeholder in square brackets, such as ProductCategory] [SUM(OrderQuantity)]. What you see in the designer

is actually a simplified version of the expression that is stored in the report definition. To view

the actual expression, right-click the placeholder text and choose Expression…. This opens the Expression Editor, showing the entire expression. Expressions always begin with an equal sign and contain a full object reference. Table 6-1 shows these two examples.

TABLE 6-1: Expression Placeholders

In this chapter’s exercise, you will see that expressions can include multiple functions and objects. When used in a data region bound to a dataset, an expression simply references a field or fields in that dataset, but can also reference different datasets, as you will see in more advanced examples in the following chapters. Expressions are the real magic behind both simple and complex report designs. This chapter introduces several features and capabilities that are demonstrated in greater detail in the chapters that follow.

Introducing Aggregate Functions and Totals

When you drop a numeric field into a group or table footer cell, an expression is added applying the SUM( aggregate function. The designer assumes that you will want to sum these values, but this function can be replaced with one of several others. Reporting Services supports several aggregate functions, similar to those supported by the T-SQL query language. For now, let’s just consider the concept of basic aggregation using the SUM function.

NOTE Y o u learn about all the functions in Chapter 7, “Advanced Design

T e c hn i q u e s . ”

When an aggregate function expression is used in a group detail, header, or footer row, the scope of the current data region or group is assumed. For example, suppose a table contains two nested groups based on the Category and Subcategory fields. If you were to drag the SalesAmount field into the Subcategory group footer, the SUM(SalesAmount expression would return the sum of all SalesAmount values within the scope of each distinct Subcategory group range.

You have a few options to sort data in a report, and the best choice will depend on a few factors. Other features (like grouping) require data to be sorted and may negate the capability to sort data after it is grouped. Be mindful that with a large result set, sorting is a costly operation that will add to the overall report execution time. Options may include the following:

➤ Sort Records in the Query —Typically, if records will be presented in a particular order, the most efficient method is to sort the data in the query before it gets to the report. This will aid in grouping as effectively as possible. If other sorting options will be used within the report, presorting in the query may be wasted cycles.

Table Interactive Sorting —Normally used in ungrouped table reports, this feature can be applied to any or all columns of the table. Interactive sorting is applied to the textbox in the table heading for each column. Clicking the sort icon displayed in the column header will

re-order rows the records displayed in that table. Clicking again toggles between ascending or descending order. In a table or matrix data region with groups, interactive sorting can be applied to the rows within a group.

Group Sorting —Every group has an optional Sorting expression. There are times when you may need to group on one set of field values and sort on a different set of corresponding val- ues. This would make sense only when the grouping and sorting fields are distinct within the same set of value ranges.

An example of group sorting can be found in the sample report Ch06 – Orders Matrix Group

Sort. The design of this report is quite simple, and Figure 6-11 shows this in the SSDT designer.

clip_image030

FIGURE 6-11: Design of sample report.

This report began with two datasets. The main query returns the MonthNumber, MonthName Country, and OrderQuantity columns. Take a look at the Year parameter properties and you’ll see that the YearList dataset query is used to populate the parameter list. Edit the SalesSummaryByMonthCountry dataset and you’ll see that the Year parameter is used to filter the results in the query’s WHERE clause. The matrix is added to the report body. The MonthName

field is dragged and dropped onto the Rows header, the Country field is dropped onto the Columns

header, and the OrderQuantity field is dropped in the Values cell to create the expression

=SUM(Fields!OrderQuantity.Value .

If you were to preview the report at this point in the design process, it would look like Figure 6-12. Out of the box, the rows and column groups are naturally sorted alphabetically.

This is probably fine for the country names, but obviously not for the months, which are in alpha- betic order, but not sorted chronologically. This is the reason the MonthNumber column has been included in the query. To fix the group order, you switch to Design view and edit the row group.

Figure 6-13 shows the Sorting page of the Group Properties dialog for the row group.

clip_image032

FIGURE 6-12: Previewing the report.

clip_image034

FIGURE 6-13: Sorting page of the Group Properties dialog.

Changing the “Sort by” expression allows you to display one field and sort by another, as you can see with the report previewed in Figure 6-14.

FIGURE 6-14: Displaying by one field and sorting by another.

Share this:

  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Paul Turley

Paul Turley

Microsoft Data Platform MVP, Principal Consultant for 3Cloud Solutions Specializing in Business Intelligence, SQL Server solutions, Power BI, Analysis Services & Reporting Services.

Related Posts

Ci/cd & devops for power bi… are we there yet.

In my view, projects and teams of different sizes have different needs. I described DevOps maturity as a pyramid, where most projects don’t require a sophisticated DevOps implementation, and the most complex solutions do. The DevOps maturity is a progression, but only for projects of a certain scale. One of the following options might simply be the best fit for a particular project. Unless you are throwing together a simple Power BI report that you don’t plan to maintain and add features to, the first and most basic managed project should start with a PBIX file or Power BI Project folder stored in a shared and cloud-backed storage location. DevOps isn’t a requirement for all projects, but version control and shared file storage definitely is.

Power BI for the Enterprise Precon: Atlanta Feb 9th

Please come to the Atlanta SQL Saturday BI Edition in February, and join me for a full-day preconference event: Designing and Developing Enterprise-scale Solutions with Power BI

Conference Sessions and Presentations in late 2023 & 2024

Are you in the California Bay area? Do you want to learn to do Power BI and Fabric the right…

Leave a Reply Cancel reply

Discover more from paul turley's sql server bi blog.

Subscribe now to keep reading and get access to the full archive.

Type your email…

Continue reading

  • SQL Server training
  • Write for us!

Rajendra Gupta

Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups

This is the 25 th article in SQL Server Always On Availability Groups series. In this article, we will explore the high availability configuration for reporting services databases.

Configure SQL Server Reporting Services for standalone databases

SQL Server Reporting Services (commonly known as SSRS) is a service to deploy paginated reports for web and mobile. Data representation is an art, and SSRS helps you to visualize the data in an informative way using various graphs, charts, KPI, matrix.

SQL Server Reporting Services all together

Image Source: Microsoft docs

Until SQL Server 2016, the reporting services comes with the SQL Server installation. If we choose the reporting services feature, it installs the reporting services and configures its databases in the local instance.

Starting from SQL Server 2017, Microsoft separated the reporting services from the standard SQL Server installation. It is a standalone application that you can download, install, upgrade independently.

In the below image, you see the SSRS architecture diagram with various components involved. You can refer to the article, SQL Server Reporting Services Architecture and Component Topology to understand these components in detail.

SSRS components

As per best practice, we should use the reporting services separated from the database instance. For this article, firstly, we configure the reporting services for the following SQL environment.

  • Reporting Services: SQLNode3\INST1
  • Reporting services database: SQLNode1\INST1

It creates the [ReportServer] and [ReportServerTempDB] database on the SQLNode1\INST1. As of now, these databases are not part of any SQL Server Always On Availability Groups.

You can download the Microsoft SQL Server 2019 Reporting Services from the Microsoft.

Microsoft SQL Server 2019 Reporting Services

Launch the SSRS installation with the following welcome screen.

Launch the SSRS installation

Choose an edition. We use the developer edition of SQL Server Reporting Services 2019.

Choose an edition

On the next page, it shows that it installs the reporting services only. It does not install the SQL database engine for you that is required to store SSRS databases. You should install SQL Services on it or a separate instance. As highlighted earlier, we use the SQLNode1\INST1 for the SSRS databases.

install the reporting services

Select the reporting services installation directory. We can go ahead with the default directory.

installation directory

After installation, it asks you to configure the report server using the Reporting Services Configuration Manager. Click on Configure Report Server.

It opens the Reporting Services Configuration Manager. Click on Start to run the reporting services.

configure the report server

We do a minimum required configuration in this article. You can refer to this article, SQL Server Reporting Service Configuration Manager to understand complete SSRS configurations.

Service account configuration: In the service account page, specify a domain account to run the reporting service. It automatically restarts the reporting services once you apply the service accounts

Service account configuration

Web Service URL: It prefills the default configuration for the SSRS web service URL

Web Service URL

Click on Apply, and it configures the web service URL. You can click on the hyperlink to open the web URL.

Web URL

Report Server Database configuration

First, we need to create the reporting services databases on the SQL instance that works as a primary replica. Currently, it does not show any SSRS database details

Report Server Database configuration

Click on Change Database. We do not have any existing database so choose “Create a new report server database”

Create a new report server

Specify the SQL instance name (Current primary replica) and authentication type. I use Windows authentication for the DB connection purpose

Specify the SQL instance

You get the flexibility to specify the database name in the report server wizard. By default, it creates a new database [ReportServer]. You should not change the database names unless you have any specific requirements

report server wizard

Specify the service credentials to connect with the report server database. SSRS wizard automatically assigns the required permissions for the SQL account we specify here

service credentials

Verify your configurations in the report server database configuration. You can note here that it creates two SSRS databases – ReportServer and ReportServerTempDB

Verify your configurations

On the next page, it completes the SSRS configurations, as shown below.

SSRS configurations

You can see the database name, SQL instance and the credentials in the database page.

database name, SQL instance

Similar to the Web Server URL, configure the Web Portal URL. You get the hyperlink for URL after configuration

configure the Web Portal URL

Connect to the SQL Server instance, and you can see both databases in the object explorer

Connect to the SQL Server

Change the recovery model for SSRS databases

As shown above, reporting services databases have the following recovery models:

  • ReportServer – Full
  • ReportServerTempDB- Simple

As you know, for a database in SQL Server Always On Availability groups, we require databases in the full recovery model. Open the ReportServerTempDB database properties, click on the options and change the recovery model to FULL.

Our both SSRS databases are in the Full recovery model now.

Change recovery model for SSRS databases

SQL Server Always On Availability Groups for Reporting services database

In this article, we create a new availability group for both reporting services databases. You can add databases in an existing availability group as well. You can refer to earlier articles for detailed steps on creating a new availability group.

In the new availability group wizard, specify the AG group name.

availability group wizard

I took full backups of both databases; therefore, it meets prerequisites to be part of an AG.

SQL Server Always On Availability Groups

We have two nodes in the cluster. AG should be in the Synchronized mode.

Specify replicas

Specify listener name and IP address for this SQL Server Always On Availability Group. SQL listener is a must for the reporting services database in AG configuration.

SQL listener configurations

Once it creates the new SQL Server Always On Availability Group, launch the dashboard, and we see reporting services databases added successfully.

AG dashboard

Deploy a sample report and create a subscription

Deploy a report in your new SSRS configuration. You can refer to SSRS articles on SQLShack for reference purposes.

For this article, I deployed a sample SSRS report. This report connects to the [AdventureWorks] database and pulls the required data.

Deploy a sample report

I have also configured a report server subscription. This subscription export the report in PDF format and stores at a shared location on a scheduled time.

Report schedule

SSRS schedule creates a SQL Server agent job on the SSRS database instance. Connect to your SQL instance and verify that the job exists. You can filter the agent jobs using the Report Server category. Do not modify this job manually in SSMS. You must use SSRS reports for any changes like report schedule, frequency, shared location or email id specifications.

SQL Server agent job

Reconfigure Report Server database configurations to SQL listener

At this step, we have done the following configurations so far in the article.

  • Our reporting services databases are configured in the SQL Server Always On Availability Group
  • We deployed a sample SSRS report

Think of a question now- If the AG failover happens and SSRS databases become active on the current secondary ( after failover – primary) what happens to my report subscriptions?

As you have seen earlier, the report subscription is a SQL agent job. Do you need to create these jobs on the new primary replica manually? I have hundreds of report subscriptions; Do I need to create all jobs?

We instructed earlier to not perform any changes to these jobs manually. If we script out jobs and configures on the new primary replica, Does it work?

Hold your horses! We have configured the databases in the high availability, but our report server is not aware of any changes? If you perform a failover, the report server won’t recognize the new primary replica instance.

Switch to the Report Server Configuration Manager -> Database -> Change Database ->Choose an existing report server database.

Report Server database configurations

In the server name, specify the SQL listener that we created earlier for SSRS SQL Server Always On Availability Group. A SQL listener always points to the primary replica. Once we configure the listener in the report server configuration, in case of failover, it automatically connects to the databases on a new primary replica.

SQL listener

Select the report server database from the drop-down.

the report server database

Specify the service credentials.

service credentials

Verify your configuration. You can verify the SQL Server instance is the SQL listener name along with the port number.

SQL Server instance

It reconfigures your report server configuration.

report server configuration

Verify the listener’s name in the database tab.

Verify the listener

Failover testing for SQL Server Reporting Services (SSRS) in a high availability mode

For testing purposes, perform a manual failover. It makes the SQLNode2\INST1 as the new primary replica.

Failover testing for SQL Server Reporting Services

Our first test passed. AG dashboard is healthy for both SSRS databases after failover as well.

Healthy dashboard

Launch the SSRS portal and view the sample report we deployed. It is also working as expected. It passes the second validation.

Launch the SSRS portal

The third validation is for the report server subscription. Select the report subscription and run it manually.

server subscription

It runs fine. You can verify the last run and the result. In case of any error, you get an error message in the result column.

Subscription status

For the final validation, instead of running the report subscription manually, schedule it to run after 2-3 minutes from the current time. It also runs successfully.

final validation

Now it is time to recall your earlier questions. We can see the report schedule SQL agent job on the new primary replica. We have not created it manually.

Job status

SSRS automatically creates the report subscription jobs on the new primary replica. Microsoft recommends restarting the SQL Server reporting services after the AG failover as well. You should do the following tasks in case any failover happens using a SQL agent job.

  • Restart SQL Server reporting services after failover
  • Remove the SSRS jobs from the old primary replica. It ensures that during failback, all SSRS jobs are created with the latest configurations

In this article, we configured the SQL Server Reporting Services in a high availability solution using SQL Server Always On Availability Configuration. It ensures your reports are always running if one of your database instances has some issues. You can go through these Microsoft documentations: High availability in SQL Server Reporting Services and Reporting Services with Always On Availability Groups (SQL Server) .

Table of contents

  • Recent Posts

Rajendra Gupta

  • How to install PostgreSQL on Ubuntu - July 13, 2023
  • How to use the CROSSTAB function in PostgreSQL - February 17, 2023
  • Learn the PostgreSQL COALESCE command - January 19, 2023

Related posts:

  • Explore failover types in SQL Server Always On Availability Groups
  • Monitor and failover a Distributed SQL Server Always On Availability Group
  • Explore dynamic management views for monitoring SQL Server Always On Availability Groups
  • Configure SQL Server replication for a database in SQL Server Always On Availability Groups
  • Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups

Grouping Data in SQL Server

Author's photo

People who work with data know that data grouping can sometimes be a chore. If you struggle and waste hours with Excel, OpenOffice, or some other tool trying to group various sets of data, take a look at this article and learn how to do the work much quicker and easier by using SQL.

You often find yourself in a situation where you need to analyze data and present the results in a form other than the one in which the data is stored. For example, there are situations where you need to group individual data according to the level of aggregation defined by a user request. In this article, you will explore how SQL can help you when it comes to effective data manipulation in terms of grouping individual data.

You work in the HR department of a startup company. The company has branches throughout Italy, and the CEO has decided to carry out workforce planning. The CEO has requested the following information:

  • Number of employees per location.
  • Number of employees per department at each location.

This information is internally stored in the company's HR SQL Server database, which contains a table with data on employees. Your job is to analyze the data stored there and provide the requested information to the CEO.

Sample Data Table

The table below is named Employees and contains sample data that will be analyzed and manipulated throughout the article examples.

In order to graphically present the grouping process in the examples that follow, you will sort sample data by location and department. Such table data will be used as a starting point for explaining the data grouping process.

You will sort the data using the following query:

Now, the sample data table looks like this:

As you can see, the data in the table is sorted in ascending order, first by location and then by department.

SQL GROUP BY Clause

The tasks defined by the CEO all require data grouping. In SQL, data grouping is performed using a GROUP BY clause.

The SQL GROUP BY clause allows us to group individual data based on defined criteria. You can group individual data by one or more table columns. In order to do the grouping properly, you often need to apply aggregate functions to the column(s) within the SQL SELECT statement.

Syntax of GROUP BY Clause

The syntax of the GROUP BY clause is the following:

Please note: When it comes to the syntax, it's important to keep in mind the place of the GROUP BY clause within the SELECT statement. Always write a GROUP BY statement after the FROM and/or WHERE clauses and before the ORDER BY clause (if one exists).

As you can see, in order to use the GROUP BY SQL clause, you need to know the basics of querying data in SQL. You can brush up on those basics in our SQL Basics in MS SQL Server course .

If you're already familiar with writing basic SQL SELECT statements, learning how to group data will be a piece of cake. Let's see how GROUP BY works in practice.

SQL GROUP BY Examples

You will solve the tasks presented in the scenario. First, you'll write a basic GROUP BY query. Afterwards, you'll go through a bit of a more complicated example in order to see how grouping can be performed on more than one table column.

Task #1: Get the Number of Employees per Location

Query analysis:.

Let's take a closer look at the process of building the above query:

Select the Location column; you will group your data using this column.

Add another column to the SELECT statement by applying the COUNT function on one of the table columns in order to get the number of employees. It doesn't matter which table column you apply the COUNT function to, since it is used to count the table rows.

3. The previous query will not work without a GROUP BY clause since it contains an aggregate function. Insert a GROUP BY clause and use the Location column for grouping. Since the column contains five different values (Florence, Milan, Rome, Turin and Verona), the final query result will have five rows.

The table below displays how the grouping is done: the data is grouped based on distinct values in the Location column, where each drawn rectangle contains data for a particular location. In other words, each rectangle represents one data group.The COUNT function will count the rows within the groups and return the number of employees within a location.

GROUP BY clause

Apply an ORDER BY clause on the Location column in order to sort the final query results in ascending order based on values in that column.

Final Result:

Task #2: get the number of employees per department at each location.

Select the Location and Department columns; you will group your data using these columns.

Like in the previous example, add another column to the SELECT statement by applying the COUNT function to one of the table columns in order to get the number of employees.

Again, the query from the previous step won't work without a GROUP BY clause since it contains an aggregate function. You now apply a GROUP BY clause on both the Location and Department columns. First, you group data by location; then you group those locations by department, effectively creating department subgroups within the location groups.

The table below shows how this grouping is done: blue rectangles represent the groups based on Location , and red rectangles represent the subgroups based on Department . Notice how the department subgroups are made within the location groups. The COUNT function will count the rows within the subgroup of each group and return the number of employees per department at each location.

GROUP BY clause on Location and Department

Apply an ORDER BY clause on the Location column in order to sort the query results in ascending order by location.

Grouping data is a common operation when it comes to data manipulation. Being able to group data easily is a very useful skill when working with datasets of different sizes. You have seen how the SQL GROUP BY clause makes grouping easy: all you need is basic knowledge of SQL and a couple of minutes to write the SQL query. In case you have it, it will be a pity if you don't upgrade it with GROUP BY functionality and get the most of it when it comes to data grouping.

This article reviewed the basics of data grouping in SQL Server, but if you'd like to explore more advanced SQL Server grouping options, be sure to check out our course on GROUP BY Extensions in SQL Server . However, if you're new to SQL, take a look at some of LearnSQL's SQL Server courses to get started.

You may also like

sql server reporting services grouping data

How Do You Write a SELECT Statement in SQL?

sql server reporting services grouping data

What Is a Foreign Key in SQL?

sql server reporting services grouping data

Enumerate and Explain All the Basic Elements of an SQL Query

  • Recent Articles
  • SQL Server 2012
  • SSRS - Reporting Services
  • SSIS - Integration Services
  • SSAS - Analysis Services
  • Dot Net (C#)
  • WCF - Windows Communication Foundation

Grouping Data in SSRS

  • Right click on table and go to properties. It opens a new window. 
  • You will find an another window for Grouping and Sorting properties.

Retrieve accurate row count for table Read more...

What is the difference between select count(*) and count(1) in sql server? Click to see Answer

  • Contributors

Venkateswarlu Cherukuru, Post-Graduate from Sri Venkateswara University, Tirupati, India. Microsoft Certified Professional in Dot Net, SQL Server & HTML5 Technologies.

Copyright © 2016. All Rights Reserved.

www.venkateswarlu.net

Generate SSRS Report On Demand with PowerShell and a Web Service

By: Jeffrey Yao   |   Updated: 2024-02-14   |   Comments   |   Related: > Reporting Services Development

There are times when we need to generate the same SSRS report over and over again but using different parameters for each run. We may also need to generate a report in a specific format, such as PDF or Excel, and then send it to different users.

For example, let's assume I am a teacher with an SSRS report that can generate a student's test score report based on the student ID, and I want to send each student their score report by email. How can I accomplish this without having to manually run the report over and over again using different parameters for each run?

SSRS provides functionality through an XML web service with a SOAP API. As such, if we can make a program send a correct web service request with the right parameter values, we should be able to get the SSRS to render a report with the needed format.

Fortunately, in PowerShell, we have a cmdlet called invoke-webrequest that can make a request to a web service. As long as we compose the right request, i.e., with proper parameter values for the SSRS service, SSRS will generate the right report with the correct format (PDF of a CSV or HTML, etc.).

Let's demonstrate how this works. First, we need to create a demo package composed of a simple SSRS report on three simple tables plus one PS script:

Create a Simple SSRS Report

As shown below, it is a straightforward report, so I won't explain how to create it. However, I will provide the key information about what data the report will use (all screenshots are from SSRS Report Builder ).

ScoreReport Layout

I created a data source named [dsScore], which is defined as the following:

data source [dsStore]

Based on this data source, two datasets were created:

  • [dsetStudentID] with the following setup. This dataset is to provide values for the report parameter, i.e., [StudentID].

dataset [dsetStudentID]

  • [dsetScore]

dataset [dsetScore]

We also created an SSRS parameter [StudentID].

report parameter [StudentID]

This parameter's available values are from the dataset [dsetStudentID]

parameter values

After the report is published to the SSRS server, we run it. If we chose, for example, StudentID = 2, we will get the following report:

ScoreReport manual rendering

This report can be saved as a PDF file by clicking the save button and choosing "PDF." This score_report.pdf file will automatically save to your computer's [Download] folder.

Manully_saved_report

To do this for each student is very manual. First, we must choose a Student ID and then save the generated report to a PDF file. If we need to generate a score report for each student in a class, operating manually can be very tedious.

Create PowerShell Script to Run SSRS Report with Parameter

To automate this via programming, we can use the following PowerShell script to do the work. I will assume you have installed the latest PowerShell SQL Server module .

The report rendering command, i.e., rs:Format, can have one of the following common formats: PDF, CSV, XML, Word, Excel, or IMAGE. For details, please see the links in the Next Steps section of this tip.

One special note here is that the parameter name in the Uri link is case-sensitive and should be exactly the same as the parameter name used in the SSRS report. In my case, it is StudentID (see Fig_Parameter above). If I use studentid instead of StudentID, it will not work.

uri_param_case_sensititive

To run the script quickly, we can copy and paste it into a PS ISE window and run it, and we will find three PDF files generated:

Three_ScoreReport_Files

If I open ScoreReport_2.pdf with Acrobat Reader, I can see the following:

ScoreReport_2.pdf

Report Delivery via Email

With all reports generated, we can easily create a T-SQL script to send out each report to individual students.

Assuming we already have SQL Server database mail set up, we can use the following T-SQL script to do the work:

If I set @debug = 1 and run the T-SQL script in an SSMS query window, I will get the following printed T-SQL commands:

Generated_Delivery_code

We can examine the code generated and see whether it is logically correct. We can even copy and paste a few lines to run manually. Or we can set @debug = 0 so the whole delivery script can be executed. Of course, SQL Server database mail needs to be set up before any email can be sent out.

This tip uses PowerShell to generate an SSRS report via a web service request. Once the files are generated, we may further handle them for other purposes, such as sending them to end users.

We can generate SSRS reports in many other formats, such as Word, Excel, CSV, or XML formats, and these types of files may be consumed by other downstream applications. It extends the presentation of the data inside SQL Server databases.

We may also modify the PS script in this tip to be a function and package it into a customized module so we can use it more conveniently.

The following links provide more information about SSRS URL access details, which are the technical foundation of this tip. Also, at MSSQLTips, we have other tips about performing similar work within an SSIS package.

  • SSRS URL Access
  • SSRS Web Service URI Parameter .
  • Execute a SQL Server Reporting Services report from Integration Services Package

sql server categories

About the author

MSSQLTips author Jeffrey Yao

Comments For This Article

Related articles.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Reporting Services with Always On Availability Groups (SQL Server)

  • 14 contributors

This article contains information about configuring Reporting Services to work with Always On availability groups (AG) in SQL Server. The three scenarios for using Reporting Services and Always On availability groups are databases for report data sources, report server databases, and report design. The supported functionality and required configuration is different for the three scenarios.

A key benefit of using Always On availability groups with Reporting Services data sources is to use readable secondary replicas as a reporting data source while, at the same time, the secondary replicas are providing a failover for a primary database.

For general information on Always On availability groups, see Always On FAQ for SQL Server 2012 (../../../sql-server/index.yml) .

Requirements for using Reporting Services and Always On Availability Groups

SQL Server Reporting Services and Power BI Report Server uses the .NET framework 4.0 and supports Always On availability groups connection string properties for use with data sources.

To use Always On availability groups with Reporting Services 2014, and earlier, you need to download and install a hotfix for .NET 3.5 SP1. The hotfix adds support to SQL Client for AG features and support of the connection string properties ApplicationIntent and MultiSubnetFailover . If the Hotfix isn't installed on each computer that hosts a report server, then users attempting to preview reports see an error message similar to the following, and the error message will be written to the report server trace log:

Error message: "Keyword not supported 'applicationintent'"

The message occurs when you include one of the Always On availability groups properties in the Reporting Services connection string, but the server doesn't recognize the property. The noted error message is seen when you select the 'Test Connection' button in Reporting Services user interfaces and when you preview the report if remote errors are enabled on the report servers.

For more information on the required hotfix, see KB 2654347A hotfix introduces support for the Always On features from SQL Server 2012 to the .NET Framework 3.5 SP1 .

For information on other Always On availability groups requirements, see Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server) .

Reporting Services configuration files such as RSreportserver.config are not supported as part of Always On availability groups functionality. If you manually make changes to a configuration file on one of the report servers, you will need to manually update the replicas.

Report Data Sources and Availability Groups

The behavior of Reporting Services data sources based on Always On availability groups can vary depending on how your administrator has configured the AG environment.

To utilize Always On availability groups for report data sources you need to configure the report data source connection string is to use the availability group Listener DNS name . Supported data sources are the following:

ODBC data source using SQL Native Client.

SQL Client, with the .NET hotfix applied to the report server.

The connection string can also contain new Always On connection properties that configure the report query requests to use secondary replica for read-only reporting. Use of secondary replica for reporting requests reduces the load on a read-write primary replica. The following illustration is an example of a three replica AG configuration where the Reporting Services data source connection strings have been configured with ApplicationIntent=ReadOnly. In this example, the report query requests are sent to a secondary replica and not the primary replica.

The following is an example connection string, where the [AvailabilityGroupListenerName] is the Listener DNS Name that was configured when replicas were created:

Data Source=[AvailabilityGroupListenerName];Initial Catalog = AdventureWorks2022; ApplicationIntent=ReadOnly

The Test Connection button in Reporting Services user interfaces validates if a connection can be established but it will not validate AG configuration. For example if you include ApplicationIntent in a connection string to a server that isn't part of AG, the extra parameter is ignored and the Test Connection button will only validate a connection can be established to the specified server.

Depending on how your reports are created and published will determine where you edit the connection string:

Native mode: Use the web portal for shared data sources and reports that are already published to a native mode report server.

SharePoint Mode: Use SharePoint configuration pages within the document libraries for reports that are already published to a SharePoint server.

Report Design: --- title: include file description: include file author: maggiesMSFT ms.author: maggies ms.date: 12/06/2018 ms.service: ms.topic: include ms.custom: include fileReport Builder or SQL Server Data Tools (SSDT) when you're creating new reports. See the 'Report Design' section in this article or more information.

Additional Resources:

Manage Report Data Sources

For more information on the available connection string properties, see Using Connection String Keywords with SQL Server Native Client .

For more information on availability group listeners, see Create or Configure an Availability Group Listener (SQL Server) .

Considerations: Secondary replicas will typically experience a delay in receiving data changes from the primary replica. The following factors can affect the update latency between the primary and secondary replicas:

The number of secondary replicas. The delay increases with each secondary replica added to the configuration.

Geographic location and distance between the primary and secondary replicas. For example the delay is typically larger if the secondary replicas are in a different data center than if they were in the same building as the primary replica.

Configuration of the availability mode for each replica. The availability mode determines whether the primary replica waits to commit transactions on a database until a secondary replica has written the transaction to disk. For more information, see the 'Availability Modes' section of Overview of Always On Availability Groups (SQL Server) .

When using a read-only secondary as a Reporting Services data source, it's important to ensure that data update latency meets the needs of the report users.

Report Design and Availability Groups

When designing reports in --- title: include file description: include file author: maggiesMSFT ms.author: maggies ms.date: 12/06/2018 ms.service: ms.topic: include ms.custom: include fileReport Builder or a report project in SQL Server Data Tools (SSDT), a user can configure a report data source connection string to contain new connection properties provided by Always On availability groups. Support for the new connection properties depends on where a user previews the report.

Local preview: --- title: include file description: include file author: maggiesMSFT ms.author: maggies ms.date: 12/06/2018 ms.service: ms.topic: include ms.custom: include fileReport Builder and SQL Server Data Tools (SSDT) use the .NET framework 4.0 and support Always On availability groups connection string properties.

Remote or server mode preview: If after publishing reports to the report server or using preview in --- title: include file description: include file author: maggiesMSFT ms.author: maggies ms.date: 12/06/2018 ms.service: ms.topic: include ms.custom: include fileReport Builder, you see an error similar to the following, it's an indication you're previewing reports against the report server and the .NET Framework 3.5 SP1 Hotfix for Always On availability groups hasn't been installed on the report server.

Report Server Databases and Availability Groups

Reporting Services and Power BI Report Server offers limited support for using Always On availability groups with report server databases. The report server databases can be configured in AG to be part of a replica; however Reporting Services won't automatically use a different replica for the report server databases when a failover occurs. The use of MultiSubnetFailover, with the report server databases, isn't supported.

Manual actions or custom automation scripts need to be used to complete the failover and recovery. Until these actions are completed, some features of the report server may not work correctly after the Always On availability groups failover.

When planning failover and disaster recovery for the report server databases, it is advised you always backup a copy of the report server encryption key.

Differences between SharePoint Native Mode

This section summarizes the differences between how SharePoint mode and Native mode report servers interact with Always On availability groups.

A SharePoint report server creates 3 databases for each Reporting Services service application you create. The connection to the report server databases in SharePoint mode is configured in SharePoint Central Administration when you create the service application. The default names of the databases include a GUID that is associated with the service application. The following are example database names, for a SharePoint mode report server:

ReportingService_85c08ac3c8e64d3cb400ad06ed5da5d6

ReportingService_85c08ac3c8e64d3cb400ad06ed5da5d6TempDB

ReportingService_85c08ac3c8e64d3cb400ad06ed5da5d6_Alerting

Native mode report servers use 2 databases. The following are example database names, for a native mode report server:

ReportServer

ReportServerTempDB

Native mode doesn't support or use the Alerting databases and related features. You configure native mode report servers in the Reporting Services Configuration Manager. For SharePoint mode, you configure the service application database name to be the name of the "client access point" you created as part of the SharePoint configuration. For more information on configuring SharePoint with Always On availability groups, see Configure and manage SQL Server availability groups for SharePoint Server (/previous-versions/office/sharepoint-server-2010/hh913923(v=office.14)) .

SharePoint mode report servers use a synchronization process between the Reporting Services service application databases and the SharePoint content databases. It is important to maintain the report server databases and content databases together. You should consider configuring them in the same availability groups so they failover and recover as a set. Consider the following scenario:

  • You restore or failover to a copy of the content database that has not received the same recent updates that the report server database has received.
  • The Reporting Services synchronization process will detect differences between the list of items in the content database and the report server databases.
  • The synchronization process will delete or update items in the content database.

Prepare Report Server Databases for Availability Groups

The following are the basic steps of preparing and adding the report server databases to an Always On availability groups:

Create your Availability Group and configure a Listener DNS name .

Primary Replica: Configure the report server databases to be part of a single availability group and create a primary replica that includes all of the report server databases.

Secondary Replicas: Create one or more secondary replicas. The common approach to copying the databases from the primary replica to the secondary replica(s) is to restore the databases to each secondary replica using 'RESTORE WITH NORECOVERY'. For more information on creating secondary replicas and verifying data synchronization is working, see Start Data Movement on an Always On Secondary Database (SQL Server) .

Report Server Credentials: You need to create the appropriate report server credentials on the secondary replicas that you created on the primary. The exact steps depend on what type of authentication you're using in your Reporting Services environment; Window Reporting Services service account, Windows user account, or SQL Server authentication. For more information, see Configure a Report Server Database Connection (SSRS Configuration Manager)

Update the database connection to use the Listener DNS Name. for native mode report servers, change the Report Server Database Name in Reporting Services configuration manager. For SharePoint mode, change the Database server name for the Reporting Services service application(s).

Steps to complete disaster recovery of Report Server Databases

The following steps need to be completed after a Always On availability groups failover to a secondary replica:

Stop the instance of the SQL Agent service that was being used by the primary database engine hosting the Reporting Services databases.

Start SQL Agent service on the computer that is the new primary replica.

Stop the Report Server service.

If the report server is in native mode, stop the report server Windows server using Reporting Services configuration manager.

If the report server is configured for SharePoint mode, stop the Reporting Services shared service in SharePoint Central Administration.

Start the report server service or Reporting Services SharePoint service.

Verify that reports can run against the new primary replica.

Report Server Behavior When a Failover Occurs

When report server databases failover and you have updated the report server environment to use the new primary replica, there are some operational issues that result from the failover and recovery process. The impact of these issues varies depending on the Reporting Services load at the time of failover as well as the length of time it takes for Always On availability groups to fail over to a secondary replica and for the report server administrator to update the reporting environment to use the new primary replica.

The execution of background processing may occur more than once due to retry logic and the inability of the report server to mark scheduled work as completed during the failover period.

The execution of background processing that would have normally been triggered to run during the period of the failover won't occur because SQL Server Agent won't be able to write data into the report server database and this data won't be synchronized to the new primary replica.

After the database failover completes and after the report server service is restarted, SQL Server Agent jobs will be re-created automatically. Until the SQL agent jobs are recreated, any background executions associated with SQL Server Agent jobs won't be processed. This includes Reporting Services subscriptions, schedules, and snapshots.

  • SQL Server Native Client Support for High Availability, Disaster Recovery
  • Always On Availability Groups (SQL Server)
  • Getting Started with Always On Availability Groups (SQL Server)
  • Using Connection String Keywords with SQL Server Native Client
  • About Client Connection Access to Availability Replicas (SQL Server)

Was this page helpful?

Submit and view feedback for

Additional resources

IMAGES

  1. SQL GROUP BY (With Examples)

    sql server reporting services grouping data

  2. Reporting in SQL Server

    sql server reporting services grouping data

  3. sql server reporting services grouping data

    sql server reporting services grouping data

  4. sql server reporting services grouping data

    sql server reporting services grouping data

  5. Reporting in SQL Server

    sql server reporting services grouping data

  6. Reporting in SQL Server

    sql server reporting services grouping data

COMMENTS

  1. Lesson 6: Add grouping and totals (Reporting Services)

    Learn SQL SQL Server Reporting Services Lesson 6: Add grouping and totals (Reporting Services) Article 01/11/2024 9 contributors Feedback In this article Group data in a report Add totals to a report Add the daily total to the report Add the grand total to the report Show 3 more

  2. Multiple Row Grouping Levels in SSRS Report

    These grouped reports organize data into multiple levels and place totals, such as sums or averages, at the end of each level. We expect a tutorial that provides just enough steps to build a ready-to-use SSRS report with multiple row grouping levels. Solution

  3. Understanding groups in a paginated report (Report Builder)

    The data in a Detail group can also include server aggregates, which are aggregates that are calculated on the data source and retrieved in your query. By default, Report Builder and Report Designer treat server aggregates as detail data unless your report includes an expression that uses the Aggregate function. For more information, see Aggregate.

  4. Grouping pane

    A group is a named set of data from a report dataset that is displayed on a data region. Groups are organized into hierarchies that include static and dynamic members. For more information, see Understanding groups (Report Builder and SSRS). If you don't see the Grouping pane, then on the Report menu, select Grouping.

  5. Reporting Services Basics: Adding Groups to Reports

    Most SSRS reports are organized into grouping levels where it's possible to add totals for each group. In this article, Kathi Kellenberger continues her SSRS series by walking through how to create both a Matrix and Table report. The series so far: Reporting Services Basics: Overview and Installation

  6. Dynamic Grouping in SSRS Reports

    Step 1 - Creating the Dataset The report's dataset must include the fields that we want to group by, along with any other fields whose data we want to include in the report. Figure 3 shows the...

  7. sql server

    sql server - Column Grouping In SSRS - Stack Overflow Column Grouping In SSRS Ask Question Asked 3 years, 1 month ago Modified 3 years, 1 month ago Viewed 9k times -1 I am new to SSRS. I am working on a report. and the data that, I have in my table in SQL SERVER is this:

  8. SSRS Report and Group Variable References

    Solution Continuing my series of tips which center on creating custom code for SSRS reports, this third tip covers the implementation of group and report variables. In the first tip, SSRS Embedding DotNet Code, I covered how to add code within a single report.

  9. Professional SSRS 2016 Preview: Chapter 6-Grouping and Totals

    Open the file named Wrox SSRS 2016 Report Solution.sln. SSDT opens with the solution and related projects listed in the Object Explorer on the right side of the report design interface. Figure 6-3 shows SSDT open with a report open in the Design window. The solution contains multiple projects.

  10. Reporting in SQL Server

    We right click upon the "Datasets" folder (above) and bring up the context menu. We select "Add Dataset" (see above). The "Dataset Properties" dialogue box opens. We give our local dataset the name "PivotTablixDS" and select "Use a dataset embedded in my report" (see above). We must new click the "New" button to create a ...

  11. Grouping data in a report

    " - [Instructor] Grouping records in a table allows you to organize information based off of common attribute values. Let's take a look at how we can apply groups to a report by creating a New,...

  12. Interactive Sorting for a SQL Server Reporting Services Report

    SQL Server Reporting Services comes with out of the box functionality known as interactive sorting. This feature allows users to sort the data at the report level in either ascending or descending order. The user can sort the data accordingly without the need to modify the dataset or report. Step 1: Create New SSRS Report

  13. Intro to Report Data in SQL Server Reporting Services (SSRS)

    Report parts are deprecated for all releases of SQL Server Reporting Services starting with SQL Server Reporting Services 2019 and all releases of Power BI Report Server starting with Power BI Report Server September 2022. ... Group and aggregate data Report data can be grouped and aggregated in the query or in the report. If you aggregate ...

  14. Configure SQL Server Reporting Services databases in SQL ...

    SQL Server Reporting Services (commonly known as SSRS) is a service to deploy paginated reports for web and mobile. Data representation is an art, and SSRS helps you to visualize the data in an informative way using various graphs, charts, KPI, matrix. Image Source: Microsoft docs

  15. Reporting Services in SQL Server Data Tools (SSDT)

    For more information, see Deployment and Version Support in SQL Server Data Tools (SSRS). Report server projects. When you install Reporting Services, the following project templates are made available in SQL Server Data Tools (SSDT): Report Server Project. When you select a Report Server Project, Report Designer opens.

  16. Grouping Data in SQL Server

    The syntax of the GROUP BY clause is the following: Please note: When it comes to the syntax, it's important to keep in mind the place of the GROUP BY clause within the SELECT statement. Always write a GROUP BY statement after the FROM and/or WHERE clauses and before the ORDER BY clause (if one exists).

  17. Grouping :: SQL Server Reporting Services:: www.venkateswarlu.co.in

    Grouping In a data region, you can group data by a single field, or create more complex expressions that identify the data on which to group. Complex expressions include references to multiple fields or parameters, conditional statements, or custom code. Grouping Data in SSRS. Right click on table and go to properties. It opens a new window.

  18. SQL Server Reporting Service- datasets- grouping

    I have to sum this column (total_qty) and show the result in table1 under every group total. Data set A columns: Date company_code location_name volumes Data set B columns: Date Company_code Total_Qty How can I get Sum(total_qty) from dataset B into table1 under group total? Also, total_Qty should change as it is placed in group. Thanks

  19. Create a basic table report (SSRS tutorial)

    Lesson 1: Create a report server project (Reporting Services) Lesson 2: Specify connection information (Reporting Services) Lesson 3: Define a dataset for the table report (Reporting Services) Lesson 4: Add a table to the report (Reporting Services) Lesson 5: Format a report (Reporting Services)

  20. Generate SSRS Report On Demand with PowerShell and a Web Service

    Solution. SSRS provides functionality through an XML web service with a SOAP API. As such, if we can make a program send a correct web service request with the right parameter values, we should be able to get the SSRS to render a report with the needed format.

  21. Reporting Services with availability groups

    To utilize Always On availability groups for report data sources you need to configure the report data source connection string is to use the availability group Listener DNS name. Supported data sources are the following: ODBC data source using SQL Native Client. SQL Client, with the .NET hotfix applied to the report server.