I’ve often been asked by a client to us a specific value in a prompt, but pass something else in the SQL to aid in performance. For example, prompting on “City Name – Zip” and just passing the indexed Zip.
This can simply and quickly be done in OBIEE without resorting to complicated RPD changes.
In this report, we will assume we have an index on the “Market Number” column, but the spec calls for the “Market Name and Number” to be prompted. Here we’re going to want to pass the “Market Number” column in the query, but don’t want the user to have to figure out what the Market Name is associated with the Market Number.
In order to do this, we need to create an auxiliary report that will be placed in the Shared Directory. The report will contain a single field (Indexed Field) and a single filter that is the “is prompted” for the prompt. Create this report and save it in the Shared Directory.
Go back into your main report, and create a filter on the Indexed field, clicking on the Advanced button and then “Filter based on results of another request”.
Select the auxillary report that you created in the previous step as the “Saved Request”. Because there is only a single value in the report, it will default to our Indexed column.
Your report should end up looking like this:
Now, when a user goes and selects a Market Name and Number (our M01 Market field) with the prompt, the original report will not see or read the value, but the auxiliary report will. Our main report will pull back any values that are associated with the selected Market Name and Number fields, but use the Market Number.
In real world testing with larger data sets, I’ve found that using more than a single value in the auxiliary report greatly diminished the benefits that were gained using this approach, but that could have been due to our data, so give it a try to help reduce the number of auxiliary reports needed.
It’s also good to note that the auxiliary report doesn’t need to be displayed on the dashboard for it to run and work.
Post a Comment