Skip to Content
0

Optionnal Prompts in Crystal Report

Apr 04, 2017 at 10:21 AM

44

avatar image

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)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Abhilash Kumar
Apr 05, 2017 at 07:46 AM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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!

0

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

0
Ian Waterman Apr 04, 2017 at 11:13 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Nicolas Juigne (FR) Apr 04, 2017 at 11:55 AM
0

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 !

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

0