Question Re: Order Optional Universe Prompts in Technology Q&A

Order Optional Universe Prompts

omacoder — Fri, 30 Oct 2020 23:21:16 GMT

I find many questions on this, so I presume there is still no answer, unless someone has come up with a workaround and can help me solve this!!

We have 7 prompts, 3 of them being optional.

They're created as business filters and native filters.

I attempted to create them all as native filters so I could use the last property "User:" in the @Prompt function.

However, it seems optional prompts cannot be created natively, (Custom SQL cannot contain optional prompts. (IES 10812)), and therefore there seems to be no way to specify the sort value of these optional prompts along with the required prompts.

If I create the prompt using Business filter type, I see in Webi that it is automatically assigning User:-1 to the sort order, and it does not appear that I can change this anywhere unless I use custom SQL in all of my queries?

Re: Order Optional Universe Prompts

ayman_salem — Sat, 31 Oct 2020 15:49:04 GMT

To properly understand your requirement.

Would you like to create a native filter as an optional filter?

or you want to sort a Native Filter?

Re: Order Optional Universe Prompts

omacoder — Sat, 31 Oct 2020 18:38:11 GMT

Hi Ayman, I would like to create a filter with an optional prompt, and set the sort order on that prompt.

Re: Order Optional Universe Prompts

ayman_salem — Sat, 31 Oct 2020 20:06:49 GMT

Hi Brian,

Just save the native filter as you wrote "With User: 5 and Optional" (no validation) and it will works.

Re: Order Optional Universe Prompts

ayman_salem — Sat, 31 Oct 2020 23:02:55 GMT

Hi Brian, just save the native filter as you wrote "With User: 5 and Optional" (no validation) and it will works.

here my test:

Re: Order Optional Universe Prompts

omacoder — Sun, 01 Nov 2020 21:23:28 GMT

Hello, the screen shots are not coming through on my end? Can you confirm they uploaded to the post correctly?

Thank you!

Re: Order Optional Universe Prompts

ayman_salem — Sun, 01 Nov 2020 21:34:01 GMT

Hi Brian, just save the native filter as you wrote "With User: 5 and Optional" (no validation) and it will works.

here my test:

...

Re: Order Optional Universe Prompts

omacoder — Sun, 01 Nov 2020 21:39:47 GMT

Thank you for re-posting with the screen shots.

Here is my native script that is displaying the error:

Database.DisplayName IN @Prompt('Database','A','lstDatabases',Multi,Free,Persistent,,User:0,Optional)

Maybe I can't use it with a list of values that I have defined at the business layer level?

FYI we're on 4.2 SP08 Patch 500

Re: Order Optional Universe Prompts

ayman_salem — Sun, 01 Nov 2020 21:49:13 GMT

Validate it without Optional. If it's okay, add it again and save it.

Re: Order Optional Universe Prompts

omacoder — Mon, 02 Nov 2020 15:54:07 GMT

That does work. Must be a defect, but now I cannot get my universe to validate successfully.

Thank you!

Re: Order Optional Universe Prompts

omacoder — Mon, 02 Nov 2020 22:05:29 GMT

I had to unaccept this answer. I rolled this out with all of the 3 optional prompts, and now when I run the report, I get:

An internal error occurred while calling 'processDPCommandsEx' API. (Error: ERR_WIS_30270) (Error: INF WIS 30270)

It's this sql for a filter that I am using. As soon as I add ",Optional" as the past parameter, refresh the report with a blank value, it then produces the above error.

HR.FactPayCheckWages.PayWeekEndDate BETWEEN @Prompt('FTEE Alternative Seasonal Lookback Period Date Start','D',,Mono,Free,Persistent,,User:60) AND @Prompt('FTEE Alternative Seasonal Lookback Period Date End','D',,Mono,Free,Persistent,,User:70)

Re: Order Optional Universe Prompts

ayman_salem — Tue, 03 Nov 2020 19:29:42 GMT

I think the reason for the error is something else.

I test your prompt in the BI launchpad and it works

P_Date: T1.BEGINDATE BETWEEN @Prompt('FTEE Alternative Seasonal Lookback Period Date Start','D',,Mono,,Persistent,,User:60,Optional) AND @Prompt('FTEE Alternative Seasonal Lookback Period Date End','D',,Mono,,Not_Persistent,,User:70,Optional)

...

FYI: I test it with 4.2 SP8-P600 with Oracle DB

but I don't think it was a bug in P500

Re: Order Optional Universe Prompts

ayman_salem — Tue, 03 Nov 2020 19:33:17 GMT

Prompt with values and the Result Report