Chapter 7. Intermediate Reporting Concepts
In This Chapter
Adding a Chart Item to a Report
Demonstrating Template Creation
Demonstrating Report Parameters
Demonstrating Adding Chart Items
Intermediate reporting concepts build on the foundational information discussed throughout Chapter 5, “Basic Report Design,” and Chapter 6, “Building a Basic Report.” This chapter describes how to create report templates and how to use those templates when fulfilling new report requests. A key component to report writing, as mentioned in Chapter 5, is ensuring that the report data fits the width of a single page. This chapter walks through how to export a report to verify that no items are cut off from the page—and if they are, how to fix the report.
Another essential feature of report writing is the ability to add parameters to reports. Using parameters produces reports that prompt viewers for details to filter and tailor the resulting data. Using parameters allows you to create more generic reports and lessens the need to write reports for specific requests. Several different styles of parameters can be utilized, including simple, dropdown, and multi-value. Each type of parameter has different capabilities to help the reader find the needed data. Each of these styles is described and demonstrated in this chapter.
This chapter also describes various types of chart items, which you can use to provide a visual representation of the data being returned from a report. A chart, the main report item used in summarized data reports, gives readers a high-level overview of the requested data. You can use several types of chart items to represent different types of data. From these reports, you can add actions to allow the reader to drill into the data to get further detailed information. Actions can build on general reports by passing along parameter values; these are useful for adding flow to the reports without requiring the reader to run several individual reports.
Creating Report Templates
Chapter 5 discussed the benefits of creating and using report templates and the common paper sizes typically used when building templates. Not only can templates provide a consistent look and feel to your reports, they save time when you’re creating a new report. The following sections discuss how to create template reports for common page sizes to ensure that data does not get cut off the width of the page and how to copy and use templates to create a new report. Note that you can also create other templates to fit any paper size, using the same methods and steps detailed here.
Creating a Report Template (8.5×11in)
You can create report templates to match any paper size used in your environment. The steps to create a report template are the same for any page size. To illustrate the process, this section discusses how to create a letter portrait (8.5×11) template. Follow these steps:
1. If you have not already done so, in SQL Server Data Tools 2014 Business Intelligence (SSDT-BI) for Visual Studio 2013, open the project containing your Patch Compliance Progression report. This example uses the project created in Chapter 6, named SSRS US.
2. To create a new report, right-click the Reports folder under the Solution Explorer in the top-right section of the screen and select Add -> New Item.
3. In the Add New Item dialog shown in Figure 7.1, select Report from the list of items. Under the Name field, enter _Template (8.5x11).rdl and click Add.
4. In the Properties section at the bottom right, use the dropdown to change the focus from Body to Report, as shown in Figure 7.2.
5. Scroll down in the Properties section and expand the InteractiveSize property so you can configure the values to match the paper size. Set the Width value to 8.5in and the Height value to 11in, as shown in Figure 7.3.
6. Below the InteractiveSize section expand the Margins property and set the Left, Right, Top, and Bottom values to 0.25in each, as shown in Figure 7.4.
Tip: Margin Sizes
Regardless of the paper size being used, the authors recommend using 0.25in margins on each side of the page. This allows the maximum space of the page to be used for the report while maintaining a small, professional-looking border around the page when printed or exported to Word and PDF formats.
7. Below the Margins section, expand the PageSize property and set the Width and Height values to the same size as the InteractiveSize property set in step 5. For this example, set the Width value to 8.5in and the Height value to 11in, as shown in Figure 7.5.
8. Change the focus in the Properties section to Body section. Expand the Size property. Set the Width value to the same width as the InteractiveSize minus the Left and Right margin sizes. For this example, set Width to 8in (that is, InteractiveSize Width of 8.5in minus 0.25in for the left margin and 0.25 for the right margin), as shown in Figure 7.6.
Tip: Ensure that the Report Width Does not Expand
As you add and resize report items, the size of your body section may change and expand. Keep an eye on the width to ensure that it does not exceed the InteractiveSize minus both left and right margins, or data will be split onto a separate page. To help with this, enable the ruler on your report by right-clicking the body of the report and selecting View -> Ruler.
9. Add header and footer sections to the report. Configure the desired style and formatting that will be common for every report in your environment. This can include adding a Report Title text box as a placeholder in your header and text boxes for the page count and date expressions in the footer section. Don’t forget to add any desired logos and background colors to the report. When you’re done with this, save the report. Figure 7.7 shows an example of a template report.
Real World: Report Height Size
The authors have mentioned the report size multiple times to ensure that data does not surpass the page width and get cut off to another page. The only consideration regarding the report height size is to make certain that items in the Design mode do not exceed the InteractiveSize height minus the top and bottom margins (10.5in for the example in this section).
Table rows automatically continue to flow onto following pages; however, if your design includes items that exceed the height size, they will get cut to following pages and will become out of sync on paper. When a report is executed, items in the Body section of the report are fully populated in the order in which they appear in Design mode. As an example, if your report includes a table above a chart, the chart item will not appear in the report until all rows of the table are populated.
Creating a New Report from a Template
You can use template reports to build new reports. To create a new report using a template, simply make a copy of the template and rename it to the desired title of your new report. Perform the following steps to create a copy of a report:
1. In the Solution Explorer on the right side of SSDT-BI, under the Reports folder, right-click your template report. This example uses the _Template (8.5x11).rdl report created in the previous section. Select Copy, as shown in Figure 7.8.
2. On the SSDT-BI toolbar, select Edit and click Paste, as shown in Figure 7.9.
3. Notice the new report named Copy of _Template (8.5x11).rdl in the Solution Explorer, under Reports. Right-click this report and select Rename. Enter the name of the new report, as shown in Figure 7.10.
4. Double-click the new report to open it in Design mode. Notice the tab at the top of the main section of SSDT-BI, above the Design and Preview buttons, indicating the name of the report you are currently viewing and editing (see Figure 7.11).
Exporting Reports from SSDT-BI
To confirm that a report is the proper width for the page size and that data columns from a table will not be cut off onto separate pages, you can export reports to PDF from SSDT-BI Preview mode. Perform the following steps to export a report within SSDT-BI:
1. Open the Patch Compliance Progression.rdl report from the Solution Explorer.
2. Click Preview at the top of the report. From Preview mode, click Export and select a file type. For this example, the PDF option is selected, as shown in Figure 7.12.
3. In the Save As dialog, select a location to save the report and click Save.
Modifying the Page Size of an Existing Report
The PDF export of the report, displayed in Figure 7.13, shows that the last column of the table does not fit within the first page and gets cut to the second page. Keep in mind that viewing a report in PDF format provides an accurate representation of how the report will appear when printed on paper. The page width problem is not noticeable when you preview the report in SSDT-BI, as you can see in Figure 7.12, where SSDT-BI displays all four columns. To correct the page width issue with the Patch Compliance Progression report, the Report and Body sizes must be adjusted to match the values detailed in the template creation steps. Follow these steps to modify the size of an existing report:
1. In Design mode, in the bottom-right corner of the Properties section, change the focus to Report.
2. Ensure that both the InteractiveSize and PageSize properties are set to 8.5in, 11in. Expand the Margins property and set the values for the Left, Right, Top, and Bottom properties to 0.25in.
3. In the Properties section, change the focus to Body.
4. Expand the Size property and ensure that the Width property value is set to 8in.
5. Once all the settings are properly set and verified, preview the report. In Preview mode, click Export and select PDF.
6. In the Save As dialog, select a location to save the report and click Save.
7. Open the newly saved report to validate that all columns appear properly on a single page width, as shown in Figure 7.14.
When items do not fit within a column width, the problem is usually the margin values and the body size values. Always validate that the BodySize value is equal to the paper width (InteractiveSize width) minus the left and right margin values.
Using Report Parameters
This section of the chapter explains parameters, their available options, and how to use them in a report to prompt the reader for information. Adding a prompt by using a parameter allows the report to adapt and tailor the data to specific information requested by the reader. It is also a great way to reduce the number of individual reports that need to be created. For example, rather than creating one report to show the patch compliance progression for all systems, a second report to show patch compliance for desktops only, and a third for server systems, you can add a simple prompt to a single report to let the user specify the data he or she wants to see.
Understanding the Value and Label Fields
It is easy to get confused by the value and the label fields because they are very similar. However, they each play a very important role when it comes to defining a parameter:
Value: The value field contains the data that is passed to a parameter (variable) in the query. Think of the value field as the ResourceID or CollectionID column data.
Label: The label field is the user-friendly name that is displayed as part of the prompt. It is translated or linked to the value field, which is passed to the query.
For example, the reader would see the computer name or the collection name. The label field for a collection would show as All Systems, and its value would be SMS00001, which would be passed to the query to run the report.
Using Default Values
The default values of a parameter are used to define the initial values that appear when a report runs. If the user does not change a value, the report is executed using the defined default value. This field is helpful when a report is most often used with the same parameter value and requires only infrequent changes.
An example of a default value would be the SMS00001 value to identify the All Systems collection for the Patch Compliance Progression report. A majority of the time this report would run to view the compliance of all systems. Running the report to see all systems does not require changes to the prompt value; however, for the occasional time that you need to compare the compliance only for your server fleet, you can change the value from the default.
Understanding Cascade Prompts
You use a cascade prompt when a report requires multiple parameters. It allows the value of one prompt to feed or limit the values available for the next prompt(s). For example, say that there are two dropdown boxes that contain lists of values:
The first dropdown box prompts the reader to select a country.
Based on the country selected, the second prompt provides a list of states or provinces to be selected.
Using Multi-Value Parameters
You use a multi-value parameter to provide additional flexibility for your readers when running a report. As its name suggests, a multi-value parameter allows readers to specify more than one value to be passed to a query. For example, if you have a report that lists computers in a collection that has a parameter and a prompt for the collection name, you can enable the option Allow multiple values on the parameter. This way, readers can select more than one collection when running the report. It is important to note that your report query must accept the parameter using the IN criteria instead of = or LIKE.
Adding a Simple Prompt
When creating reports, you may sometimes want to be able to slightly modify the report’s dataset (query) by adding another column or adding a prompt to make your report more practical or reader-friendly. Whatever the change, you can modify the dataset directly in SSDT-BI without needing to go back to SQL Server Management Studio. In this section you will add a prompt to the Patch Compliance Progression report created in Chapter 6 to select a collection so you don’t have to specify the CollectionID value of SMS00001 directly in the query. The change makes for a much more versatile report. Follow these steps to add the prompt:
1. In SSDT-BI, open the Patch Compliance Progression report by double-clicking the report item in the Solution Explorer in the top-right corner.
2. In the Report Data section in SSDT-BI, expand the Datasets folder. Right-click PatchDataset (or the name of the dataset you created) and select Dataset Properties, as shown in Figure 7.15.
3. In the Query section, scroll down to find the WHERE section and locate the line from Listing 7.1 specifying the CollectionID as SMS00001.
LISTING 7.1 Original WHERE Section Specifying the SMS00001 Collection
And FCM.CollectionID = 'SMS00001'
4. Modify the line shown in Listing 7.1 to use @Coll as a parameter for CollectionID, as shown in Listing 7.2. After modifying the query (as shown in Figure 7.16), click OK to apply the changes and close the Dataset Properties dialog.
LISTING 7.2 Modified WHERE Section Using the @Coll Parameter for CollectionID
And FCM.CollectionID = @Coll
5. To confirm that the change is successful and view a list of all parameters used in the report, expand the Parameters folder in the Report Data section in SSDT-BI. Notice the new @Coll parameter, shown in Figure 7.17.
6. Preview the report by clicking the Preview tab to view the parameter prompt. Notice that the report prompts for the @Coll parameter before it can be executed, as shown in Figure 7.18.
7. To view the report, enter SMS00001 as the value for the Coll prompt and click View Report on the far right of the Preview tab. The report executes and displays query results from the SMS00001 (All Systems) collection, as shown in Figure 7.19.
Adding a Dropdown Prompt
In the previous section you added a simple prompt to a report to tailor the query for a specified ConfigMgr collection. To identify the targeted collection, the CollectionID had to be manually keyed at the report prompt in order to view the resulting data. Unfortunately, it is not easy to remember specific collection IDs; most readers who are not ConfigMgr administrators may not even know what a collection ID represents or how to find one. To make it easier for everyone reading the report, you could add to the report a dropdown list of collection names from ConfigMgr rather than displaying a simple prompt. To do so, you would start by adding a second dataset to get all collection names and their matching collection IDs and then you would change the parameter to get available values from a query in the new dataset. Perform the following steps to create the dataset and modify the parameter:
1. In the Report Data section in SSDT-BI, right-click the Datasets folder and select Add Dataset, as shown in Figure 7.20.
2. In the Dataset Properties dialog, enter a name for the dataset. For this example, use the name PromptColl. Select the option Use a dataset embedded in my report. Under the Data source field, using the dropdown, select your data source. Under the Query field, enter the query shown in Listing 7.3 to get a list of all collection names and their associated CollectionIDs. (For your convenience, this listing is available as online content; see Appendix C, “Available Online,” for details.), Figure 7.21 shows the completed Dataset Properties dialog. Click OK to create the new dataset.
LISTING 7.3 Query for All Collection Names and IDs
SELECT
Col.Name,
Col.CollectionID
FROM
v_Collection Col
ORDER BY
Col.Name
3. In the Report Data section in SSDT-BI, expand the Parameters folder, right-click the Coll parameter, and select Parameter Properties, as shown in Figure 7.22.
4. In the Report Parameter Properties dialog, on the General tab under the Prompt field, enter a reader-friendly prompt title such as Select a Collection, as shown in Figure 7.23.
5. Click the Available Values tab on the left side of the Report Parameter Properties dialog and select the Get values from a query option. Using the dropdown under the Dataset field, select the PromptColl dataset created in step 2. Set the Value field to CollectionID. This is the value that is passed to the query when the report is executed. Under the Label field, select Name to identify the dataset column that will appear in the dropdown list for the reader to select.
When the Report Parameter Properties dialog is complete, as shown in Figure 7.24, click OK to apply the changes and close the dialog.
6. Preview the report; notice that the prompt title now shows Select a Collection and the prompt is now a dropdown menu of all available collections from ConfigMgr displayed by Name, as shown in Figure 7.25. When a collection is selected, the CollectionID is passed to the query, and the results are displayed.
Adding a Multi-Value Parameter
Adding a multi-value parameter is a good way to add flexibility to a report. A report with a simple parameter that accepts only a single value requires that the reader run the same report multiple times to get data from different collections. Using a multi-value parameter removes this requirement by allowing the reader to select multiple collections at once. You must modify the dataset slightly before enabling this feature. Follow these steps to modify the dataset and enable the multi-value feature:
1. In the Report Data section in SSDT-BI, expand the Datasets folder. Right-click PatchDataset (or the name of the dataset you created) and select Data Properties.
2. In the Query section, scroll down to find the WHERE section and locate the line from Listing 7.4, which indicates that the CollectionID is using the @Coll parameter.
LISTING 7.4 Original WHERE Section Specifying the @Coll Parameter
And FCM.CollectionID = @Coll
3. Replace the = criteria with IN, as shown in Listing 7.5.
LISTING 7.5 Updated WHERE Section Using IN Criteria for the CollectionID Parameter
And FCM.CollectionID IN (@Coll)
4. Once the query is updated, as shown in Figure 7.26, click OK to apply the changes and close the Dataset Properties dialog.
5. In the Report Data section in SSDT-BI, expand the Parameters folder, right-click the Coll parameter, and select Parameter Properties.
6. In the Report Parameter Properties dialog, check the Allow multiple values check box, as shown in Figure 7.27. Click OK to apply the change and close the Report Parameters Properties dialog.
7. Preview the report to view the parameter changes. Notice that in the Select a Collection dropdown there are now check boxes next to the collection names, as shown in Figure 7.28. Adding checks next to multiple collections and clicking View Report gets results from all the selected values.
Adding a Chart Item to a Report
Charts are very useful in reporting as they provide a clean visual representation of data. Charts are often included in summarized data reports to provide an overview of the information before a user drills down to a detailed report, as discussed in Chapter 5. Several different styles of charts, discussed in the following sections, can be used in SQL Server Reporting Services (SSRS) reports to represent different types of data.
Using Column Charts
As shown in Figure 7.29, there are several styles of column charts, including simple columns, stacked columns, cylinders, and stacked cylinders. These types of charts are useful for showing data representing counts of multiple deployed versions of an application, application metering information, deployed hardware models, operating system (OS) versions, and more.
Using Bar Charts
As shown in Figure 7.30, bar charts are very similar to column charts. The primary difference between the two is that a bar chart displays data horizontally rather than vertically. Both bar and column charts have the same types of styles available and are also useful for showing similar types of data—typically counts of various resource information, including total installed application versions, hardware types, and operating systems.
Using Line Charts
Line charts display data as a set of points connected by a line. Typically these types of charts are used to show large amounts of data over a period of time. Some examples of reports ideal for line charts are application metering (monthly usage of an application over a year) and monthly successful software updates by OS version. Line charts are available in different styles, including basic lines, smooth lines, stepped lines, 3D lines, lines with markers, and smooth lines with markers. Figure 7.31 shows these different styles.
Using Pie Charts
A pie chart, as the name suggests, displays data as a wedge or section of a pie. Pie charts are common in SSRS reporting for showing data as a percentage, as in deployments (complete vs. incomplete or success vs. failed), application upgrades and patching status (compliant vs. noncompliant), and breakdowns of hardware models or operating system versions. The pie chart is available in the different styles shown in Figure 7.32: the standard pie, exploded pie, 3D, and 3D exploded pie.
It is important to note that it becomes very difficult to read a pie chart if there are a large number of unique data wedges in the pie. In cases where it is difficult to read a pie chart, it is usually best to change the type to either a column or bar chart.
Using Doughnut Charts
A doughnut chart is identical to a pie chart with the exception that it shows data as sections, or bites, of a doughnut instead of wedges of a pie. This type of chart is available in two styles: standard doughnut and exploded doughnut (see Figure 7.33). Both pie and doughnut charts are part of the Shapes chart section in SSDT-BI.
Using Other Chart Types
There are several other SSRS-supported chart types and styles to choose from within SSDT-BI, including pyramid, funnel, area, range, scatter, and polar charts. These other chart types are not often used to represent ConfigMgr data; however, they are available and can be used if you desire. Figure 7.34 shows these remaining chart types.
Adding a Chart to a Report
Adding a chart item to a report in SSDT-BI is a simple process, although getting the chart to display the proper information requires a bit more effort than with a table item. To demonstrate adding a chart to a report, in this section you will modify the Patch Compliance Progression report to remove the existing table and add a pie chart in its place. In this way, you will transform the report into a summarized data report. Follow these steps to add and configure a chart item:
1. Create a copy of the Patch Compliance Progression report and rename the copy Patch Compliance Progression Chart.rdl.
2. Double-click the new report to open it. Notice the tab above Design/Preview that indicates the report you currently have open.
3. Before adding the chart item, delete the existing table from the report by selecting the table item in the body section, right-clicking the top-left corner of the table where the column and row frames meet, and selecting Delete (see Figure 7.35).
4. Add a chart item to the report. From the toolbox, drag the chart item to the body of the report.
5. In the Select Chart Type dialog select the desired chart—in this case the exploded pie chart, under the Shapes type (see Figure 7.36)—and click OK to add the item. The exploded pie chart is now added to the body of the report. Notice that the chart is populated with sample wedges to let you see how it will look when you run your report.
6. Before adding data to the chart, the dataset must be modified slightly to have the Status field return only the number of missing patches for a machine. By showing only the integer value, the pie chart’s wedge size accurately represents the number of patches required for each system. Under the PatchDataset folder, right-click PatchDataset (created in Chapter 6) and select Properties. In the Query section, find the CASE statement that identifies the Status column shown in Listing 7.6.
LISTING 7.6 Original CASE Statement Identifying the Status Column
CASE
When (sum(case when UCS.status=2 then 1 else 0 end))>0 then
('Needs '+(cast(sum(case when UCS.status=2 then 1 else 0 end)
as varchar(10))+ ' Patches'))
Else 'Good Client'
End as 'Status',
7. Replace the code in Listing 7.6 with the new, simpler status column shown in Listing 7.7. This new column returns only the total (sum) number of missing patches for a system. Click OK to close the Dataset Properties dialog.
LISTING 7.7 Replacement Status Column in the SELECT Section
sum(case when UCS.status=2 then 1 else 0 end) as 'Status',
8. Select the chart in the body of the report to view the Chart Data section shown in Figure 7.37. Notice that there are currently no values associated with the chart.
9. To add data to the chart, drag and drop columns from your dataset to the desired sections in the Chart Data section. For this example, drag the updated Status column, under PatchDataset, into the Values area of the Chart Data section and the Name0 column to the Category Groups section (see Figure 7.38). This results in a wedge of the pie for each system name, where the wedge size is appropriate to the number of missing patches for that system.
10. Increase the size of the chart by selecting the item and stretching the outside edges of the object or by modifying the size values in the Properties section in the bottom-right corner of the screen. Also, center the chart within the report by clicking Center Horizontal in the SSDT-BI toolbar.
Tip: Sizing the Chart Item
The chart size when running a report is the same size as it appears in Design mode. This allows you to accurately size the chart in the body of your report without needing to preview the results to confirm the chart’s size and placement in the report.
11. When you’re satisfied with the size and placement of the chart, preview the report by selecting a collection from the dropdown prompt in order to view the report data (see Figure 7.39).
Customizing Chart Items
The previous section discussed adding to a report a simple chart item with default settings. Chart items contain many modifiable features and areas. These modifications include enabling 3D effects for the chart to improve individual wedge visibility, adding data labels to the chart to identify the value represented by each wedge, and placing the legend and title areas. In this section you’ll learn how to enable 3D effects, remove the chart title, and move the legend to the bottom of the chart. Follow these steps:
1. In Design mode, right-click the pie section of the chart and select 3D Effects.
2. In the Chart Area Properties dialog, check the Enable 3D option, shown in Figure 7.40. You can also modify the Rotation (degrees) and Inclination (degrees) values. When you are finished, click OK to apply the changes and close the Chart Area Properties dialog. The 3D effects are immediately visible in Design mode, so you can see that the settings were applied successfully.
3. Remove the title area by right-clicking the Chart Title label and selecting Delete Title, as shown in Figure 7.41.
4. To move the location of the legend within the chart item, right-click the legend area of the chart and select Legend Properties.
5. On the General tab of the Legend Properties dialog, select the placement of the legend by selecting a radio button under Legend position. Three different positions are available for each of the four sides of the chart item. For this example, the bottom center position is selected, as shown in Figure 7.42. Click OK to apply your changes. The legend is immediately moved to the selected location of the chart, and you don’t need to preview the report.
6. To add data labels to the chart, right-click the pie chart and select Show Data Labels (see Figure 7.43). You now see the data labels on the sample pie chart in Design mode.
7. To make the labels easier to read, left-click a data label in the chart to select it. Notice that all labels are selected automatically. Set the font of the labels to bold by clicking Bold in the SSDT-BI toolbar or by changing the FontWeight value under the Font section of the Properties area in the bottom right of the screen and shown in Figure 7.44.
8. Preview the report to see all the changes with actual data populating the chart, as shown in Figure 7.45. Notice the 3D effect on the chart and the legend displayed below the pie chart. Because there are so many individual wedges in the pie chart, the labels are difficult to read and often overlap each other because they are displayed within each wedge.
9. To properly read the data labels of a chart when there are multiple wedges, add a label to the outside of the pie wedge by returning to Design mode and selecting the pie chart. In the Properties area in the bottom-right corner of the screen, expand the CustomAttributes section and change the value of PieLabelStyle from Inside to Outside, as shown in Figure 7.46.
10. Notice that the data labels are now displayed outside the chart, and each is linked to its respective wedge with a line. To increase or reduce the distance between the data labels and the chart, in the same Properties area, change the value of 3DLabelLineSize to a number between 30 and 200. In the example shown in Figure 7.47, the value is changed to 30, reducing the distance of the label.
11. Preview the report to view the changes using actual ConfigMgr data, as shown in Figure 7.48. Notice that the labels now appear outside the individual wedges, making it much easier to read the data.
Although a pie chart item was used here to demonstrate and explain the different customizations, you can use the same procedure to apply customizations to all other chart items in SSDT-BI. As you create reports using different chart types, try exploring the different settings to find a style that suits you and your report readers.
Adding an Action to a Chart
Adding an action on a chart allows you to link the chart’s data to another report to provide further details. Say you want to view the updates of a specific computer. If you use an action, the reader can click on a pie chart’s wedge for a specific computer name to be taken to a detailed report showing a list of patches. Follow these steps to add an action to a chart:
1. In Design mode, right-click the pie section of the chart and select Series Properties, as shown in Figure 7.49.
2. In the Series Properties dialog, select the Action tab on the left. Under Enable as an action, select Go to report. Under Specify a report, using the dropdown, select the Patch Compliance Progression report.
In order to pass along the same collection parameter, under Use these parameters to run the report, click Add. As the Name field, use the dropdown and select Coll. As the Value field, click the Function (fx) button to the right of the dropdown.
3. In the Expression dialog, select the Parameters category. Double-click Coll under Values to add it to the expression, as shown in Figure 7.50, and click OK.
4. Now that the Series Properties dialog is complete (as shown in Figure 7.51), click OK to apply the action.
5. Preview the report and hover over a pie wedge. Notice that the cursor changes to identify that the pie wedge contains a link and can be clicked. Click on any pie wedge. You are now taken to the report identified in the action, the Patch Compliance Progression report, as shown in Figure 7.52. Notice that the second report is displayed without a prompt to select a collection. This is because of the expression configured in step 3, where the value of the Coll parameter was selected. This passed the value selected in the original chart report to the Patch Compliance Progression report.
Demonstrating Template Creation
In this demonstration you will create a template report for three common paper sizes and modify existing reports to fit the template size. The purpose of this exercise is to practice and become familiar with creating reports and changing page size values. A properly created template provides a strong foundation for all reports in your environment and allows report creators to be efficient when creating newly requested reports.
Creating Template Reports
Following the instructions shown throughout the “Creating Report Templates” section of this chapter, create three report templates that meet the following criteria:
Create a template report for each of the following paper sizes:
Letter portrait (8.5×11in)
Letter landscape (11×8.5in)
Legal landscape (14×8.5in)
Ensure that each template’s margins are set to 0.25in, 0.25in, 0.25in, 0.25in.
Include a header section containing a text box item as a placeholder for the report’s title.
Include a footer section containing text boxes for the page count and current date expressions. Also add an image item with a logo.
Be sure to create and save each of the templates in the SSDT-BI project you created in Chapter 6.
Modifying Page Sizes for Existing Reports
Using the guidelines for creating report templates, modify the existing reports to meet the specified page sizes. Ensure that both the Patch Compliance Progression and Computer Hardware Information reports are updated to meet the following criteria:
Modify the Patch Compliance Progression report to fit a paper size of 8.5×11in.
Modify the Computer Hardware Information report to fit a paper size of 11×8.5in.
Modify the table item in the Computer Hardware Information report to span the full width of the report. Stretch the individual column widths accordingly.
Preview the completed report and export it to PDF. View the PDF report to ensure that all columns fit on the width of a page without any columns being cut off. Fix any problems you find and then export and examine the report again.
Export the reports to Excel and CSV and compare the features of the different formats.
Make sure to save both reports once everything is complete, as these versions will be used in the following demonstrations.
Demonstrating Report Parameters
The purpose of this demonstration is to help you become familiar with modifying datasets, adding parameters, and adding report prompts. Understanding how to create different types of prompts is important when you’re creating reports. The demonstrations in this section ask you to create and use multi-value prompts, which provide more options than simple and dropdown prompts. Note, however, that using multi-value prompts is not always a desired solution. Sometimes you need simple prompts with free-form values, and other times you might simply want a dropdown list.
When creating reports with prompts, use your best judgment about the type of prompt based on the type of report and its intended audience. Your goal is to make it easiest to use for readers who are not ConfigMgr administrators.
Updating the Patch Compliance Progression Report
Following the instructions in the “Using Report Parameters” sections of this chapter, make the following modifications to the Patch Compliance Progression report:
Add a prompt for a multi-value parameter named Coll.
Have the Coll parameter identify the CollectionID to filter the report data.
Make the prompt a dropdown list, allowing for multiple values to be selected. Ensure that the dropdown list includes all collection names from ConfigMgr.
Note: Displaying Collection Names in the Dropdown List
Remember to add a new dataset to get a list of all CollectionIDs and their matching names. Only the collection names should be visible in the dropdown prompt.
Preview the completed report to confirm that all settings and features are functioning properly, as detailed in this section. Save this report as it will be used in upcoming demonstrations.
Updating the Computer Hardware Information Report
Make a copy of your Computer Hardware Information report and name it Computer Hardware Information Prompt.rdl. Using the new copy of the report, modify the dataset as follows:
Add a parameter named Manu to the dataset query.
Have this new parameter identify the Manufacturer value to filter the report data.
Make the prompt a dropdown list, allowing for multiple values to be selected.
Ensure that the dropdown list includes all manufacturer names from the ConfigMgr environment.
Note: Adding the Manufacturer Prompt
Because the manufacturer prompt does not already exist, you need to modify the dataset query by adding a WHERE statement.
Preview the completed report to confirm that all settings and features are functioning properly, as described in this section. Save this report as it will be used in the upcoming demonstrations.
Demonstrating Adding Chart Items
The purpose of this demonstration is to become familiar with adding chart items to reports. Chart items are very useful for providing summarized data reports, giving the reader a high-level view of the data. When you add actions to a chart, the reader can choose to see additional information about the data provided.
Adding a Chart to the Patch Compliance Progression Report
Following the instructions in the “Adding a Chart Item to a Report” section of this chapter, make the required modifications to the Patch Compliance Progression report. Modify the report as follows:
Make a copy of the Patch Compliance Progression report and name it Patch Compliance Progression Chart.rdl.
Modify the query to change the Status column to return only the integer of the number of required updates.
Replace the table in the body section with a 3D exploded pie chart item.
Set the Name0 column as the Category Groups field of the chart.
Set the Status column as the Values field of the chart.
Add an action on the chart to link to the Patch Compliance Progression report.
Ensure that when a user clicks a pie wedge, the Patch Compliance Progression report opens and uses the CollectionID value selected in the Compliance Progression Chart report.
Preview the completed report to confirm that all settings and features are functioning properly, as detailed above. Save this report as it will be used in upcoming demonstrations.
Adding a Chart to the Computer Hardware Information Report
Make a copy of the Computer Hardware Information report, created in Chapter 6, and name the copy Computer Hardware Information Chart.rdl. Modify the new chart report as follows:
Replace the table in the body section with a 3D exploded pie chart to show all computer manufacturers and their representation (count) in the environment. Make the following changes in the table:
Set the Manufacturer column as the Category Groups field of the chart.
Set COUNT (Manufacturer0) as the Value field of the chart.
Add data labels, in bold font, to the chart to show the count of systems for each manufacturer.
Add an action on the chart to go to the Computer Hardware Information Prompt report.
Ensure that when a user clicks a pie wedge, the report opens and passes the Manufacturer value to the Computer Hardware Information Prompt report.
Tip: Action to Pass Selected Pie Wedge Value as Parameter
To pass the specific manufacturer name of the pie wedge that is selected to the Computer Hardware Information Prompt report’s parameter, specify the [Manufacturer0] column as the parameter value, instead of a function, on the Action tab of the pie chart.
Preview the completed report to confirm that all settings and features function properly, as detailed in this section. Notice that when you click a pie wedge, the prompt report does not ask for a parameter, and the report contains only systems belonging to the selected manufacturer. Save this report as it will be used in upcoming demonstrations.
Summary
In this chapter you have added features and customizations to the reports you created in Chapter 6. You have also created several report templates to use as a basis for new reports for your environment. This chapter has shown you how to create parameters by building on the Patch Progression Report created in Chapter 6, as well as the Computer Hardware Information report created in the demonstrations. You have learned how to add simple parameters to the report, including a free-form text box for specifying values; you have also learned how to adapt those parameters to a dropdown prompt of values populated from a query and also a prompt that supports selecting multiple values.
As you create more reports, you will begin to see which types of prompts you prefer, based on the data returned and the intended audience. If a prompt should allow for wildcards, a simple prompt is required; however, if a value should be a specific result, you want to use a dropdown or multi-value prompt from a query to eliminate the potential for mistakes and typos.
This chapter has also detailed the various chart types. It has described each type and its typical use, and it has walked you through adding pie charts to the demonstration reports. From these charts, you applied customizations such as 3D effects, data labels, and actions with links to different reports.
To further these concepts, Chapter 8, “SSRS Reporting Features,” describes advanced color codes that can be added to SSRS reports. It also walks through adding and publishing the reports created throughout the book to an SSRS website and creating report subscriptions.