Report parameter with or condition to fetch the data

Question

Hi, I am trying to create a report where the user will have to give the parameter. Scenario below

Parameter: @Number: as text value --it is not a mandatory filed,can be a blank

@Country:- as multivalue getting values from dataset created to fetch country name-mandatory field.

in the report for number parameter i allowed as blank value and for country i selected as allow multiple values.

my main data-set, to fetch the result is a simple select statement like

Select * from table1 join On table2 

where number in (@number) or country in (@country)

now when i am running the report i am able to get the result for all the number with country,but when i am giving an input to the parameter number as suppose 1001 it should show only for the particular number 1001 but it showing the result for all the Number.

need help to fix the issue

Thanks in advance

 

Answer 1

Hi,

It is normal, because you have 'or', so with countries selected in the first parameter, the query return all rows in the selected countries.

Try with this query :

Select * from table1 join On table2 

where (number in (@number) or ISNULL(@number,number)) and country in (@country)

---

Ousama EL HOR

[If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

 [User Page]     [MSDN Page]     [Blog]     [Linkedin]

Answer 2

It's fetching me again with all the number even after passing the value for number parameter in the report.

is there any way possible.

here the country parameter is a mandatory,we provide or not the value to the parameter it will still run. but in that case it will display all the number present in the country suppose Australia. But when i am providing the value to parameter it show show only to that value... 

Answer 3

It's fetching me again with all the number even after passing the value for number parameter in the report.

is there any way possible.

here the country parameter is a mandatory,we provide or not the value to the parameter it will still run. but in that case it will display all the number present in the country suppose Australia. But when i am providing the value to parameter it show show only to that value... 

Sorry, my query wasn't correct.

Try this :

Select * from table1 join On table2 

where (number in (@number)) or (number=ISNULL(@number,number) and country in (@country))

---

Ousama EL HOR

[If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

 [User Page]     [MSDN Page]     [Blog]     [Linkedin]

Answer 4

Hi Ousama EL HOR,

thank you for the solution.

its working fine...but here in my case i have an condition that country parameter is an mandatory option where as the parameter number can be blank. when using the above solution. i am asked to input the data for number also. even selecting to the parameter country. here the parameter number is also acting as the mandatory if the above query is implemented.

Answer 5

Hi Ousama EL HOR,

thank you for the solution.

its working fine...but here in my case i have an condition that country parameter is an mandatory option where as the parameter number can be blank. when using the above solution. i am asked to input the data for number also. even selecting to the parameter country. here the parameter number is also acting as the mandatory if the above query is implemented.

No, with my solution the number parameter is not a mandatory option, it can be blank or NULL.

And to have the country parameter as an mandatory parameter, is simple, juste use this :

Select * from table1 join On table2 

where (number in (@number)  and country in (@country)) or (number=ISNULL(@number,number) and country in (@country))

---

Ousama EL HOR

[If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

 [User Page]     [MSDN Page]     [Blog]     [Linkedin]

Answer 6

HI Ousama EL HOR,

i have tried the above solution too.

here when i am giving input to the parameter country and leaving blank the number parameter ---it is showing nothing but when i am providing the value for the parameter number it is showing me what i was expecting.

we only have to check now when the user select the parameter Country then he be able to see all the number related to the particular Country.even leaving the number parameter blank.

Regards,

Answer 7

Hi RK05,

According to your description, it seems that you want the @number to be an optional parameter, right?

I test this in my environment, and find that you could set "Allow null value " for parameter(which can't use together multiple parameter, so you need to use split function to manually pass multiple parameters.)

Below is my design:

You need to create split function in datasource at first(run below query in corresponding database in SSMS )

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[Split_String]
(
@List nvarchar(max),
@token nvarchar(5)
)  
RETURNS @RtnValue table 
(
--Id int identity(1,1),
Value nvarchar(100)
) 
AS  
BEGIN

SET @List = RTRIM(LTRIM(@List))
IF @List <> '' 
BEGIN
    WHILE (CHARINDEX(@token, @List)>0)
    BEGIN 

        INSERT INTO @RtnValue (value)
        SELECT Value = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@token,@List)-1))) 

        SET @List = SUBSTRING(@List,CHARINDEX(@token,@List)+LEN(@token),LEN(@List))

    END 

    INSERT INTO @RtnValue (Value)
    SELECT Value = LTRIM(RTRIM(@List))
END

RETURN
END

GO

My dataset query:

SELECT        country, number
FROM            orconditioninparameter
WHERE        (country IN (@country)) AND (number IN
                             (SELECT        Value
                               FROM            dbo.Split_String(@number, ',') AS Split_String_1) OR
                         @number IS NULL)

Then set @number parameter to  "Allow null value" in parameter properties.

You will get the result like below

Best Regards,
Zoe Zhi

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

HI Ousama EL HOR,

i have tried the above solution too.

here when i am giving input to the parameter country and leaving blank the number parameter ---it is showing nothing but when i am providing the value for the parameter number it is showing me what i was expecting.

we only have to check now when the user select the parameter Country then he be able to see all the number related to the particular Country.even leaving the number parameter blank.

Regards,

for that I think easiest thing you can do is to set default value for number parameter map to the same dataset which populates the values for it. So any time you change a country parameter, the number parameter will refresh and default will still be select all functionality (i.e selecting all available values). So it will render the report with the required data

Any time you want to filter on one (or more) of number values, you can select it within number parameter and then the query will filter resultset based on number parameter values as well in addition to your country parameter values.

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 9

Hi Ousama,

Finally it worked with the solution you provided.

Thanks a lot

Answer 10

Hi Zoe,

yes you are rite i worked in the same way to get the solution and i finally did. I worked fined.

Thank you