rdl Select All value

My rdl file has a text parameter filter defined, see the attached file Parameter, and the second attached file shows its preview.

If the (Select All) is selected, what is the value on store procedure side?

I used the following on store procedure side (@FiscalYear is parameter passed in)
select col1, col2,...
from mytable
where (nullif(@FiscalYear, 'SELECTALL') is null OR FiscalYear IN ( ....)

But I always have problem to get data using the above. What is the correct way?

Thanks.
Parameter.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.

Tony303Commented:
You don't need a specific reference to "SELECT ALL" .

Your filter will get all the items that are ticked as per your SelectAll.png, it ignores the "SELECT ALL" tick.
0
minglelinchAuthor Commented:
Thanks for answering.

You mean all selected items are included in the parameter variable @FiscalYear that is passed into store procedure, and I can ignore "Select All". That's nice. I still need to know what format @FiscalYear is for all selected items, and how I should use it in SP. Should I further parse it to a usable format, or how can I use it directly?
0
Tony303Commented:
Not knowing how exactly your report looks or is put together but...

Let's say, your report has the tablix that is showing the data.
Right click the tablix, Tablix Properties, Filters.

Add,
Expression choose Year.
Operator choose In.
Value type @FiscalYear.

This will filter the result by your parameters ticked.

T
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

ValentinoVBI ConsultantCommented:
I still need to know what format @FiscalYear is for all selected items, and how I should use it in SP. Should I further parse it to a usable format, or how can I use it directly?
Good question.  In the SP the parameter is a string which contains a comma-separated list of values.  So you do indeed need to parse that first, which can be done through several methods.  One of them is a function that splits the string into a table resultset.

See this article for a more detailed explanation: Reporting On Data From Stored Procedures (part 2)

If you don't want to read through the whole (educational) article, you can jump down to the "Right Way To Implement The Parameter" chapter...
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
ValentinoVBI ConsultantCommented:
Small sidenote: you wrote:

where (nullif(@FiscalYear, 'SELECTALL') is null

Open in new window

I don't see any reason to use this nullif function, the following would have had the same result and is more readable:

where @FisvalYear = 'SELECTALL'

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
My usual style is to create a separate sp to populate the combo box, that goes something like this...
SELECT 0 as sort_order, -1 as id, '<ALL>' as name
UNION ALL
SELECT 1 as sort_order, id, name
FROM tblCustomers -- or whatever
ORDER BY sort_order, name

Open in new window

Then in the report's SP, there are a couple of ways handle the scenario if id is -1.  Here's one..
SELECT blah, blah, blah
FROM Whatever
WHERE (id = @id OR @id = -1)

Open in new window

VV - Nice article.  Voted Yes.
0
minglelinchAuthor Commented:
Nice article, glad to read it and surprise to see my reports are buit in the same way, must be referenced this article earlier.

Nice pointing out list_to_table conversion. Now my report is still tricky. My report has an ID filter with uniqueidentifier data type and a flag filter with Active(1), Inactive(-1) and Both(-1) choice.

Is there special process for these data type?

Right now I treat uniqueidentifier same as string type, and I get the two types in SP this way:

Select cols, ...
From mytable
Where  ( WorkID in (select  rtrim(ltrim(s))  from dbo.fSplitString(@myUndentifyer, ',')))
     AND (NULLIF(@myFlag, -1) IS NULL OR FieldFlag = @myFlag)

fSplitString is a function.

I doubt these two types gave my report trouble.
0
minglelinchAuthor Commented:
Thanks for all three of you.
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.