Prompt order from multiple queries

[Former Member]

Hi experts,

I am using BI4.1 with universe source.

There are prompts from multiple queries.

I want to order the prompts in alphabetical order however it does not work.

It orders all the prompts in the first query first, then prompts in second query, third query and so on.

I have assigned the number in the description so that the prompt alphabetical order. I have also use the User:<Order> in the @prompt function. Both not work. It only order the prompts within the same query, but cannot order the prompts cross query.

Please help.

Regards,

Gordon

[sateesh_kumar1 · Accepted solution]

Hi Gordon,

It's better to have common prompts in the queries are used to create report(wrt to performance)

If we have some prompts that are not common in queries , arranging queries as per need may be good idea.

[Former Member · Accepted solution]

That means if we do not have common prompts in the queries, we cannot order the prompts across all queries in alphabetical order, right?

[Former Member · Accepted solution]

Thanks.

I add the prompts of other queries to the first query with the joins @prompt = @prompt.

Now it orders as expected.

[sateesh_kumar1 · Accepted solution]

Hi Gordon,

Good to know you resolved very quickly.Since this is a very common and quite difficult requirement .can you please elaborate the solution so, it'll be very helpful.

Thanks.

[Former Member · Accepted solution]

I placed all prompt of other queries in the first query with a certain true condition.

For example, to achieve the order below:

Query 1: 1st prompt, 4th prompt, 7th prompt

Query 2: 2nd prompt, 5th prompt

Query 3: 3rd prompt, 6th prompt.

We need to add the prompts to the first query such as:

AND (1=1 OR @Prompt('02.2nd prompt', 'A', {'Y', 'N'}, mono, free,,'N',User:2) IN ('N'))

AND (1=1 OR @Prompt('05.5th prompt', 'A', {'Y', 'N'}, mono, free,,'N',User:5) IN ('N'))

AND (1=1 OR @Prompt('03.3rd prompt', 'A', {'Y', 'N'}, mono, free,,'N',User:3) IN ('N'))

AND (1=1 OR @Prompt('06.6th prompt', 'A', {'Y', 'N'}, mono, free,,'N',User:6) IN ('N'))

As you said, the common prompt can show in alphabetical order. Then we can get the prompt in correct order.

[saurabh_sonawane]

Hi

plz go through the below Thread

[Former Member]

Hi

This is not my case because he orders the prompt in a single query and he solves the problem with the alphabetical order of prompt description with all USER:0.

However it works only for the single query but not cross queries.

Regards,

Gordon

[Former Member]

Hi

Have you tried this.

@prompt("enter the month","a" ,multi/mono, free/constrained, USER.2)

then this prompt performs as first one .Like that you can define your own prompt

for 1 query if you have 3 prompts

@prompt("enter the month","a" ,multi/mono, free/constrained, USER.1)

@prompt("enter the quarter","a" ,multi/mono, free/constrained, USER.2)

@prompt("enter the DAte,"a" ,multi/mono, free/constrained, USER.3)

again @nd query

@prompt("enter the Customer","a" ,multi/mono, free/constrained, USER.4)

@prompt("enter the Product","a" ,multi/mono, free/constrained, USER.5)

[Former Member]

Else

The Prompts will appear in the alphabetical order.

To make them appear in the order of our requirement, need to prefix a numerical with the prompt

[Former Member]

Thanks for your suggestion, however i want to do something like that:

Query 1: 1st prompt, 4th prompt, 7th prompt

Query 2: 2nd prompt, 5th prompt

Query 3: 3rd prompt, 6th prompt.

How can I do that?