In this final posting in the series, I’ll be looking at three more tips and techniques for Oracle BI Suite Enterprise Edition.
The first one is around adding a column selector to a crosstab report. To take an example, on my dashboard there’s a crosstab (or pivot table, in Siebel Analytics-speak) that currently displays a number of measures by market segment and channel. It’d be nice if this same report would support analysis by a number of dimensions and channel, not just market segment,
![](http://www.rittmanmead.com/images/sales_by_channel_and_market.jpg)
To put this feature in place, I go into Answers and add a new view – a “Column Selector” view.
![](http://www.rittmanmead.com/images/add_column_selector.jpg)
Now, I can add any valid columns from the column selector and make them available, for substitution, instead of market segment.
![](http://www.rittmanmead.com/images/select_columns.jpg)
Now, when I go back to the compound view, add this column selector view in and suitably alter the title, I can use the same report to analyze by any of these dimensions.
![](http://www.rittmanmead.com/images/sales_channel_final.jpg)
Now, using the same report, let’s say I want to alter it so that if the user clicks on any of the sales channel headers – Catalog, Direct or Internet – it launches into a new report using the Channel value as a parameter. The report I wish to link to is a bar chart showing units sold for that channel over time, like this:
![](http://www.rittmanmead.com/images/channel_bar_chart.jpg)
However – here’s the good bit – I only want to display it for the channel that’s been clicked on. To do this, I add a filter to the channel column, and specify “Is Prompted” as the condition, so as to pick up the parameter passed across from the drill action.
![](http://www.rittmanmead.com/images/channel_is_prompted.jpg)
Now, I go back to my original crosstab report, and edit the column properties for Channel, setting the value interaction to “Navigate”.
![](http://www.rittmanmead.com/images/set_val_interact_to_nav.jpg)
Then, I pick up the report to navigate to.
![](http://www.rittmanmead.com/images/pick_val_nav.jpg)
Now, when the user views the crosstab, the Channel values are hyperlinks…
![](http://www.rittmanmead.com/images/final_channel.jpg)
which, when clicked on, bring up our barchart for that particular channel.
![](http://www.rittmanmead.com/images/channel_popup.jpg)
Finally, I want to add this new crosstab into my dashboard, but alter the default behavior of a dashboard element so that the crosstab drills in place, rather than launching out to a separate window – the barchart will always pop up, this is what I want – but I can drill and alter the crosstab in place within the dashboard. To do this, I add the crosstab as usual to my dashboard, but this time, select “Drill in Place” from the section properties, like this:
![](http://www.rittmanmead.com/images/drill_in_place.jpg)
Now, when I drill into the hierarchies in this crosstab, the crosstab stays within the dashboard, only launching out when I request the bar chart report on channel sales.
![](http://www.rittmanmead.com/images/final_drill_in_place.jpg)
As I said before, thanks again to Bas Roelands from Oracle Netherlands for passing on the techniques.