Adding the SQL Report Parameters
Create the report parameters using a set of INSERT statements in the ReportParam table.
The parameterizations are of the form:
[ Operand1 Value1 Operand2 Value2 FieldName ]
An expression equivalent to the parameterization is given immediately following each VALUES clause, first in the above format, and second, if necessary, in a more understandable format.
For more information about the ReportParam table and the various parameters, see Analysis/Reporting Schema.
Step 5
-- DataType
Declare @DataType_date tinyint
Declare @DataType_integer tinyint
Declare @DataType_text tinyint
Select @DataType_date = 1
Select @DataType_integer = 2
Select @DataType_text = 5
-- ParamType
Declare @ParamType_SingleValue tinyint
Declare @ParamType_Expression tinyint /* Static SQL only */
Declare @ParamType_SelectOrder tinyint
Declare @ParamType_DateRange tinyint /* Static SQL only */
Select @ParamType_SingleValue = 0
Select @ParamType_Expression = 1
Select @ParamType_SelectOrder = 2
Select @ParamType_DateRange = 3
-- SelectOrderOperand
Declare @SelectOrderOpnd_Top tinyint
Select @SelectOrderOpnd_Top = 2
-- DateRangeOperand
Declare @DateRangeOpnd_From tinyint
Select @DateRangeOpnd_From = 1
-- ExpressionOperand
Declare @ExpOpnd_Equals tinyint
Select @ExpOpnd_Equals = 1
INSERT INTO [dbo].[ReportParam] (ReportID, ParamName, ParamDescription, ParamType, DataType, Opnd1, Val1, Opnd2, Val2, FieldName, Ordinal)
VALUES (@ReportID, ' [$TopCount] ', 'Number of users', @ParamType_SelectOrder, @DataType_integer, @SelectOrderOpnd_Top, '25', 0, '0', '', 1)
[ Select Top, 25, 0, 0, """" ]
INSERT INTO [dbo].[ReportParam] (ReportID, ParamName, ParamDescription, ParamType, DataType, Opnd1, Val1, Opnd2, Val2, FieldName, Ordinal)
VALUES (@ReportID, ' [$DateRange] ', 'User registration date', @ParamType_DateRange, @DataType_date, @DateRangeOpnd_From, '3/1/2000', 0, '3/30/2000', 'DateRegistered', 2)
[ Date From, 3/1/2000, 0, 3/30/2000, DateRegistered ]
[ 3/1/2000 <= DateRegistered <= 3/30/2000 ]
INSERT INTO [dbo].[ReportParam] (ReportID, ParamName, ParamDescription, ParamType, DataType, Opnd1, Val1, Opnd2, Val2, FieldName, Ordinal)
VALUES (@ReportID, '[$Expression]', 'User type', @ParamType_Expression, @DataType_integer, @ExpOpnd_Equals, '1', 0, '0', 'UserType', 3)
[ =, 1, 0, 0, UserType ]
[ UserType = 1 ]
INSERT INTO [dbo].[ReportParam] (ReportID, ParamName, ParamDescription, ParamType, DataType, Opnd1, Val1, Opnd2, Val2, FieldName, Ordinal)
VALUES (@ReportID, ' [$SingleValue] ', 'Email qualifier', @ParamType_SingleValue, @DataType_text, 0, ' Email is not Null', 0, 0, '', 4)
[ 0, Email <> Null, 0, 0, """" ]
Copyright © 2005 Microsoft Corporation.
All rights reserved.