OBIEE in IL: OBIEE - Narrative and dual Y axis Google Chart

We covered the following Narrative issues:
Basics of Narrative.
Creating Tiles with Narrative.
OBIEE and Narrative - Drill = Navigate to BI Content.

OBIEE Narrative and Google Charts - Basic.

So I needed a graph with 2 side (Y) axis. In OBIEE there is an option of Line-Bar of this kind, but I needed 2 lines. So I decided to use Google Visualization, as was covered in the last post.

One personal word of caution: In OBIEE 10g there was a trick that allowed this sort of graphs. Few cases I new that used it, stoped eventually because "the customer doesn't understand what line belongs to what axis".

Still, in a POC you need to be able to show all is possible, then to explain...

I created a table with 5 columns:

Year, Month-1, DOM (Day of Month), Revenue and Quantity from Sample Sales. (It's Month-1 because in java-scripts January is month no. 0, February=1...)

I went to Google Playground - Advanced - Annotated Time Line. The original code was:

function drawVisualization() {
var data = new google.visualization.DataTable();
data.addColumn('date', 'Date');
data.addColumn('number', 'Sold Pencils');
data.addColumn('string', 'title1');
data.addColumn('string', 'text1');
data.addColumn('number', 'Sold Pens');
data.addColumn('string', 'title2');
data.addColumn('string', 'text2');
data.addColumn('number', 'Sold Papers');
data.addRows([
    [new Date(2009, 1 ,1), 30000, null, null, 4645, null, null, 12345],
    [new Date(2009, 1 ,2), 14045, null, null, 2374, null, null, 44444],
    [new Date(2009, 1 ,3), 55022, null, null, 5766, null, null, 76545],
    [new Date(2009, 1 ,4), 75284, null, null, 1334, 'Out of Stock', 'Ran out of stock on pens at 4pm', 16345],
    [new Date(2009, 1 ,5), 41476, 'Bought Pens', 'Bought 200k pens', 6467, null, null, 41345],
    [new Date(2009, 1 ,6), 33322, null, null, 3463, null, null, 33665]
]);

var annotatedtimeline = new google.visualization.AnnotatedTimeLine(
      document.getElementById('visualization'));
annotatedtimeline.draw(data, {
                  //'allValuesSuffix': '%', // A suffix that is added to all values
                  'colors': ['blue', 'red', '#0000bb'], // The colors to be used
                  'displayAnnotations': true,
                  'displayExactValues': true, // Do not truncate values (i.e. using K suffix)
                  'displayRangeSelector' : false, // Do not sow the range selector
                  'displayZoomButtons': false, // DO not display the zoom buttons
                  'fill': 30, // Fill the area below the lines with 20% opacity
                  'legendPosition': 'newRow', // Can be sameRow
                   //'max': 100000, // Override the automatic default
                   //'min': 100000, // Override the automatic default
                   'scaleColumns': [0, 1], // Have two scales, by the first and second lines
                   'scaleType': 'allfixed', // See docs...
                   'thickness': 2, // Make the lines thicker
                   'zoomStartTime': new Date(2009, 1 ,2), //NOTE: month 1 = Feb (javascript to blame)
                   'zoomEndTime': new Date(2009, 1 ,5) //NOTE: month 1 = Feb (javascript to blame)
              });
}

In the Playgraound I changed it to fit my needs. (Nothing big, removed few Columns, changed few presentation options...)

function drawVisualization() {
var data = new google.visualization.DataTable();
data.addColumn('date', 'Date');
data.addColumn('number', 'Revenue');
data.addColumn('number', 'Quantity');
data.addRows([
    [new Date(2009, 1 ,1), 30000, 34645],
    [new Date(2009, 1 ,2), 14045, 44444],
    [new Date(2009, 1 ,3), 55022, 76545],
    [new Date(2009, 1 ,4), 75284, 16345],
    [new Date(2009, 1 ,5), 41476, 41345],
    [new Date(2009, 1 ,6), 33322, 33665]
]);

var annotatedtimeline = new google.visualization.AnnotatedTimeLine(
      document.getElementById('visualization'));
annotatedtimeline.draw(data, {
                                //'allValuesSuffix': '%', // A suffix that is added to all values
                                'colors': ['blue', 'red', '#0000bb'], // The colors to be used
                                'displayAnnotations': false,
                                'displayExactValues': true, // Do not truncate values (i.e. using K suffix)
                                'displayRangeSelector' : true, // Do not sow the range selector
                                'displayZoomButtons': false, // DO not display the zoom buttons
                                'fill': 0, // Fill the area below the lines with 20% opacity
                                'legendPosition': 'newRow', // Can be sameRow
                                'scaleColumns': [0, 1], // Have two scales, by the first and second lines
                                'scaleType': 'allfixed', // See docs...
                                'thickness': 2, // Make the lines thicker
                               });
}

Next I need to replace the data addrows block:
data.addRows([
    [new Date(2009, 1 ,1), 30000, 34645],
    [new Date(2009, 1 ,2), 14045, 44444],
    [new Date(2009, 1 ,3), 55022, 76545],
    [new Date(2009, 1 ,4), 75284, 16345],
    [new Date(2009, 1 ,5), 41476, 41345],
    [new Date(2009, 1 ,6), 33322, 33665]
]);
With:
      var myArray=[];
    myArray.push([new Date(@1,@2,@3), @4, @5]);
    data.addRows(myArray);

And add before the content of prefix the following:

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
if(document.URL.indexOf("saw.dll?Answers") == -1)
   google.load('visualization', '1', {packages: ['annotatedtimeline']});
   And after the end of prefix:

google.setOnLoadCallback(drawVisualization);
</script>
<body style="font-family: Arial;border: 0 none;">
<div id="visualization" style="width: 800px; height: 400px;"></div>

Please note: annotatedtimeline in google.load('visualization', '1', {packages: ['annotatedtimeline']}); is the name of the specific visualization.

So we have now the prefix:

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
if(document.URL.indexOf("saw.dll?Answers") == -1)
   google.load('visualization', '1', {packages: ['annotatedtimeline']});
function drawVisualization() {
var data = new google.visualization.DataTable();
data.addColumn('date', 'Date');
data.addColumn('number', 'Revenue');
data.addColumn('number', 'Quantity');
      var myArray=[];

The Narrative:

myArray.push([new Date(@1,@2,@3), @4, @5]);

The postfix:

data.addRows(myArray);
var annotatedtimeline = new google.visualization.AnnotatedTimeLine(
      document.getElementById('visualization'));
annotatedtimeline.draw(data, {
               //'allValuesSuffix': '%', // A suffix that is added to all values
              'colors': ['blue', 'red', '#0000bb'], // The colors to be used
              'displayAnnotations': false,
              'displayExactValues': true, // Do not truncate values (i.e. using K suffix)
              'displayRangeSelector' : true, // Do not sow the range selector
              'displayZoomButtons': false, // DO not display the zoom buttons
              'fill': 0, // Fill the area below the lines with 20% opacity
              'legendPosition': 'newRow', // Can be sameRow
              'scaleColumns': [0, 1], // Have two scales, by the first and second lines
              'scaleType': 'allfixed', // See docs...
              'thickness': 2, // Make the lines thicker
          });
}
   google.setOnLoadCallback(drawVisualization);
</script>
<body style="font-family: Arial;border: 0 none;">
<div id="visualization" style="width: 800px; height: 400px;"></div>