BusinessObjects Board

Optional prompts / conditions

sdk development
#1

I am looking to provide my users 4 prompts of which they can provide a value for one or more. To my knowledge, BO/WebI does not provide a facility for optional prompts (if the user doesn’t provide a value). I see 2 alternatives, using the matches expression and putting a % for the fields not used or using the WebI Reporting SDK (Java) and adding only those conditions the user provides values for.

Due to performance reasons, I am leaning towards the latter. Has anyone had any experience using the Reporting SDK to add conditions and better yet, does anyone have any sample code they could share with me? I have tried to use the code from the HTML Query Panel (provided in WebI 2.7) but have not been able to follow it very well.

Any help/suggestions would be appreciated.

Regards-
Brian Bauer

bbauer (BOB member since 2002-07-16)

Running a report that has a prompt but leaving prompt blank
#2

You can’t implement an optional prompt in full client, since it does’n enable the ‘RUN’ button till you fill out all the prompts… but it keeps the ‘Run Query’ button enabled in InfoView, so you can do it there .

You can build a full client report using ‘OR’ conditions. Publish it, and while running dont input the values for the optional ones. It works fine.

Hope this helps…

Anjan Roy (BOB member since 2002-07-10)

#3

I am not quite sure I follow what you are saying… won’t an exception be thrown in WebI SDK if I do not fill in all of the prompts with values? How will I be able to get around this?

Wouldn’t it be easier to dynamically add conditions to a report based on the values provided from the user?

bbauer (BOB member since 2002-07-16)

#4

What I meant was, use the full client report and view it using InfoView. You dont need SDK here…

Anjan Roy (BOB member since 2002-07-10)

#5

Brian, I’ve run into the same problem: the need to provide users with optional prompts. I’ve developed a workaround that works fairly well for us, though it does have its limitations.

Let’s say your query has two conditions, both of with are optional. Define them generally as:

Condition 1: Table1.Column1 = xxxx
Condition 2: Table2.Column2 = yyyy

Either one or both can be filled in, however, you want to ignore them if the user chooses to do so. BusOb does not provide this option, so you’re always left with these two conditions in the WHERE clause of your query. Practically speaking, though, you can ignore either condition if you do a self join:

Table1.Column1 = Table1.Column1

This does not place any limit on your query; you’ll return the same dataset as if the join was not included.

We’re using Oracle, so I’ve built a DECODE (you could probably use a CASE statement in any other db) into the definition of the prompts. And I include a “dummy” value in the LOV–a blank line or text like “No Choice”. If your users are comfortable enough with BusOb, you can rely on them to type in a predetermined “dummy” value into a non-constrained prompt. Either way will work, but your users must enter a prompted value, “dummy” or otherwise, in order for them to get past BusOb’s prompt screen.

Then, define your conditions as:

Condition 1:
Table1.Column1 = decode(@prompt(…),‘No Choice’,Table1.Column1,@prompt(…))

Condition 2:
Table2.Column2 = decode(@prompt(…),‘No Choice’,Table2.Column2,@prompt(…))

If the user enters the “dummy” prompt value (in this case “No Choice”), then the decode resolves the condition to a self-join, which is, effectively, no join at all. If they choose an “Actual” value, then your conditions work as expected. And you can use any combination of “AND” and “OR” operators.

The self joins do cause some degradation in query efficiency, but it’s not noticeable in the instances in which I’ve used it. There may also be some situations in which the query results are affected unexpectedly, but I’ve not run into this either.

Give it a shot, it’s worked great for us. If you have any questions, let me know.

David DeLong (BOB member since 2002-08-16)

#6

