Tips and tricks for using OBIEE 12c

1. Creating graphs from pivot tables and changing the values to show as percentages
2. Setting the tabs to automatically open on the criteria tab and not the results tab
3. Change the subject area of a report using the XML
4. Repeat duplicate values in each row in a table
5. Prevent a new column in the criteria being added to all existing views but display in new views
6. Keep Hidden Prompts hidden everywhere

1. Creating graphs from pivot tables and changing the values to show as percentages

A handy feature to note in 12c is the ability to create graphs based on values in the created pivot table.

This can be useful when you want the exact numbers plotted in your pivot table also plotted in a visual graph next to it.

There are a variety of options for the graph style, position of the graph as well as the shading and colouring of the graph.

If you have used the nifty function “Percent of” in the pivot table (on the metric, select ‘Show Data As’ > ‘Percent Of’ > Column, Row, Section, Page, Column Parent or Row Parent) then it is also useful to have the graph y axis labelled accordingly (as a percent). By default, the graph scale will not change (it will stay as 0 to 1), so users will have to manually change the axis label using the “Edit Graph Properties” located next to the ‘Position’ drop down. From there, select ‘Percentage (%)’ in the “Abbreviate” drop down and this will align the scale on the graph out of 100%.

2. Setting the tabs to automatically open on the criteria tab and not the results tab

When opening an analysis to edit, having the default option set to the Results tab can cause heavy strain on the system especially with large reports, as the Results tab automatically triggers a query to run.

It is possible to change the default setting to open on the Criteria tab. After logging in, select ‘My Account’ from the top right corner. Select ‘Start on Criteria tab when editing Analysis’ from the “Analysis Editor” drop down.

3. Change the subject area of a report using the XML

In some cases, especially when thinking about security, you might have built a report in a subject area, only to find that it should have been built in a different subject area. The XML can be manipulated to change the subject area without having to recreate the analysis in the new subject area if the tables in all subject areas are the named in the same way.

(Make sure to regression test and ensure all the columns are now coming from the new subject area)

In the Advanced tab go the third line of XML and change the name of the subject area to the new subject area. Be careful when entering the new subject area to ensure that it is spelt correctly and has the correct case as it is case sensitive.

4. Repeat duplicate values in each row in a table

There is now a great new feature in 12c which allows users to repeat duplicate values in each row by using a setting on the table properties. In previous versions, users would have to manually set the Value Suppression to ‘Repeat’ in every column which can be a very time-consuming task for large reports!

5. Prevent a new column in the criteria being added to all existing views but display in new views

This feature is useful when adding new metrics to a pre-existing analysis where you want to add a new table with new values. It means that the existing tables will not be affected when you add or remove metrics unless you select ‘Display in existing and new views’. It can save formatting time and ensure that pre-existing views remain intact.

6. Keep hidden prompts hidden everywhere

There are many options for ‘Hidden Prompts’ however, most of them will reveal the ‘hidden’ prompt when exporting the dashboard to PDF as shown below.

One way around this is to put the prompt that requires hiding into a section. Apply a condition on this section based on an analysis which contains a statement which will never be true e.g. 1=0.

On the condition, set ‘True If Row Count’ ‘is greater than 0’. This will always bring a ‘False’ response since 1 will never equal 0, and therefore the section will never show. The benefit of using this method is that the hidden prompts will not show when exporting to PDF.