Extended @Prompt Syntax: Default Value, Purge Values

Posted on by 139 Comments

During my report development efforts the other day I knew that I needed a business objects universe-level prompt, but I also knew that it was mandatory to “purge” the prompt values. Like a good little Business Objects developer I always purge all of the data from my reports and reports’ prompts before I publish them for User Testing and promotion to production; however, in Business Objects XI R2 I have found that WebI reports have a strangle-hold on prompt values and they don’t want to give them up without a fight.

So I started looking around to see if I was just missing something obvious. What I found on my search was some completely unsupported extended @prompt syntax. The funny thing was that I did not have to install anything to enable it. The syntax worked immediately, just by passing in the additional parameters. Note: this is unsupported officially by BO, but BusinessObjects obviously developed it for some larger client and slipped it into the XI R2 code. Therefore, there is no promise that this will be supported in the future, but there is hope.

Anyway, on to the syntax:
@Prompt('1) Enter Product Family','A','Product Hierarchy\Product Family',multi,free,[Not_Persistent],[<{default_value(s)_or_leave_blank}>],[User:#])

With this syntax you can now add 3 additional/optional parameters after you universe-level prompt’s “free/constrained” option.

  1. Prompt Persistence: Default value is “Persistent”; however, the value “Not_Persistent” is the reason most Business Objects WebI report writers want to use this syntax.  Using “Not_Persistent” removes the last prompt used with the report; it purges the value.  This can be very important in many settings because it can be the difference between an end-user accidentally running the report for some unwanted parameter value.
  2. Default Value(s): If left blank no default value used.  If a value or values is desired then the set should be surrounded by { } characters and separated by commas (much like hard-coded LOV values earlier int he @Prompt parameters).  This feature might seem to counter the “Not_Persistent” setting; however, it may be possible that you always want your Web Intelligence report prompt to include the default “*” value even while you are testing or running it for other values.  Therefore, some might find that using this option with the “Not_Persistent” option provides value and extended functionality to their WebI reports.
  3. User:#: This particular value has been ignored by many, but it also provides some interesting value.  It allows the universe business objects developer to define the relative order of the prompt.  Yes, no more not-so-tricky “1.”, “1)”, or “1-” prefixes.  In fact, I believe this option provides better value because it defines order of universe-level prompts and it two prompts have the same “User:#” value then they are sorted alphabetically.

The following are some examples of potentially usage of this extended @Prompt Syntax:

@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,{'Kiwi','Guava'},User:0)
@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',MULTI,FREE,Not_Persistent,{'*'},User:9)
@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,,User:1

I have found this syntax to be invaluable in my BusinessObjects Designer and Web Intelligence report development efforts.  As good little programmers we know that universe-level prompts are a “best practice”; however, without this syntax they can retain last-run prompt values and cause confusion and even misleading and blatantly wrong business intelligence.  This syntax finally brings universe-level prompts into the 21 century and makes our jobs all a little easier and maybe even more secure.  😉

Tip: Including “All” or “*” In Your LOV

Try hard coding “All” or “*” in the object’s LOV, in the universe. You could do this by editing the object’s LOV and adding a query with UNION that will only return the word “All”. You may have to make sure that the LOV for the object is returning a character data type for this to work properly.

Enjoyed this post? Share it!

 

139 thoughts on “Extended @Prompt Syntax: Default Value, Purge Values”

  1. Hi Julian,

    I have a prompt which has blank for the default value just as in your 3d example: @Prompt(‘Enter Product Family’,’A’,’Product Hierarchy\Product Family’,multi,free,Not_Persistent,,User:1)
    but this cannot pass parsing showing me the error: “error parsing default values parameter (7th parameter)”. I’ve tried to leave it blank, to put {} only, but it still gives me the same error. When I write {”} it still gives an error but if I put space: {‘ ‘} it passes. The problem is that in this case the space becomes e default value but I need a mandatory prompt without any default value, with User paremeter specified. If I dont’t put User it works fine but I need to tell the prompt the order that I need but for fill in the User parameter I must specify a default. Is there any workaround? Thanks you in advance.

  2. Hi Julian,

    I have the following under the WHERE group in my Infoview SQL query:

    fn_adjusted_date(MQG_PBO_CALLTIME.CREATE_DATE) BETWEEN @prompt(‘Enter Reporting Start Date:’,’D’,’MQG_PBO Call Time\MQG_Create Date’,Mono,Free,Not_Persistent,{’11/12/2011 12:00:00 AM’},User:1) AND @prompt(‘Enter Reporting End Date:’,’D’,’MQG_PBO Call Time\MQG_Create Date’,Mono,Free,Not_Persistent,{’11/14/2011 12:00:00 AM’},User:2)

    However, I wanted the default values to be equal to sysdate or some date relative to the sysdate. I tried replacing {’11/12/2011 12:00:00 AM’} with {TO_CHAR(TRUNC(sysdate),’MM-DD-YY HH:MI:SS AM’))}, but to no avail. What can I place there instead?

    I scanned the responses to see if my question was already asked. There were similar ones, but I tried asking still in case I misunderstood their questions.

  6. Hi Prasanthi, for me, “quick prompt” refers to the prompt that one can quickly create from WebI. There you can quickly create a prompt, but you cannot edit some of the settings of the prompt. An “optional prompt” is either (1) a prompt that is created in Designer and configured to allow the user to input a special character or characters that logically cause the prompt to by bypasses or (2) in XI 3.0 or later an optional prompt can be created by checking a box in WebI that tells the WebI engine that the user is not required to enter any value for the specified prompt.

  7. I’m working in Deski 12.3 and am having major problems with date prompts. Although they seem to be working fine in the query conditions (once we’d solved the locale issue), I still can’t do anything with them at the report level – any formula using the results comes up as #ERROR.

    The date is being input as 31/03/2011, USERRESPONSE displays that value, and when you use ISDATE and ISSTRING on the output of the USERRESPONSE both compute to TRUE. Formatting the column produces exactly the same no matter the what date format you choose. Using the USERRESPONSE in TODATE and FORMATDATE both singly and together produces #ERROR. Other formulae (like an age calculation) produce #SYNTAX.

    Could this be down to the fact that the date prompt is only asking for a date not a date and time?

  8. Hi Maddz, to the best of my knowledge a Prompt set to the datatype of “D” should be able to handle date and/or date-time inputs. Could you please share your code that is producing the issue?

  9. Hi Julian,

    I don’t think there is a datatype involved because it’s a standard prompt output in the Deski query builder. For what it’s worth, here’s the SQL output for the query prompts:

    AND SERVICE_PROVISION.ACTUAL_START_DATE = @variable(‘Enter FY start:’))

    and further on:

    AND SERVICE_PROVISION.ACTUAL_START_DATE = @variable(‘Enter subsequent FY start:’))
    )

    These are the 4 date prompts which I’m using in the query and in variables in the report (e.g. age at [first] FY end). Is it the @variable bit that’s the problem?

    You have to remember we are using Deski for our reporting as Webi is currently too limited in functionality (no grouping of variables for instance – I know you can do it with IF, but there’s no way I am manually typing in 750+ service descriptions to group), and also I don’t do SQL.

  10. Hi Maddz, I see that you are using the “@Variable” function and not the “@Prompt” function. I do not recommend using “@Variable” for promtps without a corresponding “@Prompt”, such as:


    --use this first:
    @Prompt('Enter FY start:','D','',mono,free)

    --then later you can use this to refer to the same prompt value submitted by the user:
    @Variable('Enter FY start:')

    Without explicitly declaring your prompt first I don’t think you can reasonably expect anything less than trouble.

  11. And this works in Deski? I’m guessing this will mean editing the SQL script generated by Deski which is a route I’ve never used.

    I wonder if this is the reason our universe-level date prompts aren’t working in Deski apart from the ones that return a ‘fixed’ date (like current FY end) as opposed to ones which request the user to either select or input a date.

  12. I’ve tried it and it doesn’t work. Overtyping the prompts in the SQL script and running it reverts the SQL back to the @variable, and telling the report not to generate SQL before running just drops you back into the query screen.

  13. Hi Julian, do you know if there would be a more advanced guide on how I can use business objects infoview? I can’t find any clear/detailed documentation on how some stuff should work (stuff like table breaks – and alerters & sorting applied on table breaks, variable qualification functionalities – measure variable as filters & detail variables in table breaks, merged variables – counting across data sets and counting in one data set, etc.). I would appreciate any suggestion you can give. Thanks

  14. Hi

    Is there a way of adding a Universe/Report prompt so that it updates all queries in one go?

    I have a report with multiple queries for customer data. I want the user to specify the customer number once with a prompt rather than for each query.

    Is this possible in BOXI 3.1?

    Cheers

  15. Hi Rob, only if the reports are all already using a universe prompt condition can you update the prompt condition in the universe and have it flow Down to all of the reports that use the prompt condition.

  17. Hi all,

    I tried this. And it seems to work only for free prompts. Once I have a constrained prompt, the values are always kept in memory.

    Did some one tried this and got a better result.

    Thanks in advance.

    Rodrigue.

  20. Hi Julian,

    I can see that in business objects infoview, ALL similar fields in different queries automatically merges once there is one merged field. Would you know how I can disable this automerging?

  21. Found an option under document properties > Data Synch Options. I unchecked “Auto-merge dimensions”. Hopefully it works.

  23. No problem, Julian. I just thought of asking an expert first before wasting time trying to dig into the tool (especially when a solution can never be achieved).

    I have heard of some tool called BobJ. I am currently more acquainted with Infoview and I was wondering if I should invest time in trying to study better reporting tools than this. Would you know what’s the diffence between BobJ and Infoview? Or would there be other better reporting tools?

  24. Hi Julian

    Is there a nifty way of defaulting the prompt to the maximum value of a LOV? Or do you have to go via .NET or VB?

  25. Hi Michael, Unfortunately, the regular default value syntax does not support any dynamic values. .NET or VB are your best bets, but it won’t be easy.

  26. Hi Julian,

    Would it be possible to merge two different Infoview reports (Query + Actual Report). Currently, I am not able to find any option in Business Objects Infoview.

  27. Hi Dado, data merging is limited only to the data in the data cube (from the queries) in Business Objects. Merging an aggregated or filtered report level “table” would not be possible explicitly, but since the underlying data is merge-able it my have the result you are after.

  29. Hi,

    There are scheduled reports in excel format.
    Is there a way to check whether reports ran sucessfully and the reports conatin data without admin rights?

  31. Hi Deepak, yes, the rights to administer schedules are customizable on the folder. The settings are there. In BO XI 3.1 look under the “General” and “General” settings.

  32. i need to save prompts for Business object report release 4.0 by using vb.net code, please help me.
    note: the integration between business object and asp.net alredy achived in my project.

  33. Hi Julian,

    I have a prompt which has blank for the default value just as in your 3d example: @Prompt(‘Enter Product Family’,’A’,’Product Hierarchy\Product Family’,multi,free,Not_Persistent,,User:1)
    but this cannot pass parsing showing me the error: “error parsing default values parameter (7th parameter)”. I’ve tried to leave it blank, to put {} only, but it still gives me the same error. When I write {”} it still gives an error but if I put space: {‘ ‘} it passes. The problem is that in this case the space becomes e default value but I need a mandatory prompt without any default value, with User paremeter specified. If I dont’t put User it works fine but I need to tell the prompt the order that I need but for fill in the User parameter I must specify a default. Is there any workaround? Thanks you in advance.

  34. Hi Jaya, I understand your problem. The only workaround I can think of is to build login into your WHERE clause so that a single space, ” “, is equivalent to everything. Such as:

    Table.prod_family_name IN @Prompt(‘Enter Product Family’,’A’,’Product Hierarchy\Product Family’,multi,free,Not_Persistent,,User:1)
    OR
    " " IN @Prompt(‘Enter Product Family’,’A’,’Product Hierarchy\Product Family’,multi,free,Not_Persistent,,User:1)

    Now, this may need some adjusting to meet your needs, for example what if spaces are used for something else. So maybe you would use a question mark ( ? ) instead.

  35. Hi,

    This is the code for a Derived table i created in BO Universe.

    This gives the no of days between days but it has to deduct the Thursdays and Fridays which it is not doing so.

    Am i missing something? Please Help.

    SELECT COUNT ( * ) as “TargetDays”
    FROM (SELECT ROWNUM rnum
    FROM all_objects
    WHERE ROWNUM <= to_date(
    @Prompt('2-Enter End Date:','D',,Mono,Free,Persistent)) – to_date(@Prompt('1-Enter Start Date:','D',,Mono,Free,Persistent)) + 1)
    WHERE TO_CHAR (TO_DATE (@Prompt('1-Enter Start Date:','D',,Mono,Free,Persistent),'DD/MM/YYYY') + rnum, 'DY') NOT IN
    ('THU', 'FRI')
    AND NOT EXISTS
    (SELECT NULL
    FROM syounus.attendance_holidays
    WHERE holiday_start_date =
    TRUNC (TO_DATE (@Prompt('1-Enter Start Date:','D',,Mono,Free,Persistent),'DD/MM/YYYY' ) + rnum))

  36. Hi Saleem, maybe its my present illness, but I can’t see why you are doing this: “TO_CHAR (TO_DATE (@Prompt('1-Enter Start Date:','D',,Mono,Free,Persistent),'DD/MM/YYYY') + rnum“. Why are you adding rnum here? If I were you I would debug this by forcing in some hard-coded dates and getting your SQL straight before you put it BO.

  37. Greetings Julian,

    This has been an interesting read. I had no clue of the User:# parameter.

    I do however have a problem which you may know of:

    I have a date range which drives my report to fetch data from the table of concern. So in my webi report, I have formulated the condition (in the where part) as –

    usage_date between LOWDT and HIGHDT. LOWDT and HIGHDT are universe objects coded as,
    LOWDT
    ——-
    CASE
    WHEN @Select(Date Range\Date Range)=’Last 30 days’ THEN trunc(sysdate)-30
    WHEN @Select(Date Range\Date Range)=’Last 60 days’ THEN trunc(sysdate)-60
    WHEN @Select(Date Range\Date Range)=’Last 12 Months’ THEN add_months(trunc(sysdate),-12)
    WHEN @Select(Date Range\Date Range)=’Previous Month’ THEN add_months(trunc(sysdate,’mm’),-1)
    WHEN @Select(Date Range\Date Range)=’This Month’ THEN trunc(sysdate,’mm’)
    WHEN @Select(Date Range\Date Range)=’Year to Date’ THEN trunc(to_date(’01/01/’||to_char(trunc(sysdate),’YYYY’),’mm/dd/yyyy’))
    WHEN @Select(Date Range\Date Range)=’Previous Year’ THEN trunc(to_date(’01/01/’||(to_char(trunc(sysdate),’YYYY’)-1),’mm/dd/yyyy’))
    WHEN @Select(Date Range\Date Range)=’Specific dates’ THEN trunc(to_date(@Prompt(‘Enter a Start Date:’,’D’,,Mono,Free,not_persistent,{‘1/1/2015′}),’dd-mm-yyyy hh24:mi:ss’))
    END

    HIGHDT
    ——-
    CASE
    WHEN @Select(Date Range\Date Range)=’Last 30 days’ THEN trunc(sysdate-1)
    WHEN @Select(Date Range\Date Range)=’Last 60 days’ THEN trunc(sysdate-1)
    WHEN @Select(Date Range\Date Range)=’Last 12 Months’ THEN trunc(sysdate-1)
    WHEN @Select(Date Range\Date Range)=’Previous Month’ THEN trunc(trunc(sysdate,’mm’)-1)
    WHEN @Select(Date Range\Date Range)=’This Month’ THEN (CASE WHEN trunc(sysdate)=trunc(sysdate,’mm’) THEN trunc(sysdate) ELSE trunc(sysdate-1) END)
    WHEN @Select(Date Range\Date Range)=’Year to Date’ THEN trunc(sysdate-1)
    WHEN @Select(Date Range\Date Range)=’Previous Year’ THEN trunc(to_date(’12/31/’||(to_char(trunc(sysdate),’YYYY’)-1),’mm/dd/yyyy’))
    WHEN @Select(Date Range\Date Range)=’Specific dates’ THEN trunc(to_date(@Prompt(‘Enter an End Date:’,’D’,,Mono,Free,not_persistent,{‘1/1/2015′}),’dd-mm-yyyy hh24:mi:ss’))
    END

    This was written with the objective that only when @Select(Date Range\Date Range) [which is a prompt object coded in the universe] is answered with “Specific dates”, should the prompts for entering a start date and end date appear. They should for all other choices be unaccessible. However, these 2 prompts are visible even before I can select a value for @Select(Date Range\Date Range) in my report. Is there another way I can achieve this objective

Leave a comment

Your email address will not be published. Required fields are marked *