Adding the SQL Report Parameters

  • Article

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.