Query help please

  • I am having issue with multiple selections in SSRS.

    1. If I select @Region='AMER' it should load only ('US','CANADA')

    2. If I select @Region='APJ' it should load only APJ countries

    3.If I select @Region='EMEA' it should load only EMEA countries

    4. If I select @Region='LA' it should load only LA countries

    We are having cascading filters

    1. Region, 2. Country 3. Segment etc

    I have created below code for the country data set. When I select multiple region like 'amer' and 'apj'. SSRS report fails

     

    I hope I have provided necessary details please guilde how to solve

    IF

    (@Region='AMER')

    BEGIN

    SELECT DISTINCTGbl_Parnt_CountryAS 'COUNTRY'FROMNetwork_Eligible_Acct_Rep

    WHEREGbl_Parnt_CountryIN('US','Canada')

    ORDERBY1 ASC

    END

    ELSE

    IF (@Region='APJ')

    BEGIN

    SELECT DISTINCTGbl_Parnt_CountryAS 'COUNTRY'FROMNetwork_Eligible_Acct_Rep

    WHEREGbl_Parnt_Regional_Parent IN(@Region)

    ORDERBY1 ASC

    END

    ELSE

    IF (@Region='EMEA')

    BEGIN

    SELECT DISTINCTGbl_Parnt_CountryAS 'COUNTRY'FROMNetwork_Eligible_Acct_Rep

    WHEREGbl_Parnt_Regional_Parent IN(@Region)

    ORDERBY1 ASC

    END

    ELSE

    IF (@Region='LA')

    BEGIN

    SELECT DISTINCTGbl_Parnt_CountryAS 'COUNTRY'FROMNetwork_Eligible_Acct_Rep

    WHEREGbl_Parnt_Regional_Parent IN(@Region)

    ORDERBY1 ASC

    END

  • If I read your query correctly, you have IF statements to cover the 4 regions specified individually, but nothing else.

    If the value used is not 1 of the 4 values you listed, nothing happens. Your IF statements are not written to handle the combination of values.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Correct how can I pass multiple values and get results

  • Unless I am misunderstanding what you need. Why not use a split list. In SSRS set the Paramter to Multi-Select. Use a Split List function in your query.

    SELECT DISTINCT Gbl_Parnt_Country AS 'COUNTRY'

    FROM Network_Eligible_Acct_Rep

    WHERE (@Region IS NULL OR Gbl_Parnt_Country IN (SELECT Val FROM dbo.Split(@Region, ',')))

    I'm using one I have on my Db but Jeff M has article for the Split8k

    ***SQL born on date Spring 2013:-)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply