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.
End of Article
Prev. page
1
[2]
next page -->
