We’re now on Part 5 of our SQL Server Reporting Services Basics Series. In this installment, we’ll learn how to use a range parameter. If you want to review the series: Part 1, Part 2, Part 3, Part 4.
You may remember in Part 4 we created a simple parameterized report. We’re going to build upon the same report and add few elements to how we use the parameter.
If you followed Part 4, you have everything that you need to run this report. We only need to modify it a bit to make it work with the range parameter. Let’s dig in.
Create Dataset For Range Parameter Source
When I say “range parameter”, I mean a parameter that uses a preset range of values; example 1 – 100.
For the purpose of this demo, I have created a lookup table for the ranges. You may remember from Part 4 that our existing parameter is called Order Total. We’re going to use the same parameter.
Let’s create a new table called DollarTotalRange:
CREATE TABLE DollarTotalRange
(
RangeID INT NOT NULL IDENTITY(1, 1),
MinTotal INT NULL,
MaxTotal INT NULL,
RangeLabel VARCHAR(150)
)
Let’s populate that with some data that we’re going to use for our ranges:
INSERT INTO dbo.DollarTotalRange ( MinTotal, MaxTotal )
VALUES ( 1, 1000 )
, ( 1001, 3000 )
, ( 3001, 5000 )
, ( 5001, 8000 )
, ( 8001, 10000 )
, ( 10001, 20000 )
, ( 20001, 30000 )
, ( 30001, NULL ) ;Let’s also populate the RangeLabel column with the combination of the MinTotal and MaxTotal columns as ranges:
UPDATE dbo.DollarTotalRange
SET RangeLabel = CAST(FORMAT( MinTotal, '#,#' ) AS VARCHAR (25)) +
' - ' +
ISNULL( CAST(FORMAT( MaxTotal, '#,#' ) AS VARCHAR (25)), 'MAX' ) ;Let’s take a look at our ranges:
Modify the Stored Procedure with the new Parameter
Let’s modify the existing stored procedure so it can use the new parameter type. It is important to note that the data type of the Order Total parameter is FLOAT. We’re changing that to STRING.
We’re going to make sure also that we can accommodate the possible maximum value of
ALTER PROCEDURE [dbo].[OrderTotalQuery] (
@vOrderTotal VARCHAR(255) = NULL
)
AS
BEGIN
DECLARE @Min INT, @Max INT
SELECT @Min = MinTotal, @Max = MaxTotal FROM dbo.DollarTotalRange WHERE RangeLabel = @vOrderTotal
SELECT c.CustomerName
, SUM(sol.Quantity * sol.UnitPrice) AS OrderTotal
FROM Sales.Orders so
JOIN sales.OrderLines sol ON so.OrderID = sol.OrderID
JOIN sales.Customers c on so.CustomerID = c.CustomerID
GROUP By c.CustomerName, sol.OrderID
HAVING SUM(sol.Quantity * sol.UnitPrice) >= @Min AND SUM(sol.Quantity * sol.UnitPrice) <= ISNULL(@Max,999999999)
ORDER BY OrderTotal Desc
ENDCreate a New Report Dataset
First thing first, let’s create a new stored procedure based on the DollarTotalRange table we created above.
CREATE PROCEDURE [dbo].[DollarRangeSelect]
AS
BEGIN
SELECT MinTotal, MaxTotal, RangeLabel
FROM dbo.DollarTotalRange ;
END ;Open your Visual Studio and load you SSRS Solution if you haven’t loaded it yet. Navigate to the Solution Explorer, and under Shared Dataset create a new dataset called ds_SSRSTutorial_001_DollarRange. Select the Stored Procedure for the Query Type. Then select the stored procedure we created above.
Then leave everything else as is. Click OK.
Modify the Existing Report Dataset
Under the Shared Dataset, open the existing ds_SSRSTutorial_001_OrderTotalQuery. Click on the Parameters tab and change the data type of the @vOrderTotal parameter from FLOAT to TEXT.
Click OK.
Create a new Report Parameter
Create a new report parameter based on the dataset we just created above. Name it rpt_ds_SSRSTutorial_001_DollarRange.
Click OK.
Modify the Report Parameter
On the Report Data window, navigate to the report parameter.
- Navigate to the Report Data window. Edit the
@vOrderTotal parameter - From the General tab, change the data type of the parameter @vOrderTotal to Text.
- On the Available Values tab, Select the Get values from a query option. Select the
rpt_ds_SSRSTutorial_001_DollarRange dataset we created above. Select RangeLabel column for both Value field and Label field.
Let’s Preview the Report
Deploy the Report!
If you are not familiar with your options of deploying your report, go back Part 3: Report Deployment.
Next, we will continue to work on another type of report parameter, multi-value parameter.
