CODEWITHSUNDEEP
sql-serverreporting-services
Leonardo
Leonardo

Reputation: 81

SSRS - Executing report when parameter is NULL

I'm creating a report in SSRS using a stored procedure. My report is simple, basically pulls basic information of a client in different programs.

In my stored-procedure I have two parameters: @StartDate DATETIME and @VendorId INT = NULL.

I'm setting @Vendorid to NULL because I would like to get all clients in every program.

I have two options in my sproc:

IF (@VendorID > 0) --Select Program
BEGIN
    INSERT INTO @Report
    SELECT * FROM table1 WHERE VendorId = @VendorId
END

IF (@VendorID IS NULL) --All Programs
BEGIN
    INSERT INTO @Report
    SELECT * FROM table1
END

This report gives me the flexibility to get clients from different programs from the day they were enrolled. Hence, if I want to get all clients in every program I simply execute the sproc with the @StartDate parameter. I would like to do the same in SSRS. I just can't figure how to set the @vendorid parameter function the same way in my sproc.

Any insight would be helpful!

Upvotes: 0

Views: 771

Answers (1)

Jin Thakur
Jin Thakur

Reputation: 2773

AS you know generally in SSRS you can have parameters as Query string or selected value so Just add 

SELECT * FROM table1 WHERE  @VendorId=-1 OR VendorId = @VendorId

To specify a custom default value here -1 Switch to Design view.

In the Report Data pane, right-click @VendorId, and then click Parameter Properties.

Click Default Values > Specify values > Add. A new value row is added.

In Value, type -1.

Click OK.

Preview the report.

You have drop down and you can use -1 as none selected.

https://learn.microsoft.com/en-us/sql/reporting-services/tutorial-add-a-parameter-to-your-report-report-builder?view=sql-server-ver15

Upvotes: 1

Related Questions