SSRS 2016 Tutorial: Enhanced Report Items Part 1

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.

In this SSRS Tutorial, you will learn how to:

• Create charts
• Create gauges
• Add a map to a report
• Use data bar, indicator, and sparkline report items
• Lab exercise

Files needed:

• /SampleReports
• /Answers
• /StudentX
• \Classfiles\ReportBuilder\Starter Solutions\Ch7-Queries.txt

Setup:

• SSRS 2016 installed and configured in Native Mode
• The Adventureworks2014 and AdventureworksDW2014 databases that can be downloaded from here:
  • AdventureWorks2014FullDatabaseBackup.zip
  • AdventureWorksDW2014FullDatabaseBackup.zip
• The samples and answers projects in the zip file downloaded and then extracted
  • Use SQL Server Data Tools for Visual Studio 2015 or later to deploy first the SampleReports.sln solution/project and then the Answers.sln solution/project located under the Setup folder
  • If you are not running SQL Server on your local computer, change the TargetServerURL in the project properties of each project before deploying.

Some of the Try It practices in this tutorial build on one another. Answers for each report along the way can be found in the /Answers folder of your report server.

With SQL Server 2008R2, Microsoft provided a nice sample report named Employee_Sales_Sumary_2008R2.rdl displaying many of the graphical features covered in this tutorial. This report demonstrates charts, gauges, data bars, and sparklines, which some of the main graphical features that will be explored in this tutorial.  This class uses a set of upgraded 2008R2 reports.

Exploring Graphical Features

By performing the following steps, you will explore the graphical features implemented in the Employee_Sales_Sumary_2008R2.rdl report. Later in the tutorial, you will again work with each of the report item types.

1.    In Report Builder, open /SampleReports/Employee_Sales_Summary_2008R2.

2.    Use the Save As feature to save a copy to your /StudentX folder in case you make any changes to the report.

Figure 1: Yearly Sales Comparison Chart

3.    In the Report Design area, click inside the data area of the line chart titled Yearly Sales Comparison to select the Chart Area as shown in Figure 1.

4.    Review the Chart Data area that appears. You can use this area to modify the fields that are used for each data series. You can also define aggregates and groupings to be used.

5.    Right-click in different areas of this chart and review the options available.

Figure 2: Quota Indicator

6.    To the right of the chart that you just worked on, click twice (do not double click) on the red down arrow next to the title Met Quota? to bring the Indicator report item into focus as shown in Figure 2. An indicator is a very basic gauge, and therefor uses the Gauge Data window for configuration changes.

7.    Click the down arrow next to Indicator1 in the Gauge Data area, and then click Change Indicator Type. Review the options available.

8.    Click Cancel.

Figure 3: Sparkline

9.    Click in the text box associated with Sparkline report item located in the far right column of the Tablix data region that summarizes annual sales as shown in Figure 3.

10. Right-click the text box containing the sparkline. Notice that both Sparkline and Tablix sections are presented. The menu should be similar to the one in the figure. If you only have a few options, click closer to the edges of the text box to select the text box rather than the Sparkline report item.

11. On the menu presented, click Change Sparkline Type.

12. Review the options in the Select Sparkline Type dialog box, and then select a different type, such as the Stepped Line to the left of the currently selected option

13. Click OK.

14. The report design is updated to show the new sparkline type, but you won't know exactly what it looks like until you preview the report with data. Occasionally, these graphical objects look strange while in design mode.

15. Change the sparkline back to the Line with Markers type.

Figure 4: Data Bar

16. Below the table with the Sparkline report item is a bar graph style Chart report item and a Data Bar report item under the heading Compare seasonal sales…

17. Click on the text box with the Data Bar text box under the [Title] column as shown in Figure 4. Like the sparkline, the data bar is embedded inside of a Tablix data region. This time the Tablix is configured as a matrix. The data bar will graphically present the data at the intersection of a particular Title and Category.

18. Right-click the text box with the Data Bar and review the options available. The menu should look similar to the one in the figure.

19. Press Esc to close the menu.

Figure 5: Data Series Menu

20. Right-click in the center of the data bar and review the menu. It should look similar to Figure 5.

21. Click Show Data Labels on the menu. Notice the values added to the bar.  Right-click one of the new data labels, and then click Show Data Labels to toggle the data labels back off.

22. Preview the report to review the charts and other graphical elements explored in this Try It.

23. Leave Report Builder open for the next Try It.

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.

