SSRS Filter Selection

I defined an .rdl file paramter as Text data type and allow multiple values. See the attached  ParameterProperty screen.

See the attached SelectAll screen. When 'Select All' is selected, all values in the drop down will be displayed in the filter text entery.

Instead of all item values are displayed, I want to display the word "All" when "Select All" item is selected (when all items are selected). Is there a way to do it or how can I do it?

Thanks.
ParameterProperty.png
SelectAll.png
LVL 1
minglelinchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
Hi,

Unfortunately I don't think there's a way to do this. As far as I know there is no control for that particular display.
On a separate note, I would use Int datatypes, not text, to process calendar years if your data set allows it.
0
ValentinoVBI ConsultantCommented:
Your only option here is to not use the default "select all" functionality but create one yourself instead.

The first step is changing the dataset used to fill the Available Values, you need to add an "All" record there, like:

<your current query>
UNION ALL
SELECT -1 as TheValue, '<ALL>' as Label

Open in new window

This takes Kvwielink's comment about using integers into account.  As -1 is definitely not a year which you'll need in your FiscalYear list, this makes it available for special cases such as the 'all' record here.

The next part is the dataset that retrieves the report data: it will need to take 'all' into account as well.  Assuming you're using integers for the parameter value, the WHERE clause would be something like this:

WHERE (FiscalYear IN (@FiscalYear) OR -1 IN (@FiscalYear))

Open in new window

This assumes you'll keep the multi-value option.  If single value then:

WHERE (@FiscalYear = FiscalYear OR @FiscalYear = -1)

Open in new window

0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Nice solution! Wish I thought of that :p.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

ValentinoVBI ConsultantCommented:
I'm not the inventor of this trick either, though perhaps I should write an article about it... :)
0
minglelinchAuthor Commented:
Thanks all for comments.

ValentinoV, it's clear for your steps. I followed but:
-With a UNION in dataset, -1 is in the list, but the defalut 'Select All' is also in the list.
-If I set the default value as -1, the -1 item is selected while others are not.
-The label 'All' is not in the list, but -1 is in.
0
ValentinoVBI ConsultantCommented:
-With a UNION in dataset, -1 is in the list, but the defalut 'Select All' is also in the list.

The "select all" will always be in the list if you keep the "Allow multiple values" checkbox activated.  There's no way around it except to deactivate that checkbox.

-If I set the default value as -1, the -1 item is selected while others are not.

That's the expected behavior.  Selecting "ALL" in the parameter will return all data because you've taken care of that in the other dataset but the filter will not display all items as selected, only the "ALL" item...

-The label 'All' is not in the list, but -1 is in.

Could you post the query that you use to populate the parameter?  It seems like you've swapped the Label and Value fields...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
minglelinchAuthor Commented:
This is the query -
SELECT DISTINCT CAST(FiscalYear AS varchar(50)) AS FiscalYear, CAST(FiscalYear AS varchar(50)) AS Label
FROM  mytable
UNION ALL
SELECT -1 as FiscalYear, 'ALL' as Label


If  "select all" is always there, add another 'All' item may be confusing.
0
ValentinoVBI ConsultantCommented:
Concerning the query: what type is that FiscalYear field?  If int then you can just do this:

SELECT DISTINCT FiscalYear, CAST(FiscalYear AS varchar(50)) AS Label
FROM  mytable
UNION ALL
SELECT -1 as FiscalYear, 'ALL' as Label 

Open in new window

If  "select all" is always there, add another 'All' item may be confusing.

Possibly, but if you need users to be able to select multiple years at once then I'm afraid that's the only option.  Educating the users should solve this "problem"...
0
ValentinoVBI ConsultantCommented:
In addition to the query response above: are you sure you've set FiscalYear as Value and Label as Label in the parameter's Available Values settings?
0
minglelinchAuthor Commented:
Thank you for the idea. I made it work as expected, just 'Select All' is always there which does not look good.
0
minglelinchAuthor Commented:
Thanks for the post.
0
ValentinoVBI ConsultantCommented:
Glad to hear I could help, good luck with the project!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.