Decision Support

Construct a Report using the Central Report Template

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

Note: The following instructions are based on a Reporting Services (RS) 2008 version of the Report Template file. Instructions for the RS 2005 version can be found here.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 RS2008

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 of the report) to the report metadata code for your report
    1. While on the Design tab, expand the Parameters folder in the left sidebar menu, and double-click ReportID to open it.

      menu for report parameters

    2. In the Report Parameter Properties window, select Default values from the left sidebar.
    3. Change the Value from ADMIN1003 to the value for your report.
    4. Click OK.
      Choose the default values for this parameter
  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. The steps below describe how to add parameter values. If there are no parameters, enter Parameters = None
    1. Right click on the ReportParameters textbox under Report Information

AC_SS_036.jpg

  1. Select “fx Expression”

AC_SS_037.jpg

  1. Edit parameters per the example below (="Parameters: Award Year = " & Parameters!AwardYear.Value & ", Award Quarter ="  &  Parameters!AwardQuarter.Value & …….   )

AC_SS_038.jpg

 

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 has been set to the following expression:

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

  3. The actual property entry looks like this:

    actual property entry

  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.
  5. Note: You may also be interested in an alternate method of exporting raw data, using XSLT file. Learn more.

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

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 Style Selection function is stored as part of the Report Properties. 
  2. From the menu, select  Report > Report Properties, then click the Code option in the left sidebar.
    Report 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.
  4. 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
  5. 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.
  6. 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.

Table Row

    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.