Charts

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. Report Builder, has a Chart Wizard template which allows you to quickly create a report with a chart. Additionally, when you are in the Report Design view, you can add charts to existing reports either manually, or by launching the New Chart wizard.

Adding a Chart without the Wizard

Like the table and matrix report items, charts can be added to a report by clicking the Chart report item from the Insert tab and then either clicking Chart Wizard; or by clicking Insert Chart, and then clicking inside the report body.

If you add the Chart report item using the Insert Chart option, Report Builder 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 6.

Figure 6: Select Chart Type Dialog Box

Charts are data regions and are required to be bound to a dataset. If you do not yet have a dataset defined within your report, Report Builder 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 7.

Figure 7: Chart Data Window

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.

• Chart Legend
• Chart Area
• Title
• Series
• Series Group
• Category Group

You can choose between custom and built-in palettes for charts. If you will be printing your report in black and white, consider using the Greyscale palette.

Creating a Chart Report

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 years' sales based on the information located in the SalesPerson table. Please note that the sample database is a static entity, so the current year in the database will likely not be the same as the actual current date.

Figure 8: Select Chart Type 3-D Columns

1.    Click File | New.

2.    On the New Report page, click Blank Report.

3.    Change to the Insert tab, click the drop-down arrow below Chart, and then click Insert Chart.

4.    Use the mouse to click and drag an outline that defines the size of your new chart.

5.    When the Select Chart Type dialog box opens, click the 3-D Column type as shown in Figure 8.

6.    Click OK.

Figure 9: Dataset Properties

7.    In the Dataset Properties window, type SalesTerritory in the Name field.

8.    Select Use a shared dataset.

9.    Select the SalesTerritory shared dataset. If it is not visible, click Browse and locate it in the /Datasets folder.

10. Click OK.

Figure 10: Configuring the Chart Title

11. Click on the words Chart Title to activate the title area of the chart.

12. Right-click the chart title area, and then click Title Properties.

13. On the General page, remove Chart Title from the Title text box, and then type Annual Sales Comparison by Territory.

14. On the General page, review the Title position options by selecting different radio buttons. When you are finished exploring these options, return to the Chart Title Properties dialog box.

15. Click Font in the page selection area on the left side of the Chart Title Properties dialog box.

16. On the Font page, select Tahoma for the Font.

17. Select 12pt for the Size.

18. Select Bold for the Style.

19. Click OK.

Figure 11: Configuring the Chart Data

20. Click somewhere within the picture of the chart to activate the Chart Data window.

21. Drag the SalesYTD field from the Report Data Window to the Values area of the Chart Data window.

22. Drag SalesLastYear field from the Report Data window to just below the [Sum(SalesYTD)] entry in the Values area of the Chart Data window. Your Values area should look similar to Figure 11.

23. In the Category Groups area of the Chart Data window, right-click the Details group, and then click Category Group Properties.

Figure 12: Group Properties

24. In the Category Group Properties window, select [Territory] from the Label drop-down list.

25. In the Group expressions area, click Add.

26. In the Group on drop-down box, select [Territory].

27. Click Sorting to change to the Sorting page.

28. On the Sorting page, Click Add.

29. Select [Territory] in the Sort by drop-down list.

30. Click OK. Your Chart Data should look similar to Figure 13.

Figure 13: Chart Data Window

31. Click the Run button to preview your report. Your Design and Run views should look similar to Figure 14 and Figure 15, respectively.

Figure 14: Design View

Figure 15: Run View

32. If time permits, experiment with other chart options, types and formatting.

33. Save your report as ChartSampleRptX in your /StudentX folder.

34. Leave Report Builder for the next Try It.

You can find a completed report at: /Answers/Ch7-ChartSampleRptCompleted-1.

If you are adding multiple charts to your report and using the same categories across the charts, you may want the colors to coordinate.  For example, you might want the Bikes category to consistently display as a red bar, Accessories as yellow, and Clothing as green. You can find more information on how to accomplish this in the SQL Server 2014 documentation under "Specify Consistent Colors across Multiple Shape Charts (Report Builder and SSRS)" and also at http://msdn.microsoft.com/en-us/library/dd239350.aspx.

Using the Chart Wizard

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.

Using the Chart Wizard in Report Builder

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.

Figure 16: The Chart Wizard

1.    Click File|New.

2.    In the New Report or Dataset dialog box, on the New Report page, click Chart Wizard.

3.    On the Choose a dataset page of the New Chart wizard, click Create a dataset.

