Select * in Parameter 1

[ddnh]

I've created a parameter field caled RNumber. When running the report I'd like to be able to either enter a specific number and that record is displayed, or enter a symbol and have all records displayed. I've used the following following formula (RNumberSelect) for other parameters and it worked fine. (They are strings, not numbers.)

IF {?RNumber} = "%" THEN
"*"
ELSE
{?RNumber}

In the selection criteria I have the following

{vw_name.RNumber} like {@RNumberSelect}

This isn't working for this parameter though. The only difference between this and the others is that this is a number, the others are strings. Any suggestions?

 

[synapsevampire]

Create a number parameter and enter 0 for the record number.

Now use:

IF {?RNumber} <> 0 THEN
{MyTable.MyRecord} = {?RNumber}
ELSE
true

This assumes that you have a recordnumber field.

If not, some databases won't support a recordnumber function, Oracle willl, but You'll need to create a SQL Expression to retrieve it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ddnh]

I changed the {@RNumberSelect} formula to your suggestion and tried to {vw_name.RNumber} like {@RNumberSelect} in the selection criteria. The error reads &quot;a string is required here.&quot; It places the cursor right before {vw_name.RNumber}. Do I need to change the selection criteria or put your formuals somewhere else?

 

[synapsevampire]

Why do you have a {@RNumberSelect}? That indicatres that you're creating a formula somewhere...

You should have something with a {?***} as the parameter, not a formula.

Then in the record selection formula, use

IF {?RNumber} <> 0 THEN
{vw_name.RNumber} = {?RNumber}
ELSE
true

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ddnh]

I thought you were suggesting I change the formula. I didn't realize you wanted me to replace what I had in the selection criteria.

This didn't work either. It allowed me to select one record only. I am trying to select one record OR all records. If I put &quot;*&quot; or &quot;%&quot; when entering my parameter, it says a digit is required.

Any other suggestions?

 

[ddnh]

Thanks for your help synapsevampire, but I was able to figure it out.

I changed my parameter to a string value and used the following in the selection criteria:

ToText ({vw_name.RNumber} ,0,'') like {@RNumberSelect}


I left the RNumberSelect formula as I originally had it:

IF {?RNumber} = &quot;%&quot; THEN
&quot;*&quot;
ELSE
{?RNumber}

Now if I enter &quot;%&quot; I get all the records or if I enter one RNumber, I get that specific record only.

 

[synapsevampire]

That's not very efficient, will return the wrong results, and there's no reason to use a RNumberSelect formula at all.

Your record selection should be:

(given {?Rnumber} is changed to a numeric parameter)

If {?Rnumber} <> 0 then
{vw_name.RNumber} = {RNumber}
else
true

As in the previous post.

What this does is if the number entered is 0, it will not pass anything to the database (much better than like *), and if it isn't 0, it will explicitly find the RNumber entered.

To test the inaccuracy of your solution, enter 1 as the number, it will return anything that starts with 1, as in 1, 10, 11, 12...

To verify it's inefficiency, aside from the obvious overhead of using a like, which is ill advised, select database->Show SQL Query, note that it probably isn't passing the SQL to the database, and if it does, it's inefficient SQL.

Try my post again, and don't alter it.

No formulas, just a numeric parm and a record selection criteria.

If you experience problems, state the nature of the problem, not that this didn't work, that won't help someone resolve your problem.

If you really want to enter % as the default for all rows, alter your record selection formula to:

IF {?RNumber} <> &quot;%&quot; THEN
{vw_name.RNumber} = val({RNumber})
ELSE
true

Again. no formulas, and avoid the LIKE predicate to improve performance.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ddnh]

OK, that worked just fine. When I originally tried it, I didn't realize I should be entering 0 if I wanted all records. I noted when I said it didn't work that if I entered * or % it told me a digit was necessary. I was trying to explain how it didn't work.

As for my solution, it works just fine and gives accurate results. I tried your test, but it failed. I am either getting all of the records, the one record I want, or no records (if I enter a 6 for example. there is nothing in the DB with 6, 60, or 600, but there are plenty of records in the 6000s...none were returned).

Since I don't know anything about performance issues or never heard to stay away from LIKE, I'll take your advice and use your solution since it's working properly. Thanks again for your help.

 

[synapsevampire]

Glad it worked, since you were referencing a formula still, I had thought that you were converting it in there.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com