cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Reports - Optional Parameters

Former Member
0 Kudos

Dear Crystal Reports Gurus,

I am trying to create the following optional parameters:

1. Employee code (Ma@from OHEM) - Is defined as a string parameter even though it is a number. The way the parameter is labelled allows for me to select the code from the List of employees. If i was to define this parameter as a number, i would lose this option of selection and would have to enter the code manually when generating the report.

2. Department -  This field is also a number value. When i define this parameter as a number and attempt to generate my report with only parameter 1 (above), i get the error message 'Optional Parameter not support; enter valid value'. I get this message despite defining both paramters (Employee code+department) as optional parameters.

Both parameters should subsitute one another i.e. either Employee code or Department should be selected. I have a third parameter  - Year which is a number value.

Below are my record selection formulas with the respective error messages.

{_HRMATRIXL.U_Year} = {?Year} and

(not HasValue({?Ma@from ohem}) OR {_HRMATRIX.U_EmpId} = Tonumber({?Ma@from ohem})) and

(not HasValue({?Department}) OR {OHEM.branch} = {?Department})

Error message (in german so i'll try my best to translate)

ERROR: String is not numerical

Details: ErrorKind

Error in file """crw{C84.....}.rpt Error in formula

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Marvin,

When you get a pop-up with the error message, CR should highlight part of the code that throws the error. Could you tell us what part of the above code it highlights?

Also, I didn't understand why you couldn't setup the {?Ma@from OHEM} parameter as Number. You should get List of Values even if the parameter in Number.

P.S: By any chance, have you set the {?Ma@from OHEM} parameter to 'allow multiple values'?

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

The webpage stalled before i could paste the error message and i opted to save it before losing everything i had typed in. The error message is as follows:

ERROR: String is not numerical

Details: ErrorKind

Error in file """crw{C84.....}.rpt Error in formula:

(not HasValue({?Ma@from ohem}) OR {_HRMATRIX.U_EmpId} = Tonumber({?Ma@from ohem}))

and

'

String is not numerical

Details: errorKind.

I have the allow multiple values option as false in my {?Ma@from OHEM} parameter.  Should it be otherwise?

abhilash_kumar
Active Contributor
0 Kudos

Hi Marvin,

Webpage? Oh wait, are you running this report from a web application?

Does it work from the CR Designer first?

P.S: The allow multiple values should be True only if you want the prompt to accept multiple values, otherwise leave it as it is. If it is set to true, then you can't use the ToNumber() like you have, since the numbers are no longer numbers, they're comma separated strings.

You need to loop through each value in the prompt and convert it to the prompt. Don't worry about this though, as the option is False anyway.

You also didn't tell why you've created a Sting parameter when a number parameter should be just fine.

-Abhilash

Former Member
0 Kudos

HI Abhilash,

I am running crystal from the CR designed application. I meant this (scn.sap) webpage stalled.

I've once again set both optional parameters to be number parameters and removed the Tonumber from the record selection formula. For one, the {?Ma@from OHEM} parameter does not display a list of values i.e. is a free text field.

Below is my new formula based on Prathamesh's suggestion below.

((not HasValue({?deparment}) OR {OHEM.branch} = {?deparment}) OR

(not HasValue({?Ma@from ohem}) OR {_HRMATRIX_EmpId} = {?Ma@from ohem})) and

{_HRMATRIX.U_Year} = {?Year}

The error message i get when i attempt to generate the report using the each optional parameter individually is 'Optional parameter is not supported;enter valid value'. This happens each time i attempt to generate a report using one of the optional parameters that is defined as a number.

I tried this out on Monday and actually got one it work using an ISNULL formula for one parameter and a HasValue for another all combined in an IF function. I overwrote it (silly me) since it only worked with one parameter and not with the other. Both parameters were then defined as string parameters.

Thanks in advance for your advice.

abhilash_kumar
Active Contributor
0 Kudos

Hi Marvin,

1) The Record Selection formula should not have an OR; what you had earlier is right:

(

(not HasValue({?deparment}) OR {OHEM.branch} = {?deparment})

AND

(not HasValue({?Ma@from ohem}) OR {_HRMATRIX_EmpId} = {?Ma@from ohem})

)

AND

{_HRMATRIX.U_Year} = {?Year}

2) Does the report work fine from the CR Designer?

3) Optional Parameters were introduced only in CR 2008. So if your web application is using old runtimes of CR, optional prompts will not work.

4) When you create the Number prompt, what type do you select - Static or Dynamic? Could you Select Dynamic > Choose the Number field as the Value field and see if this helps?

-Abhilash

Former Member
0 Kudos

Dear Abhilash,

I'm using crystal reports 2008 and it's installed on a virtual server (so is SAP) that i access via a remote desktop connection. I am therefore not using a web application.

With regards to your 4th question, i initially set up both optional parameters as static values; below a screen shot of my parameter window. The moment i switched the list of values to be dynamic, the parameter type automatically switched to string. It unfortunately did not work. Below is a screen shot of my parameter window

I keep getting the same error message: 'Optional parameter is not supported;enter valid value'. It seems to me that this is tied to the fact the parameters are defined as numbers and not strings. When i define the parameters as strings, i get the error i described in my initial message above.

What could i be doing wrong?


Thanks Abhilash for your assistance.

abhilash_kumar
Active Contributor
0 Kudos

Well, a parameter of type Number can also be defined as Optional.

Could you answer these:

1) Does the report work fine from the CR Designer?

2) What is the datasource of the report?

-Abhilash

Former Member
0 Kudos

   The report works perfectly fine in the CR designer. Datasource is as follows:

Database Name (DATA_REAL)

  • dbo
    • Tables - Some values are derived from standard tables(Standard + AddOn tables) from SAP B1

    • Stored Procedures - Our consultant had to create some tables as stored procedures in the SQL Server studio due to the fact that the fields.tables in their standard format weren't convenient for CR report generation. Most fields in the report come from such (stored procedures) tables. _HRMATRIX however is a standard table (part of an Add-on) that does not exist as a stored procedure. Could this be where the problem lies?

Have i answered your question?

Rgs, Marvin

abhilash_kumar
Active Contributor
0 Kudos

Aha! That helps.

Please re-post the question to the SAP Business One Space here :

http://scn.sap.com/community/business-one

Since the report works fine from the designer and it doesn't work in SAP B1, I'm guessing it doesn't support all features of CR.

I hope you get some advice in the B1 space.

-Abhilash

Former Member
0 Kudos

Thanks alot for your help Abhilash. I'll take it up next week.

Best regards,

Marvin

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Marvin,

You can simply declare the 2 parameters as numbers and set the 'Allow multiple values' and 'Optional Prompt'  properties to 'True' in the Edit Parameters dialogue box.

Given that {?Year} is not an optional prompt and atleast one of the {?Ma@from OHEM} and {?Department} should be key-ed in, here is how  the record selection would look like:

{_HRMATRIXL.U_Year}={?Year} and

(

1st condition

OR

2nd condition

)

Look at the 'OR' and the conditions should not have "tonumber()" in them.

-Prathamesh

Former Member
0 Kudos

Hi Prathamesch,

I responded to your message in my response to Abhilash above.The 'Allow multiple values' option did not bring about the desired outcome for me neither did the formula with OR instead of AND.


Thanks anyway for your assistance.

Marvin