SSRS 2012 Tutorial: Enhanced Report ItemsDownloads required:
A well-designed standard report can be very effective in presenting data to users. Enhancing those reports with expressions and parameters can greatly increase their utility. The best way to present certain types of data, however, is often in a chart, graph or other visual element. In addition to adding visual interest to your reports, these elements often allow the users to spot trends or understand the data in a way that is much easier than trying to decipher rows and columns full of numbers.
With SQL Server 2008 R2, Microsoft provided a nice sample report named Employee_Sales_Sumary_2008R2.rdl displaying many of the graphical features covered in this chapter. This report demonstrates charts, gauges, and sparklines, which are three of the main graphical features that will be explored in this chapter. At the time the class was published, new samples for SQL Server 2012 were not yet available.
By performing the following steps, you will explore the graphical features implemented in the Employee_Sales_Sumary_2008R2.rdl report. Later in the chapter, you will again work with each of the report item types.
Each of the objects that you viewed in the Try It are data bound report items like tables, lists, and matrices. When you add one of these graphical report items to your report, you will be required to bind a dataset to this object.
With charts, you can greatly enhance the visual interest of your reports. Charts help you present report data in a format that is familiar to managers and is frequently easier to interpret. SSRS features a wide variety of chart styles.
Charts in SSRS function in a similar manner to charts in Excel. Since the Report Wizard in SSDT does not support charts, you will need to manually add and configure the charts in your reports. Report Builder, however, has the Chart Wizard template which allows you to quickly create a report with a chart.
Adding a Chart with Report Designer
Like all other report items, charts can be added to a report by dragging the Chart report item from the Toolbox window to the design surface, by selecting the report item and then drawing the shape on the design surface, or by double clicking the Chart report item in the Toolbox.
Once you add the Chart report item to the Design Surface, the Report Designer opens the Select Chart Type dialog box. The dialog box displays a variety of chart types. Each chart type presents unique options for grouping and presenting data. For example, a pie chart supports groups and nested groups, such as sales by category or by month. Line charts, on the other hand, support an x and y series and are better at displaying trends.
Although the dialog box does not have separate pages for each category of chart types, the links on the left side of the dialog box will jump the cursor to the first chart type of the corresponding category in the type listing. For example, if you click Area on the left side of the dialog box, the first chart in the Area category is selected as shown in Figure 8.
Charts are data regions and are required to be bound to a dataset. If you do not have a dataset defined within your report, Report Designer will present the Dataset Properties dialog box after you click OK in the Chart Type dialog box. If one or more datasets already exist within the report, the chart will be displayed on the design surface when you click OK in the Chart Type dialog box. The first field that you add to the chart will define the dataset that will be bound to the chart.
When you click inside of a chart, the Chart Data window appears. You can add fields to the chart by dragging them from the Report Data window to Chart Data window, or by clicking the plus sign in the appropriate section of the Chart Data window as shown in Figure 10.
Like other report items you have worked with in the course, the Chart Properties dialog box and Properties window associated with the chart allow you to configure settings that affect all areas of the chart, such as the dataset that is bound to the chart, the color palette associated with the chart, the chart’s visibility, and much more. Additionally, there are dialog boxes to control the properties associated with each of the following elements of the chart.
By performing the following steps you will create a report that includes a Chart data region. This chart with represent Total Sales by Territory for the current and previous year’s sales based on the information located in the SalesPerson table. Please note that the database is a static entity, so the current year in the database will likely not be the same as the actual current date. A sample of the completed report can be found by opening the EnhancedReports.sln file from the \Samples\EnhancedReports 2 Charts Completed folder.
Using the Chart Wizard in Report Builder
As mentioned earlier, you can use the Chart Wizard template in Report Builder to quickly create a report that includes a chart. Although Report Builder will walk you through the first few steps of creating a chart from within a wizard environment, you will still need to finish configuring the chart on your own.
By performing the following steps you will use Report Builder and the Chart Wizard to create a pie chart that shows sales by sales territory. A completed report can be found at \Samples\SampleChartWizardRpt.rdl
With gauges, you can start creating reports that resemble the scorecards and dashboards that have become popular in the business intelligence (BI) community. Like charts, gauges give a pictorial representation of summary data rather than columns and rows of numbers. While charts typically are used to compare a series of aggregated values, gauges typically present the state of a single series. For example, a bar chart will compare the values for actual sales vs. goals across several years. On the other hand, a gauge will show the state of the data along a continuum, like a fuel gauge.
You can use a gauge to display Key Performance Indicators (KPIs), values from a matrix, or even add multiple gauges in a single gauge panel to compare values presented in different gauges.
By performing the following tasks, you will add a gauge to an existing table that will provide a visual to compare a salesperson’s year-to-date sales with their individual sales quota. A sample of the completed report can be found by opening the EnhancedReports.sln file from the \Samples\nhancedReports 4 Guages Completed folder.
The mapping feature was added in SQL Server 2008R2 and allows you to add maps to your reports based on locality information stored within your databases. You can use a bubble map to shows areas on a map where you have clusters of clients, or you can use a line map to show routes from a convention center to local hotels or points of interest. To make the reports more interesting, you can add a Bing map layer that will allow you to choose between aerial, road, or a combined background display for your map.
The mapping feature uses the concept of layers to allow you to add data points representing customer locations and a polygon outlining sales territories all with a background of an aerial Bing map.
Although you can use both Report Designer and Report Builder to add maps to your reports, Report Builder offers a wizard. If you haven’t worked with reports before, the map wizard in Report Builder may not be intuitive enough to just jump in to creating your first map.
When you add a Map report item, you must define the source of the spatial data to be used. You will also be able to define the source when you add a new layer to your map. The Choose a source of spatial data page of the New Map Layer wizard displays the following options:
The areas of a map item are shown in Figure 24. Like the other visual report items, each of these areas has a set of properties that you will use to configure the map.
By performing the following steps, you will create a report that contains a map showing cities in Ohio with customers of the AdventureWorks company. A sample of the completed report can be found by opening the EnhancedReports.sln file from the \Samples\EnhancedReports 5 Map Completed folder.
In SQL Server 2008R2, Microsoft added three additional features that will allow you to create the dashboard type reports that many analysts are looking for. Each of these report items typically work best when used embedded within a Tablix report item so that the graphic is displayed for each row that is returned from the data set.
A data bar is a very basic chart. Like a gauge, a data bar typically graphically displays a single value for the corresponding row of data being returned from the data set. A data bar in the report header may not be that beneficial, but when added to a table or matrix, the report consumer can easily identify trends and problem rows. Data bars can display more than one value, but this usage is not common. You can use a data bar to show the relative value of each sales person’s year-to-date sales.
Once you have created a data bar, if you decide that a more complex chart would work better, you can right-click the data bar and select Convert to Full Chart. This process is not as easily reversed because all non-compatible features must be removed from the chart before conversion.
Indicators are small single icons that can be used to either quickly show the state of a given value, or a trend between values. You can use the Indicator report item to show a red, yellow, or green flag for the current row based on one of your business’s key performance indicators, such as annual growth. When you define an indicator, you must define what determines the color or shape of the indicator. For example, coming within 90% of the goal would produce a green flag, within 75% a yellow flag, and less than 75% a red flag. If you do not define the range of values to be used for each indicator, the range of values will be split equally over the number of indicators that exist in the indicator type that you choose.
Like the other report items in this section, sparklines are frequently embedded within a Tablix data region to display information about the corresponding row from the data set. Unlike data bars and indicators, sparklines are generally used to display a series of values, rather than a single value. For instance, you can use a data bar to show a sales person’s year-to-date sales in comparison to all sales for the year, while using a sparkline to show their monthly totals so that you can compare the trends month to month for each salesperson.
You will typically define a sparkline for a row within a grouping. For example, you may add a sparkline representing total sales within the year grouping, and define the point values along the x axis of the sparkline to represent a more detailed level, such as months.
Like charts, when you add a sparkline report item to your report, you will manage the data being presented through the Chart Data window. From our example above with the sparkline representing monthly sales within a particular year, when you add the sales amount value as the first series, the default behavior is to sum the field selected if it is a numeric field, or provide a count of non-numeric fields. When you select the down arrow next to the Sum value that is added, you are presented with the options to change the field being used and the aggregate being used as shown in F. Like most fields in SSRS, you can also define a custom expression to provide the Y values for the sparkline.
By performing the following tasks, you will create a report that includes a data bar and an indicator. Each of these report items will display a different value with regard to a sales person’s year-to-date sales and sales quotas. The dataset only includes sales people with quotas. Also note that the query is only returning the monthly sales amounts for the year 2008, which in the database is the current year. In real life, a variable would be used for the year, but for simplicity in the practice, a fixed year was used since the data never changes. If you did not complete the previous Try It, you can use the EnhancedReports.sln file located in either the EnhancedReportsStarter or EnhancedReports 5 Map Completed folder. A sample of the completed report can be found by opening the EnhancedReports.sln file from the \Samples\EnhancedReports 6 Visualization Completed folder.
You can nest one data region inside of another data region. You saw this feature in several of the Try Its in this chapter. When nesting a report item, it is important to remember the scope of each area on the report surface and understand how that will relate to your new report item. For example, if you add a chart to the text box located in the Tablix Corner (the upper left corner of a matrix), it will present static data that will appear only once, at the beginning of the report. If you add a Sparkline report item to a text box in the Details area of the report, you will receive one Sparkline for every row of data being returned from the dataset query.
In addition to adding graphical report items to a Tablix data region, you can also nest other report items. For example, you can nest a Table report item inside of a List report item to provide details under a group heading with page breaks between each grouping.
With SQL Server 2008R2, Microsoft added a feature called Report Parts. With Report Parts, you can save and deploy charts, maps, or any object in a report individually. You can then re-use or build from those report parts in new reports. Report Parts will be covered in more detail in Chapter 10, Working with Report Builder. Because nested data regions do not exist exclusive of the data region of which they are a part, you cannot save them as report parts.
By performing the following steps, you will nest a table inside of a list item. This will allow you to work with a report similar to ones that the Report Wizard creates when you select the options to group objects at the page level. A sample of the completed report can be found by opening the EnhancedReports.sln file from the \Samples\EnhancedReports 7 Nesting Completed folder.
Reporting Services offers a wide variety of options for organizing data within a report. In this and previous chapters, you have already worked with hiding groups to provide reports that users can drill down into. Additionally, you have nested data regions inside of each other to correlate different types of data regions such as tables and data bars. In addition to these features, SSRS provides you with the ability to define subreports that can be used in multiple locations and inserted into parent reports. Drillthrough reports are launched when a user clicks on a link in a report. You will use parameters to pass information from the parent to the child report
Working with Subreports
Subreports offer many advantages when designing reports. First, they allow you to reuse portions of a report in more than one parent report. Additionally, because they can use the same or different data sets from the parent report, they add flexibility for complex reports.
Because subreports are generated at the same time as the parent report, they do not offer any performance benefits. Additionally, they may even slow performance over nested or separate data regions because SSRS processes each instance of the subreport as a separate report, even though it is displayed within the parent report.
A subreport can be added to an empty location on the parent report’s report body, or it can be added to a data region. If you add a subreport to a data region, it will repeat for each group or detail row in which it was embedded.
You can pass information from the parent report to the subreport by using parameters. You must first create a report parameter in the subreport. When you add the subreport to the parent report, you can define the name of the report parameter and the value that you want to pass to it on the Parameters page of the Subreport Properties dialog box. For subreports nested in a data region, you will use a field expression to define the value to be passed for each row or group. If you are using Report Designer in SSDT, and the subreport and parent report are in the same project, you will be able to select the parameter from a list as shown in Figure 41. Otherwise, if the subreport is located on the report server, you need to be sure to type the name exactly as it is defined in the subreport.
By performing the following steps, you will insert a subreport that stands on its own, without the need to pass parameters. The subreport will appear on the first page of the parent report. A sample of the completed report can be found by opening the EnhancedReports.sln file from the \Samples\EnhancedReports 8 Subreport Completed folder.
In addition to making it easier to design reports with complex requirements, drillthrough reports have the added benefit of improving performance. Drillthrough reports are not generated until the action linked to the drillthrough report is activated. Because the drillthrough report is an independent report on its own, it can be launched directly from Report Manager in addition to being launched from the parent report.
The top or general level of information can be displayed on the first report. You will then use the Go to report option on the Action page of the appropriate object to configure the report to be launched when a user clicks on that object.
When you configure an action for an object, it does not automatically format the object to alert the user that if they click on the object, it will do something different. For text boxes, you will frequently format the text to be blue and underlined, the universal sign for a hyperlink. SSRS also gives you the ability to design a tool tip that will be displayed when you pause the mouse over the object. In some cases, you may want to add additional text to explain what will happen if they click the object.
There are many situations where the use of a drillthrough report would be valuable including the following:
Like subreports, you will use parameters to pass values from the parent report to the drillthrough report.
By performing the following steps, you will add a drillthrough report to the OhioCustomerMapRpt report that will provide detailed information about a particular customer. A sample of the completed report can be found by opening the EnhancedReports.sln file from the \Samples\EnhancedReports 9 Drillthrough Completed folder.
Required files can be found under the \Labs folder.
Exercise 1: Creating a complete solution
The goal of this exercise is to review many of the concepts learned in the course up to this point. This is the final chapter covering Report Designer in detail and this exercise will allow you to practice and build these concepts together, from the creation of the project through the creation of the basic reports that will be used in the following exercises. A sample solution can be found in the Exercise1Solution\EnhancementsLab folder. Answers may vary depending on how the directions are interpreted.
Exercise 2: Adding data visualization report items to a report
The goal of this exercise is to allow you to practice the steps involved with adding data visualization report items to an existing report. Step by Step directions are provided in the Solutions to Exercises section. Answers may vary depending on how the directions are interpreted. If time permits, feel free to explore additional report items and options. A sample solution can be found in the Exercise2Solution folder.
Exercise 3: Adding a correlated sub-report
The goal of this exercise is to allow you to practice the steps involved with adding a subreport to a parent report. You will also practice sending a parameter through to the subreport. Step by Step directions are provided in the Solutions to Exercises section. Answers may vary depending on how the directions are interpreted. A sample solution can be found in the Exercise3Solution folder.
Exercise 1 Solution: Creating a complete solution
Exercise 2 Solution: Adding data visualization report items to a report
Exercise 3 Solution: Adding a correlated sub-report
Author: Ann Weber, one of Accelebrate’s instructors