DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Big Data
  4. Make Your Reports Dynamic by Putting HTML Code Snippets in Your Data Warehouse

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

Mpumelelo Msimanga user avatar by
Mpumelelo Msimanga
·
Apr. 14, 14 · Interview
Like (0)
Save
Tweet
Share
9.58K Views

Join the DZone community and get the full member experience.

Join For Free

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.

enter image description here

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.


enter image description here

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.

HTML code style Data warehouse Database Data (computing)

Published at DZone with permission of Mpumelelo Msimanga, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • SAST: How Code Analysis Tools Look for Security Flaws
  • A Simple Union Between .NET Core and Python
  • Distributed Stateful Edge Platforms
  • Artificial Intelligence in Drug Discovery

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: