Optional Prompt on WEBI Report level.

[former_member419024]

Is any possible optional prompt on WEBI report level with requirement of current year and current month as default value when prompt occur? The optional prompt in WEBI seem like only available on fixed value. Also trying on universe side but getting error when changing getdate() to year(getdate()). Any idea?

A.BATCACTYR = (Case When @Prompt('Enter This Year','C','Year Others',mono,constrained,,{'999'})='999' Then getdate() else @Prompt('Enter This Year','C','Year Others',mono,constrained,,{'999'}) END).

[former_member201488 · Accepted solution]

Here's an idea for you:

It's a mechanism for using default values alongside optional prompts.

HTH

NMG

[former_member419024 · Accepted solution]

Will try it now before off work. Thank you!

[former_member419024 · Accepted solution]

  Trying to try this on universe level with derived table as below:

where

(A.BATCTRCDE='T413' or (A.BATCTRCDE='T405' and E.REPTYPE in (select REPLCODE from CDE_T_T3586)))and A.SACSCODE in ('FG', 'CO')  

and

(

    A.BATCACTYR  =  @Prompt('Enter This Year','N','Year Others',Mono,Constrained,Persistent,,User:-1,Optional)

    AND

    (

     A.BATCACTYR  =  year(getdate())-1

     OR

     A.BATCACTYR  =  @Prompt('Enter This Year','N','Year Others',Mono,Constrained,Persistent,,User:-1,Optional)

    )

   )

But I get error as attach above.

[former_member201488 · Accepted solution]

I don't think I can help, I've only ever used the mechanism via WEBI, and I'm not sure optional prompts at Universe level are applicable to 3.x?

NMG

[former_member419024 · Accepted solution]

Thank for reply.

[former_member189638 · Accepted solution]

Does this work on the database end? May I know which database are you using?

[former_member419024 · Accepted solution]

Yup it work, I am using MS SQL Server. I created filter based on the method above in IDT with filter in Filter Type:Business. After that only pull the filter into query filter on WEBI side, doing this way is because I have many query filter to do on WEBI so it convenience me.