Decision Support

Construct a Report using Report Templates - RS 2005

Use the UW Report Template as a starting place in report creation. This template contains the standard components required to publish reports on UW’s central server, as set by the Data Management Committee. The standard elements include the UW Logo, header elements, an Executive Summary, and report metadata in the footer area. This template is to be used for:

  1. Online and printed reports, and
  2. Lists exported for non-report uses, such as mailing lists.

Using the Report Template

The following information describes how to customize the standard elements of the template, how to set the report to display with or without metadata, and how to use the built-in functions which govern the look, feel and behavior of the report.

report template

Customize the Template Standard Elements

  1. Open a Report Template file in an Internet Explorer browser window (Internet Explorer is the supported browser)
  2. You can select from two report template files. Both files contain all the same components – the only difference is Report_Template is in a portrait orientation and Report_Template_landscape is in a landscape orientation. Note that each template file contains correspondingly-sized subreport files.  
  3. Copy the file to your computer
    1. Click on the Properties tab at the top of the page

      report parameters

    2. Click Edit under Report Definition and a save window will appear

      report parameters2

    3. Save the file to a local folder on your computer
    4. Open the file with Microsoft SQL Server 2008 Report Builder 2.0
  4. Change the Report ID (see top right corner) to the report metadata code for your report
    1. While on the Layout or Data tab, select from the menu: Report > Report Parameters

      parameters menu

    2. In the Report Parameters window, select the ReportID parameter.
    3. Change the Default value from ADMIN1003 to the value for your report.
    4. Click OK.
      reportID
  5. Change the Report Title and Subtitle textbox contents to the name and subtitle of your report.
    Report Parameters
  6. Remove the text box containing the link to the report template instructions:

    red text

  7. Change the DataRefresh textbox (to the left) to display the correct information for your respective data source. subtitle
  8. Change the values in the Parameters text box below, to reflect appropriate values for your report. If there are no parameters, enter Parameters = None

change-param

Report Display - Report or Raw Data

  1. The report template contains built-in functionality that allows you to choose how the report is rendered - either with metadata, or stripped down as raw data for export to Excel.
  2. For each object to be hidden when rendering the report in Raw Data mode, the Hidden property in the Visibility section has been set to the following expression:

    =IIF(Parameters!RenderingType.Value = "RawData",True,False)

  3. The actual property entry looks like this:

    visibility

  4. If you add additional objects to your report that should be hidden when rendering in Raw Data mode, be sure to set this property.

Built-In Functions

Reporting Services can be tailored to add functionality.  The Report Template contains two added functions – the first governs colors and fonts, and the second provides a way to display a concatenated list of multiple parameter selections which is a very useful form of metadata.

Color and Font Management

Reporting Services does not have a formal style sheet feature. In order to maintain consistent colors and fonts across reports, a user-defined function called StyleSelection has been added to the report template, and has been applied to the relevant template elements. The following information describes the function and how it is applied in the report.

The StyleSelection Function
  1. The Style Selection function is stored as part of the Report Properties. 
  2. From the menu, select  Report > Report Properties, then click the code tab.
    properties
  3. Scroll through the function to see the available values.  Note how it is divided into: Background Colors, Font Colors, Font Weights and Font Sizes.

The features shown below are built into the Report Template file and no modification to their settings is required.  The purpose of this section is to describe how these settings work so you can add/adjust/remove them to customize the reports.
Note: Colors and fonts are not prescribed by the Data Management Committee.

  1. The Report Template file contains color and font style settings for the following report objects:
    1. Column headings
    2. Table detail rows
    3. Table Subtotal rows
    4. Table Total row
  2. To invoke the style, a code snippet is applied to the respective report property. This code snippet then retrieves the value from the StyleSelection function.
  3. To see this in action, select the top row of the table, called TableRow1, by right-clicking on the icon on the far left of the row and choose Properties.

toprow1

    1. On the Properties list, see the values for:
      1. BackgroundColor

        =code.StyleSelection("COLUMNHEADINGBACKGROUND")

      2. Color

        =code.StyleSelection("COLUMNHEADINGFontColor")

      3. Font

        Normal, Arial, =code.StyleSelection("COLUMNHEADINGFontSize"), =code.StyleSelection("COLUMNHEADINGFontWeight")

    2. Note how the function is invoked through the  =code.StyleSelection statement, and then the relevant style is set by the value passed to the function ("COLUMNHEADINGFontSize")
The ParameterList Function

This function lets you select more than one value from a drop down list and have every value selected be displayed in the report result, or more technically, provides the ability to display a concatenated list of parameters selected from a multi-value parameter. For example, you select Arts & Sciences and Engineering from a drop down list of colleges and you want to follow best practices by displaying these selections so the reader can readily see what values are governing the report output.  This function makes that display possible. Here is an example of how, using ‘college’ as the multi-value parameter:

  • This example assumes your report contains a parameter called college
  • Set the college parameter to multi-value

Report Parameters Properties

  • Add a new text box to the report
  • Right click the text box and select Expression…
  • In the Edit Expression dialog box, paste the following expression, then click OK

="School/College: " & code.ParameterList(Parameters!college.Label)

  • When the report is rendered, the new text box will display a concatenated list of all values the user selected in the college parameter drop down list.