BusinessObjects Board

Optional Prompts

building reports desktopintelligence
#1

We are designing reports in BO to be run from WEBI. The user interface will present a number of prompts which are used in the criteria section
to limit the data returned by the report.

We would like to make each of the prompts optional. If the user fills any of them in they will be combined with AND to make up the where clause of the SQL.
Any that are not filled in by the user should be ignored.

A creative solution suggested by one of our designers is to edit the SQL code and then check the box to not regenerate the SQL when the report is run.

For example, consider the case of 2 optional prompts:

SELECT … FROM …
WHERE
( dbo.notification.customer_number = @variable(‘Cust Nbr’)
OR @variable(‘Cust Nbr’) = ’ ’
)
AND ( dbo.notification.purchase_order_number = @variable(‘PO Nbr’)
OR @variable(‘PO Nbr’) = ’ ’
)

The two prompts are used with AND to restrict what data will be returned. Each is combined with an OR @variable(‘prompt’) = ’ ’ so that if the prompt is not filled in, the query will still return data.

The problem with this solution is that if any change is made to the report, we have to edit the SQL to add all the “OR” clauses for all the prompt variables.

We would like to know if there is a way to make the prompts optional, but any that are filled in will be used with an “AND” to further restrict the data returned,
without having to edit the SQL and lock it.

We would like BO to automatically generate the SQL when a change is made to the report,
but still allow the prompts to be optional for the user.

Thank you,
Barry KornReich
bkornreich@amadeuslink.com

********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. **********************************************************************

Listserv Archives (BOB member since 2002-06-25)

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

In a message dated 00-04-03 09:24:27 EDT, you write:

We would like to make each of the prompts optional.
If the user fills any of them in they will be combined with AND to make up the where clause of the SQL.
Any that are not filled in by the user should be ignored.

Unfortunately, this is not a feature. If any prompt values are left blank, the “OK” button to generate the query and submit the report is not available.

In the full client you can get around this by using scripting. I don’t believe that the WEBI interface supports scripting at this time.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

#3

Why don’t you create Condition objects:

