Undo drills and view prompts

1.    About ‘Undo drills and view prompt values’ message

We have the following scenario:
A dashboard prompt on column A.
A report on this dashboard, which uses column A as a table/pivot table prompt.
In some cases, even though the dashboard prompt seems to be applied, you get an error on the page for the report:
Undo drills and view prompt values - warning
Once you click on the ‘Undo drills and view prompt values, the report will run, and will have the dashboard prompt applied.

2.    Scenario Details

We have this generic scenario:
–         a Time prompt
Time prompt
–         a simple report based on time, with the column exposed in the report and also prompted
with a column prompt used in the table view
Test report Criteria
which will result in a view like the one bellow
Table view - 2011 default
While saving this report, we add it to the dashboard, resulting in a simple dashboard, like this:
Dashboard
However, after certain changes, you get to the situation where the dashboard will throw the following error : (The layout of this view combined with the data, selections, drills, or prompt values chosen resulted in no data. Undo drills and view prompt values.)
Dashboard - error
Which is warning the user about some saved selections he’s not aware of.
Once the user clicks on the Undo drills and view prompt values, the report displays data as it should be, for the selected time period.

3.    Where Is the Problem?

The main question is now, where the problem is and how did it started?
Table view - default selection
Let’s see the steps we’ve been through and where it might be.

When creating the report, the report prompt column was populated by default, picking up the first value for the column, in the columns default sort order, correct?

Well, when saving the report like that, and adding it to the dashboard, we had no actual problem.

However, once we played around with the report, trying a couple of other selections in the column prompt, we save it again, with the default value back to the original one.
If we now go to the dashboard, we encounter our mysterious error.

What happened?
The report had now perceived, after the various selections, that the last selection is a saved selection which must be run initially on report load. That is the ‘saved selection’ we were not even aware of.

How to check for it?
If you go to edit your report, in the Advanced tab, you will notice the report XML.
You will see in there is a section/tag called ‘staticMemberGroup’.

If you still have a version of your initial report saved, and go to the Advanced tab to study the report’s XML, you will notice this report has no section/tag called ‘staticMemberGroup’.
report with hidden saved selection
Fig. 1 Report with hidden saved selection
report with no hidden saved selection
Fig. 2 Report with no hidden saved selection

4.    How to Fix?

Solving? We have two options, so far.

  1. XML level option
  2. View level option

XML level option implies changes on the report at XML level.
What you need to do is edit the XML and just delete the entire ‘pageEdgeState’ section/tag, and apply the changes.
xml level fix
View level option implies changes only on the table/pivot table with prompted column level.
What you need to do is edit the view, remove the column from the prompt section (move it to excluded), wait for the report view to load, save it, then move the column back in the prompt section and save the report.

Note: Please note this tests were done on OBIEE 11.1.1.7.1.