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.
Data Analytics & Technology by John DaCosta 