CUST_NBR_COND with where clause as
(dbo.notification.customer_number = @variable(‘Cust Nbr’)
OR @variable(‘Cust Nbr’) = ’ ')

PURCHASE_ORDER_NBR with where clause as
(dbo.notification.purchase_order_number = @variable(‘PO Nbr’)
OR @variable(‘PO Nbr’) = ’ ')

Vasan
Baton Rouge International Inc.
Data Warehousing Division

bkornreich@AMADEUSLINK.COM on 04/03/2000 09:25:38 AM

A creative solution suggested by one of our designers is to edit the SQL code and then check the box to not regenerate the SQL when the report is run.

For example, consider the case of 2 optional prompts:

SELECT … FROM …
WHERE
( dbo.notification.customer_number = @variable(‘Cust Nbr’)
OR @variable(‘Cust Nbr’) = ’ ’
)
AND ( dbo.notification.purchase_order_number = @variable(‘PO Nbr’)
OR @variable(‘PO Nbr’) = ’ ’
)

The two prompts are used with AND to restrict what data will be returned. Each is combined with an OR @variable(‘prompt’) = ’ ’ so that if the prompt is not filled in, the query will still return data.

******************************************************************************* Note: The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Ernst & Young LLP

Listserv Archives (BOB member since 2002-06-25)

#4

Barry,

A colleague of mine put this clever little SQL together, some time ago, and I’ve been using it ever since. It enables the user to select one or more items, or type the word ALL. This achieves the required goal of ‘optional’ prompting.

(Port_Client.ClientCode IN @Prompt(‘Select a CLIENT or key ALL’,‘A’,‘CLIENT\Client Code’,multi,free)
OR
‘ALL’ IN @Prompt(‘Select a CLIENT or key ALL’,‘A’,‘CLIENT\Client Code’,multi,free))

Just pile it into a conditional object, or use it as the ‘Where’ clause of a dimension / measure object.

Happy prompting!

Ang.

Listserv Archives (BOB member since 2002-06-25)

#5

Angela,
This can be done only if the prompted fields are character fields. Numeric fields will still have problem !
Abhijit

A colleague of mine put this clever little SQL together, some time ago, and
I’ve been using it ever since. It enables the user to select one or more items, or type the word ALL. This achieves the required goal of ‘optional’ prompting.

(Port_Client.ClientCode IN @Prompt(‘Select a CLIENT or key ALL’,‘A’,‘CLIENT\Client Code’,multi,free)
OR
‘ALL’ IN @Prompt(‘Select a CLIENT or key ALL’,‘A’,‘CLIENT\Client Code’,multi,free))

Just pile it into a conditional object, or use it as the ‘Where’ clause of a dimension / measure object.

Happy prompting!

Ang.

Listserv Archives (BOB member since 2002-06-25)

#6

Ofcouse, you have to re-write the sql for numeric and date types. You can make it easy for the user by creating condition objects with the whole sql in where clause for mostly used objects.

Vasan

ABhattacharya@EA.COM on 04/03/2000 07:49:38 PM

cc:

Angela,
This can be done only if the prompted fields are character fields. Numeric fields will still have problem !
Abhijit

A colleague of mine put this clever little SQL together, some time ago, and
I’ve been using it ever since. It enables the user to select one or more items, or type the word ALL. This achieves the required goal of ‘optional’ prompting.

(Port_Client.ClientCode IN @Prompt(‘Select a CLIENT or key ALL’,‘A’,‘CLIENT\Client Code’,multi,free)
OR
‘ALL’ IN @Prompt(‘Select a CLIENT or key ALL’,‘A’,‘CLIENT\Client Code’,multi,free))

Just pile it into a conditional object, or use it as the ‘Where’ clause of a dimension / measure object.

Happy prompting!

Ang.

Listserv Archives (BOB member since 2002-06-25)

#7

hi all,
I have a problem in the prompts. Actually i have to four prompts out of which two are mandatory and the remaining two are optional, i.e any either one of the prompt should be entered.

Eg.

Start Time:
Stop Time:
Measurement Object1:
Measurement Object2:

Start Time and stop time are mandatory but either one of the Measurement objects (i.e 1 or 2) should be entered.

Best Regards,
-ziauddin

__________________________________________________ Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/

Listserv Archives (BOB member since 2002-06-25)

#8

Ziauddin,

I had the same issue with 8 prompts. The best way we figured to solve the issue was to write the prompts in the SQL screen in editing the data provider section that gives the end user the option to either select/type in values or type ALL. Here’s what we wrote:

AND (
( (EDWARD description) IN
@variable(‘PROMPT1’,‘A’,‘Folder\Dimension’,MULTI,FREE) or ‘ALL’ in @variable(‘PROMPT1’,‘A’,‘Folder\Dimension’,MULTI,FREE ) )
AND ( (EDWARD description) IN
@variable(‘PROMPT2’,‘A’,‘Folder\Dimension’,MULTI,FREE) or ‘ALL’ in @variable(‘PROMPT2’,‘A’,‘Folder\Dimension’,MULTI,FREE ) ) AND ( (EDWARD description) IN
@variable(‘PROMPT3’,‘A’,‘Folder\Dimension’,MULTI,FREE) or ‘ALL’ in @variable(‘PROMPT3’,‘A’,‘Folder\Dimension’,MULTI,FREE ) ) AND ( (EDWARD description) IN
@variable(‘PROMPT4’,‘A’,‘Folder\Dimension’,MULTI,FREE) or ‘ALL’ in @variable(‘PROMPT4’,‘A’,‘Folder\Dimension’,MULTI,FREE ) ) )

Let me know if this makes any sense at all. I’m very unfamiliar with code as this was this first time I had to write any.

Michelle

Date: Mon, 20 Aug 2001 05:57:15 -0700

hi all,
I have a problem in the prompts. Actually i have to four prompts out of which two are mandatory and the remaining two are optional, i.e any either one of the prompt should be entered.

Eg.

Start Time:
Stop Time:
Measurement Object1:
Measurement Object2:

Start Time and stop time are mandatory but either one of the Measurement objects (i.e 1 or 2) should be entered.

Best Regards,
-ziauddin

_________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

Listserv Archives (BOB member since 2002-06-25)

#9

Is it possible to have optional prompts in BusinessObjects? I tried all means and cannot find any solution. Even I found the often suggested Predefined Prompts are very inefficient. We ran Oracle Explain Plan utility and found that the Predefined prompts are very inefficient. It is doing Full Table scan even for querying on Primary key. A sample sql is as follows that are formed out of Predefined prompts:
SELECT ORDERO.ORDNO, ORDERO.OTOTAL FROM ORDERO WHERE ( ( ORDERO.ORDNO IN (‘TEST-001’)or ‘%’ IN (‘TEST-001’) ) ); The second IN clause is forcing FULL SCAN on the database.

Appreciate very much for your suggestion.

Tapan Das
Direct: (301) 315-4766

Listserv Archives (BOB member since 2002-06-25)

#10

In a message dated Fri, 5 Oct 2001 3:20:56 PM Eastern Daylight Time, Tapan Das Tapan_Das@NEXTLINX.COM writes:

Is it possible to have optional prompts in BusinessObjects? I tried all means and cannot find any solution. Even I found the often suggested Predefined Prompts are very inefficient. We ran Oracle Explain Plan utility and found that the Predefined prompts are very inefficient. It is doing Full Table scan even for querying on Primary key. A sample sql is as follows that are formed out of Predefined prompts:
SELECT ORDERO.ORDNO, ORDERO.OTOTAL FROM ORDERO WHERE ( ( ORDERO.ORDNO IN (‘TEST-001’)or ‘%’ IN (‘TEST-001’) ) ); The second IN clause is forcing FULL SCAN on the database.

Without writing VBA code specifically for each report, no, it is not possible. You would need to create a dialog box with each prompt, and add conditions to the query on the fly for each prompt that was filled in.

The problem is that with a prompt dialog box, you cannot even check for null or empty values since BusObj forces every box to have a value.

The ability to have optional prompts is one of the most popular requests, and one that I desperately hope they include in version 6, or whatever the next big rewrite will be called. Number two most popular request? Cascading list of values… :slight_smile:

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

#11

I got the ER number is 2963 from tech support. When is Version 6.0 expected?

Thanks,
Tapan

Is it possible to have optional prompts in BusinessObjects?
I tried all
means and cannot find any solution. Even I found the often suggested Predefined Prompts are very inefficient. We ran Oracle
Explain Plan utility
and found that the Predefined prompts are very inefficient.
It is doing Full
Table scan even for querying on Primary key. A sample sql
is as follows that
are formed out of Predefined prompts:
SELECT ORDERO.ORDNO, ORDERO.OTOTAL FROM ORDERO WHERE ( ( ORDERO.ORDNO IN (‘TEST-001’)or ‘%’ IN (‘TEST-001’) ) ); The second IN clause is forcing FULL SCAN on the database.

Without writing VBA code specifically for each report, no, it is not possible. You would need to create a dialog box with each prompt, and add conditions to the query on the fly for each prompt that was filled in.

The problem is that with a prompt dialog box, you cannot even check for null or empty values since BusObj forces every box to have a value.

The ability to have optional prompts is one of the most popular requests, and one that I desperately hope they include in version 6, or whatever the next big rewrite will be called. Number two most popular request? Cascading list of values… :slight_smile:

Listserv Archives (BOB member since 2002-06-25)

#12

In a message dated 01-10-06 11:46:31 EDT, you write:

When is Version 6.0 expected?

No idea. Late next year, I would imagine, since they are projecting SP 4 for January.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)