Issue: OBIEE Dashboard prompt not filtering report correctly despite passing same data value.
Cause: The issue was that the actual database column say for eg: X_STATUS was having trailing spaces in database. In OBIEE when you create a dashboard prompt from this column, OBIEE automatically does a trim of the data and presents it to you.
To make things simple, imagine I am having a status 'Accepted ' in database. Note that there are 5 trailing spaces in this column in the physical table. When you create a dashboard prompt the trailing spaces are removed and shown inside prompt. So the data in the prompt is 'Accepted' instead of 'Accepted '. Now what happens when you prompt is that the filter passed in physical SQL would be as below
SELECT X_STATUS
FROM TABLE_NAME
WHERE X_STATUS='Accepted'
Now when the query reaches the table, X_STATUS column is searched for 'Accepted' instead of the original data with 5 trailing spaces. Hence a match is not found.
Resolution: Issue debugging was followed in steps:
First we checked if the dashboard prompt presentation column and report filter column was the same. It was the same.
Next we checked the physical query log and found the query passed to database. Checked for a cache metadata hit. Next we took the query and executed in database. This gave as no result as we explained earlier.
Next we did a DISTINCT on the X_STATUS column. Which gave us all distinct values, from the value we copied the Accepted data column and pasted it inside two single quotes (''). If there are spaces in the data the quotes will not end after the last letter.
The solution is to do a trim on the column in OBIEE report filter or the better solution is to do a trim on the actual physical column in Data warehouse.
Lesson Learned: The lesson learned was that when you create a dashboard prompt. OBIEE automatically does a trim function on it.
