Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jul 18, 2010 at 04:07 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 20, 2010 at 01:29 PM

    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)

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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!!