4.    Click Next.

5.    On the Choose a connection to a data source page, select the AdventureWorks shared data source. If necessary, click Browse, and then switch to the /Data Sources folder, select the AdventureWorks data source, and then click Open.

6.    Click Next.

Figure 17: Command Area of the Query Designer

7.    On the Design a query page, click the Edit as Text button.

8.    In the text window, type the following code into the command area as shown in Figure 17. Note: This passage of code can be found in \Classfiles\ReportBuilder\Starter Solutions\Ch7-Queries.txt under Query1. The code can be copied and pasted into the appropriate area.

SELECT  Sales.SalesTerritory.Name
     ,Sales.SalesOrderHeader.SubTotal
FROM  Sales.SalesTerritory
    INNER JOIN Sales.SalesOrderHeader
      ON Sales.SalesTerritory.TerritoryID =
                   Sales.SalesOrderHeader.TerritoryID

9.    Click Next.

Figure 18: Choosing a Chart Type and Arranging the Chart Fields

10. On the Choose a chart type page, click Pie.

11. Click Next.

12. On the Arrange chart fields page, drag SubTotal from the Available Fields box to the Values box.

13. Drag Name to the Categories box.

14. Click Next.

15. On the Preview page, click Finish.

16. The new report and chart are automatically opened and displayed in Report Builder. Your report should look similar to the one in Figure 19.

Figure 19: Report Builder Results

Figure 20: Adding Titles and Data Labels

17. Click in the area labeled Click to add title and type Sales by Territory.

18. Change the chart title to Sales in Thousands.

19. Right-click in the center of the pie chart, and then click Show Data Labels.

Figure 21: Series Label Properties

20. Right-click on any of the Data Labels that were added in the previous step, and then click Series Label Properties.

21. Click Number to switch to the Number page.

22. On the Number page, click Currency in the Category box

23. Change the Decimal places to 0

24. Select the Use 1000 separator (,) option

25. Select the Show values in option, and verify that Thousands is selected in the drop-down box.

26. Click OK.

Figure 22: Resizing Arrows

27. Click on a blank area of the report.

28. Pause the mouse over the dotted line separating the graph from the page footer, until the double arrow appears as shown in Figure 22. Click and drag the line down to approximately the six inch mark on the ruler.

29. Click anywhere in the chart report item to activate it.

30. Use the resizing handles to adjust the graph to fill the report horizontally, while trying to maintain a pleasing aspect ratio.

31. Click Run in the Views section of the Home ribbon to preview your report. Your report should look similar to the one in Figure 23.

Figure 23: Finished Report

32.  Save your report as SampleChartWizardRpt in your /StudentX folder.

33. Leave Report Builder open for the next Try It.

A completed report can be found at/Answers/Ch7-SampleChartWizardRpt Completed-1.

Gauges

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.

Adding Guages

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.

1.    Click the Report Builder button, and then click Open.

2.    Browse and Open /SampleReports/SampleGaugeRpt .

3.    Use the Save As option to save a copy of the report as SampleGaugeRptX in your /StudentX folder.

Figure 24: Adding a Gauge

4.    Click the gauge item from the Insert ribbon.

5.    Click in the cell in the second row of the empty column on the right side of the report.

6.    On the Select Gauge Type dialog box, click Bullet Graph.

7.    Click OK.

Figure 25: Linear Pointer Properties

8.    Select the Gauge in the table (not the text box holding the gauge). This will make the Gauge Data window visible to configure.

9.    Click the down arrow next to the LinearPointer1 row, and then click Pointer Properties.

10. In the Linear Pointer Properties dialog box, click the expression button next to the Value drop-down list.

11. In the Expression dialog box, type the following code.

=((Fields!SalesYTD.Value)/Fields!SalesQuota.Value)*100

12. Click OK to close the Expression window, and then click OK to close the Linear Pointer Properties window.

13. In the Gauge Data window, click the down arrow next to the LinearPointer2 row, and then click Delete Pointer.

14. Save your changes, and then run your report. Your report should look similar to Figure 26.

Figure 26: Final Gauge Report

You can find a completed report at /Answers/Ch7-SampleGaugeRptCompleted-1.

Add a Map to a Report

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.

Like other features in Report Builder, you can choose to add a map directly to an existing report, or you can make use of the Map Wizard.

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:

