It has been awhile, I think a year now, since my last blog. It has just been a very busy past year. In any event, I came across a problem that involves presentation hierarchy columns. The ever-beloved new feature of 11g , which all users and clients love to use for pretty much all reports.
The issue: How to create a navigational report which will filter the presentation hierarchy column based on the selected record from the calling report? This is otherwise known as report navigation via action links.
Well, there is few solutions already out there by Venkat from Rittman Mead here. And similar others like this one, here. Pretty much, almost the same idea and way of doing it. Each will work, but takes lots more development and maintenance. There should be an easier way, right! Like the “is prompted” option in filters. But presentation hierarchy columns cannot be used in the filter section, only their corresponding level attribute counterparts can be added in the filter section. This leads to the 2 solutions linked above to some extent. To use any sort of filtering or pruning of the hierarchy columns however, remember it would need to be done in the “selection steps” section.
So what is the easier way that I found, which is simple and easier to manage!?
First it all centers around selection steps and the “overridable” attribute for the xml attribute “saw:selectionStep” within the advanced tab. But you do not even have to go there to change anything, as I will show you the simple steps to be able to set this using the UI. However, if you have an older version of 11g instance (I am using 220.127.116.11 version) you will need to modify the xml, which I will describing later.
The first step is to open up the “Selection Steps” pane in the target report.
Then “Edit” the 1 step within the presentation hierarchy column.
Third step is to switch the value of the “Action” drop down to: “Start with all members”, which will change the interface. Now you will only see “Override with Prompt” checkbox. Just mark this box and you are all set.
This will add and set the “overridable” attribute = “true” as seen and highlighted below.
Now in older versions of 11g, I can not remember which version, this checkbox was added. Before that, you did not have an easy way with the UI to set this. Instead, what you needed to do is go into the advanced tab and set the overridable=”true” attribute. Sometimes, you will not see the “saw:dimensionSelection” so you would need to create it. Easiest way to do that, was to go to the “Selection Steps” pane and select one value in the first selection step-> “Start with members”. Then you would go back to the selection step and edit it to change back to “Start with all members”. Now once that action is done, the xml section is already created for you and all you have to do is ensure that the “overridable” attribute = “true” for the “saw:selectionStep”.
My results can be seen here in the example:
Here is the calling, or high level report:
Here is what the low level or target report looks like with no filtering
Here is the same exact low level, target report navigated from the high level report based on selecting the “Accounts Receivable” value.