Search code examples
javascriptcognos-10

Javascript Table within Cognos, using List as Data

We have Javascript that will produce a chart from a table of data within the code.

We are looking to use Cognos to automate the process of pulling the data. Essentially we want to use a list within Report Studio to populate the table section of the below Javascript code.

We tried adding a HTML item before and after a list with the code that is before and after the table, and the page populates as blank.

Any help would be appreciated. Thank you 

<script type="text/javascript"     src="https://www.gstatic.com/charts/loader.js"></script>

<script type="text/javascript">
  google.charts.load("current", {packages:["timeline"]});
  google.charts.setOnLoadCallback(drawChart);
  function drawChart() {

var container = document.getElementById('example5.1');
var chart = new google.visualization.Timeline(container);
var dataTable = new google.visualization.DataTable();
dataTable.addColumn({ type: 'string', id: 'Room' });
dataTable.addColumn({ type: 'string', id: 'Name' });
dataTable.addColumn({ type: 'date', id: 'Start' });
dataTable.addColumn({ type: 'date', id: 'End' });
dataTable.addRows([

['Job1', '',  new Date(0,0,0,18,0,0), new Date(0,0,0,18,0,1) ],
['Job2', '',  new Date(0,0,0,18,3,0), new Date(0,0,0,18,3,7) ],
['Job3', '',  new Date(0,0,0,17,30,0), new Date(0,0,0,18,0,39) ],
['Job4', '',  new Date(0,0,0,18,0,0), new Date(0,0,0,18,10,19) ],
['Job5', '',  new Date(0,0,0,18,0,0), new Date(0,0,0,18,0,22) ]


  ]);

var options = {
  timeline: { colorByRowLabel: true }
};

chart.draw(dataTable, options);
  }

</script>

<div id="example5.1" style="height: 500px;" style="width: 100px;"></div>
Solution

  • We figured out how to do this.

    First, we had to place the Javascript within a HTML Item within a Singleton. Once the HTML item with the JS was in the singleton, we were able to get the Chart to show (with the static table) when running the Cognos report.

    Since we wanted the table within the JS to run from data in a Cognos Query, we removed the existing JS from the HTML, and placed the first part of the JS in an HTML item within a Singleton.

    First part of the JS:

    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

<script type="text/javascript">
  google.charts.load("current", {packages:["timeline"]});
  google.charts.setOnLoadCallback(drawChart);
  function drawChart() {

var container = document.getElementById('example5.1');
var chart = new google.visualization.Timeline(container);
var dataTable = new google.visualization.DataTable();
dataTable.addColumn({ type: 'string', id: 'Room' });
dataTable.addColumn({ type: 'string', id: 'Name' });
dataTable.addColumn({ type: 'date', id: 'Start' });
dataTable.addColumn({ type: 'date', id: 'End' });
dataTable.addRows([

    Next place a Repeater to the right of the HTML item. Place a HTML item within the Repeater. In the properties of this HTML item, change the Source Type to Data Item Value. In the Data Item Value, place your Data Item that you would like displayed within the table section of the JS.

    NOTE: Make sure you format the Data Item with the same format as the table is in the original JS. Our Data Item code to make the table is outlined below. Use the [FinalDataItem] in the HTML within the Repeater.

    [ConcatenatedDataItem]

    '['''+[JobName]+''''+', ''' +
[ExecutionStatus] + ''''
+', new Date(0,0,0,'
+cast(Datepart({HH}, [StartTime]) as nvarchar(10)) + ',' 
+ cast(DatePart({MI}, [StartTime]) as nvarchar(10)) + ',' + cast(DatePart({SS}, [StartTime]) as nvarchar(10)) + '), new Date(0,0,0,' + cast(Datepart({HH}, [EndTime]) as nvarchar(10)) + ','
+ cast(DatePart({MI}, [EndTime]) as nvarchar(10)) + ',' + cast(DatePart({SS}, [EndTime]) as nvarchar(10)) + ') ]'

    Since each row needs a comma at the end, except the last row, we used the following method.

    1) [RowNum]: Create a Data Item to give each row a number using running-count([YourConcatenatedDataItem]).

    2) [MaxRowNum]: Make another data item to get the Max Row Number for the report using Maximum([RowNumberDataItem] FOR REPORT). Set both Aggregate Functions to Automatic.

    3) [FinalDataItem]: Create a new Data Item. In this new Data Item we will make it so every row has a comma at the end, except the last row. Use CASE WHEN [RowNum] <> [MaxRowNum] THEN [YourConcatenatedDataItem]+',' ELSE [YourConcatenatedDataItem] END.

    Back on the report you will want to add another HTML item to the right of the Repeater. In this HTML item you will place the last part of the JS within it.

      ]);

var options = {
  timeline: { colorByRowLabel: true }
};

chart.draw(dataTable, options);
  }

</script>

<div id="example5.1" style="height: 500px;" style="width: 100px;"></div>