• Map Gallery: A set of default reports into which your spatial data is embedded. For a default instance of Reporting Services, the map gallery is located at \Program Files\Microsoft SQL Server\MSRS11.MSSQLServer\Reporting Services\ReportServer\ReportBuilder\RptBuilder_3\MapGallery.
  
  
• ESRI shapefile: A set of files that contain geometrical shape data such as points, polylines, and polygons in a shp file as well as properties or attributes that describe the shapes in a dbf file. These are often used in geographic information systems to represent particular regions, areas, or geographic features.
  
  
• SQL Server spatial query: Uses a query built to retrieve data from columns in your database with a geography or a geometry data type.

You can get more information about working with spatial data types in a white paper named New Spatial Features in SQL Server Code-Named "Denali". When this book was published, this white paper could be found at http://go.microsoft.com/fwlink/?LinkId=226407 . Please note that Microsoft links and papers frequently move and change, but you can usually find it by searching on all or part of the title.

The areas of a map item are shown in Figure 27. Like the other visual report items, each of these areas has a set of properties that you will use to configure the map.

Figure 27: Map Areas

Creating a Map Report

By performing the following steps, you will create a report that contains a map showing sales from states within the US.

1.    In Report Builder, click File|New.

2.    On the New Report page of the New Report or Dataset dialog box, click Map Wizard.

Figure 28: Choosing a Source for Spatial Data

3.    The New Map wizard automatically opens. On the Choose a source of spatial data page select Map gallery.

4.    In the Map Gallery, select USA by State.

5.    Click Next.

6.    On the Choose spatial data and map view options page use the slider to scale the map, and use the arrows to move the map to resemble Figure 28.

7.    Click Next.

8.    On the Choose map visualization page select Color Analytical Map, and then click Next.

Figure 29: Choose an Analytical Dataset and Specifying Match Fields

9.    On the Choose the analytical dataset page, select Choose and existing dataset in this report or a shared dataset.

10. Select SalesMapFor2011. If the dataset is not visible, click Browse and change to the /Datasets folder to locate it.

11.  Click Next.

12. On the Specify the match fields for spatial and analytical data page, click the check box under Match fields next to STUSPS.

13. Select StateProvinceCode from the drop-down list under Analytical Dataset Fields as shown in Figure 29.

14. Click Next.

15. On the Choose color theme and data visualization page, in the Field to visualize drop-down list, select [Sum(SalesAmt)]. 

Figure 30: Choose Color Theme and Data Visualization

16. Set the Color rule drop-down to Red-Yellow-Green.

17. Click Finish.

18. If necessary, move the map to the upper left corner of the report body, and then resize the report to be approximately 10.5 inches wide x 6.5 inches tall.

Remember, you can enable the rulers by clicking View, and then clicking the checkbox for Ruler.

Figure 31: Adding Titles

19. Change the report title to Sales by State 2011.

20. In the Map Title text box, type the following: Click on any state to see detailed information for that state.

The actual implementation of the feature to go along with this textbox will be implemented in the drillthrough report Try It later in the course.

21. Save the report as SalesMap2011X in your /StudentX folder.

22. Run the report.

23. Return to Design view.

The following steps require Internet connectivity. If you do not have Internet connectivity, you can explore other map options.

Figure 32: Adding a Bing Map Layer

24. Click inside the map report item to activate the Map Layers window. You may need to click twice.

25. In the Map Layers window, click the Add Layer icon, and then click Tile Layer.

26. Click the down-arrow next to TileLayer1, and then click Tile Properties.

27. Set the Type to Aerial.

28. Click OK.

29. Click the drop-down arrow next to PolygonLayer1, and then click Layer Data.

30. On the Visibility page, set the Transparency (percent) property to 60, and then click OK.

31. Save your changes, and run the report to see the new Bing map layered underneath the colored state polygons.

32. If time permits, continue to explore the map options available in your report.

You can find a completed report at: /Answers/Ch7-SalesMap2011Completed-1 With Bing Layer. If you don't have Internet available, you can view the plain  /Answers/Ch7-SalesMap2011Completed-1 report for the mid-point solution without the Bing maps layer.

Data Bar, Indicator, and Sparkline Report Items

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.

Data Bars

A data bar is a very basic chart. Like a gauge, a data bar 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 incompatible features must be removed from the chart before conversion.

Indicators

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.

When you define the ranges, Report Builder will allow you to overlap the ranges, but data points that match exactly will go into the lower range.  For example, if you use .5 as the high end of the red range and the low end of the yellow range, the value .5 will always be red. If you do not overlap the values, you need to make sure that you carry your values to the highest level of decimal places represented by your data.  If a data value falls between the end of one range and the beginning of the next range, the indicator will be left blank.

