A while back I created a post describing how to produce an organization chart in Oracle APEX using Google visualizations. If you didn’t catch that then go and take a look here first before reading on as it will provide the background reading to this post.
So in this post I am going to demo how we can do this in OBIEE – and it’s actually quite easy because OBIEE has already done a lot of the work for us.
First we need a level based hierarchy (or even just a representation of a hierarchy as levels across columns). This is how all BI Applications hierarchies are implemented, for example the organization and position hierarchies. I am going to use SampleApp with the “Sample Sales”.”Offices” hierarchy.
Then we simply select all the columns in our hierarchy into a simple analytic. As we have multiple top level nodes I have applied a filter to restrict to just one company, however this isn’t necessary – if you have multiple top level nodes then you simply get multiple trees.
If we use the default Table view then we see something like this. Note I have changed the column order in this view simply to make the hierarchy structure clearer.
So now we want to create our organization chart. We will use a Narrative view as this allows you to produce HTML for each row returned in the analytic. Create a new narrative view and remember to tick Contains HTML Markup
As per the APEX example, we need to set up the chart first, so we place the following code in the Prefix.
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> <script type="text/javascript"> google.charts.load('current', {packages:["orgchart"]}); google.charts.setOnLoadCallback(drawChart); function drawChart() { var data = new google.visualization.DataTable(); data.addColumn('string', 'Name'); data.addColumn('string', 'Manager'); data.addColumn('string', 'ToolTip');We now need to output our relationships. This is easy – because we have all the columns in our view. To reference a column value we use @N where N is the postional reference of the column in the criteria. We need to create a parent->child relationship from the office to the department (@1 -> @2), department to the organization (@2 -> @3) and organization to the company (@3 -> @4).
data.addRows([ ['@1', '@2', ''], ['@2', '@3', ''], ['@3', '@4', ''] ]); Due to executing the narrative for each row of the analytic, we are of course adding rows that have already been added in previous iterations. However Google takes care of that for us and we see no side effects. We then simply close off the chart and create a dummy element to hold the rendered chart:
var chart = new google.visualization.OrgChart(document.getElementById('chart_div')); chart.draw(data); }
</script>
<!--Div to hold the rendered org chart -->
<div id="chart_div"></div>Our view now looks as follows.
Click Done, and that’s all there is to it. We now have an organization chart rendered for our office structure:
We can use any of the configuration options/methods that Google provides, for example we can modify our call to chart.draw() to be chart.draw(data, {allowCollapse: true}); which then makes the chart collapsible.
Of course you can do various things with this such as only showing to a specified level, or write yourself a little JavaScript routine to collapse at a certain point to avoid rendering large charts. Similarly with a BI Applications environment you may do any number of enhancements such as displaying the holders of a position within a position hierarchy. A kind of reverse version of the demo Google uses.
If you’ve done anything interesting or outside the box with Google charts then feel free to comment below.