Dynamic Prompt in Dashboard?

[Former Member]

Hello,

I've opened a post here, and referring to that post, I know that we can have dynamic prompt in JOIN/CROSSTAB (I know we usually put the prompt in WHERE statement) in IDT, but when I create dashboard by using Query Browser, I can't get the prompt. The prompt only exist in the query itself (q1.png). How can I have the prompt out of query window, I mean how can I trigger the use of Query Prompt Selector (q3.png) (if no object is not under the query filter panel as shown in q2i.png)?

I hope that someone can understand my explanation... Sorry for the poor explanation. I don't know how to explain well.

Thank you.

Regards,

amano

[Former Member · Accepted solution]

Hi Amano

Give the prompt as optional prompt in query.

Now put a query prompt selector in dashboard whose source prompt is our prompt in query and destination prompt also our prompt in query. The drop down in query prompt selector's properties will catch the prompts in all queries used. You have to select from that.

Regards

Sandeep

[Former Member · Accepted solution]

Hi Sandeep,

I don't know how to make it as optional prompt, since I used derived table in IDT...

Eg. My query as following:

SELECT lecName, [F] AS Female, [M] AS Male

FROM

(SELECT l.lecName, s.gender, s.studName

FROM "tblLecturer" l LEFT JOIN "tblStudent" s ON l.lecID = @Prompt('LecID:', 'A',,,,)) p

PIVOT

(COUNT(studName)

FOR gender IN ([F], [M])

) AS  PVT

How do I make a prompt in Dashboard?

Thanks.

Regards,

amano

[Former Member · Accepted solution]

Please see the snapshot from dashboard query builder...

Check the optional prompt box.

Sandeep

[Former Member · Accepted solution]

Hi Sandeep,

Based on my query above, it is impossible to do like you. I know how to create prompt, but based on my query, I do not know how to do so.

SELECT lecName, [F] AS Female, [M] AS Male

FROM

(SELECT l.lecName, s.gender, s.studName

FROM "tblLecturer" l LEFT JOIN "tblStudent" s ON l.lecID = @Prompt('LecID:', 'A',,,,)) p

PIVOT

(COUNT(studName)

FOR gender IN ([F], [M])

) AS  PVT

I need l.lecID as my prompt, but in my query browser (q1.png), there is no such thing because my prompt has been wrapped. However, when I refresh data, that prompt will display on screen (q2.png) and my prompt will not under Filter section as what you showed...

Thank you.

Regards,

amano

[former_member190855 · Accepted solution]

Hi Aamano

Why don't you create a pre-define filter with prompt instead of adding the filter to the derived table?

Then you can add the filter to your query panel filter.

Runali

[Former Member · Accepted solution]

Hi Runali,

If pre-defined, it means that user is not able to change the filter value by themselves... I want to let user to filter the value based on drop down etc.

Thanks.

Regards,

amano

[former_member190855 · Accepted solution]

You can use the @prompt function in condition where clause

http://help.sap.com/businessobject/product_guides/boexir31SP3/en/xi31_sp3_designer_en.pdf

Check page 584 and around in the above manual.

It will prompt user to enter value

Runali

[Former Member · Accepted solution]

Hi Runali,

Nope, I don't want my prompt in WHERE statement... If it is under WHERE statement, it will be easier, but my query is not in WHERE statement.

SELECT lecName, [F] AS Female, [M] AS Male

FROM

(SELECT l.lecName, s.gender, s.studName

FROM "tblLecturer" l LEFT JOIN "tblStudent" s ON l.lecID = @Prompt('LecID:', 'A',,,,)) p

PIVOT

(COUNT(studName)

FOR gender IN ([F], [M])

) AS  PVT

Thanks.

Regards,

amano

[former_member190855 · Accepted solution]

Hi Amano

you can create the same table above without using the promot there.. by joining the LecId of both tables you are using. Then use the condition object with @prompt function filter by LecId

Runali

[Former Member · Accepted solution]

Hi Runali,

Sorry, I don't get you...

Are you asking me to create another view that will get the same result like above?

Regards,

amano

[former_member190855 · Accepted solution]

Hi Amano

I am saying - create the view as

SELECT l.LecId, lecName, [F] AS Female, [M] AS Male

FROM

(SELECT l.lecName, s.gender, s.studName

FROM "tblLecturer" l LEFT JOIN "tblStudent" s ON l.lecID =s.LecID) p

PIVOT

(COUNT(studName)

FOR gender IN ([F], [M])

) AS  PVT

or whatever is the column name in tblStudent to which I.lecId should join to. Select the lecture.LedId in select statement (optional).

The create a condition object with LecId= @Prompt fn following the syntax in the pdf I posted earlier.

So your view is created without the filter but you can now add this condition obj in your WebI/dashboard query so that it prompts before user. When user enters a value it filters the query

Runali

[Former Member · Accepted solution]

Hi Runali,

Thanks. It works. Just in case, the correction will be as following:-

SELECT lecID, lecName, [F] AS Female, [M] AS Male

FROM

(SELECT l.lecID, l.lecName, s.gender, s.studName

FROM "tblLecturer" l LEFT JOIN "tblStudent" s ON l.lecID = s.lecID) p

PIVOT

(COUNT(studName)

FOR gender IN ([F], [M])

) AS  PVT

WHERE lecID = 'L05'

Thank you so much.

Regards,

amano

[Former Member]

HI Amano,

Prompt in query will not appear in prompts tab of query browser in dashboard.

If you are using prompt at filter level then only it will display the prompt in prompts tab .. to map it further in excel cells.

Hence make sure that you don't have any prompt in query....

Hope this is helpful...

Regards

Subbarao M

[Former Member]

Hi Subbarao M,

This is sad... *sighs*

Does this mean that the query following cannot be done by having query prompt and I can only change the query in order the get the desired results?

SELECT lecName, [F] AS Female, [M] AS Male

FROM

(SELECT l.lecName, s.gender, s.studName

FROM "tblLecturer" l LEFT JOIN "tblStudent" s ON l.lecID = @Prompt('LecID:', 'A',,,,)) p

PIVOT

(COUNT(studName)

FOR gender IN ([F], [M])

) AS  PVT

Regards,

amano

[former_member190855]

Hi Amano

In the IDT - have you set the Prompt to directly apply to the class/object its part of? If so, it applies the prompt to the query but doesn't appear in the Query panel filter area.

Can you please post the definition of your prompt from IDT?

Runali

[Former Member]

Hi Runali,

Let's said, my query as following:

SELECT lecName, [F] AS Female, [M] AS Male

FROM

(SELECT l.lecName, s.gender, s.studName

FROM "tblLecturer" l LEFT JOIN "tblStudent" s ON l.lecID = @Prompt('LecID:', 'A',,,,)) p

PIVOT

(COUNT(studName)

FOR gender IN ([F], [M])

) AS  PVT

This is a derived table I've created in IDT and I put a prompt, as you can see there. This is what I mean the prompt from IDT. How to allow query prompt selector to be triggered from my query above? Is that possible?

Thanks.

Regards,

amano

[Former Member]

Hi Amano,

Give it a try ......

1)drag and drop  the lecID object in the filter pane and put the prompt

2) go to sql tab and see the generated sql..  now you have to change the query by deleting the "@prompt" in sql generated ...(note:don't change where condition of the query generated).

let me know the result

Regards

Subbarao M

[Former Member]

Hi Subbarao,

Are you saying it to be done in dashboard?

I'm sorry, but in my query, there is no lecID displayed... lecID is not under SELECT statement. Please refer to my screenshot attached.

Thanks.

Regards,

amano