Parameter Help

Question

Parameter Help

Paul Morris-1011726

SSCommitted

Points: 1879
More actions
April 10, 2012 at 6:21 am

#391980

I have a set of reports that I am trying to consolidate into a single report using a drop down list for filtering.
There are only 3 values:
Printing = lasertrk
Copying <> lasertrk
Both = (return all rows)
They are defined in the parameter properties under available values as follows:
Label Value
Both *
Printing lasertrk
Copying <> lasertrk
The Printing filter works fine but I am havind some trouble with the other two. Can you pass a parameter with a '<>' without it seeing it as text? And how would I return all the rows when the Both is selected?
The query returns all the rows but I can't seem to figure out how to us the drop down to display them.
Any help would be appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Tek SSCommitted Points: 1697 More actions · Answer 1

Paul Morris-1011726 (4/10/2012)
I have a set of reports that I am trying to consolidate into a single report using a drop down list for filtering.
There are only 3 values:
Printing = lasertrk
Copying <> lasertrk
Both = (return all rows)
They are defined in the parameter properties under available values as follows:
Label Value
Both *
Printing lasertrk
Copying <> lasertrk
The Printing filter works fine but I am havind some trouble with the other two. Can you pass a parameter with a '<>' without it seeing it as text? And how would I return all the rows when the Both is selected?
The query returns all the rows but I can't seem to figure out how to us the drop down to display them.
Any help would be appreciated.

If I'm understanding your question, then maybe this will help you:

Create a parameter. In the parameter, go to Available Value. In here you enter your three options, Both, Printing, and Copying for Labels and then enter B, P, and C for values.

Then in the tablix properties, go to the Filter section. Click on the Add button. In the Expression part, click the fx button. Then you'll enter an expression like this:

=IIF(Parameters!PrintCopyBoth.Value="B","1",Fields!Column.Value)

After your done with this part, go back to the Filters section and under VALUE click on the fx button again and enter this:

=IIF(Parameters!PrintCopyBoth.Value="B","1", IIF(Parameters!PrintCopyBoth.Value="P","P","C"))

Click OK to everything and then test it.

Since you enter the three values in the parameter manually, you should get a drop down box with Copy, Print, and Both, but you can only select one. This is the way you want it right?

The the filter will do the job of bring back data that's only print, copy, or both.

Hope this helps you out.

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 2

Thanks for the reply... I will test tomorrow and give feedback.

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 3

So I have had some time to look over this and had a couple questions:

First let me give better info, the parameter name is @Type and the column name is unitid.

So based on this:

Then in the tablix properties, go to the Filter section. Click on the Add button. In the Expression part, click the fx button. Then you'll enter an expression like this:
=IIF(Parameters!PrintCopyBoth.Value="B","1",Fields!Column.Value)

Mine should look this:

=IIF(Parameters!type.Value="B","1",Fields!unitid.Value)

Does this go in the Value field?

=Parameters!type.Value

After your done with this part, go back to the Filters section and under VALUE click on the fx button again and enter this:
=IIF(Parameters!PrintCopyBoth.Value="B","1", IIF(Parameters!PrintCopyBoth.Value="P","P","C"))

Mine would look like this:

=IIF(Parameters!type.Value="B","1", =IIF(Parameters!type.Value="P", "P", "C"))

So for clarification for me (ie not sure and would like to understand), the second piece is going into the filters section in the tablix?

Am I adding a second filter or changing the expression there?

I understand the first piece (=IIF(Parameters!type.Value="B","1",....), but I am struggling to undertand the second part (...=IIF(Parameters!type.Value="P", "P", "C")...)

If you don't mind explaining or directing me to an article or something to help, I would appreciate it.... and again I apprecaite the time to hellp me...

Tek SSCommitted Points: 1697 More actions · Answer 4

I hope I can explain this, so here goes.

If you select Both, which has the value of B, it gives the value of '1', which the first part of the filter will also be one. Because both the expression and value of the filter are equal, both Printing and Copying will be returned.

If you select either Copy or Print, then in the first part of the filter, it will be the value of either P or C. So you're already saying that, the unitid value equals P.....

and then in the second part of the filter if the parameter is P, then it gives it a value of P. With the given values, you are saying in the filter only return records with unitid that only equal to P, or if copy C.

Now this depends if you use P or C as values in the unitID field in the table. If not, you might have to tweak it to fit in your environment.

It also took me a little while to understand this, but I've used this expression for different situations, such as in my case, Pay/Suspend records, Time/Expense records.

I hope my explaination was clear. I'm not very good and explaining how some of my expressions work, I just know that they do work.

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 5

Thanks for the reply... that was pretty clear.... I will play with it and let you know....

Thanks again!

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 6

OK made some progress but I am stuck...

The "Both" and "Printing" selection works from the drop down list on the report... the "Copy" selection is where I am stuck.

This is what I have:

In the Tablix properties under Filters:

Expression:

=IIF(Parameters!type.Value="B","1",Fields!unitid.Value)

Value:

=IIF(Parameters!type.Value="B","1", IIF(Parameters!type.Value="P", "LASERTRK", Fields!unitid.Value IS NOT "LASERTRK"))

The last part in bold is causing me grief... it basically needs to be not equal "lasertrk".... another option is like %cop% and %clr%...

Any ideas without having to use customn code?

Tek SSCommitted Points: 1697 More actions · Answer 7

Paul Morris-1011726 (4/25/2012)
OK made some progress but I am stuck...
The "Both" and "Printing" selection works from the drop down list on the report... the "Copy" selection is where I am stuck.
This is what I have:
In the Tablix properties under Filters:
Expression:
=IIF(Parameters!type.Value="B","1",Fields!unitid.Value)
Value:
=IIF(Parameters!type.Value="B","1", IIF(Parameters!type.Value="P", "LASERTRK", Fields!unitid.Value IS NOT "LASERTRK"))
The last part in bold is causing me grief... it basically needs to be not equal "lasertrk".... another option is like %cop% and %clr%...
Any ideas without having to use customn code?

If you want to use %cop% method, maybe this can help:

=IIF(Parameters!type.Value="B","1", IIF(Parameters!type.Value="P", "LASERTRK", Fields!unitid.Value LIKE "%" & "cop" & "%"))

Have you tried this? Fields!unitid.Value <> "LASERTRK"

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 8

Sorry it took so long to reply... got pulled away for something else...

I tried with <> and like but it is throwing an error cannot compare string with boolean.... I tried to convert it but that didn't work...

If you (or anybody) has an idea it would be great... going to play with it some more...