asked on

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

Tony303

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.

minglelinch

ASKER

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?

Tony303

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

ASKER CERTIFIED SOLUTION

ValentinoV

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ValentinoV

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 Horn

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.

minglelinch

ASKER

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.

minglelinch

ASKER

Thanks for all three of you.