Creating Parameters on the fly in SQL Server Reporting Services 2008 (SSRS 2008)

SQL Server Reporting Services is relatively easy to use, however for the first time user it is a little tricky how to report parameters that correspond to parameters/values in your query / stored procedure which generates your dataset.

When prompted to enter a query, simply use a standard t-sql variable without declaring the variable, and SSRS auto-wires the parameters for you.


SELECT
[LoginID]
,[OrganizationNode]
,[OrganizationLevel]
,[HireDate]
,[SalariedFlag]
FROM
[AdventureWorks2008].[HumanResources].[Employee]
WHERE
-- do not declare these variables ahead of time, to see how the report parameters are generated for you
[HireDate] >=  @start_date
AND [HireDate] < @end_date

If you paste the code above in the query editor, the parameters @start_date and @end_date will automatically be created for you as report parameters.

The tricky bit is that when the code is parsed in the Report Wizard, the parameters are initially defined as strings. This creates issues when you need to do operations such as DateAdd on the parameters you are passing.

To handle this slight nuance, I simply declare variables within my queries which use the values from the automatically generated parameters as follows:

DECLARE @start_date DATETIME
DECLARE @end_date DATETIME
-- by passing the report parameter(undefined variable) to a query parameter (defined variable, the type conversions are handled before the query is parsed)
SET @start_date = @rpt_param_start_date
SET @end_date = @rpt_param_end_date


SELECT
[LoginID]
,[OrganizationNode]
,[OrganizationLevel]
,[HireDate]
,[SalariedFlag]
FROM
[AdventureWorks2008].[HumanResources].[Employee]
WHERE
[HireDate] >= @start_date
AND [HireDate] < @end_date

By doing this, when the query is initially parsed, and the system passes in the values as strings, the declarations within your code handles the type casting.  After getting these to work, I then go into each individual report parameter and appropriately modify the data types.

While it is somewhat redundant, it allows you to more rigidly follow the defined work flow in the Report Wizard.

Advertisements

One thought on “Creating Parameters on the fly in SQL Server Reporting Services 2008 (SSRS 2008)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s