getting the value from multivalued blank parameter return nothing.

Question

Hi,

I have three parameter (@person_id, @Person_name, @Supervisor_name), all have multivalues and blank property enabled.

Columns of the report are Person_id, Person_name, Supervisor_name, Claims_done, average_claims_perday created with dataset table with same columns.

The dataset which return the data has filter in query:

where @person_id in (@person_id)
or [PersonName] in (@Person_name)
or Supervisor_name in (@supervisor_name)

The requirement is out of three parameter, if any of the parameter is blank, then query should gives the result based on the paramter that are selected with multivalued.

For Example: dataset creates the following result.

11	abc	john	12	3
22	def	john	345	9
33	ghi	bryan	89	7
44	jkl	bryan	45	6
55	mno	bryan	60	7

If i select the parmeter @Person_name = 'mno' and @Supervisor_name = 'John' and kept @person_id blank then it should give the result:

11	abc	john	12	3
22	def	john	345	9
55	mno	bryan	60	7

 if i select @person_id = 11, 44 and @Supervisorname  = 'John', and left the @Person_name blank, then it should give the result:

11	abc	john	12	3
22	def	john	345	9
44	jkl	bryan	45	6

When i keep any of the parameter blank, the report doesnt shows anything, IF i select at least one value for all parameters, it gives perfect result.

Any help is appreciated.

Thanks,

Punia

Answer 1

([Person_id] in (@person_id) or @person_id = Nothing)
and ([PersonName] in (@Person_name) or @person_name = Nothing)
and (Supervisor_name in (@supervisor_name) or @Supervisor_name = Nothing)

Answer 2

Thanks for replying Tim, But unfortunately that didnt work. Is there is any other work around?

Thanks

Answer 3

Hi There

You can use something like this for your parameter which might have blank values

IF EXISTS ( select * from CRM_Example where CONVERT(varchar(10), Code) in (@productfamily))
begin
SELECT     Code, Hot, Cold, acid, tin, Colered, slab
FROM         CRM_Example
WHERE    CONVERT(varchar(10), Code) IN (@productfamily)
end
else
SELECT     Code, Hot, Cold, acid, tin, Colered, slab
FROM         CRM_Example

I hope this will help

If you have any question please let me know.

Many Thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 4

Hi Syed, i didnt get the solution u suggest. Do i have to write it in the sql query in dataset?

Anyhow, it works perfectly when i run the query behind the dataset by leaving one/two of the parameters blank and just put the values in one parameter. But didn't work out in report.

I updated the question with example at the top with more details, in case if i missed something before.

Thanks

Answer 5

Hi There

In order to work with multivalued with blank values please do the following

If you have attached your multivalued parameter with a dataset please change the query like this

Select labelName , lD

From

Yourtable

Union all

‘Any’ as  labelName, ‘’ as ID

Select labelName , lD 
From
Yourtable
Union all
'Any' as  labelName, '' as ID

And set your default values as

for default value choose sepecify values and put
""

Please make sure to check allow blank values

Now when you run the report by default Any has been selected and you can run your report

If you have any question please let me know.

Many Thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 6

HI Syed,

I solved my problem. I did almost the same thing. In the dataset from where parameters are populated, i put the union thing there.

Select Person_name from table 
union
Select ' Unkown' as Perosn_name
order by Person_name

And now when i select ' Unknown' in parameters the result is given based on other parameters. I did the same thing for other parameters too.

Thanks for help.

Punia