Make Your Reports Dynamic by Putting HTML Code Snippets in Your Data Warehouse

Business Intelligence (BI) tools such as Cognos are good at fetching data from a database and displaying the data on a web page. This post details how you should put some of your HTML reporting components in your data warehouse to give you a flexible and easily maintained set of components to include in all your reports. Even though I am writing this post using Cognos as an example, you can use this technique for any BI tool that is accessed by a web browser. That should be 90% of all BI tools.

Perhaps because I do not do it often enough, I find creating custom Cognos templates tedious. The process involves iteratively modifying XML documents, modifying cascading style sheets and quite often you need to restart the Cognos server to see the changes. This process has to be repeated with every Cognos upgrade to make sure your templates continue to work well. To reduce the technical dept incurred when you create custom Cognos templates I suggest storing some of your report specific templating components as HTML in your reporting database or data warehouse . This tip does not apply to the overall Cognos portal but items in each reports created in Report Studio.

To show you what I mean I am going to use two common items that appear in reports, logo images and export buttons. I found some code from Suresh_BI in an IBM forum to create the buttons:

<inputtype='image'src="..."value="PDF"onclick="javascript:gCognosViewer.getRV().viewReport('PDF')"><inputtype='image'src=""value="XLSX"onclick="javascript:gCognosViewer.getRV().viewReport('spreadsheetML')">

The code above will give you the image export buttons below. You need to ensure the paths to the images a correct.

I first tested the HTML code in a Report Studio report by pasting the code into an HTML element and running the report. Once satisfied that the code worked, I inserted this code into a table in the data warehouse.

I then put the HTML code into a database table. Below is the CREATE TABLE statement for the table I used.

CREATETABLE cognos_items 
(
cognos_item_id INT
, cognos_item_name VARCHAR(60), cognos_item VARCHAR(4000));

You need to pay attention how quotation characters are handled by your database. I have chosen to use double quotes for the HTML and single quotes for the database string.

INSERTINTO mmsimang.cognos_items
(cognos_item_id, cognos_item_name, cognos_item)VALUES(1,'Export Buttons',' <input type="image" src="..."value="PDF" onclick="javascript:gCognosViewer.getRV().viewReport("PDF")">
<input type="image" src="" value="XLSX"  onclick="javascript:gCognosViewer.getRV().viewReport("spreadsheetML")">');

Include the cognos_items table in the Framework Manager model and publish in a package. In Report Studio create a query that includes the export buttons from the cognos_items table.

Place an HTML Item from the Toolbox within a singleton item also from the Report Studio Toolbox. Then change the Source Type property of your HTML Item toa Data Item Value from the query your created with the HTML code.

You are done. If you run your report the code from the database will render the export buttons in your report.

Dynamic Functionality

The advantage of using this solution is that you can now make changes to all your reports containing the HTML component without touching your Cognos server. You just need to run an UPDATE statement on your database table. Let’s say you decided that you did not want users to export to Excel you could comment (or cut) out the line for Excel export.

UPDATE
cognos_items
SET
cognos_item ='<input type="image" src="..." value="PDF" onclick="javascript:gCognosViewer.getRV().viewReport("PDF")">
<!-- <input type="image" src="" value="XLSX"  onclick="javascript:gCognosViewer.getRV().viewReport("spreadsheetML")"> -->'WHERE
cognos_item_id =1

The UPDATE statement above comments out the export to MS Excel button.

The same technique can be used to display images in your report. The URLs to the images can be stored in the database. If you wanted all your reports to use a different logo you would only have to update the database table. This spares you having to open and change each report using Report Studio. The following code inserts a logo URL in the cognos_items table.

INSERTINTO cognos_items (cognos_item_id, cognos_item_name, cognos_item )VALUES(2,"Company 1 Logo","<img src='....png'">

HTML is capable of more than just displaying buttons and images. It really is up to how creative you are in your use of HTML and dare I say JavaScript.

To summarize

• You are taking advantage of the connection Cognos is already making to the database. A few more rows of data are not going to make a big difference in report performance.
• Updates are done on the database without a need for a server restart. One SQL UPDATE statement is used to update all the reports.
• Cognos items tables can be joined to other tables in the data warehouse to give dynamic logos/HTML for different parts of the business.
• You can use this technique for any BI tool that is accessed by a web browser. That should be 90% of all BI tools.

You will really appreciate this this technique once you have several hundred reports and need to make a change in all the reports. Yes your first need to include the HTML component in all your reports. You can do this during the next upgrade when you have to open and run all the reports to make sure that they are working.