• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

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?

  • 3
  • 2
  • 2
  • +1
1 Solution
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.
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?
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.

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

This will filter the result by your parameters ticked.

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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

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
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.
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.
minglelinchAuthor Commented:
Thanks for all three of you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now