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