Sparklines

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 selected field if it is a numeric field, or to 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 Figure 33. Like most fields in SSRS, you can also define a custom expression to provide the Y values for the sparkline.

Figure 33: Changing the Sparkline Field Data

Using Data Bars and Indicators

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 sales amounts for the "current year". The database does not link to a specific year in this case. In real life, a variable would be used for the year, but a fixed year was used for simplicity in the practice since the data never changes.

1.    In Report Builder, open the report named SampleDataVisualizationRpt.

2.    Use the Save As feature to save the report as SampleDataVisualizationRptX in your /StudentX folder.

Figure 34: Adding the Data Bar

3.    On the Insert tab in the Data Visualizations group, click Data Bar.

4.    Click in the text box in the second row of the column titled YTD Sales as shown in Figure 34.

5.    In the Select Data Bar Type dialog box, verify that the default type of Bar is selected in the Data Bar section.

6.    Click OK.

7.    Click in the center of the data bar that appears. The Chart Data window should appear.

8.    In the Chart Data window, click the plus sign in the ? Values area as shown in Figure 35, and then click SalesYTD.

Figure 35: Chart Data Window

9.    Click outside of the new data bar field to close the Chart Data window.

Figure 36: Adding an Indicator

10. On the Insert tab, click the Indicator report item.

11. Click in the text box in the second row of the column titled Sales Quota.

12. In the Select Indicator Type dialog box, in the Symbols area, click to select the 3 Flags type as shown in Figure 36.

13. Click OK.

14. Click the flag that appears. The Gauge Data window should appear with a row for Indicator1.

15. In the Gauge Data window, click the down arrow next to Indicator1, and then click Indicator Properties.

Figure 37: Indicator Gauge Data

Figure 38: Indicator Properties Dialog Box

16. Click Values and States to change to that page.

17. Change the States Measurement Unit to Numeric.

18. Change the following values in the Indicator states area:

    a.    Red: Start 0 – End .5

    b.   Yellow: Start .5 – End .99

    c.    Green: Start .99 – End 100

Note: The finished page should look similar to Figure 38. The value of 100 in the end allows for salespeople who drastically exceed their quotas.

19. Click OK in the Indicator properties dialog box.

Figure 39: Gauge Data Window

20. In the Gauge Data window, click the down arrow next to (Unspecified) as shown in Figure 39, and then click Expression.

21. In the Expression windows, either click on the appropriate fields and arithmetic operator, or type the following expression.

=Fields!SalesYTD.value / Fields!SalesQuota.value

22. Click OK.

23. Save your changes, and then Run your report.

24. Leave Report Builder open for the following Try It.

You can find a completed report at:

/Answers/Ch7-SampleDataVisualizationCompleted-1.

Lab

Exercise 1: Adding data visualization report items

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.

1.    Use Report Builder to open and review the ProductSalesRpt report. Save the report to your StudentX folder.

2.    Add a sparkline to the last column of the row associated with the CalendarYear group total. Use the Line with Marker Sparkline type.

3.    Configure the sparkline to include a category group based on the Month rather than the (Details) group. Note: This field uses the numeric value for each month of the year. If you are using a month name, you would need to be sure that your data was sorted based on a calendar and not alphabetically.

4.    Use a Sum of the SalesAmount field as the Values for the sparkline.

5.    Configure the series to include a tooltip that shows the sales amount total, formatted as Currency with no decimal places.

6.    Configure the Horizontal Axis to be a Scalar axis type and to have a Minimum value of 1 and a maximum value of 12.

7.    Copy the Sparkline item to the text boxes associated with the total rows for the Category and Product groups as well.

8.    Change the background colors of each grouping to something unique to help users differentiate the groups.

9.    Modify the marker and line colors of at least one of the Sparklines to help differentiate between them. Hint: The Color property in the Appearance section of the Chart Series Properties window, or on the Fill page of the Series Properties dialog box control the color of the lines connecting the markers.

10. Run the report.

11. You can find a completed report at: /Answers/Ch7-ProductSalesRptCompleted-1.

Solution to Exercise 1

Exercise 1: Adding data visualization report items

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.

1.    Use Report Builder to open and review the ProductSalesRpt report. Save the report to your StudentX folder.

(1)      In Report Builder, open, review, and run the /SampleReports/ProductSalesRpt report.

