Why does my parameter field fail?

[johnwolf]

Hello

Using CR 8 and Oracle Database

I am creating a report to help final user find the last prices paid for a product by a customer within a date range

My tables are like

INVOICES HEADER
Customer Number
Invoice Number (1)
...

INVOICES DETAILS
Invoice Number (1)
Product ID (2)
Quantity
Price
Unit price
...

PRODUCTS DETAILS
Product ID (2)
Supplier Number
Capacity
Color
...

Final User knows only Supplier Number

Parameters for Customer ID and date range are passed by the customer application and work fine. They do not appear in the selection formula so I guess they are passed trough a SQL intruction. I don't really know how it works.

I created an additional parameter formula which is
{?SUPPLIER_NUMBER}

And a selection formula which is
{PRODUCT_DETAILS.SUPPLIER_NUMBER}={?SUPPLIER_NUMBER}

If I enter "Q2612A" in the parameter prompt, the report does not filter anything

And if I use a "hard" formula selection like
{PRODUCT_DETAILS.SUPPLIER_NUMBER}="Q2612A",

data is filtered anfd the report is OK

I must be missing something obvious

thanks for your help

 

[dgillz]

Please post your entire record selection formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[kskid]

I always make sure my parameters are all uppercase to match my database fields.

{PRODUCT_DETAILS.SUPPLIER_NUMBER}=uppercase({?SUPPLIER_NUMBER})

 

[johnwolf]

Thanks to both of you

The upper case is a good one as I cannot control final users typing, but here it is not the case.

Then regarding the record selection formula, there is an additional condition, the entire formula is

{PRODUCT_DETAILS.PRODUCT_ID}[1]<>"V" and {PRODUCT_DETAILS.SUPPLIER_NUMBER}={?SUPPLIER_NUMBER}

Thanks to first post I ran the report and entered a parameter without the first condition and it worked !!

But still this first condition has to be there and now the question is the following:

With a record selection formula which is
{PRODUCT_DETAILS.PRODUCT_ID}[1]<>"V" and {PRODUCT_DETAILS.SUPPLIER_NUMBER}={?SUPPLIER_NUMBER}

When I enter "Q2612A" in the parameter prompt, data is filtered by the first condition and the parameter does not apply.

But if I use a "hard" formula selection like
{PRODUCT_DETAILS.PRODUCT_ID}[1]<>"V" and {PRODUCT_DETAILS.SUPPLIER_NUMBER}="Q2612A",

Data is filtered and the report is OK.

Thanks for your help

 

[CR85user]

What datatype is your parameter?

Are the customer IDs always a specific format? If so then apply an edit mask to ensure conformance and if you still do not get the result you expect then place the parameter in the report to see what value is shown.

'J

CR8.5 / CRXI - Discovering the impossible

 

[johnwolf]

Data type for the parameter is string.

Customer ID are all and always in the same format.
Apparently no trouble here.

 

[dgillz]

Please explain the [1] following {PRODUCT_DETAILS.PRODUCT_ID}. This typically would indicate the first element of an array.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[johnwolf]

PRODUCT_ID is a string
This condition is to eliminate all records whose PRODUCT_ID begin with the letter "V".

 

[CR85user]

Hi Johnwolf - As I stated previously, can you place the parameter itself in the report to see what value is shown after entering a value at runtime.

There are lots of ways to reference the first character test:

not({table.field} startswith 'V')
not({table.field}[1] like 'V')
not(left({table.field},1) like 'V')

Try one of the variants in place of your current first test to see if it has any impact.

'J

CR8.5 / CRXI - Discovering the impossible

 

[kskid]

Try this.

{PRODUCT_DETAILS.PRODUCT_ID} StartsWith "V" and
{PRODUCT_DETAILS.SUPPLIER_NUMBER}={?SUPPLIER_NUMBER}

 

[johnwolf]

Thanks,

From what I understand, the problem is related with the use of a parameter in the record formula selection.

The part of the formula selection on PRODUCT_ID always worked

Though, I tried every one of your suggestions but I does not change the results.

I am wondering if the fact that the report is called directly from another application passing its parameters and selecting records is not compatible with adding another parameter in a record selection formula of the report.

If anyone has a clue of a similar problem ?

Thanks in advance

 

[CR85user]

If you test each part of the selection criteria seperately, do they both work?

E.g.

//Selection 1
not({table.field} startswith 'V')

Does this return the expected results?

//Selection 2

{PRODUCT_DETAILS.SUPPLIER_NUMBER} like {?SUPPLIER_NUMBER}

Does this return the expected results?

//Selection 3

not({table.field} startswith 'V')
and {PRODUCT_DETAILS.SUPPLIER_NUMBER} like {?SUPPLIER_NUMBER}

What is returned for this test in comparison to what you expect to see based on the previous two selection criteria?


If both of the first tests are returning expected results and the 3rd is not then post back and we can check to see if it is something to do with table linking or such.

'J

CR8.5 / CRXI - Discovering the impossible

 

[dgillz]

Using parameters in a record selection formula is not a problem, I have been doing it for 10 years.

Silly question: How are you running this report? Are you running it from with Crystal itself, or from another application? It could be the application itself trying to set the parameter value, which would explain why a hard coded selection formula works and a selection formula referencing the parameter fails.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[johnwolf]

As I mentioned it in the first post, but maybe quickly and unclearly, I am running the report from another application.

If I understand you well, this would explain why the record selection formula with a parameter fails, am I correct ?

If anyone has got an explanation for this or a way to turn this around, thnaks in advance.

 

[dgillz]

Ok, run the report from within crystal itself. If it runs and returns the expected results, it is the other application.

What is the other application? You may have to go to them for tech support on this one.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[johnwolf]

You are right, It works from CR and not from the application. Sorry for misunderstanding the cause.

Opened a ticket at the application editor.

Thanks to all contributors.