Optionnal Prompts in Crystal Report

[Former Member]

Hello Experts,

I've got an incredible issue concerning optional prompts dynamic parameters on CR for Business one.

Indeed, i've created 4 optional prompts dynamic parameters. Unfortunately, 1 of them (the last it seems) is always not working.

When i move the last parameter on the formula, then the concerned parameter is working! And the "new" last one is not.

Is there any restrictions on Optional prompt parameter number?

FYI, formula used :

if not (hasvalue({?Statut})) then True else {OINV.DocStatus}={?Statut}

and if not (hasvalue({?Date})) then True else {OINV.DocDate}={?Date}

and if not (hasvalue({?@Select "CardName" from OCRD where "CardType"='C' order by "CardName"})) then True else {OCRD.CardName}={?@Select "CardName" from OCRD where "CardType"='C' order by "CardName"}

and (not HasValue({?@SELECT distinct T0."UserSign", T1."U_NAME" FROM OINV T0 INNER JOIN OUSR T1 ON T0."UserSign" = T1."USERID"}) OR {OINV.UserSign} = {?@SELECT distinct T0."UserSign", T1."U_NAME" FROM OINV T0 INNER JOIN OUSR T1 ON T0."UserSign" = T1."USERID"})

and {OINV.CANCELED}='N'

(in that case, Parameter on user is not working)

[abhilash_kumar · Accepted solution]

Hi Nicolas,

Please see if this code works:

(not (hasvalue({?Statut})) OR {OINV.DocStatus}={?Statut})
AND
(not (hasvalue({?Date})) OR {OINV.DocDate}={?Date})
AND
(not (hasvalue({?@Select "CardName" from OCRD where "CardType"='C' order by "CardName"})) OR {OCRD.CardName}={?@Select "CardName" from OCRD where "CardType"='C' order by "CardName"})
AND
(not HasValue({?@SELECT distinct T0."UserSign", T1."U_NAME" FROM OINV T0 INNER JOIN OUSR T1 ON T0."UserSign" = T1."USERID"}) OR {OINV.UserSign} = {?@SELECT distinct T0."UserSign", T1."U_NAME" FROM OINV T0 INNER JOIN OUSR T1 ON T0."UserSign" = T1."USERID"})
AND {OINV.CANCELED}='N'

Also, while in the formula editor, look for an option on the top that says 'Exceptions for Nulls'; change that to 'Default Values for Nulls'

-Abhilash

[Former Member · Accepted solution]

Hello Abhilash,

It seems to be good!!

Thanks a lot, only a question on my side, what it the influence of the option "Exceptions for Nulls and Default values for null".

I don't catch what makes your answer good for me....Formula or this option?

Thanks a lot!

[Former Member · Accepted solution]

Crystal Formula and select statements do not work with NULLS. The whole formula or that clause in select statement fails when it encounters a NULL

Setting to default gives the null a value in the case of numbers = 0 and text = ' '.

Ian

[Former Member]

Hi Ian,

Thanks for update, theses 2 fields allows my customer to have a "Name Description" rather than a code for UserSign. I don't think it is link to my problem because the problem can arrive on any other Dynamic Parameter, depending on their position.

But thanks a lot !

[Former Member]

Have you tried writing clause in same style as CardName filter? That is using If..then.. else

Your user filter is using an OR

Ian

[Former Member]

I'm not sure to catch what you mean....:) Could you detailled a little?

[Former Member]

You appear to have two fields in the last select

SELECT distinct T0."UserSign", T1."U_NAME"

Should it only have one

SELECT distinct T0."UserSign"

Ian