SQL Server Reporting Services

Microsoft SQL Server Integration Services (SSIS) lets you create and populate a central repository of Microsoft SQL Server 2005 information. --Microsoft SQL Server Reporting Services (SSRS) lets you tap into that repository data and deliver customized reports to Microsoft SQL Server 2005 database administrators (DBAs) and information technology (IT) staff. --Microsoft SQL Server Reporting Services (SSRS) provides all the features necessary to build a full reporting solution that lets Microsoft SQL Server 2005 database administrators (DBAs) and information technology (IT) staff quickly access status.

To use a report parameter to filter the data displayed on the reports, you must first create a data set to populate the parameter values and labels. The data set will use the common technique of passing a NULL value in addition to the list of server names. Next, you need to create the report parameter that uses this data set, allowing NULL values in the Report Parameter properties. Finally, use custom expressions in the report filter to display all servers or just one server.

Listing 2 contains the query that will create the data set for the Server List report parameter. The Union statement will pass not only the distinct server names but also the literal string "Select All," which will be associated with the NULL value. In addition, the Order by clause is necessary to force the words "Select All" to show at the top of the drop-down list.

Figure 4 shows the report parameter properties. This figure shows the options that are needed to populate the drop-down list for the Server List parameter. Note that the Allow null value checkbox is selected and that the values are derived from a data set, called Server_List_Parameter, which comes from the query in Listing 1. Also, the default value is set to NULL. As an added benefit, you don't need to enter a selection before the report will run—the report will automatically run with all servers as the value passed.

Next, you must tie the parameter value to a filter value to show or exclude data on the report. As I explained previously, the selection is one server or all servers. Figure 5 shows the filter, which is in the report's table properties. The filter uses the Iif statement for the If/Then/Else logic. The logic says that if the parameter value is NULL or Nothing, the value is 1; otherwise it's the value of the parameter and field respectively. When the parameter choices force the logic to evaluate 1=1, show all servers; otherwise show only the server that matches the value of the selected parameter and the field value of the report data.

An interesting design aspect of this report is the use of the Switch function for color-coding the Last Run status column. Although you can use a series of nested Iif functions rather than the Switch function to color-code this column, using Iif is problematic for any value comparisons over 2. Figure 6 shows the Switch function as an expression for the background color property in the Last Run column.

Report #3: Modified Jobs

The final report, Modified Jobs, is a straightforward modification to the existing Server Jobs report. Companies that don't have an effective change management solution (and many that do) sometimes find undocumented code or job changes that don't work as expected, or that fail. Although finding the exact source of an undocumented change can be difficult, simply knowing that a change occurred at least gives the DBA a starting point for further investigation.

The Modified Jobs report shows all the jobs that have incurred a change in the past n days. Instead of tying a report parameter to a filter in this case, I tied the report parameter to a query parameter to limit the data. This method limits the actual number of rows that are delivered to the report from the source database and should therefore increase performance. Figure 7 shows the report and the drop-down list for the number of days in the past to look for modified jobs.

Listing 3 contains the query that returns the data for the report, which is also tied to the Last Day report parameters. Note the use of the query parameter @Last_Day in the query's WHERE clause. When the report runs, the report parameter Parameters!Last_Day.Value will pass to the query as an integer value for the WHERE clause to evaluate the job's modified data and the Package Run date. The Package Run date is the date on which the SSIS repository package ran.

A final note about the Modified Jobs report is that it uses the report parameter value in the report header textbox with the following expression:

="Modified Jobs in the Last " &  Parameters!Last_Day.Value & "   Days"

This expression gives the report a dynamic header by combining the literal string "Modified Jobs in the Last...Days" with the variable parameter Last_Day value for the number of days selected.

After the reports are complete, you can publish them to the SSRS Web front end. Assuming that the TargetServerURL location is set up in the project's properties, you can simply right-click the reports in BIDS Solution Explorer and select Deploy.

SSIS + SSRS = KISS

Last month, I explained how to use SSIS to populate a repository database. In this article, I show you how to use SSRS to create three customized reports. Combining the features of SSIS and SSRS lets DBAs create quality reports that are simple to use and that provide the day-to-day information DBAs need to do their jobs. The reporting solution that I outline is both inexpensive and fully customizable—two attributes that don't always go hand in hand.