{"id":1862,"date":"2014-08-27T00:00:00","date_gmt":"2014-08-27T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-reporting-services-basics-customizing-ssrs-reports\/"},"modified":"2021-05-17T18:33:16","modified_gmt":"2021-05-17T18:33:16","slug":"sql-server-reporting-services-basics-customizing-ssrs-reports","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/sql-server-reporting-services-basics-customizing-ssrs-reports\/","title":{"rendered":"SQL Server Reporting Services Basics: Customizing SSRS Reports"},"content":{"rendered":"<div class=\"article-content\">\n<p>The previous article in this series, <a href=\"https:\/\/www.simple-talk.com\/sql\/reporting-services\/sql-server-reporting-services-basics-building-ssrs-reports\/\">Building Basic Reports<\/a>, showed how to use the SQL Server Reporting Services 2012 (SSRS) Report Wizard, and Report Designer, for rapid report prototyping. This article will explore many of the functions and controls we can use every day, as an SSRS developer, to produced more advanced, custom reports.<\/p>\n<p>It will investigate:<\/p>\n<ul>\n<li>Use of <strong>expressions<\/strong> that allow you to dynamically control nearly every aspect of reporting from control properties to data binding<\/li>\n<li><strong>Common functions<\/strong> that allow run calculations and manipulate data within the report itself<\/li>\n<li><strong>Custom code<\/strong>, used to build your own library of functions within the report.<\/li>\n<li><strong>Grouping levels<\/strong>, used to organize the data<\/li>\n<\/ul>\n<p>You will also set up a <strong>Matrix report<\/strong> and create a <strong>sub-report<\/strong>. Lastly, you will add some flair to the report by adding <strong>dynamic drills<\/strong> and <strong>sorting<\/strong>.<\/p>\n<h2>Getting started<\/h2>\n<p>In order to work through the examples, you will need to have installed and configured SQL Server 2012, SQL Server 2012 Reporting Services, and SQL Server Data Tools (SSDT). The examples will also work with the 2008 R2 version of Reporting Services, in which case you will need Business Intelligence Development Studio (BIDS) instead of SSDT for report development. For further details, please refer back to the <em>Installing and Configuring Reporting Services<\/em> section of the <a href=\"https:\/\/www.simple-talk.com\/sql\/reporting-services\/sql-server-reporting-services-basics-building-ssrs-reports\/\">Building Basic Reports<\/a> article. SQL Server 2014 was released just a few months before this article was written. There were no changes in report development with this release, so any examples in this series will work in this version, too.<\/p>\n<p>The examples in this article pick up where the previous article left off, but if you are starting from scratch, the code bundle for this article (see the Code Download at the bottom of the article) contains all the files you need to get started. First, create the <code>ReportDemo<\/code> database, by running the <code>ReportingDemoDatabaseScript.sql<\/code> script. Next, start a new Report Server project in SSDT. Select <strong>Project<\/strong> | <strong>Add Existing Item<\/strong> to add to the project the shared data source (<code>ReportDb.rds<\/code>) and the sample report, <code>FirstReportMan.rdl<\/code> from the downloaded project.<\/p>\n<p>Open up the shared data source and set the server and database for your SQL Server. If using SQL Server authentication, enter the login and password to point to your copy of the database. Finally, rename the report <code>ExpressionReport.rdl<\/code> and double-click it in the <strong>Solution Explorer<\/strong> to load it into the designer.<\/p>\n<h2>Expressions<\/h2>\n<p>An expression is a short statement, written in code, which sets the value of a property. An expression can set the value of a property based on just about any data available in the report including parameters, report global values, fields in a dataset, and built-in functions.<\/p>\n<p>There are many common requests that we can satisfy by adding expressions to our reports. One common request is to alternate the background colors of the rows to make a large report more readable. Another popular reason to add an expression is to change the font color for certain values, such as red for negative profit numbers.<\/p>\n<p>The expression feature adds a great deal of versatility to SSRS because it allows most aspects of the report to behave dynamically. Rather than using a hard-coded value for a particular property, SSRS resolves the expression to set the value for the property.<\/p>\n<p>Highlight any control or element on a report and scroll through its <strong>Properties<\/strong> window and you will notice that as well as hard-coding most of the property values, you can also set them through an expression (that&#8217;s the <code>&lt;expression&gt;<\/code> option you see everywhere). You will also see buttons labeled <strong><em>fx<\/em><\/strong> in the pop-up <strong>Properties<\/strong> dialog. Either route will access the expression editor, shown in Figure 1.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-1b33cbbc-926c-4e7c-8812-287af2fb9a44.png\" alt=\"2045-1-1b33cbbc-926c-4e7c-8812-287af2fb9\" \/><\/p>\n<p class=\"caption\">Figure 1<\/p>\n<p>As you can see in Figure 1, you can build expressions based on simple constants, parameters, dataset fields, functions and operators.<\/p>\n<p>For example, in order to highlight in <strong>bold<\/strong> type all the customers from a particular state, you can build a simple expression and apply it to the <strong>FontWeight<\/strong> property of the detail rows in the table. Click the left edge of the row to select the entire row. The expression might look as follows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">=iif(Fields!State.Value = \"CA\", \"Bold\", \"Normal\")<\/pre>\n<p>The expression starts with &#8220;=&#8221; and then <code>iif<\/code>, which is a flow control statement commonly used in SSRS expressions. Its format is:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">=iif (boolean statement, true result, false result)\r\n<\/pre>\n<p>If the <code>boolean statement<\/code> is true, then the <code>true result<\/code> is returned, and if not, the <code>false result<\/code> is returned. So, in this case, if the <strong>State<\/strong> value is California, then the expression returns the value &#8220;Bold&#8221;, for the <code>FontWeight<\/code> property, and if not, it returns &#8220;Normal&#8221;.<\/p>\n<p>When you run the report, it will display in bold text each row where the value of the <code>State<\/code> field is California. Add the <strong>State<\/strong> field to the report and try this out. This simple expression will work if you are only interested in the data from California. However, it would be ridiculous to have one report for each state, so you can make this expression even more dynamic by adding a report parameter that will determine which <strong>State<\/strong> to highlight when the report is run.<\/p>\n<p>First, add the new parameter to the report. From the <strong>Report Data<\/strong> window right-click on the <strong>Parameters<\/strong> folder and select <strong>Add Parameter<\/strong>. Call the new parameter <strong>HighlightState<\/strong> and change the <strong>Prompt<\/strong> property to <strong>Highlight State<\/strong>. On the <strong>Available Values<\/strong> page, select <strong>Specify Values<\/strong> and then use <strong>Add<\/strong> to enter the following:<\/p>\n<div>\n<table>\n<tbody>\n<tr>\n<td>California<\/td>\n<td>CA<\/td>\n<\/tr>\n<tr>\n<td>Florida<\/td>\n<td>FL<\/td>\n<\/tr>\n<tr>\n<td>Louisiana<\/td>\n<td>LA<\/td>\n<\/tr>\n<tr>\n<td>Illinois<\/td>\n<td>IL<\/td>\n<\/tr>\n<tr>\n<td>Texas<\/td>\n<td>TX<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>Now click on <strong>OK<\/strong>.<\/p>\n<div class=\"tips\">\n<h4 class=\"indented\">Predefining parameter values using a query<\/h4>\n<p class=\"indented\">When setting up a list of pre-defined values for a parameter, you&#8217;ll see a <strong>Get values from a query <\/strong>option. You can use this option by defining a dataset on the data tab of the report designer. For example, if there was a <code>State<\/code> table in the database, you could define a dataset to retrieve the data for the parameter dropdown from that table. Once you choose the <strong>Get values from a query<\/strong> option, you select the dataset, the value field (this is the field from the dataset that contains the value passed to the report) and the label field (the field from the dataset that contains the value displayed to the user when they are prompted for parameters).<\/p>\n<\/div>\n<p>The next step is to set up the expression. Click on the table control in the report and highlight the whole detail row so that you can see the <strong>FontWeight<\/strong> property for the entire row. In the <strong>Properties<\/strong> window, select the<strong>FontWeight<\/strong> property and choose <strong>&lt;expression&gt;<\/strong> from the drop-down list to open the expression editor.<\/p>\n<p>Typing &#8220;<strong>=iif (<\/strong>&#8221; should start up IntelliSense. You can also use the categories, items and values in the bottom half of the window to help build the expression. Click on<strong> Fields (Customers)<\/strong> to get a list of the fields in this dataset, and then double-click on <strong>State<\/strong>, to build the <strong>Fields!State.Value<\/strong> part of the expression. Add the equal sign and then click <strong>Parameters <\/strong>and <strong>HighlightState<\/strong>. Type in the rest of the expression, which will look as follows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">=iif(Fields!State.Value = Parameters!HighlightState.Value, \"Bold\", \"Normal\")<\/pre>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-e61aae13-7222-49bc-b7bb-5dbf3eb98dbd.png\" alt=\"2045-1-e61aae13-7222-49bc-b7bb-5dbf3eb98\" \/><\/p>\n<p class=\"caption\">Figure 2<\/p>\n<p>Now, if the state code equals the state code that was passed to the report as a parameter, the expression will return the value <strong>Bold<\/strong>. If it does not, it will return <strong>Normal<\/strong>. The value returned is then used to set the property for that instance of the row.<\/p>\n<p>Switch to the <strong>Preview<\/strong> tab, select a state and run the report. All the rows with customers from the selected state should be displayed in bold.<\/p>\n<p>Just in case you need to add alternate background colors to a report, the expression to use is<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">= IIf(RowNumber(Nothing) Mod 2 = 0, \"Silver\", \"White\")\r\n<\/pre>\n<p>You can change the colors to any valid colors you wish to use.<\/p>\n<h3>Using common functions in expressions<\/h3>\n<p>The expression editor allows you to build expressions based on common functions, such as aggregate functions, date\/time functions, string functions and more.<\/p>\n<p>For example, within the <strong>Common Functions <\/strong>node, shown in Figure 2, click on the <strong>Date &amp; Time<\/strong> category, double-click on the <strong>Weekday<\/strong> function to add it to the expression builder and then simply place the cursor in the expression box after the function. Type in the opening left parentheses and you should instantly see IntelliSense for the function selected, as shown in Figure 3.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-40eb19e0-6de6-4283-ab87-3a7ee272f37c.png\" alt=\"2045-1-40eb19e0-6de6-4283-ab87-3a7ee272f\" \/><\/p>\n<p class=\"caption\">Figure 3<\/p>\n<p>In this example, you will use the <strong>Y<\/strong><strong>ear<\/strong> function so that the report returns only the year portion of the <strong>DateOfBirth<\/strong> field.<\/p>\n<p>Right-click on the <strong>DateOfBirth<\/strong> detail cell and select <strong>Text Box Properties<\/strong>. On the <strong>General<\/strong> tab, find the <strong>Value<\/strong> field and click the <strong><em>fx<\/em><\/strong> button to bring up the expression editor. Change expression to read.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">=Year(Fields!DateOfBirth.Value)\r\n<\/pre>\n<p>After clicking <strong>OK<\/strong> to close the dialog box, preview the report to make sure that the function worked.<\/p>\n<p>Another type of function that you&#8217;ll often use in expressions is an aggregate function. For example, say the report is an order form with a list of items and their prices. You could use the <strong>SUM<\/strong> aggregate function on the <strong>Price<\/strong> field to create a total price on the report. Or, if you had a report that listed all the purchase dates and the total amount of each purchase, you could use the <strong>AVG<\/strong> aggregate function to get the average purchase price for that customer. You will generally add aggregate functions to rows outside the detail row in specific grouping levels. You will learn how to add additional levels to the report in the <em>Grouping<\/em> section found later in this article.<\/p>\n<h3>Global variables<\/h3>\n<p>The <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/dd255216.aspx\">Built-in fields<\/a> collection includes a number of global variables that SSRS makes available to a report during processing. It includes both the <strong>Globals<\/strong> collection, providing details of the report itself, such as the page number, or the name of the report, and the <strong>User<\/strong> collection, providing details about the user who ran the report.<\/p>\n<p>In this example you will use these global variables to create a page-numbering scheme for the report. First, add a footer to the report, where the page number will display (in the <strong>Design<\/strong> tab select the <strong>Add Report Footer<\/strong> option from the <strong>Report<\/strong> main menu.)<\/p>\n<p>From the toolbox, drag a textbox into the footer, right click on it, open up the expression editor and build the following expression:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">=\"Page \" &amp; Globals!PageNumber &amp; \" of \" &amp; Globals!TotalPages\r\n<\/pre>\n<p>Switch to the <strong>Preview<\/strong> tab and run the report and you should see a footer on the page that reads something like &#8220;Page 1 of 1&#8221;.<\/p>\n<p>Some other globals worth noting are:<\/p>\n<ul>\n<li><code>ExecutionTime<\/code> &#8211; this tells the date and time the report was run<\/li>\n<li><code>ReportServerUrl<\/code> &#8211; this allows you to verify that you are getting your report from the correct source<\/li>\n<li><code>UserID<\/code> &#8211; this shows under which User ID the report was run<\/li>\n<\/ul>\n<p>You could also add these variables to the report footer and use a parameter to hide or show them. That way, even in production, your users can see a tidy, clean report, but you can see the same report with all the extra information on it.<\/p>\n<p>As a best practice, I have always added the execution time and page numbers to every report.<\/p>\n<h3>Custom functions<\/h3>\n<p>There are about 100 common functions in SSRS 2012, and they can handle most of what you will need to do. Occasionally, however, there is still a need to set an expression with more control than the common functions can manage and for that purpose, you can write custom VB.NET functions and use them in expressions.<\/p>\n<p>In this section you will write a custom <code>GetColor<\/code> function that returns a different color, depending on the value that is passed to it (you can find the full list of available colors in the <strong>Color<\/strong> dropdown list in the properties window). Then, you will use that function to set the <strong>background color<\/strong> for the <strong>status<\/strong> field in the report detail table.<\/p>\n<p>Navigate to the <strong>Design<\/strong> tab. From the <strong>Report<\/strong> menu select <strong>Report Properties<\/strong> and then jump to the <strong>Code<\/strong> tab. Copy the following code into the code window to set the color based on the value in the customer <strong>status<\/strong> field:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">Public Function GetColor(ByVal status as String) as String \r\n   IF status = \"100\" Then \r\n      Return \"White\" \r\n   End IF \r\n   IF status = \"101\" Then \r\n      Return \"Yellow\" \r\n   End IF \r\n   IF status = \"102\" Then \r\n      Return \"Tomato\" \r\n   End IF \r\nEnd Function<\/pre>\n<p>Click <strong>OK<\/strong> and close the window.<\/p>\n<p>Now that you have a function that returns color names, wire up that function to an expression. Add the <code>CustomerStatus<\/code> field to the report. Right-click on the <strong>CustomerStatus<\/strong> cell and open the <strong>Text Box Properties<\/strong> window.<\/p>\n<p>On the <strong>Fill<\/strong> page, click the fx button next to the <strong>Fill Color<\/strong> dropdown, and add the following line of code to create an expression. Please note that custom functions must be called using <code>=code.&lt;myfunction&gt;<\/code>.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">=code.GetColor(Fields!CustomerStatus.Value)<\/pre>\n<p>When the report runs and this expression is resolved, SSRS will call the <code>GetColor<\/code> function and pass the value of the customer status for that instance of the row to the function. The function takes over and determines which color should be returned. SSRS then uses that value as the value for the background property for that cell.<\/p>\n<p>Navigate to the <strong>Preview<\/strong> tab and run the report.<\/p>\n<h2>Subreports<\/h2>\n<p>A subreport is simply a report embedded into another report. In SSRS, a subreport is just another report (unlike some reporting tools, where a subreport is a special construct). Subreports can take parameters and execute their own datasets. In fact, in SSRS you can execute a subreport on its own.<\/p>\n<p>In my experience, the need for subreports is somewhat rare. I have used them in the past to combine into the same report data from multiple sources. The other reason I have seen subreports used is to build dashboards. Instead of just adding the charts, gauges and tables directly to a report, you can create a subreport for each component. Then by adding the parts of the dashboard as subreports, you can control the placement of those parts. On an SSRS report with objects next to each other horizontally, one object will influence and change the placement of other objects when viewing the report. By using subreports, you can get around that problem.<\/p>\n<p>You can add a subreport to a cell of a table or directly onto the report design area. To add a subreport to a report, simply drag a subreport control onto the report, or a table cell, and tell it which report to display. If the subreport requires parameters, you have to map the main report value to the subreport parameter. It&#8217;s actually very simple.<\/p>\n<p>Add a new report to the project by right-clicking on the <strong>Reports<\/strong> folder and selecting <strong>Add<\/strong> | <strong>New Item<\/strong>. In the <strong>Add New Item<\/strong> dialog, select <strong>Report<\/strong> and call it <code>MainReport.rdl<\/code>. Add a new data source to the report, pointing to the <code>ReportD<\/code><code>B<\/code> shared data source. Create a new dataset called <code>Customers<\/code> using the shared data source and the following query:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">SELECT CustomerID, FirstName, LastName \r\nFROM Customer<\/pre>\n<p>While in the <strong>Design<\/strong> tab, drag a table to the report design area. Drag the <code>FirstName<\/code> column from the <code>Customer<\/code> dataset in the <strong>Report Data<\/strong> window into the first column of the Data row of the table, and set up the second column to display the customer&#8217;s last name. Label the third column <strong>Address<\/strong>, but with nothing in the data row. Preview the report, and it should look as shown in Figure 4.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-196c1151-ec73-4b79-8371-75ddac0c9fa3.png\" alt=\"2045-1-196c1151-ec73-4b79-8371-75ddac0c9\" \/><\/p>\n<p class=\"caption\">Figure 4<\/p>\n<p>Pretend that the address information comes from a different system and you will create a separate report with this information so that it can be combined into the main report. Create the second report, and call this one <code>AddressSubReport.rdl<\/code>. This time, create a dataset called <code>Addresses<\/code> that uses the shared data source, and use the following query text:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">SELECT Address , \r\n       City , \r\n       State , \r\n       ZipCode \r\nFROM   Customer \r\nWHERE  ( CustomerID = @CustomerID )\r\n<\/pre>\n<p>In a previous section, you learned how to add an expression to control the value of a property. You can also create a new field in the dataset with an expression. In this example, you will combine the <strong>C<\/strong><strong>ity<\/strong>, <strong>S<\/strong><strong>tate<\/strong> and <strong>ZipC<\/strong><strong>ode<\/strong> fields into one <strong>CityLine<\/strong> field. Creating a calculated field is very helpful when an expression is needed in multiple cells of the table, or if one expression must build on another one. When you add a calculated field to a dataset, the calculated field will show up in the <strong>Expression Builder<\/strong>. In this case, it makes adding the expression to the report easier. You could also create the new field in the SQL query. This is a nice feature when modifying the query is not possible, for example when your dataset is a stored procedure and you are not allowed to change it.<\/p>\n<p>Right-click on the <strong>Addresses<\/strong> dataset and choose <strong>Add Calculated Field<\/strong>. Name the field <strong>CityLine<\/strong> and click the fx button. Add this expression:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">=Fields!City.Value &amp; \", \" &amp; Fields!State.Value &amp; \" \" &amp; Fields!ZipCode.Value\r\n<\/pre>\n<p>In the <strong>Design<\/strong> tab, add a <strong>Listbox<\/strong> control to the report, which allows you to add text boxes and other controls without being confined to a grid. Add two text boxes to the listbox. Drag the <strong>Address<\/strong> and <strong>CityLine<\/strong> fields into the text boxes. Drag the listbox to the upper left area of the report. Drag in the report edges so that they are lined up against the listbox. The report design should look as shown in Figure5.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-242b8a44-e596-47eb-84b4-bf2aed9a7196.png\" alt=\"2045-1-242b8a44-e596-47eb-84b4-bf2aed9a7\" \/><\/p>\n<p class=\"caption\">Figure 5<\/p>\n<p>Now preview the report and use &#8220;100&#8221; for the <strong>CustomerID<\/strong> parameter.<\/p>\n<p>It&#8217;s now time to embed the <code>AddressSubReport <\/code>into <code>MainReport<\/code>. The report will look like the data came from one source. Switch back to <code>MainReport.<\/code> Drag a <strong>SubReport<\/strong> control into the detail cell for the column labeled <strong>Address<\/strong>. Right-click on the <strong>SubReport<\/strong> control and select <strong>Subreport Properties<\/strong>. In the <strong>Subreport Properties<\/strong> dialog choose <strong>AddressSubReport<\/strong> from the subreport dropdown.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-d2e52b7a-5ae6-4048-8833-5b6b0d355dfa.png\" alt=\"2045-1-d2e52b7a-5ae6-4048-8833-5b6b0d355\" \/><\/p>\n<p class=\"caption\">Figure 6<\/p>\n<p>Next, switch to the <strong>Parameters<\/strong> tab. This is where you connect the subreport to the main report, by indicating which value from the main report is to be passed to the subreport to fulfill its parameter requirements.<\/p>\n<p>Click <strong>Add<\/strong> to insert a parameter to map. In the <code>Name<\/code> column choose <strong>CustomerID<\/strong>, and in the <strong>Parameter Value<\/strong> column choose <strong>[CustomerID]<\/strong>. This will wire up the subreport to whichever customer is being displayed in the row of the main table.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-36e00c82-87da-4f3c-bc5b-d91a7d5369fc.png\" alt=\"2045-1-36e00c82-87da-4f3c-bc5b-d91a7d536\" \/><\/p>\n<p class=\"caption\">Figure 7<\/p>\n<p>Switch to the <strong>Border <\/strong>tab. Change the border color to <strong>LightGray<\/strong> and click the <strong>Outline<\/strong> button. The properties will look like Figure 8.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-4003e535-0b17-43f2-8bf8-5a691918e33d.png\" alt=\"2045-1-4003e535-0b17-43f2-8bf8-5a691918e\" \/><\/p>\n<p class=\"caption\">Figure 8<\/p>\n<p>Click <strong>OK<\/strong> to close the dialog, and then preview the main report. You may need to expand the size of the <strong>Address<\/strong> field if it is too small.<\/p>\n<h2>Grouping<\/h2>\n<p>One of the most important features of many SSRS reports is grouping. Grouping allows you to create a hierarchy on the report as well as add totals and subtotals. The <strong>Design<\/strong> tab of the report has a section at the bottom, shown in Figure 9, displaying the row and column groups.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-bc7f010d-2106-4c10-8837-01b95a8ed812.png\" alt=\"2045-1-bc7f010d-2106-4c10-8837-01b95a8ed\" \/><\/p>\n<p class=\"caption\">Figure 9<\/p>\n<p>To demonstrate how to add a hierarchy to a report, add a new report called <code>GroupingReport<\/code>. Add a data source pointing to the shared data source, <strong>ReportDB<\/strong>. Add a dataset called <strong>CustomerSales<\/strong> with this query:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">SELECT C.CustomerID , \r\n       State , \r\n       PurchaseID , \r\n       PurchaseDate , \r\n       YEAR(PurchaseDate) AS PurchaseYear , \r\n       PurchaseAmount \r\nFROM [dbo].[Customer] AS C \r\n     JOIN [dbo].[Purchase] AS P ON C.CustomerID = P.CustomerID\r\n<\/pre>\n<p>When designing a report with grouping levels, it&#8217;s important to determine the levels up front. It&#8217;s a good idea to mock up the report on a white board or paper. In this case, you will group on <strong>PurchaseYear<\/strong>, <strong>State<\/strong>, and <strong>CustomerID<\/strong>.<\/p>\n<p>Add a table to the report. Add the <strong>PurchaseID<\/strong>, <strong>PurchaseDate<\/strong>, and <strong>PurchaseAmount<\/strong> fields to the detail row of the report. Format the <strong>PurchaseDate<\/strong> and <strong>PurchaseAmount<\/strong> fields.<\/p>\n<p>At this point, it is easy to add a grand total to the report. Right-click on <strong>PurchaseAmount <\/strong>and select <strong>Add Total<\/strong>. The expression <code><strong>SUM(PurchaseAmount<\/strong><\/code><code><strong>)<\/strong><\/code> is automatically added to a new row in the report. Type in <strong>Grand Total<\/strong> in the cell to the left and bold the row.<\/p>\n<p>Now that the details and grand total are in place, add the innermost level of grouping above the details, <strong>CustomerID<\/strong>. Drag the <strong>CustomerID<\/strong> field into the <strong>Row Group<\/strong><strong>s<\/strong> section at the bottom of the report right above the details group. The <strong>CustomerID<\/strong><code><\/code>column is automatically added to the report. Run the report, and you&#8217;ll see that <strong>CustomerID <\/strong>is not only added to the report, it is a grouping level.<\/p>\n<p>The next level is <strong>State<\/strong>. Add it to the <strong>Row Groups<\/strong> section above <strong>CustomerID<\/strong>. Finally, add <strong>PurchaseYear<\/strong> above <strong>State<\/strong>. The <strong>Row Groups<\/strong> section should look like Figure 10.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-f622113c-701a-4964-8307-5f635ee853b9.png\" alt=\"2045-1-f622113c-701a-4964-8307-5f635ee85\" \/><\/p>\n<p class=\"caption\">Figure 10<\/p>\n<p>Now preview the report to see how the groups appear in the report. To modify group properties such as sorting and visibility, right-click on the group name in the <strong>Row Groups<\/strong> window. To add a subtotal in a level, right-click on a cell in that level and add a row inside the group. For example, right-click on the <strong>State<\/strong> cell and select <strong>Insert Row <\/strong>|<strong> Inside Group &#8211; Below<\/strong>. This will add a new row to that group. Drag the <strong>PurchaseAmount<\/strong><code><\/code>into the cell under the detail row&#8217;s <strong>PurchaseAmount<\/strong>. Since this is outside the detail the <code>SUM<\/code> function is automatically applied. You may want to drag the <strong>State<\/strong> field to this row and format it so that it is obvious that the new value is a subtotal.<\/p>\n<p>Preview the report to see how it looks.<\/p>\n<h2>The Matrix Control<\/h2>\n<p>Use the Matrix control to create pivot table style reports. Pivoting turns actual data values into column headers. In this section, you will set up a simple Matrix report to show you the count of customers with each status, in each state. When creating a matrix report, you end up with column groups in addition to row groups. Because the column groups expand across the page to the right, column groups should be a very limited set, such as months of the year, or a given set of years.<\/p>\n<p>First, create a new report called <code>MatrixReport.rdl<\/code>. Add the shared data source, and then add a dataset with this query:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">Select CustomerID, State, CustomerStatus from Customer<\/pre>\n<p>The matrix control looks quite a bit different than the table control, as shown in Figure 11.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-c07076d9-faa4-4675-8bfa-147366a81bb9.png\" alt=\"2045-1-c07076d9-faa4-4675-8bfa-147366a81\" \/><\/p>\n<p class=\"caption\">Figure 11<\/p>\n<p>Change the value of the <strong>Data<\/strong> cell to <strong>CustomerID<\/strong>. The expression automatically changes to sum up the <strong>CustomerID<\/strong> values. Most of the time, the matrix is used to sum up a value. In this case, it doesn&#8217;t make sense to add them up, but it does make sense to count the customers. Using the expression builder, change the expression to <code>=Count(Fields!CustomerID.Value)<\/code>.Change the value of the <strong>Columns<\/strong> cell, to <strong>CustomerStatus<\/strong>. In the <strong>Rows<\/strong> cell, change the value to <strong>State<\/strong>. The report design should look as shown in Figure 12.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-6f588cbf-322c-4015-8fe6-6a94440185e2.png\" alt=\"2045-1-6f588cbf-322c-4015-8fe6-6a9444018\" \/><\/p>\n<p class=\"caption\">Figure 12<\/p>\n<p>Preview the report and then format as desired. You should see the individual customer status values populated across the top of the report. In the <strong>Column Groups<\/strong> window, you will see <strong>CustomerStatus<\/strong> as a grouping level. After some formatting, my report looks as shown in Figure 13.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-cef17bcc-b0b6-479c-b2b6-04b59fdc406c.png\" alt=\"2045-1-cef17bcc-b0b6-479c-b2b6-04b59fdc4\" \/><\/p>\n<p class=\"caption\">Figure 13<\/p>\n<p>You can add additional column or row grouping levels and it is possible to change a regular table into a matrix by adding column grouping. I have seen much better results by starting with the type of report that I needed up front.<\/p>\n<p>To convert a table into a matrix, follow these steps. Add a new report with the same dataset as the matrix report. Add a table control. Remove two of the columns leaving one column. Remove the <strong>Header<\/strong> row. You will have just one cell left. Drag <strong>CustomerStatus<\/strong><code><\/code>to <strong>Column Groups<\/strong>. Drag <strong>State<\/strong><code><\/code>to <strong>Row Groups<\/strong>. In the <strong>Row Groups <\/strong>section, delete the <strong>Details<\/strong> group. Change the <strong>Data<\/strong> cell to <strong>CustomerID<\/strong>. Modify the expression so that it counts the values instead of adding them up. The report should now show the identical data to the one created above.<\/p>\n<h2>Adding drill downs<\/h2>\n<p>The drill down functionality in SSRS allows you to create areas of a report that will expand and collapse, much like a tree view. If you followed along with the first article in this series, you saw this functionality demonstrated with the report created with the wizard, <code>FirstReportWZ.rdl<\/code>. That report has <strong>state<\/strong> sections that expand to show the details. This allows the user of the report to see the summary and only view the details or child groups when needed.<\/p>\n<p>Create a new report called <code>DrilldownReport.rdl<\/code>. The query for the dataset is as follows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">SELECT LastName, FirstName, City, State FROM Customer<\/pre>\n<p>Navigate to the <strong>Design<\/strong> tab and drag a new table control onto the page. Add the <strong>FirstName<\/strong>, <strong>LastName<\/strong> and <strong>City<\/strong> fields to the table details. Next, you need to add a group to the table. The earlier section on Grouping, explained how to add groups to the <strong>Row Groups<\/strong> window. Another technique is to add the groups directly to the table by right-clicking one grouping level and adding a new level. In this case right-click on the detail row and select <strong>Add Group<\/strong> | <strong>Row Group<\/strong> | <strong>Parent Group<\/strong> as shown in Figure 14.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-bd08218e-e5d6-4bec-9b07-d2a0c29f0067.png\" alt=\"2045-1-bd08218e-e5d6-4bec-9b07-d2a0c29f0\" \/><\/p>\n<p class=\"caption\">Figure 14<\/p>\n<p>When the <strong>Tablix<\/strong><strong> Group<\/strong> dialog appears (it&#8217;s called Tablix because it can be a table or a matrix), select <strong>State<\/strong> in the <strong>Group by<\/strong> dropdown and check <strong>Add Group Header<\/strong>. Click <strong>OK<\/strong>. Now the report is grouping on <strong>State<\/strong>. Add some formatting, and preview the report.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-a99718dd-b5b8-43fb-afdc-ff9b7aef3562.png\" alt=\"2045-1-a99718dd-b5b8-43fb-afdc-ff9b7aef3\" \/><\/p>\n<p class=\"caption\">Figure 15<\/p>\n<p>You now have grouping, but you still need to enable the drilldown. Go back to the <strong>Design<\/strong> tab. In the <strong>Row Groups<\/strong> window, right-click on the <strong>Details<\/strong> group and select <strong>Group Properties<\/strong>. On the <strong>Visibility<\/strong> page, click <strong>Hide<\/strong>, check <strong>Display can be toggled by this report item<\/strong>, and select <strong>State<\/strong> from the list. The dialog should look as shown in Figure 16.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-6cdab9b0-77a5-4603-be8e-382d75e9adf9.png\" alt=\"2045-1-6cdab9b0-77a5-4603-be8e-382d75e9a\" \/><\/p>\n<p class=\"caption\">Figure 16<\/p>\n<p>Click <strong>OK<\/strong>. Switch to the <strong>Preview<\/strong> tab and run the report. Now only the <strong>States<\/strong> appear, but you can see the customer in each state by using the tree-style +\/- controls as shown in Figure 17.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-3b8c1730-a6a8-4fd4-b3df-4ec4a4da1d45.png\" alt=\"2045-1-3b8c1730-a6a8-4fd4-b3df-4ec4a4da1\" \/><\/p>\n<p class=\"caption\">Figure 17<\/p>\n<h2>Adding dynamic sorting<\/h2>\n<p>It is very easy to add dynamic sorting to a table. Re-open the <code>ExpressionReport.rdl<\/code> report. Navigate to the <strong>Design<\/strong> tab of the report designer. Click in the cell labeled <strong>First Name<\/strong> in the header of the table. Right-click and select <strong>Text Box Properties<\/strong>. Switch to the <strong>Interactive Sorting<\/strong> tab. Check the checkbox for <strong>Enable interactive sorting on this textbox<\/strong>. Next, set the <strong>Sort by<\/strong> expression to the value that is displayed in the column &#8211; in this case <strong>[FirstName]<\/strong> and click <strong>OK<\/strong>. Now repeat this process for the rest of the report.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-ea879895-b881-43a0-9980-e13923b6030f.png\" alt=\"2045-1-ea879895-b881-43a0-9980-e13923b60\" \/><\/p>\n<p class=\"caption\">Figure 18<\/p>\n<p>Preview the report, and you should have interactive sorting on each column. By holding down the shift key, you can sort on multiple columns.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2045-1-4dd3589a-d28a-4963-9114-2464b95f5a0c.png\" alt=\"2045-1-4dd3589a-d28a-4963-9114-2464b95f5\" \/><\/p>\n<p class=\"caption\">Figure 19<\/p>\n<h2>Wrap up<\/h2>\n<p>I hope this look into some of the more advanced features of SQL Server Reporting Services 2012 will help you make your reports more interactive and better adapted for your users. Part 3 of the series will take an in-depth look at the visual controls.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>While the Reporting Wizard and basic report tables will do for prototyping, very soon we&#8217;ll want to add some finesse to our reports, and allow our report users to create different views of the same data, dynamically. Kathi Kellenberger shows how to use expressions, functions, grouping, the matrix control, and other features, to add a degree of user control to the reports while maintaining visual clarity.&hellip;<\/p>\n","protected":false},"author":110218,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[4242,5962,5963,5113,5964,4379,4179,4150,4151,4400],"coauthors":[11292],"class_list":["post-1862","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-basics","tag-custom-reports","tag-expressions","tag-kathi-kellenberger","tag-matrix-report","tag-reporting-services","tag-source-control","tag-sql","tag-sql-server","tag-ssrs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1862","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/110218"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1862"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1862\/revisions"}],"predecessor-version":[{"id":85104,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1862\/revisions\/85104"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1862"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1862"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1862"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1862"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}