Help with suppressing details 1

[jaburke]

Hi,
My report has a parameter with these values and using a like statement in the SQL:

123* = Blue
124* = Green
125* = Yellow
1235* = Pink

The problem is that if the user selects 123* for Blue, he is also going to get Pink records back. I can't really think of how to handle this, so I was going to try to suppress the details section if parameter = 123* and database_column = 1235*. Any ideas? I'm not able to figure out the syntax. Should I do it differently?

 

[lbass]

I'm assuming the asterisk is only in the record selection formula as a wildcard, not in the data, and that the parameter pick list contains numbers without the asterisk. Just change your record selection formula to:

(
(
{?Parm} <> "1235" and
{table.field} like {?Parm}+"*"
) or
(
{?Parm} = "1235" and
{table.field} like "1235*"
)
)

-LB

 

[jaburke]

Thanks for the reply. You are correct, I am using the * as a wildcard, but it is in the parameter. I'm only displaying the text for the users and in the where I'm doing a:

table.field like {?Parm}

 

[jaburke]

Oh, I forgot something. The reason I am doing it like this is because there's also a parameter:

* = All

So they can choose one of the Colors or the All and my like statement still works.

 

[lbass]

Can you verify that the field for the colors is actually longer than the numbers displayed in your sample?

-LB

 

[jaburke]

Yes, here is some sample "colors" values.

343
343124
343632
343521
343333
343910

So 353521 will come back with 343*, even though 3435* is also a color. So, if they pick 343*, I don't want 3435* to come back or either I want to hide it somehow.

 

[lbass]

Try:

(
(
{?Parm} <> "*" and
(
left({?Parm},4) <> "1235" and
{table.field} like {?Parm}
) or
(
left({?Parm},4) = "1235" and
{table.field} like {?Parm}
)
) or
{?Parm} = "*"
)

-LB

 

[jaburke]

Perfect!! Thanks so much. Now if I want the user to be able to select multiple parrameters, what is the syntax for an array in the record selection?? I'm confused about how I'd declare a variable there?

Thanks again!

 

[lbass]

Well, usually you would still just use:

{table.field} = {?Parm}

But are you talking about the same scenario as previously except with multiple values allowed?

-LB