(2)      Use the Save As option to save your report to your /StudentX folder.

2.    Add a sparkline to the last column of the row associated with the CalendarYear group total. Use the Line with Marker Sparkline type.

Figure 40: Adding the Sparkline

(3)      From the Insert ribbon, double click the Sparkline report item.

(4)      Click in the last column of the row associated with the CalendarYear group total, as shown in Figure 40.

(5)      In the Select Sparkline Type dialog box, click Line with Markers, and then click OK.

3.    Configure the sparkline to include a category group based on the Month rather than the (Details) group.
Note: This field uses the numeric value for each month of the year. If you are using a month name, you would need to be sure that your data was sorted based on a calendar and not alphabetically.

Figure 41: Chart Data Window

(6)      Click near the middle of the new sparkline object to display the Chart Data window as shown in Figure 41.

(7)      In the Chart Data window, click the plus sign in the Category Groups section, and then click Month. Your Chart Data window should look similar to Figure 42.

4.    Use a Sum of the SalesAmount field as the Values for the sparkline.

Figure 42: Values Section of the Chart Data Window

When you add 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. By clicking on the down arrow next to the Y Value field, you can modify the field and/or the aggregate to be used by the chart

5.    Configure the series to include a tooltip that shows the sales amount total, formatted as Currency with no decimal places.

Figure 43: Series Properties

(9)      Click the down arrow next to the SalesAmount series, and then click Series Properties.

(10)    On the Series Data page, click the Expression button next to the Tooltip drop-down list.

(11)   In the Expression dialog box, type the following code to format the Sales Amount aggregate as currency with no decimal places:

=FormatCurrency(Sum(Fields!SalesAmount.Value),0)

(12)   Click OK to close the Expression window, and then click OK to close the Series Properties window.

6.    Configure the Horizontal Axis to be a Scalar axis type and to have a Minimum value of 1 and a maximum value of 12.

Figure 44: Horizontal Axis Properties

(13)   Right click the sparkline that you added to your report, and then click Horizontal Axis Properties.

(14)   In the Horizontal Axis Properties window, click Scalar (Numbers/Dates) for the Axis type

(15)   Clear the check from Always include zero.

(16)   Set the Minimum value to 1 and set the Maximum value to 12. Simply type the numbers in place of the word "Auto".  The Horizontal Axis Properties should be configured as shown in Figure 44. Click OK. 

7.    Copy the Sparkline item to the text boxes associated with the total rows for the Category and Product groups as well.

Figure 45: Sparkline Object Name

(17)   Select the Sparkline and press CTRL+C to copy it.

(18)   Select the Text Box associated with the total rows for the Category group and press CTRL+V to paste the Sparkline.

(19)   Similarly, select the Text Box associated with the Product groups and press CTRL+V to paste the Sparkline.

8.    Change the background colors of each grouping to something unique to help users differentiate the groups.

Figure 46: Product Group Row

(20)   Click the row handle to the left of the Product group row as shown in Figure 46.

(21)   In the Properties window, in the Fill section, click the drop down list to the right of the Background color field, and then click More colors.

(22)   In the Select color dialog box, click Gainsboro, and then click OK.

(23)   Click the row handle to the left of the Category grouping, and use the Properties window to change the BackgroundColor property to LightGrey.

(24)   Click the row handle to the left of the CalendarYear grouping, and use the Properties window to change the BackgroundColor property to Silver.

9.    Modify the marker and line colors of at least one of the Sparklines to help differentiate between them.
Hint: The Color property in the Appearance section of the Chart Series Properties window, or on the Fill page of the Series Properties dialog box control the color of the lines connecting the markers.

Figure 47: Category Chart Series Properties

(25)   Click in the sparkline text box associated with the Category group, and then click again directly on the line in the text box. This should bring the focus to the Chart Series.

(26)   In the Properties window for Chart Series, expand the Marker item within the Appearance section.

(27)   Change the BorderColor and Color properties associated with the marker to Blue.

(28)   Additionally, just above the Marker properties, change the Color Setting to Blue. The completed settings are shown in Figure 47. Note: The Color property above Marker controls the color of the line connecting the markers.

(29)   Similarly, change the markers and line colors associated with the CalendarYear group to Midnight blue.

10. Run the report.

Figure 48: Completed Report

(30)   Run the report. The completed report should look similar to Figure 48.

(31)   Save your changes to the report.

You can find a completed report at: /Answers/Ch7-ProductSalesRptCompleted-1.