How to code a dynamic multi select prompt in a universe derived table?

[Former Member]

Has anyone coded a dynamic multi-select prompt within the SQL of a derived table in the universe?

The values of the prompt should be populated from a database table making it a dynamic prompt u2013 not static as in hardcoding the values.

I have coded a multi-select prompt outside the derived table in a filter. This does work and displays properly in a multi-select drop down with code and description. However, in my case, the result of this is bad performance u2013 execution time is 6 minutes when it should be 3 seconds.

Bottom line here is I need a dynamic multi select prompt inside the SQL of the derived table. Is this possible?

I canu2019t find much from google or SAP forums on this specific question.

Google resources Iu2019ve checked out

http://biguru.wordpress.com/2009/07/19/businessobjects-universe-design-best-practices/

http://forumtopics.org/busobj/viewtopic.php?p=675811&sid=bf3dc50d5e60c61ef51d1a2bccc27e22

Thanks!

Keith

[Former Member · Accepted solution]

I don't see why it would not be possible. You would have to create extra objects in your universe to provide the source for the LOV, but it works okay.

If you can provide some more details about what you have tried and the syntax you're looking for, it would help me understand the specifics of your question.

[Former Member]

Dave, here is the SQL I have in a derived table. It's very simple. You say "to create extra objects in your universe to provide the source for the LOV". But how do reference this object within the derived table (DT)? I don't see how. When you're editing the SQL in the DT, all you get is an editor - there are no options to reference anything. In the bold @prompt at the bottom, I want to get the 3 values from a table and not hard code them. Thanks.

SELECT

A.EDI_PERSON_ID,

D.PED_LAST_NAME,

A.VRA_LOC_CD,

A.VRA_CAMPUS_CD,

A.RAS_BEGIN_DATE,

A.RAS_END_DATE

,CASE

WHEN @Prompt('Enter Begin Date','D',,mono,free) > '1900-01-01'

THEN@Prompt('Enter Begin Date','D',,mono,free)

END AS PARM_BEGIN_DT

,CASE

WHEN @Prompt('Enter End Date','D',,mono,free) > '1900-01-01'

THEN@Prompt('Enter End Date','D',,mono,free)

END AS PARM_END_DT

FROM EDURPT.EDUTB_RES_ASSIGN A,

EDURPT.EDUVW_PERSON D

WHERE

D.EDI_PERSON_ID=A.EDI_PERSON_ID

AND A.RAS_BEGIN_DATE <= @Prompt('Enter End Date','D',,mono,free)

AND A.RAS_END_DATE >= @Prompt('Enter Begin Date','D',,mono,free)

AND

A.VRA_LOC_CD =

@Prompt('Choose a Loc-CD','A',{'ROCH','SCOT','JACK'},Mono, free)

[Former Member]

This values option in your @Prompt() syntax is where the reference to the other object would go. Let's suppose I create a class in my universe called LOV and an object called Values. If I look at this object, it points to a column in a database that contains the values that I want to source for my LOV in my derived table. You would change your prompt from this:

@Prompt('Choose a Loc-CD','A',{'ROCH','SCOT','JACK'},Mono, free)

to this:

@Prompt('Choose a Loc-CD','A','LOV\Values',Mono, free)

[Former Member]

Wow!! that was it! it worked! I did what you suggessted and it worked. Here is the new prompt.

A.VRA_LOC_CD =

@Prompt('Choose a Loc-CD','A','Edurpt Edutb Pvt Definitn Campus Lov\Pvd Designer Val (Campus Code)',Mono, free)

Thanks Dave!

[Former Member]

Dave, the Multi select prompt is working. I want to display in the prompt a code and description but only pass the code to the SQL? In my LOV object, I can concatenate CDDESC which will display but that CDDESC is also passed into the SQL. It would be nice to SUBSTR and pull only the CD. That works with Mono but not Multi. Any ideas?

[Former Member]

So the answer is "yes" but not quite in the way you might expect.

You have created the object where you can "borrow" the LOV for your derived table prompt. What you need to do is this. First, you need to create another object in your universe (I put it in the same class as the "code" object) that contains your object description. Then do this:

1. Double-click on the code object

2. Select the properties tab

3. Click on the Edit button. This does not edit the object definition itself, it edits the LOV definition.

4. On the query panel, add the Description object created earlier. Make sure it is the second object in the query panel.

5. You can opt to sort either by code or description, whichever makes sense to your users

6. Click "OK" to save the query definition, or click "Run" if you want to populate the LOV with values.

7. Make sure you click "Export with Universe" on the properties tab once you have customized the LOV, else your computer is the only one that will include the new LOV definition

8. The Hierarchical Display box may also be checked; for this case you have a code + description which are not hierarchical, so clear that box

That's it. When you export your universe, the LOV will go with it. When someone asks for a list of values for the code, the list will show both codes and descriptions, but only the code will be selected.

You do not need to make any changes to your current derived table prompt once the LOV has been customized.

[Former Member]

It works! We're getting closer! it displays perfect in the universe and in WEBI but not in Crystal which is my reporting tool (boo!). I was able to add the description into the query panel of the code object. It displays as you would expect - code first, desc second. I checked "export with the univ", unchecked "hierarchical", saved, and exported. This may be another one of those features that doesn't work in Crystal but WEBI it does. It works in WEBI just as you said which is very encouraging! I'll keep testing with Crystal. Thanks again Dave!!