Report Parameters

SQL Server Reporting Services reports can accept parameter values. To demonstrate the flow of parameter values from the Blackbaud CRM UI to a SSRS report and to stored procedures called by a SSRS report, let's consider a report in Blackbaud CRM called Volunteer Schedule.

From the Volunteers functional area, when you click the task for the Volunteer Schedule report, the Volunteer Search screen appears.

You may notice you can only select one name from the search results.

When you select a name, a page for the Volunteer Schedule report appears. There are three fields and a button to view the report. Unless you select Specific Date from the Date field, the From and To fields are calculated for you based on your selection in Date.

At this point, you have selected a constituent through a search screen and you are now selecting a From and To date. There is also a Date field with options which affect the behavior of the To and From fields. From this information, you may surmise there are at least three parameters on the report, maybe four:

  1. Volunteer

  2. To

  3. From

  4. Date?

When you run the report there are no other clues about the report parameters. But you can see more information about the report using a Feature search in Administration > Application > Features.

When you select Volunteer Schedule, the Feature Metadata page for the Volunteer Schedule report appears. Notice there is no information about parameters.

Remember there was another report when you searched? Let's look at that one.

The report called Volunteer Schedule Report does have parameters listed on its Feature Metadata page. There are four and they seem to match the four potential parameters identified earlier. They are:

  1. VOLUNTEERID (Volunteer)

  2. STARTDATE (From)

  3. ENDDATE (To)

  4. DATETYPE (Date)

The stored procedure associated with this report reflects the name of the report just as the stored procedure for the first report does. Now let's go back to the Volunteer Schedule page and look at it in Design Mode.

It turns out the report in question is the second report, the one with the four parameters. You can tell by the ID for the Report element on the section which holds the report. We can also see one of the values is passed to the report feature on the page, VOLUNTEERID. This makes sense because we had to select a volunteer before the page appeared.

          <Report ID="26fd62fd-abd9-4986-b9da-8da5bcc54154"> 
            <ParameterValues> 
              <ParameterValue ID="VOLUNTEERID" xmlns="bb_appfx_commontypes"> 
                <Value> 
                  <PageContextID /> 
                </Value> 
              </ParameterValue> 
            </ParameterValues> 
          </Report>

The page itself has a ContextRecordType of Volunteer. This is how the Volunteer from the Volunteer Search is passed from the search to the page and to the report feature on the page section.

<PageDefinitionSpec 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    ID="6d6e3ba1-e24c-4e12-a82a-c32ca1e0606e"
    Name="Volunteer Clientside Schedule Report Page"
    Description="Shows a report of a volunteer schedule."
    Author="Blackbaud Product Development"
    ExpressionDataFormID="c131267b-a107-474e-843c-ea182058aa59"
    ContextRecordType="Volunteer"
    xmlns="bb_appfx_pagedefinition"
    >

If you view the XML for the report from the Feature Metadata page, you can see the name of the RDL file for the report.

If you explore the RDL file in Report Explorer, you can attempt to open the report.

But notice you can't pass all of the parameters needed by the report. You have no way to pass VolunteerName from this interface. The report is designed to be opened in the context of a Volunteer obtained through search. The parameters exposed on the page do not include that.

However, from SQL Server Reporting Services Report Manager, you can enter values for each parameter. And notice there is a fifth parameter. There is VolunteerName and also VOLUNTEERID. At this point you may suspect that the uppercase parameters reflect database fields and the camel case parameters are something else.

You can run the report with just three of the items:

  1. VOLUNTEERID

  2. STARTDATE

  3. ENDDATE

The report will run but nothing will appear. Let's keep looking.

VolunteerName is friendlier way to display a Volunteer than a uniqueidentifier (GUID). VolunteerName is used to display the name of the volunteer on the report. The UI Model determines VolunteerName through a search using VOLUNTEERID. The UI Model also displays the parameters STARTDATE and ENDDATE using DateRange and a handler to set the values of STARTDATE and ENDDATE. These are exposed in the application as From and To. But when you access the report through Report Manager, you are outside of Blackbaud CRM. So no handling is performed.

The Report Spec has these form fields (IDs):

  • VOLUNTEERID

  • STARTDATE

  • ENDDATE

  • DATETYPE

Following is a screen shot of a fragment of the spec which shows where these fields are defined. In the WebUIComponent element you can see how the Report Spec connects to the UI Model.

But we are still missing something. Let's look at some details about the report.

When we look at the parameters for the report, we see there are more than what the report shows us in either Blackbaud CRM or Report Manager.

Rather than attempt to run this report by finding those values, let's drill another level down and look at the stored procedure used by the report. Let's open the report in Report Builder.

We can determine the stored procedure used by the report by looking at the Dataset Properties for the dataset in the report. The stored procedure is dbo.USP_DATALIST_VOLUNTEERSCHEDULEREPORT. So the report uses a data list.

If we look at the dataset for the report, we can see some familiar fields. When we look at the Parameters, we can see four:

  1. @VOLUNTEERID

  2. @FROM

  3. @TO

  4. @CURRENTAPPUSERID

Be careful not to confuse the report parameters with the dataset parameters. In this case the four dataset parameters are mapped to four of the report parameters. Some of the report parameters are for display on the report.

In SQL Server Management Studio we can take a look at the stored procedure.

We could execute the stored procedure with values similar to what we were passing from Report Manager:

use [BBInfinity]
go

declare @return_value int

exec @return_value = [dbo].[USP_DATALIST_VOLUNTEERSCHEDULEREPORT] @VOLUNTEERID = '07113775-56C0-458D-923B-E2BB5C5292B8',
	@FROM = N'2012-09-24 00:00:00.000',
	@TO = N'2012-09-24 23:59:59.000'

select 'Return Value' = @return_value
go

And again nothing is returned:

We need to supply @CURRENTAPPUSERID if we want anything to come back. App user IDs are contained in the dbo.[APPUSER] table.

use [BBInfinity]
go

declare @return_value int

exec @return_value = [dbo].[USP_DATALIST_VOLUNTEERSCHEDULEREPORT] @VOLUNTEERID = '07113775-56C0-458D-923B-E2BB5C5292B8',
	@FROM = N'2012-09-24 00:00:00.000',
	@TO = N'2012-09-24 23:59:59.000',
	@CURRENTAPPUSERID = '0F85138C-D979-49CE-BB02-AEE55E4E4B22'

select 'Return Value' = @return_value
go

The data list stored procedure dbo.USP_DATALIST_VOLUNTEERSCHEDULEREPORT executes a stored procedure: dbo.USP_REPORT_VOLUNTEERSCHEDULE.

Note: This report happens to use a data list stored procedure for the data set's query. A report can use any stored procedure which returns useful results. A stored procedure can be defined in the Report Spec or in a Stored Procedure Spec and referenced in the Report Spec. A query could be embedded in the RDL file itself instead of in a stored procedure. The second stored procedure is not required either. In this case, the data list stored procedure is reusable as a data list in other Blackbaud CRM features.

Procedure dbo.USP_REPORT_VOLUNTEERSCHEDULE calls a series of functions to determine security properties for the current app user. It conditionally calls another series of functions to find To and From dates based on the date range. And it performs the actual query.