Using string parameters

[Former Member]

We have our Purchase Order Number set up as character with a length of 14.The Purchase Order Number in the database is stored as 5 spaces and then 9 digits.I am using Crystal Reports 2008 and trying to use the Purchase Order Number as a parameter.Since our main system automatically pads the 5 spaces for the end user I would also like to extend this feature into the parameter.The Purchase Order Number parameter needs to allow for multiple entries and ranges.

I have prototyped the following for a single entry and it works:

• Using a parameter called ?PurchaseOrderNbr; I enter 9 digits
• Using a formula called @PadThePurchaseOrderNbr; RIGHT("" & ({?PurchaseOrderNbr}), 14)
• Using a filter that is coded as: {PURCHORDER.PO_NUMBER} = {@PadThePurchaseOrderNbr }

I have tried various coding schemes to mimic the systax that CR 2008 uses in the Select Expert with no luck, when I have multiple values or a range.Is there any body out there that has a solution?

Thank you.

[DellSC · Accepted solution]

The reason your formulas aren't working is because a multi-select parameter contains an array of values, not just a single value, so you would have to update all of the values in the array.

Try this instead:

ltrim({PURCHORDER.PO_NUMBER}) = {?PurchaseOrderNbr}

Instead of adding the spaces to the parameter, this will take them off of the field value. The problem with this is that you're applying a Crystal formula to a field, so Crystal will pull all of the data into memory and filter it there.

A better solution is to use your database's syntax to trim the spaces off of the field in a SQL Expression. In both SQL Server and Oracle, this looks like:

trim("PURCHORDER"."PO_NUMBER")

You would then put this in the Select Expert:

{%PO_Number} = {?PurchaseOrderNbr}

This will push the trim down to the database for processing instead of doing it in memory which will make the report run faster.

-Dell

[Former Member · Accepted solution]

Thank you Dell. Now I learned what the purpose of the SQL Expression.

My supervisor tried to get me training, but there is no budget, so I really appreciate your simple solution.