here’s a doco i have that you might find useful - it allows you to add “*” for all in prompts (and if you publish the report with *'s in all fields, that will be the default value, and users will only have to change the ones they want)… let me know if you have questions after looking at the doco…

actually can’t figure out how to attach doco’s here so i’m going to cut and paste below:

@Prompt Definitions in Designer

  1. Here’s how to create a pre-defined prompt that uses a ‘*’ for all in a character field:

@Select(Settlement Details\Settlement Action Type) IN @Prompt(‘Settlement Action Type(s): (Use * to select all.)’ , ‘A’ ,‘Settlement Details\Settlement Action Type’ ,multi,free) OR ‘*’ IN @Prompt(‘Settlement Action Type(s): (Use * to select all.)’ , ‘A’ ,‘Settlement Details\Settlement Action Type’ ,multi,free)

  1. Here’s how to create a pre-defined prompt that uses a ‘*’ for all in a date field:

TRUNC(ICIS_ENF_CONCLUSION_SETTLE.SETTLEMENT_LODGED_DATE) =
TRUNC(TO_DATE(@Prompt(‘Settlement Lodged Date (MM/DD/YYYY): (Use * to select all.)’ , ‘A’ , , mono , free),‘MM/DD/YYYY’)) OR ‘*’ =
@Prompt(‘Settlement Lodged Date (MM/DD/YYYY): (Use * to select all.)’ ,
‘A’ , , mono , free)

  1. Here’s how to create a pre-defined prompt that uses a ‘*’ for all in a numeric field:

TO_CHAR(TIME_DIMENSION_SETTLE_LODGED.FISCAL_YEAR) IN @Prompt(‘Settlement Lodged Fiscal Year: (Use * to select all.)’,‘N’,‘Settlement
Details\Settlement Lodged Fiscal Year’ , multi , free) OR ‘*’ IN
@Prompt(‘Settlement Lodged Fiscal Year: (Use * to select all.)’,‘A’
,‘Time Dimension\Fiscal Year’ , multi , free)

  • You also need to create a separate object to be used for your LOV (If you are using one). Notice that I am referencing two different objects as the LOV. The second object ‘Time Dimension\Fiscal Year’ needs to have the following in the select statement:
    TO_CHAR(TIME_DIMENSION.FISCAL_YEAR,‘YYYY’)
  1. Here’s how to create a pre-defined prompt that uses a ‘*’ for all in a date range:

(’’= ANY @Prompt(‘Settlement Lodged Date From (MM/DD/YYYY): (Use * to select all.)’ , ‘A’ , , mono , free) OR
TRUNC(ICIS_ENF_CONCLUSION_SETTLE.SETTLEMENT_LODGED_DATE) >= TRUNC(TO_DATE(@Prompt(‘Settlement Lodged Date From (MM/DD/YYYY): (Use * to select all.)’ , ‘A’ , , mono , free),‘MM/DD/YYYY’))) AND ( '’ = ANY @Prompt(‘Settlement Lodged Date To (MM/DD/YYYY): (Use * to select all.)’ , ‘A’ , , mono , free) OR
TRUNC(ICIS_ENF_CONCLUSION_SETTLE.SETTLEMENT_LODGED_DATE) <= TRUNC(TO_DATE(@Prompt(‘Settlement Lodged Date To (MM/DD/YYYY): (Use * to select all.)’ , ‘A’ , , mono , free),‘MM/DD/YYYY’)) )

BOzilla (BOB member since 2002-08-29)

#7

here’s a doco i have that you might find useful - it allows you to add “*” for all in prompts (and if you publish the report with *'s in all fields, that will be the default value, and users will only have to change the ones they want)… let me know if you have questions after looking at the doco…

actually can’t figure out how to attach doco’s here so i’m going to cut and paste below:

@Prompt Definitions in Designer

  1. Here’s how to create a pre-defined prompt that uses a ‘*’ for all in a character field:

@Select(Settlement Details\Settlement Action Type) IN @Prompt(‘Settlement Action Type(s): (Use * to select all.)’ , ‘A’ ,‘Settlement Details\Settlement Action Type’ ,multi,free) OR ‘*’ IN @Prompt(‘Settlement Action Type(s): (Use * to select all.)’ , ‘A’ ,‘Settlement Details\Settlement Action Type’ ,multi,free)

  1. Here’s how to create a pre-defined prompt that uses a ‘*’ for all in a date field:

TRUNC(ICIS_ENF_CONCLUSION_SETTLE.SETTLEMENT_LODGED_DATE) =
TRUNC(TO_DATE(@Prompt(‘Settlement Lodged Date (MM/DD/YYYY): (Use * to select all.)’ , ‘A’ , , mono , free),‘MM/DD/YYYY’)) OR ‘*’ =
@Prompt(‘Settlement Lodged Date (MM/DD/YYYY): (Use * to select all.)’ ,
‘A’ , , mono , free)

  1. Here’s how to create a pre-defined prompt that uses a ‘*’ for all in a numeric field:

TO_CHAR(TIME_DIMENSION_SETTLE_LODGED.FISCAL_YEAR) IN @Prompt(‘Settlement Lodged Fiscal Year: (Use * to select all.)’,‘N’,‘Settlement
Details\Settlement Lodged Fiscal Year’ , multi , free) OR ‘*’ IN
@Prompt(‘Settlement Lodged Fiscal Year: (Use * to select all.)’,‘A’
,‘Time Dimension\Fiscal Year’ , multi , free)

  • You also need to create a separate object to be used for your LOV (If you are using one). Notice that I am referencing two different objects as the LOV. The second object ‘Time Dimension\Fiscal Year’ needs to have the following in the select statement:
    TO_CHAR(TIME_DIMENSION.FISCAL_YEAR,‘YYYY’)
  1. Here’s how to create a pre-defined prompt that uses a ‘*’ for all in a date range:

(’’= ANY @Prompt(‘Settlement Lodged Date From (MM/DD/YYYY): (Use * to select all.)’ , ‘A’ , , mono , free) OR
TRUNC(ICIS_ENF_CONCLUSION_SETTLE.SETTLEMENT_LODGED_DATE) >= TRUNC(TO_DATE(@Prompt(‘Settlement Lodged Date From (MM/DD/YYYY): (Use * to select all.)’ , ‘A’ , , mono , free),‘MM/DD/YYYY’))) AND ( '’ = ANY @Prompt(‘Settlement Lodged Date To (MM/DD/YYYY): (Use * to select all.)’ , ‘A’ , , mono , free) OR
TRUNC(ICIS_ENF_CONCLUSION_SETTLE.SETTLEMENT_LODGED_DATE) <= TRUNC(TO_DATE(@Prompt(‘Settlement Lodged Date To (MM/DD/YYYY): (Use * to select all.)’ , ‘A’ , , mono , free),‘MM/DD/YYYY’)) )

hope that helps!!!

BOzilla (BOB member since 2002-08-29)

#8

Another alternative for Oracle (sorry I don’t have the SQL server and DB2 code at hand).

Simillar except that we use # instead for * but just change the character in the code and you will be fine.

Works for any type of objects

decode(greatest@Prompt(‘Period Name(s) or #’,‘A’,‘Period\Period Name’,multi,free),’#’,’#’,S_PERIOD.NAME) in @Prompt(‘Period Name(s) or #’,‘A’,‘Period\Period Name’,multi,free)

ClaireB :de: (BOB member since 2002-08-09)