Skip to Content
avatar image
Former Member

Optionnal Prompts in Crystal Report

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)

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Apr 05, 2017 at 07:46 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Apr 04, 2017 at 11:13 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 04, 2017 at 11:55 AM

    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 !

    Add comment
    10|10000 characters needed characters exceeded