CODEWITHSUNDEEP
sqlvisual-studio-2017ssrs-2017
HenderPar
HenderPar

Reputation: 1

How to Pass Report Parameter as if it were a Query Parameter?

I am trying to make a report that allows a user to input an ID and then check this ID against several payee tables to see if the value already exists. The code requires an input and then displays a value of 1 if the code already exists and a 0 if the code is available for use. This works in the SQL code when setting the ID as a query parameter, but does not appear to work when creating an SSRS report and passing a report parameter through; the report displays no data.

I have tried adding a tablix column to display the parameter for validation. This field remains blank when attempting to pass a report parameter, but does properly display when hardcoding the query parameter. 

DECLARE @id varchar(8)

SELECT @id,
    CASE
        WHEN @id IN (
                    SELECT id
                    FROM payee_1
                    )
        THEN 1
        WHEN @id IN (
                    SELECT id
                    FROM payee_2
                    )
        THEN 1
        WHEN @id IN (
                    SELECT id
                    FROM payee_3
                    )
        THEN 1
    ELSE 0
    END as validation

The SQL query produces the results I would like to see in the SSRS report. It simply shows the input ID and a 1 if not available (or a 0 if available). When input into SSRS, the report parameter never passes through as if it were a query parameter and my report ends up being blank.

Upvotes: 0

Views: 1991

Answers (1)

tgolisch
tgolisch

Reputation: 6734

When your report is open for edit (Visual Studio), in the "Report Data" panel (docked on the left, by default), expand "Datasets". Right-click your Dataset and choose "Dataset Properties".

Report Data panel in Visual Studio

In the "Dataset Properties" window, on the left, click "Parameters". The parameters shown are populated from your query. If you have a parameterized query, then this will give you the option to bind your query parameters ("Parameter Value") to your report parameters, or set up other defaults or formulas, etc.

Dataset Properties window in Visual Studio

Upvotes: 1

Related Questions