Using Like Statement with Parameters

[Former Member]

I have an Excel document which has 7000+ login IDs which I need to use as parameters.

I have added them as a static parameter, and can use them in my report when I am looking at the Login ID table. I have to manually select each page and pull all Available Values into the Selected Values filed. This works, but I would like to find a more efficient way to do this.

On top of that, I need to parse a "Description" field (free form) for those same login IDs. I have been trying to find some way to use the Like function with the Parameter.

I have tried using   Like "*" + {?LogIDs} + "*" in the Select Expert. But when I run the report, Crystal blows up and shuts down.

I have tried putting it in a Formula, but I get an Array error message.

Any help would be greatly appreciated.

[abhilash_kumar]

Hi David,

Alright, one issue at a time.

For the 1st one I guess you're trying to see all the values in a single list rather than having to browse through multiple pages to select the values in the prompt, isn't it?

This is controlled by a registry key setting which limits the values to 200 per page/batch.

What version of CR are you using?

-Abhilash

[Former Member]

That would benifitial, that way I could pull all of the LogIDs in at once, instead of having to go through each page and "Add All"

Were using CR 2008

[Former Member]

That would benifitial, that way I could pull all of the LogIDs in at once, instead of having to go through each page and "Add All"

Were using CR 2008

[abhilash_kumar]

Hi David,

To increase the batch size, browse to:

HKEY_CURRENT_USER\Software\Business Objects\Suite 12.0\Crystal Reports\ReportView

and look for a key called 'PromptingLOVBatchSize'. Double-click the key and select 'Decimal' and set the value to 5000. Then, make sure you select 'Hexadecimal' and click OK. (We've noticed sometimes that the value is not saved unless we keep Hexadecimal selected).

Restart Crystal Reports designer and you should have all the values in one batch.

-Abhilash

[Former Member]

Is 5000 the highest you can go?

[abhilash_kumar]

Well, I don't think there's a cap on what value you can specify in there.

You can change that to 10000 or something over 7000.

-Abhilash

[Former Member]

That worked to bring all the available parameters into one window and allowed me to select all at once.

Thanks,

Any idea on the second part? How to parse a free form field for the same list of parameters?

[jyothirmayee_s]

Hi,

Try setting default value for parameter as "ALL"  and add below statement to Select Expert:

If {?Parameter}= 'ALL'

Then {Database.Field} like '*'

Else {Database.Field} = {?Parameter}

Thanks,

Jothi

[Former Member]

That works if Im looking for all values in the database filed containing the LogIDs, but I have the list of LogIDs in the Excel doc which is a sub set of all LogIDs in the DB.

I have used this list of LogIDs to create a static Parameter, but that option is very tedious to use.

[JWiseman]

to add to Jyothirmayee's suggestion and sorry Abhilash for interupting your piece of the thread, i'd recommend ensuring that the select statement is passed to the database.

to ensure that the select is passed to the database, you'll want to structure it so that it's something like:

{field} = (result)

where the result could be a set of if then else statements, a formula containing a like clause etc.

i know that doesn't explain it that well, so please see the blog post here for a detailed set of instructions.

cheers,

jamie