March 18, 2015 at 8:13 am
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
March 18, 2015 at 8:26 am
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.
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]
March 18, 2015 at 8:29 am
Correct how can I pass multiple values and get results
March 18, 2015 at 8:37 am
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