Note: I’ve been meaning to start a series on the basics of SQL Server Reporting Services (SSRS). The last time I did this was on a book on SQL Server 2012 Reporting Services published by Packt. SSRS has since evolved into a better reporting platform with the addition of KPI’s, mobile reports, etc.
We’re now in Part 4 of our SSRS Basics Series, in which we cover simple parameterized report. Before we proceed, if you are a total newbie to SSRS and you’ve never created nor deployed an SSRS report, Part 1 shows you how to install SSRS. Part 2 shows you how to create a simple table report. And, Part 3 shows you how to deploy that report to the SSRS Report Server.
First thing first: Create the following stored procedure. This contains the dataset of our next report.
CREATE PROCEDURE OrderTotalQuery ( @vOrderTotal FLOAT = NULL )
AS
BEGIN
--Set Default OrderTotal Value
IF @vOrderTotal IS NULL
SET @vOrderTotal = 1000 ;
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 ) >= @vOrderTotal
ORDER BY OrderTotal DESC ;
END ;We have one parameter in this stored procedure, called vOrderTotal. We are assigning it the NULL marker as a default. But let’s aside this NULL for our next post. For now, take note that it’s there and that we will use it later in the next post.
Basically, what this query does is return the Customers with equal to or more than X amount, where X = variable @vOrderTotal.
What a parameter does is it allows us to specify a value on runtime. We don’t need to go back in the database and hard-code the value. But of course, you already know that.
Create the Report
Open the existing project. Go back to Part 2 here if you’re not sure what I am referring to. Create a new report and name it rdl_SSRSTutorial_001_OrderTotalQuery.
Create the Dataset
Create a new dataset under the Shared Dataset in the project. Again, if you’re not sure how to do that, check the Part 2 of this series. Name it ds_SSRSTutorial_001_OrderTotalQuery. For the data source, we use the same Data Source that we previously created, DS_SSRSTutorial_001. We’re not creating a new Data Source because we’re basically connecting to the same database.
Select Stored Procedure for the Query Type. And select the OrderTotalQuery stored procedure that we created above.
Select the Parameters in the pane on the left. You may notice that the parameter of our stored procedure has been automatically detected. Let’s leave it as is for now. We’re going to go back here in the next post.
Click OK.
Parameterized Report
Drag the Table component into the report canvas. Like what we’ve seen in Part 2, dragging a table component into the designer displays the Dataset Properties dialog. Let’s name our report’s dataset as rpt_table_ds_SSRSTutorial_001_OrderTotalQuery. Use Shared Dataset and select the ds_SSRSTutorial_001_OrderTotalQuery dataset.
Again, you may notice that our parameter is detected automatically.
Click OK.
Preview the Report
Let’s preview the report to make sure it’s working. Input 10000 in the Order Total box. Click the View Report button.
You may notice that the parameter is named v Order Total, which is automatically copied from the parameter of our stored procedure. Let’s rename that by removing the letter v.
Rename the Report Parameter
Click the Design tab. Make sure you select
In the Report Data pane, navigate to Parameters >
Change the Prompt. Remove the letter v. Click OK.
Build and Deploy the Parameterized Report
Build and deploy both the dataset and report. If you’re not sure how to do that, refer to Part 3 of our series which covers report deployment.
Parameterized Report in Action
Here’s our basic parameterized report in action.
Let’s make our parameter intelligent in the next installment of our SQL Server 2017 Reporting Services Basics Series. Next is about Range Value Parameter.
