none
IF ELSE SProc and then executed from within SSRS through parameters

    Question

  • Fairly new to Stored Procedure within SSRS.

    I have worked on numerous reports having parameters passed directly within SSRS dataset nut this is a bit new to me and seems like I am getting a bit confused.

    I got a requirement to merge 2 reports with exactly same layout and then execute the report based on a dropdown selection from SSRS.

    My Stored Procedure is something like below.


    USE [ReportsDB]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[rpt_WithoutExternalReferences]
    (
        @XNumber INT,
        @YNumber INT
    )   
    AS
    
    BEGIN
    SET NOCOUNT ON
    
    IF (@XNumber = NULL)
        BEGIN
            SELECT 
                adviser_account.adviser_account_id,
                dealer.old_system_reference,
                external_reference.external_reference AS Missing_refernce,
                entity.name AS Name,
                entity.given_names As Given_Names,
                entity_address.property_name,
            FROM Composer_adviser_account AS adviser_account
            INNER JOIN Composer_entity AS entity
            ON adviser_account.entity_id = entity.entity_id
            LEFT JOIN Composer_party_external_reference AS external_reference
            ON adviser_account.adviser_account_id = external_reference.party_id  
            WHERE
                external_reference.party_type_id = 3
        END
    ELSE
        BEGIN
            IF (@YNumber = NULL)
                BEGIN
                    SELECT 
                        dealer.dealer_id,
                        dealer.old_system_reference,
                        dealer.fsa_firm_reference_id as Missing_refernce,
                        entity.name AS Name,
                        entity.given_names AS Given_Names,
                        entity_address.property_name,
                    FROM Composer_dealer AS dealer
                    INNER JOIN Composer_entity AS entity
                    ON dealer.entity_id = entity.entity_id
                    INNER JOIN Composer_address AS entity_address
                    ON entity.entity_id = entity_address.entity_id
                    WHERE dealer.fsa_firm_reference_id IS NULL
                END
            END
        END
    END

    Now, I want to call this Stored Procedure from SSRS, where Users can either select XNumber = Null OR YNumber = NULL from a dropdown and then the corresponding portion of the code runs.

    Any help will be much appreciated.

    Thanks in advance.

    Friday, November 08, 2013 11:09 PM

Answers

  • Hi SweetsUK,

    In your case, we can use report parameter to control the tablix visibility to work around the issue.

    In my test, I create a dataset with three fields: Sales Territory Group, Sales Territory Country, Calendar Year, Sales Amount. You can refer to the steps below:
    1. Add a table in the report body. Add four fields in the table.
    2. Add a parameter ReportParameter1 in the report. Click Available Values in the left pane in the Report Parameter Properties dialog box. Select “Specify values”.
    3. Click Add icon to add two values with below values:
    Label: Xnumber     Value: Xnumber
    Label: Ynumber     Value: Ynumber
    4. Right click one column, select “Column Visibility”. Select “Show or hide based on an expression”.
    5. Click the expression icon, type below expression:
    =IIF(Parameters!ReportParameter1.Label="XNumber",True,False)

    Please refer to the screenshots below:
     

    There is an article about report parameter, you can refer to it.
    http://msdn.microsoft.com/en-us/library/dd220464.aspx

    If there are any misunderstanding, please feel free to let me know.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Monday, November 11, 2013 8:06 AM
    Moderator

All replies

  • Alternately, I was also thinking of using a dropdown box with Title as [Is Missing References] and within the dropdown box have 2 options i.e. XNumber and YNumber. Then accordingly the query portion is executed and data is returned.

    Friday, November 08, 2013 11:15 PM
  • Hi SweetsUK,

    In your case, we can use report parameter to control the tablix visibility to work around the issue.

    In my test, I create a dataset with three fields: Sales Territory Group, Sales Territory Country, Calendar Year, Sales Amount. You can refer to the steps below:
    1. Add a table in the report body. Add four fields in the table.
    2. Add a parameter ReportParameter1 in the report. Click Available Values in the left pane in the Report Parameter Properties dialog box. Select “Specify values”.
    3. Click Add icon to add two values with below values:
    Label: Xnumber     Value: Xnumber
    Label: Ynumber     Value: Ynumber
    4. Right click one column, select “Column Visibility”. Select “Show or hide based on an expression”.
    5. Click the expression icon, type below expression:
    =IIF(Parameters!ReportParameter1.Label="XNumber",True,False)

    Please refer to the screenshots below:
     

    There is an article about report parameter, you can refer to it.
    http://msdn.microsoft.com/en-us/library/dd220464.aspx

    If there are any misunderstanding, please feel free to let me know.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Monday, November 11, 2013 8:06 AM
    Moderator
  • Hi Alisa,

    Many thanks for the step by step illustration....helped me a lot to deliver my requirement.

    Can't thank you enough for your quick and accurate response.

    Regards !

    Monday, November 11, 2013 11:42 PM