BusinessObjects Board

Optional Query Prompt

Can I have an optional query prompt?
Currently if I do not enter any value in the query prompt the “OK” button is not enabled.
I want the query prompt field to be optional i.e if the user enters a date then the report takes into accoun the value entered at teh prompt into the condition.
However, if the user does not enter a date then there is no condition and ALL data is returned.
Can this be done in some way?


surdee (BOB member since 2004-06-11)

This post is more or less your only option (prompts must be filled out).


Andreas :de: (BOB member since 2002-06-20)

Hi,

Yes, you will have to select some value in the prompt, you cannot leave it blank. The following is the workaround which I have adopted. I have created a dummy value ALL, this value is selected by the user when he does not want to apply any condition.

For this:

  1. You will have to modify the query for LOV. Following is the query we have modified for the Plant Name Prompt.

select ‘All Plants’ from dual
UNION
select distinct dim_plant.plant_name from dim_plant

  1. You will have to modify your data provider query, because the database table dim_plant does not have value like ‘All Plants’. I have modified in the following way:

DIM_PLANT.PLANT_NAME = DECODE(@prompt(…), ’All Plants’, DIM_PLANT.PLANT_NAME, @prompt(…))

Don’t forget to check the ‘Do not generate query’ option. Hope it helps…

Ajay :oops:


ajaymjain (BOB member since 2004-05-18)

I suggest you to use a macro instead of DECODE in your SQL.
Ths macro will build the SQL based on user prompts accordingly.

Following can be a sample:


Sub main()
Dim sqlQuery As String
Dim country As String
country = busobj.ActiveDocument.Variables(“country”).Value

sqlQuery = "SELECT Resort_Country.country,Resort.resort,Service_Line.service_line, Service.service,‘FY’+ Format(Sales.invoice_date,‘YYYY’),region.region "
sqlQuery = sqlQuery & "FROM Country Resort_Country,Resort,Service_Line,Service,Sales,Region,Invoice_Line,City,Customer "
sqlQuery = sqlQuery & “WHERE (City.city_id=Customer.city_id) AND (City.region_id=Region.region_id) AND (Resort_Country.country_id=Resort.country_id) AND (Customer.cust_id=Sales.cust_id) AND (Sales.inv_id=Invoice_Line.inv_id) AND (Invoice_Line.service_id=Service.service_id) AND (Resort.resort_id=Service_Line.resort_id) AND (Service.sl_id=Service_Line.sl_id)”

'Debug.Print sqlQuery
If country = “US” Then
busobj.ActiveDocument.DataProviders.Item(“Query 1 with beach”).SQL = sqlQuery & " AND (Resort_Country.country = ““US””)"

Else
busobj.ActiveDocument.DataProviders.Item(“Query 1 with beach”).SQL = sqlQuery

End If

'Debug.Print sqlQuery
End Sub


Call this macro in before refresh event or any other appropriate event.
Be careful, because this macro will override the default SQL generated by BO.

Sudhakar


ssudhakar (BOB member since 2004-03-01)