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?
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:
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
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:
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.