Chapter 5. Basic Report Design
In This Chapter
Understanding the Purpose of a Report Series
Understanding Report Design Considerations
Part II of this book, “About Data and Retrieval,” discussed the data that can be gathered from SQL queries. Now it is time to consider overall report design. Before getting your hands dirty with SQL Server Data Tools Business Intelligence (SSDT-BI) for Visual Studio 2013 and beginning to create reports, you should understand what you will be creating, how you will create it, what you want your report to look like, and how you want individuals viewing the report to interact with it.
Here’s why this is important: New Configuration Manager (ConfigMgr) administrators often do not spend enough time upfront on basic report design—such as templates, styles, and functionality—before beginning to create and publish reports. As an administrator’s skills with the reporting tool mature, his or her reports start to include additional features, such as drillthroughs, interactive sorting, charts, and more. While these newer reports are often more useful, they differ from the earlier ones in their look, feel, and functionality.
The lack of a standard look and feel can make it difficult for those viewing reports to become comfortable using them, particularly if some may allow sorting by columns or using a drillthrough to view detailed reports, while others do not include any interaction. As a result, the administrator needs to update older reports or templates or re-create old reports from scratch if the design files were not saved.
To help avoid such growing pains, the authors strongly recommend spending the time to learn the basics of report design, detailed in this chapter. The next chapter, “Building a Basic Report,” introduces you to the report-writing tool SSDT-BI for Visual Studio 2013 and walks through creating actual reports.
Understanding the Purpose of a Report Series
One of the challenges a ConfigMgr administrator faces is how to display all important data in a useful manner to everyone who might use the report, particularly when you will have a wide range of report viewers, including service desk staff, service desk managers, chief technology officers (CTOs), and end users. Reports can range from simple to very complicated, depending on what you want them to show. The key is having your report design and writing start with a good foundation and a well-thought-out query. Once this base is established, you can build on the query to create a functional, easy-to-read report that addresses the needs of multiple audiences.
One way to display all important data in a useful manner to everyone who might use the report is to build a summarized data report that contains a drillthrough to individual reports containing more specific information. This is referred to as a report series. Following is an overview of a sample report series:
Count of MS Office SKUs: This report shows all the Microsoft Office products installed in your environment by stock-keeping unit (SKU).
List of PCs by MS Office SKU: This report provides a list of computers with a particular Office SKU installed.
MS Office Details: This report shows a detailed list of all Microsoft Office products installed on a specific computer.
Using a general report series design works for everyone from your CTO to your end users. Here’s why:
The CTO is able to see high-level information necessary to make upgrade/purchase decisions about the environment.
The service desk manager can use a drillthrough to see additional details such as the computers or users that will be affected.
The frontline service desk technologist can view what software products are installed on the specific computer he is troubleshooting or upgrading.
Using a Summarized Data Report
A summarized data report is intended to present a high-level view (or summary) of the data. This type of report is useful for obtaining an overview of the environment without needing to see specific details. For example, say you want to determine the number of installations of a product for purposes of license purchasing or performing a true-up. Management often needs such information, and it might also be included in presentations. Figure 5.1 shows an example of a summarized data report. In most cases, this type of report includes the following features:
A chart for visual representation of the data
A table with the following characteristics:
Several columns that help explain the chart
Interactive sorting enabled on each column
Shading applied to alternate rows to improve readability
Ability to navigate to more detailed reports
Standard page format, such as letter size (8.5×11in) or A4 size
The letter paper size is the standard in the United States and is roughly equivalent to the A4 paper size in Europe. Regardless of the paper sizes that may be applicable for you, the same general guidelines and suggestions apply. For a comparison of paper sizes, see http://www.prepressure.com/library/paper-size.
Using a List Report
A list report is intended to expand on the information contained within the summarized data report. It provides additional data to help complete a picture as it pertains to the previous type of report. For example, say you want to see a list of systems with Microsoft Office Standard 2013 installed. This report is useful for administrators to see what systems or users would be impacted with a version upgrade. An example of a list report is shown in Figure 5.2.
A list report’s design contains the following features:
Typically uses the landscape page orientation with a paper size of either 11×8.5in (letter) or 14×8.5in (legal)
Includes a table with the following characteristics:
All columns on one page (width)
Typically fewer than eight columns
Interactive sorting enabled on each column
Shading applied to alternate rows to improve readability
If necessary, provides the ability to navigate to another report for more details
Using a Detailed PC Report
A detailed PC report provides information about a specific computer. It may include information such as a list of installed software, hardware information, operating system details, history, and more. When linked from a summarized data report, it typically contains details about a single computer as it pertains to the summary information, such as all installed applications from a specific vendor. Figure 5.3 shows an example of a detailed PC report, listing all Microsoft Office 2013 products installed on the computer named CM12-CM4. A good detailed report usually has the following features:
A table with the following characteristics:
All columns on one page (width)
Typically fewer than six columns
Interactive sorting enabled on each column
Shading applied to alternate rows to improve readability
Similar page size/orientation to the list report (landscape 11×8.5in (letter) or 14×8.5in)
Different sections within the report (header, body, footer) containing variable details
Understanding Report Design Considerations
A couple major design considerations are important for SSRS report writing:
Reports should look consistent. It is important that custom reports be consistent in features such as drillthroughs, interactive sorting, and so on, in addition to having a consistent look and feel.
The page layout should be consistent between the screen and printed versions. A report that displays properly on a computer screen may not be readable on paper due to information being cut off from the page. This could cause table columns and data to fall on separate pages and make the report almost impossible to follow when reading page to page. To prevent this from occurring, you should test and preview your reports on paper or by using the Export to PDF option.
To save time and ensure that your reports display properly, consider creating a template for each common page layout. Using these templates, you can quickly create a new report without having to worry about the initial page setup, margins, or report size. The templates can include other general settings and items to help save you time, including your company logo, custom color codes, and header and footer sections.
Providing a Consistent Look and Feel
A major pain point for someone using ConfigMgr reports is inconsistencies in look and feel. The built-in ConfigMgr reports that are installed with the reporting services point system role provide a similar look and feel and include some consistent basic features. Figure 5.4 presents two, side-by-side, built-in ConfigMgr reports as a comparison.
As shown by both of the reports in Figure 5.4 and any other built-in report, it is important to have consistency in the color style, table layout, and report header information. Each of these reports contains Microsoft System Center Configuration Manager as the component name in the top-right corner on a light blue background, followed by the report name in a dark, bolded font. An expandable Description section is also included in the header to describe the purpose of the report.
Notice the similarities between the tables shown in the two reports in Figure 5.4. Although each displays different data, the font of the header row is boldface, and each column allows for interactive sorting, indicated by the up and down arrows next to the column name. Each data row of the table alternates between a white background and a gray background, making it easier to read and follow a single row between each column. One last observation is the underlined data within the Product Name column, indicating that this is a hyperlink that opens a more detailed information report for that specific product. (The report on the right-hand side of Figure 5.4 does not contain hyperlinks on any column because there are no further details to provide.)
Now compare these reports to two different custom reports that were created for ConfigMgr, shown in Figure 5.5.
With a quick glance at these two custom reports, you can quickly see that they look very dissimilar. Not only do these reports look unprofessional, it is clear that they have quite a bit of room for improvement. They would be much better if they had a common header with a company logo and font and table styles with alternating row colors. It’s difficult to say whether these reports were created by the same person or in the same company or where this data originated.
These are only two examples of reports. Imagine if every custom report available in this environment were completely different, with different styles or features. For a manager or technician trying to get valuable information from ConfigMgr, it can quickly become frustrating and confusing to try to locate and find the right report and to determine which report has the right combination of needed data. It is also difficult to understand how to navigate or use features of these reports. A feature such as interactive sorting can easily go unnoticed since it is only available on the Application Name column on the right-hand side of the report.
When presented with such different report styles, a user typically resorts to exploring and using the built-in reports to try to locate or piece together the desired data rather than coming to you for reports that contain the required data. The danger here is that non-administrators who don’t understand ConfigMgr or its data are interpreting the output of reports they might stumble upon, potentially making important and costly decisions around it.
For example, someone without ConfigMgr experience could easily click on the Software File report, retrieve information regarding the quantity of photoshop.exe installations, and purchase enough licenses to accommodate the number shown in the report. However, he or she may not have taken into account that this number included all file shares where the source files are stored to allow administrators to install the software and any USB sticks connected to systems being used as installation media. The purchase now is for a far greater number of licenses than the actual number of installations, but the correct number could have been accurately gathered by running a report of Photoshop installations discovered from Programs and Features (v_Add_Remove_Programs). The built-in reports are good at providing general, high-level information, regardless of the environment or implementations you may have, but they rarely provide the specific data that may be pertinent or requested.
Printed Versus Interactive Reports
When you are building a report, you generally view it within the design tool you are using by using the preview option or through the SSRS Configuration Manager website. This is referred to as interactive mode. However, there are times when it is necessary to print a report or export it from the website. When this occurs, you need to be aware of the page layout and where there are page breaks in the report. A report may look great in interactive mode but may cut off at the wrong places when printed or exported.
The example shown in Figure 5.6 is taken from interactive mode, where the report looks great and all columns are visible.
However, as Figure 5.7 shows, when this report is printed, the last column is not on the same page. The Last Hardware scan date column is cut off and appears on page 2 of the report. This is due to page sizing and must be addressed to make the report useful in interactive mode and as well as when printed. An easy way to avoid this situation is to create a template report for each page size and ensure that it does not exceed the width of the page.
FIGURE 5.7 Example of the same report shown in Figure 5.6, now printed and missing the last column on the right.
The Benefits of Using Report Templates
Templates can ensure proper printing and exporting of reports, and they can also contain most custom configurations to provide a consistent experience in different reports. When you are creating templates to fit page sizes, you should also include the header and footer configurations, custom color codes (explained in Chapter 8, “SSRS Reporting Features”), your company logo, and any other desired features. In addition to enabling the creation of reports that have a consistent look and feel—even those created by different administrators—using templates can significantly reduce the time and effort required to create a new report.
Begin planning your templates around the page sizes you want to support. The following templates are based on the most commonly used paper sizes for printing:
Letter Portrait template (8.5×11in): This template is the letter page size with a portrait orientation. This layout is preferred for summarized data reports containing charts, graphs, and tables with only several columns (typically four or fewer). Since this page layout is not very wide, the authors do not recommend using it with detailed reports that contain large tables with many columns.
Letter Landscape template (11×8.5in): This page template uses the letter paper size with a landscape orientation. This layout is ideal for list and detailed PC types of reports containing tables with several columns, as it is wider than the Letter Portrait template.
Legal Landscape template (14×8.5in): This template uses legal paper size with a landscape orientation. This size is slightly wider and allows you to create reports that contain tables with a large number of columns that will not easily fit on an 11×8.5 inch sheet of paper. But beware: Not all printers have the ability to use or are outfitted with legal-sized paper. If your environment doesn’t allow for this size, it may be best not to create reports using this template.
The authors strongly recommend that you notify users when reports are created using a legal paper size. This can be done either in the report comments in SSRS or as a note on the report itself. Such notification can prevent someone from accidentally sending these reports to the wrong printer tray or to a printer that does not have legal size sheets of paper.
The Ability to Export Reports
SSRS reports can easily be exported to different formats directly from the report website. The same is true for exporting reports from SSDT-BI. There are several reasons an administrator might want to export a report. The following sections highlight some of the most common reasons and export formats used for SSRS reports.
Exporting to PDF
Exporting directly from SSDT-BI is a practical way to test the viewability of a report to ensure that it is printer friendly before adding it to the SSRS website. Exporting to PDF format is the recommended way to ensure that a report displays properly on a page without any columns or data being cut off. You might also need to export a report to PDF when you want to keep the layout and style of the report but not allow the data to be altered or modified—so you can, for example, share it with a client.
Exporting to CSV
Reports are often exported to provide data to someone who might not have sufficient permissions to access the reports directly from the report website, either because this is a one-time request or the data is being used for audit purposes. If you want to export a report’s data only in plain text, with no formatting or styles, you can choose Export to CSV (comma-separated values).
Exporting to Excel
If you need to provide a report to someone and want to maintain the layout and style as it appears in SSRS but want to allow the data to be manipulated, select the Export to Excel option. This option allows you to use and apply Microsoft Excel features on the report data, such as copying/pasting, highlighting, removing columns or rows, filtering table data, adding or modifying chart properties, incorporating formulas and functions, and more. This format can allow someone using an existing report to slightly customize it or make use of the data.
Tip: Using Excel to Modify a Report
If someone is using Microsoft Excel to modify or customize a report on a regular basis, the authors recommend that you create an SSRS report to suit his or her needs. Doing so can prevent constant frustration or wasted time and effort from requiring the user to export and modify the data manually.
Exporting to XML File
The Export to XML export option returns an XML-formatted report that contains only data. Layout information and page breaks are not maintained in this export format. The XML file can be used to import the data into a database or within a custom application that supports data input from XML.
Exporting to MHTML (Web Archive)
The Export to MHTML (web archive) option provides a fully formed HTML page of a report. This is the same format used to view a report from the SSRS website using a web browser. The file output from this export is a fully formed HTML page containing the HTML, HEAD, and BODY tags. The HTML file output from the report is supported by Internet Explorer, Firefox, Chrome, Safari, and other modern browsers.
Exporting to TIFF File
Exporting using the TIFF format generates a report as an image file with a .tif extension. This image file can be used to display the report in an image viewer and then print it. The report can also be converted from this format to many other image formats, including .bmp, .gif, .jpeg, and .png. The TIFF format can be useful for adding a report as a picture on a website, in a presentation, or in a Word document.
Exporting to Word
Select the Export to Word option to export a report in .docx format, supported by Word 2007 and higher. From the exported document, the contents of the report as well as styles can be modified using Microsoft Word. The Word page size of the export is set by the report properties. Not all features of the report are supported with this format; for example, interactive sorting is not supported, and data will be sorted in Word as it was when the report was exported. Hyperlink and drillthrough links are supported on text box and image items as hyperlinks; however, they are not supported on chart or map items. Charts, images, and map report items are converted as static images in Word. For additional information on the Word format, see https://msdn.microsoft.com/en-us/library/dd283105.aspx.
Using SSRS Report Subscriptions
It is important not to overlook the reporting export features and to make sure your reports function properly even if you don’t believe they will be printed. The SSRS report website provides a subscription feature that allows users with access to reports to subscribe to individual reports that are executed according to a scheduled date and time and delivered via email or file sharing. When creating a subscription, a user specifies a delivery method, format, and schedule. The report is run, exported to the specified format, and delivered to the user based on the defined schedule. Note that if a report doesn’t fit on a page or isn’t scaled properly for a regular page size, this may cause issues if someone wants a report to be automatically run and be made available at certain intervals. Further information, along with instructions for setting up subscriptions, is provided in Chapter 8.
Using Custom Report Requests
Some reports are easy and fairly straightforward, such as the All applications installed on a system or All systems with a specified application reports. As you start creating custom reports, publishing them on the SSRS website, and sharing their links with various people and groups, you will realize that many people are interested in the data gathered by ConfigMgr. Service desk technicians will want to see reports for deployed hardware makes/models along with driver versions and client health. Software licensing departments may want to know the applications that are deployed, the number of installs for each, and their usage to forecast budgets and true-up agreements. Development teams may want to know the frequency and time of use as well as how many systems are using an application that they plan to replace. Security officers will be interested in software update compliance, the impact of vulnerable software versions, and antivirus installation confirmation. The list of requests for ConfigMgr data can soon begin to feel overwhelming.
Rather than deal with each request one at a time as each request is brought forward, consider spending some time before you begin creating and publishing reports to come up with a solid report request process.
Gathering Report Information
A good foundation for a request process provides a clear and easy way to gather information from the requestor. At first it may be easy to sit down with each requestor and discuss each individual request one at a time. However, this individual gathering of information becomes very time-consuming and is not maintainable in the real world. The best way to avoid this situation and to be proactive is to develop a checklist or a request form. This way, when you receive a request, it already includes most of the information you need to start planning and creating the report.
The authors include a sample request form, Requesting a Report, available as online content for this book (see Appendix C, “Available Online”), to help you get started. Figure 5.8 shows an example of this request form. You can modify the sample form as required to suit your needs and environment. The following sections describe the fields in this form to provide you with further information on its use.
Report Information
This section of the form requests general information about the requested report. These questions are high level and typically require simple yes/no answers, and other sections of the form get into details about these areas. The Report Information section includes the following fields:
Ticket Number: Requires an IT service management (ITSM) ticket number as a tracking mechanism for the requested report.
Requestor: Identifies the individual requesting the report. Any follow-up questions, as well as the report delivery, should be done with the individual listed here.
Requestor’s Email Address: Identifies the email address of the requestor for further follow-up or delivery of the completed report.
Requestor’s Telephone Number: Identifies the telephone number of the requestor for further follow-up regarding the requested report.
Report Title: Identifies the name of the report that will be used on the SSRS website and in the report’s header section.
Description: Provides a short overview of the report and should identify the type of report and its purpose. This information is listed on the SSRS website by the report title. You can also include it in the report header.
Intended Usage: Identifies the purpose of the report, how it will be used, what the report is aiming to accomplish, and the questions answered by this report’s data. This field should help you understand the intent of the request, as well as ensure that the request is aligned with its purpose. More information regarding this field is provided further in the “Questioning Report Requirements” section of this chapter.
Prompt: Uses a simple Yes/No value to identify whether the report will prompt the viewer for a parameter value to filter or tailor the data output. If the value is Yes, further information for the prompt should be answered in the Parameters section.
Chart: Uses a simple Yes/No value to identify whether the report will include a chart item. If the value is Yes, further information regarding the chart should be provided in the Chart Details section.
Table: Uses a simple Yes/No value to identify whether the report will include a table item. If the value is Yes, further information regarding the table should be provided in the Table Details section.
Page Orientation: Identifies the page orientation the report should use. This could be either portrait or landscape. Consider this field a suggestion; if the client is asking for a large table or a lot of information, you may have no choice but to provide the report in landscape orientation.
Paper Size: Indicates the desired paper size of the report. You can provide a check box for each paper size supported in your templates. Again, this field should be considered a suggestion and not a requirement.
Replacing a Report: Identifies whether this request is to update or replace an existing report. Information on the report life cycle is provided in the “Report Life Cycle and Maintenance” section of this chapter.
Primary Data Exists Within ConfigMgr Console: (Optional) Helps identify the requested data and where it can be found within the ConfigMgr console. Not all requestors may be able to answer this item, but if populated, this could save you time by helping you understand what the requestor is looking for and therefore help you locate the correct SQL view for use by the query in the report.
Available Mock-up of the Report: Uses simple Yes/No value to identify whether a mock-up or sample of the requested report exists. If the value is Yes, the Location of Mock-up field should be completed.
Location of Mock-up: Identifies the location of a sample or mock-up of the requested report. This is an optional field. If provided, the mock-up can help save you time designing the report and understanding the request.
Parameters
The Parameters section of the request form collects additional details regarding each required prompt of the report. This section should be duplicated for each parameter that is to be included in the report. As you may notice, this section includes an extra column, Further Details/Comments. This column is included to help provide additional details for the requested field. The Parameters section includes the following fields:
Name: Specifies a unique name for the parameter. This information is used when creating the report parameter and to identify the parameter’s value within the report.
Subtitle with Prompt Details: Uses a simple Yes/No value to identify whether a subtitle for the prompt and the selected value should be shown in the report’s header section, under the report title. Yes is assumed on all reports by default. Further information can be provided to identify the subtitle name and location.
Prompt Text: Indicates the prompt text displayed immediately after running a report, before the query is executed and any data is provided. This should indicate to the report viewer what input is required in order to provide tailored data output from this report. An example is provided as Select a collection.
Optional Prompt: Uses a simple Yes/No value to identify whether the prompt is optional, allowing the report to be run without the viewer providing any prompt value.
Default Value: Indicates the default value for the prompt, if any. If a value is provided, the prompt’s value field is prepopulated at runtime.
Prompt Type: Identifies the type of prompt displayed when running the report. Possible values are text box, which provides free-form value entry; drop down, which displays a list of values to choose from; and multi-select, which provides a list of values to choose from when multiple values can be selected.
Cascade Prompt: Uses a simple Yes/No value to identify when multiple prompts are required and any subsequent prompt values should be based on, or filtered on, the value of the initial prompt. If the value is Yes, the cascade prompt hierarchy should be identified under the Further Details/Comments column.
Chart Details
The Chart Details section of the request form collects additional information regarding each chart required for the report. If multiple charts are requested, this section should be duplicated and answered for each individual chart. As you may notice, there is an extra column in this section, Further Details/Comments. This column is included to help provide additional details for the requested field. The Chart Details section contains the following fields:
Chart Name: Specifies the name of the chart. The name must be unique, and by default it is set to Chart<ID> (for example, Chart1). This name is used as a reference between report items and is not visible when running the report.
Chart Title: (Optional) Specifies the label that appears above the chart itself.
Type of Chart: Identifies the type of chart item to use to represent the data. Possible options include pie chart, bar chart, line chart, pyramid chart, donut chart, and more. To make it easier for the requestor to choose, provide a list of check boxes to choose from for each chart type.
Include Legend: Uses a simple Yes/No value to specify whether to include a legend. If Yes is chosen, a legend can be placed either to the left, right, above, or below the chart to show the different data values along with their colors in the chart.
Include Labels: Uses a simple Yes/No value to specify whether to include labels. Yes indicates that labels should be shown on the chart. A label can be displayed either inside or outside a bar, line, or pie section to identify the data value of that indicator.
Custom Fill Colors: Uses a simple Yes/No value to specify whether to allow custom fill colors. If the requestor requires custom fill colors for the chart, the RGB color codes to use should be provided in the Further Details/Comments column. The standard colors used in the template are used by default.
Drillthrough on Chart Items: Uses a simple Yes/No value to specify whether the items in the chart can contain an action that allows each chart item to be clicked to redirect to a detailed report of the data. Typically the detailed report contains a table listing all individual items that comprise that specific chart item.
Table Will Provide More Details of Chart: Uses a simple Yes/No value to indicate whether a table should follow the chart to describe the results. If Yes is selected, the table details should be provided in the next section of the form, Table Details.
Table Details
The Table Details section of the request form collects additional information regarding each required table. Like the Chart Details section, this section should be duplicated for each table that is to be included in the report. An extra part to this section, Table Layout, outlines exactly how the table item in the report should be created. The Table Details section includes the following fields:
Table Name: Specifies the name of the table item in the report. The name must be unique, and by default it is set to Table<ID> (for example, Table1) This name is used as a reference between report items and is not visible when running the report.
Table Title: Uses a simple Yes/No value to specify whether to include a table title. If Yes is selected, a label is added above the chart with the desired title, indicated in the Further Details/Comments column.
Number of Columns: Indicates the total number of columns to be included in the table. Further details about the columns are provided in the Table Layout section of the form. Note that the number of columns can affect the page layout required for the report.
Interactive Sorting: Offers the options Yes/Some/No. If Yes is chosen, all columns of the table will have the interactive sorting option enabled, allowing the columns to be sorted manually by the report viewer. If Some is selected, the columns where interactive sorting are desired should be listed in the Further Information/Comments column.
Alternating Row Colors: Specifies the color to use for alternating rows. The default value is White/Silver. If different alternating colors are desired, they should be indicated in the Value field.
Match Chart Colors (if Applicable): Specifies whether the chart colors match the table background. If a chart is included in the report, the chart colors that are used will match the table background colors by default. If this is not desired, select No and specify the color details for use in the table.
Drillthrough: Specifies whether the user can use a drillthrough in a table to view a more detailed report. For count and list reports, the default value is Yes. However for computer detail reports, the default is No. If Yes is selected, the reader of the report will be able to click on a field in the table to open a more detailed report, such as a System Name field linking to a computer detail report.
Table Layout
The Table Layout subsection of the Table Details section collects layout details for the table item. Based on the desired number of columns, indicated in the Table Details section, fill out this section to provide the following for each column:
Column #: Specifies the column number and is prefilled with numbers 1 through 8. The fields below should be completed for each column included in the table.
Column Title: Indicates the name for the column, which will appear in the header row.
Column Data: Indicates the information that will be provided by this column.
Column Action (Link): Specifies the action, or link, that will be set on the column data. The action can be either a drillthrough to a detailed report or a URL to a website.
Export
The Export section of the request form collects additional information regarding the intended use of the report. It confirms whether the report is intended to be exportable, and if so, the formats that should be validated before the report is published to the SSRS website. The Export section includes the following fields:
Exportable: Uses a simple Yes/No value to indicate whether the report will be used in an exported format. If Yes, ensure that the layout of the report is functional and maintained against the formats indicated in this section. SSRS allows all reports to be exported; this field is intended to provide insight into how the report will be used and to double-check that the format renders the report properly and as intended.
Select Export Formats: Provides a selection of all formats that are expected to be used when running and exporting the report. By default, all reports should be rendered and validated in TIFF and PDF formats, as these are typically the most commonly used types.
Security
The Security section is intended to collect details around any specific required access or restrictions to run the report and any subscription information. Requestors may not know the answers to some of these fields; based on the data of the report and SQL views used in your query, you will be able to determine whether extra permissions within ConfigMgr are required to view the data. The Security section includes the following fields:
Required Role-Based Administration (RBA): Specifies any extra permissions required for ConfigMgr, through role-based administration, required to view the report data. This is optional for requestors, as most are not aware of required permissions; however, this should be filled out by the report author for tracking purposes. More information regarding role-based administration is provided in Chapter 9, “Role-Based Administration and Reporting.”
Restrict Access to an AD Group: Indicates all groups that will require access if access to the report should be restricted. When the report is added to the SSRS website, only members of those indicated groups will see the report listed.
Access Required to ConfigMgr Collection: Indicates the collection if the data requires additional access to a ConfigMgr collection. Once again, most requestors may not know if access to a collection is required. However, this should be completed by the report author for tracking and auditing purposes.
Configure Subscription: Uses a simple Yes/No value to indicate whether any subscriptions should be configured for this report in SSRS. Clients may be able to configure their own subscriptions in SSRS; however, it is much easier to have the subscriptions created by an administrator once the report is published to SSRS.
Subscription Type: Provides two options: Email and File share. By default, Email is selected if subscriptions are requested.
Subscription Run Schedule: Indicates when the subscription should be run for the report as well the recurrence (for example, the first of every month).
Subscribers/Location: Indicates a list of email addresses to be used if the subscription type is Email or the report path location if the subscription type is File share. For Email, the list of email addresses is set as the recipients of the report; these people will receive an email with the report as an attachment from SSRS, based on the run schedule.
Report Customizations
The Report Customizations section is included for the requestor to provide any details around settings or items that should not follow the default or template styles and settings used in most reports. Many fields in this section look for justification as to why a customization is required in order to determine whether the extra time and effort incurred by not following the default template is valid. The Report Customizations section includes the following fields:
Include Subreports: Uses a simple Yes/No value to indicate whether the report should include a subreport. If a subreport is chosen, provide the requested subreport name under the further details/comments column. By default, reports do not include any subreports; this value is considered a customization because the requested report must be the correct paper size and orientation to match the subreport.
Images: Uses a simple Yes/No value to indicate whether additional images are to be included in the body of the report. This is not a typical request; therefore, the default value is No.
Customization Required from the Default Template Report: Indicates whether any changes or customizations are required to the report template. These can include changes to the header and/or footer sections of the report and may include different logos, URLs, colors, font styles, and more. If any changes are required, justifications must be provided, as explained next.
Justification: Provides justification for the requested customizations to the default template. Using a template saves a considerable amount of time, and any customizations can significantly increase the amount of effort required. Always ensure that justifications are valid. Some examples could include reports for external clients or different branding for regions or customers.
Detail Each Customization Required: Provides specific details for each customization to the template that is required. If different logos are required, they should be provided here, or a location for them should be specified. If colors should be different, the RGB color codes and details where they should be used are to be provided in this field.
Sample Report Form with Examples
To help illustrate how each field in the sample request form relates to a completed report, the next few figures contain numbered items, as follows:
Figure 5.9 shows the completed report in the folder view of the SSRS website.
Figure 5.10 shows the report’s prompts immediately after execution.
Figure 5.11 shows the executed report once a prompt has been provided.
The numbered items on these figures relate to the request form’s fields and the values listed below:
Report Title (1): Count of Operating System Versions
Description (2): Summarized data view of all Operating System versions by Collection
Prompts: Yes
Name: Coll01
Prompt Text (3): Select a collection
Prompt Type (4): Dropdown
Subtitle with Prompt Details (5): Yes
Optional Prompt: No
Default Value: None
Cascade Prompt: No
Chart (6): Yes
Chart Name: Chart1
Chart Title (7): Count of Operating Systems
Type of Chart (8): Stacked Column
Include Legend: No
Include Labels (9): Yes (inside labels are shown)
Custom Fill Colors (10): No (default template colors are used)
Drillthrough on Chart Items: No
Table Will Provide More Details of Chart: Yes
Table (11): Yes
Table Name: Table1
Table Title: No
Number of columns: 3
Interactive Sorting (12): Yes (all columns)
Alternating Row Colors (13): White/Silver
Drillthrough: Yes—Systems with specified Operating System (link to existing report)
Table Layout: See Table 5.1.
Paper Size: Letter (8.5×11in)
Replacing a Report: No
Primary Data Exists Within ConfigMgr Console: Resource Explorer (Hardware | Operating System | Caption)
Tip: Where to Publish the Request Form
If your environment or help desk has already published request forms, whether for hardware, software, or as part of another request process, try to combine or add the report request form that you will create to this same process. You can save time by using an already defined process for client requests and also facilitate report creation.
Keep in mind that only the main fields and options of the request form are described in the preceding list, as some fields provide background information for follow-ups, and not every field applies to every report. The request form itself is available as online content (see Appendix C).
Questioning Report Requirements
Before blindly creating a report based on a completed request form, take the time to go through the requirements to fully understand the request and to ensure that it is practical. Some request forms may not provide enough information to understand the requirements, and you may need to have a meeting with the requestor. The biggest question you need to ask—and challenge—is how the report will be used. The Intended Usage (or Purpose) field of the request form is meant to help you understand and answer this question, but sometimes the answer you get is not enough. Following is a list of questions you should ask before creating a report and how to address them:
Is the request asking for too much data or too many fields to be properly displayed on a page?
This may be the case if a request is asking for too much data, such as too many fields or columns; asking for information that does not go well together (for example, all applications installed on all systems, with versions, hardware information, operating system version, and patch compliance); or asking for a query that will return so many rows that it will be unusable.
When too much data or too many fields are requested, propose to break up the report into a report series that contains a summary report of the data with drillthrough links to go into detailed reports that provide further information. This way the viewers get a high-level view of the information they want, and from that information, they can choose to click on the data where they want to see more information (such as system names to see their hardware details).
How frequently will this report be used?
Try to determine the frequency or usage of the report. For example, if the purpose of the report is to satisfy an audit from a vendor on license information, chances are it will only be used one time.
If the report is only to be used once or very infrequently, it is not worth the time and effort required to create a full report. Instead, create and execute the SQL query in SQL Server Management Studio and save the results in Microsoft Excel. This way you can save time and avoid wasting effort on a report that will be used a few times and then forgotten.
Do the description and intended usage/purpose of the report align with the requested data and items?
Sometimes a requestor does not know what information he wants to see, so he adds a lot of details or is unclear on the specific need.
In this scenario, when the usage or description does not match up with the data requested, it is best to sit down to understand what the requestor wants to get from the report. Always begin by understanding what the report will be used for and then propose to the requestor what data should be included in the report.
Real World: Understandinging Report Requests
Keep in mind that requestors do not always know what they want to see. As an administrator and report creator, it is also your job to ensure that a report is usable and meets the overall purpose. Just because someone asks for certain data does not mean that is what is needed. Always ask questions to clarify requests when you are unsure or when requests aren’t clear. This saves you a lot of time that you might spend creating useless reports and going back and forth with the requestor.
Report Verification and Delivery
Once the data has been gathered and you have created the requested report, it is important to confirm that everything functions as it should and that all requested elements have been addressed. A problem with unplanned and unorganized report writing is the amount of back and forth required with the report requestor, which often leads to wasted time and effort. Not only is it important to proactively gather all necessary information to create a report, it is equally important to have a good checklist and ensure the proper delivery of the report. Before providing the requestor with his requested report, take the time to verify all elements of the report both within SSDT-BI and once it is published from the SSRS website. Following is a checklist of items to help you verify that a report is ready for requestor testing and approval:
The report has been created using a template that matches the paper type and page orientation requested.
The report title is visible in the header portion of the report.
If applicable, the prompt values are listed in the header section of the report.
If applicable, required parameters and prompts appear when the report is run.
If a dropdown list of available parameters was requested, the expected dropdown list is populated and functional.
If a default value was specified, this value appears when running the report.
If a chart was requested, the report contains a chart.
The requested chart type is used to represent the data.
The legends and labels appear as requested on the chart item.
The report owner has been identified in a tracking system, either in the report description or knowledge base.
The report is sorted by default on the requested fields.
If requested, the data is grouped by the specified data field.
Any requested drillthrough links and hyperlinks function as intended.
Any desired conditional formatting functions as requested.
All identified file types have been tested for accuracy, page breaks, and style formatting.
Access to the report has been granted to all requested user accounts, security groups, and distribution lists.
The report subscription feature has been tested with your account to ensure that the report type is as requested, delivery is functioning, and formatting is correct.
Tip: Report Subscriptions
Do not worry about configuring subscriptions or security roles until the client has signed off and tested the report. Only then should you create the requested security roles and subscriptions to ensure that the report isn’t inadvertently used or emailed with incorrect data.
Once you have walked through this checklist and ensured that all elements of the report are functioning and to your liking, have the report requestor validate it to ensure that the report is to his satisfaction and is what he wanted. Always provide the requestor with a link to the report so the requestor can run it independently when he wants. Once the report is accepted, ensure that the proper subscription details are created based on the request. Creating new subscriptions can be difficult or confusing for some users, and for reports that are scheduled for infrequent recurrence, it may take quite some time for a user to realize it didn’t run properly. It is usually easier and quicker to create a subscription on behalf of the user in such a case.
These recommendations can help ensure that a user is satisfied with his reports, and they show a mature reporting process.
Report Life Cycle and Maintenance
Over the life of ConfigMgr, many reports will be created, replaced, retired, or no longer required. Keeping track of these reports over time can become cumbersome, and old reports that are no longer needed can begin to add up. The authors recommend that you keep track of custom reports and ensure that any reports that are no longer required or used are removed from the report website. Clients will often be satisfied with a report once it is created; however, due to the fast rate of change and product version releases, the needs of a report may change as well. This can lead to modifying reports or completely replacing them. In these cases, it is important that you verify and consult with identified report owners before removing published reports. When possible, try to consolidate similar reports and remove reports that are no longer required. The authors recommend reviewing all custom reports every 6 to 12 months to ensure that they are still valid and being used.
Tip: Report Life Cycle
It is important to include both the Owner and Replacing an Existing Report fields in the report request form. This can help you keep your reports current and eliminate any old reports that are no longer required.
When removing a report from the SSRS website, rather than actually delete it, it is best to be cautious and copy the report to a file share. If at a later time a report is requested that is similar to one that has been retired, you will be glad you have this backup.
To assist with maintenance and cut down on the time required to modify and create new reports, the authors strongly recommend creating one central SSDT-BI report server project location, stored on a file share that is backed up regularly. The steps to create a report server project are described in Chapter 6, “Building a Basic Report.” When multiple administrators are creating reports, having a central location greatly reduces the time and effort required to modify existing reports as the reports are stored and available immediately when opening the report server project, regardless of who created the initial report. This method also ensures that all reports are created from the same templates and follow the same basic formatting and styles, and it allows for collaboration and sharing of features previously created and tested from existing reports. Utilizing this central report server project, any updates or modifications can simply be uploaded to replace the existing report on the SSRS website.
Tip: Providing for Report Backups
ConfigMgr does not back up custom reports (.rdl files) as part of its backup task. Make sure that you plan for this and configure a recurring backup job for either the .rdl files or the report server database.
Creating separate folders on the SSRS website to store custom reports also greatly helps report viewers find and run their reports. When the reporting services point role is installed, ConfigMgr automatically creates a reporting folder structure where out-of-the-box reports are placed. This can sometimes get overwhelming or confusing for end users trying to find custom reports they have requested. To alleviate this problem, create a new folder to store custom reports. This also helps with report maintenance for administrators, as all the custom reports can be easily identified from the default ConfigMgr reports.
Real World: Multiple Custom Report Folders
If you want to create multiple folders to store different groups or types of reports, use a common identifier to start the folder name, such as your company’s name or initials. This helps with sorting and finding the folders that contain your reports. An example is shown in Figure 5.12, where all custom reports are stored within Enhansoft folders.
Summary
This chapter has discussed many concepts that are crucial to basic report design. It is important to understand the different types of reports within a series: the summarized data report, list report, and detailed report. As you begin to create reports in the upcoming chapters, you will see how different data fits into the different series and how clients interact and navigate between them. While creating ConfigMgr reports, always remember and keep in mind the following design rules that have been covered throughout this chapter:
When you are satisfied with the look of a report, export it to PDF to simulate a printed copy for final review. As you review it, consider the following:
Verify that the font is easy to read and all table rows are easy to follow; use different colors for alternating rows.
Check that the page breaks are in appropriate locations (for example, not cutting off the footer or a chart).
Ensure that no columns are cut off from the page (width).
Confirm that table headers are repeated on each page.
Ensure that the report does not contain too many columns or too much. Ensure that everything fits on a single page width (either in portrait or landscape orientation). As you review it, consider the following:
If columns break onto a second page, it becomes difficult to read the data and keep track of individual rows.
If the number of pages within a report is excessively large, the report will not be readable. Consider creating summary reports instead.
Watch your row heights and line wrapping. If you see wrapping, you might need to change page sizes, adjust column widths, or remove columns altogether.
Be consistent from one report to the next, using templates and existing reports as starting points. This consistency makes the readers feel comfortable with the data that is being presented. Use the same consistent formats for the following areas:
Report titles
Header and footer areas
Color schemes
Fonts
Always save a copy of a report. This reduces the time required to make simple adjustments and helps you when you create new reports as you can copy an existing report to use as a starting point.
Over the next few chapters, you will have chances to practice and put these design concepts to use while creating reports. All report items will be explained and detailed, giving you opportunities to see them first hand. Chapter 6 walks you through the design tool and has you create a new report server project and your first set of SSRS reports. Chapter 7, “Intermediate Reporting Concepts,” explains, among many other concepts, how to create report templates that have been introduced in this chapter, and Chapter 8 discusses how to create report subscriptions in the SSRS website.