0

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.

HenderPar
  • 1
  • 1

1 Answers1

1

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

tgolisch
  • 6,549
  • 3
  • 24
  • 42
  • The parameter value has been set and associated in SSRS. I had actually tried setting this association prior to your comment, but it still did not pass through properly. https://imgur.com/ZbbwPXR https://imgur.com/sA1mBmf – HenderPar Apr 30 '19 at 20:13
  • Is it working now? If not, could you give a little more explanation or a screen shot or something? Maybe show your SQL query. – tgolisch Apr 30 '19 at 20:15
  • It is not working. I have attached some screen grabs. What I would anticipate to happen in the report is: the user inputs the lookup code, the validation should show 1 if the code exists in any of the payee tables, and the id should show the code passed through by the user. I plan on cleaning up the form once functionality is in place – HenderPar Apr 30 '19 at 20:17