First a background:One of the reasons why i was exposed to Cognos Reporting Development was when one of the projects i got involved with had some reporting requirements. We used to have the reports published via Intelliprint Reporting Software (owned by Synaptris) in our Domino Databases until the company where i work for decided to purchase Cognos 8 BI Platform. This decision only means one thing. We have to migrate the Intelliprint Reports to Cognos Platform. I got mixed emotions. I thought i would lose my job since i dont know much about Cognos. Fortunately, the company considered my Notes Skills and trained me with Cognos 8 development. Learning a new platform i.e. Cognos 8 is something new to me. Don’t get me wrong though, Intelliprint Reporting Software is an excellent reporting tool which is one-of-a-kind (we used this mostly in our client side reporting). But Cognos offers more than just a reporting solution. It is actually a business intelligence (BI) and performance management software which is diverse, perfect for a distributed computing (for n-tier environment). Cognos offers dozens of other products, mostly they are known for PowerPlay online analytical processing (OLAP) tool, its Impromptu report and query language but i am currently exposed to its Reportnet Environment. In general, Cognos Products allows users to extract corporate data, analyze it, and then assemble reports for analysis, business decision making, scorecarding, dashboards, and event management.

The Dilemma: How to link Lotus Notes Data with Cognos BI Suite?

Ok so here’s the big problem we have to deal with. How do we link Lotus Notes data with Cognos Reports? From my research and experience, there are actually several ways to connect Lotus Notes Data with Cognos.

(1) Option 1. Using Cognos Bi Modeler for Lotus Notes

Actually i haven’t tried this yet, but I found this on my research a few months back. Factsheet

Cognos 8 Business Intelligence Data Modeler for IBM Lotus Domino is designed to allow users to access Lotus Notes applications traditionally isolated from BI access, according to officials at Ottawa-based Cognos. This capability is provided through a simple wizard-style interface that allows you to integrate Lotus Notes databases and applications with your Cognos enterprise BI infrastructure.With the tool, users can select Lotus Notes data sources, tables and columns and label them with names that are meaningful to the business without making any changes to the existing Notes applications, according to Cognos. In addition, users can present and manage Cognos reports within the IBM WebSphere Portal and the IBM WorkSpace client to support collaboration and performance management projects -

Resources Links ,

Collaborative and Business Intelligence for Notes

DataModeler Information

(2) Option 2. Using XML (Extensible MarkUp Language) via Lotus Notes Agent (realtime report generation)

This is pretty much straight forward. This is what i do at the moment for my Cognos Reports.

a. First, you prepare a valid XML which can be extracted from Lotus Notes via an Agent which would serve as reference data source for Cognos to use. Test your Lotus Notes Web Agent to work in the browser. Be sure to add dataset xmlns for cognos. i.e.

Print “Content-type: text/xml” ‘prevents Domino from sending default headers
Print |<?xml version=’1.0′ encoding=’utf-8′ ?>|
Print |<dataset xmlns=”http://developer.cognos.com/schemas/xmldata/1/” xmlns:xs=”http://www.w3.org/2001/XMLSchema-instance“>|

sample output can be seen here:

sample script source code can be found in this forum, feel free to register and participate.

http://www.techronnati.com/blog/forum/viewtopic.php?f=40&t=16

Ensure that you have a valid XML file when you generate GenerateXML?OpenAgent in the browser.

b. Suppose you have a valid xml file generated by http://servername/yourdatabase.nsf/GenerateXML?OpenAgent , you use this URL Source as part of the connection string (for datasource) in Cognos. Specify this in the following format connection string:

;LOCAL;XML;http://<yourservername>/<yourdatabasename>/GenerateDMSXML?OpenAgent

c. Test the connection by clicking on the “test” button.

d. Now that you have successfully link the XML data to Cognos Reports, it’s time to make a Cognos framework model and publish it. You have to create a query first. For those new to Framework Manager, FM is a metadata modeling utility. A model is a business image of the information coming from one or more data sources. When adding security and multilingual capabilities to this business presentation, one model can be used for the reporting, ad hoc querying, and analysis needs of many groups of users around the globe.

d.1 Creating a Query. Right Click on the Model and choose Query Subject.

d.2 Specify Query Subject Name. and Select Datasource. Click Ok.

d.3 Select the specified Datasource name. For this example, the name of the datasource i specified was DMS_XML . Notice that i also have other database sources coming from other database engines i.e. Oracle 10g, Sybase. I would suggest you create a standard naming convention so that right away you know where the source comes from. Click Next.

d.4 Expand the table and Select all the fields. This would serve as the columns for your report. Click Next.

d.5 Verify the select statement. When you click on the Test button. you would see the extracted data from the Lotus Notes database.

d.6 This is sample output taken from the XML source - Lotus Notes Data.Click on Finish to go back to framework manager.

d.7 After successfully making a query. You need to publish your report. Right click on the Package. Publishing wizard window would appear. Click Next.

d.8 This would publish the model to the server which would be the basis when creating our report.

e. Then you can now create reports. Creating a report involves the following steps:

• Open your Report Studio
• Specifying your package
  - > Choose a package by going to cognos connection>report studio>Select a module from the list selected.
• Choose report template
  - > Create a empty report first,click one of the predefined report templates ,click OK.
• Adding query items
  - > In the insertable objects plane,select the query item that you want to add to report,drag it to the desired location.
• Saving report
  - > Save the report from file menu.
• Running the report
  - > Open the report that you want to run.
  - > From the Tools menu, click Validate Report.
  - > A message box appears indicating whether any errors were found in the report.
  - > If you want to enable Design Mode Only filters defined in the package, from the File menu,
  click Use Model Design Filters.
  - > Use these filters to limit the amount. Open the report that you want to run.
  - > From the Tools menu, click Validate Report.
  - > A message box appears indicating whether any errors were found in the report.
  - > If you want to enable Design Mode Only filters defined in the package, from the File menu,
  - > click Use Model Design Filters.
  - >If you want to set run options, from the Run menu, click Run Options.
  - > From the Run menu, click one of the options to produce the report in the format you want.
  You can actually produce various kinds of report to be displayed in HTML, PDF, CSV, XLS, or XML. So you can choose which best fit your needs.

There you have it. I hope you learned something from this tutorial.

For more information on reviews, tips, and discussion, Feel free to browse the following Techronnati Forums:

If you enjoy our post and was satisfied by the information we deliver, you can show your support by donating some cents via paypal. Thank you very much.

Related Content

Subscribe to the post comments feeds or Leave a trackback