Hi there,

I am Looking for assistance in making the the (Select All) option work for a particular report. The parameter is for product families (which there are about 47 unique results for). The report is also influenced by two other parameters, one being a date type i.e 'MTD' 'YTD' 'MAT'. When a long date type such as MAT is selected, selecting all families causes the report to get stuck in an endless loop.

I've tried creating my own <Select All'> item in the parameter dataset, then I have the opposite issue, the <Select All> selection works perfectly but when I try and tick two or more product families I recieve the following error:

"An expressions of non-boolean type specified in a context where a condition is expected, near ',' "

Parameter Dataset:

SELECT        '<Select All>' AS family_description, '<Select All>' AS family_code
UNION ALL
SELECT DISTINCT family_description,  family_code
FROM            dim_item AS item
ORDER BY family_description

Snippet From Main Report Dataset:

Where 

sales.oe_branch_code IN (@Branch)
And 
sales.order_status <> 'X' and sales.line_status <> 'X'

AND (item.family_code IN (@Family) OR @Family = '<Select All>')

Any help is appreciated

Thanks Kindly

SQL Novice

 

Hi,

There is not a lot to go on here, but a couple of hints for you to try.

Try declaring the filtered parameters into a temp table. This will not only help you with testing where you can display the result set, but you can also then select from it later on.

DECLARE @Params TABLE(family_description NVARCHAR(100),family_code NVARCHAR(20))
INSERT INTO @Params
SELECT DISTINCT family_description,family_code
FROM dim_item item
ORDER BY family_description

Your WHERE clause should be something more like this, which gives you a distinct list of family codes.

WHERE sales.oe_branch_code IN (@Branch)
AND item.family_code IN (SELECT DISTINCT family_code FROM @Params)

your problem is here...

AND (item.family_code IN (@Family) OR @Family = '<Select All>')

Please see following link for way to enable multi parameters in SSRS.

For your main report dataset.is it text or procedure ?

Hi there,

I am looking for asssistance in making the (Select All) option work for a particular report. The parameter is for product families (which there are about 47 of). The report is also influenced by two other parameters, one being a date type  i.e 'MTD' YTD' 'MAT'. When a long date type such as MAT is selected, selecting all families causes the report to get stuck in an endless loop.

I've tried creating my own <Select All> item in the parameter dataset, then I have the opposite issue, the <Select All> selection works perfectly but when I try and tick two or more product families I recieve the following error:

" An expression of non-boolean type specified in a context where a condition is expected, near ',' "

Parameter Dataset:

SELECT        '<Select All>' AS family_description, '<Select All>' AS family_code
UNION ALL
SELECT DISTINCT family_description,  family_code
FROM            dim_item AS item
ORDER BY family_description

  Snippet From Main Report Dataset:

Where 

sales.oe_branch_code IN (@Branch)
And 
sales.order_status <> 'X' and sales.line_status <> 'X'

AND (item.family_code IN (@Family) OR @Family = '<Select All>')

Any help is appreciated!

Thanks Kindly

SQL Novice

• Edited by KCBA Tuesday, March 31, 2015 6:45 AM
• Merged by Vicky_LiuMicrosoft contingent staff, Moderator 5 hours 58 minutes ago The same question

Hi,

You can create stored procedures with parameter that coming from report with default value = Parameters!Parameters1.Count

then you must parse MultiValue param using this function

and compare count from function to count from report.

Hi KCBA,

I have check the query you have provided the issue cause by the query " AND (item.family_code IN (@Family) OR @Family = '<Select All>')" you are using which is incorrect.

If the parameter @Family have set as "Allow multiple values" then you don't need to add new label "Select All" for the multiple value parameter already have this section, if the parameter @Family is not the multiple values parameter and you want to select all the values or just select one, you can modify the query as below which will works fine:
"AND (item.family_code = (@Family) OR @Family = '<Select All>' "

If you are using the stored procedure and have issue about add multiple value parameter in the where clause, you are take reference to below similar case about how to create an function to make it work:
Passing multi-value parameter in stored procedure ssrs

If I have some misunderstanding, please try to provide more details information about the relationship of all the parameters (Cascading or not ), current result  you have got and expect result you want(Snapshot).

Any problem, please feel free to ask.

Regards,
Vic