Embed Google Maps in IBM Cognos Reports - QuontraSolutions

IBM Cognos has a built-in map functionality that provides great visualization. With multiple layers on each map you can display data at different levels and even create drill throughs to other reports. It is a nice feature to visually present your data. However, everything has its limitations. There are some rich map features that IBM Cognos maps may not be able to provide; such as adding additional content dynamically onto the map, searching, on-demand zoom in/out, actual location, overlays, better mobile device support, etc.  

Google Maps is one of the leading online free map services that many web users like you are using daily. Today, with globalization of business many companies have offices all over the world. So it becomes a very natural request from your business users: can we put our business data on Google Maps so that we can easily see how our branch offices perform globally? Or in other words, can we place Google Maps inside our Cognos reports? Before we get to the answer, let’s first take a look whether this is even a technical possibility.

Note:

• All examples are designed for IBM Cognos 10 Report Studio based reports. It is assumed that the reader is familiar with Cognos Report Studio.

• For more information about the Google Map API please go to:
  http://code.google.com/apis/maps/documentation/javascript/
  

IBM Cognos Architecture

IBM Cognos 10 is a web-based application with Service Oriented Architecture (SOA). Built off industry proven technology, such as XML, SOAP, and Web Services Definition Language (WSDL), IBM Cognos is designed for large scalability, availability, and openness. There are three tiers of IBM Cognos:

• Web Tier – web UI and windows based UI for the end user
• Application Tier – all services to handle user requests and deliver responses/results
• Database Tier – incudes the JDBC connection to content store, metric store and generic data source connections via various API’s

Here is a diagram that briefly illustrates the IBM Cogons Architecture:

Method to Integrate Google Map in Report Studio

Google Map has several API’s that can be easily used to integrate with 3^rd party applications, such as Maps JavaScript API, Maps API for Flash, Google Earth API, Static Maps API and Web Services. From the above IBM Cognos architecture diagram we see that Cognos is a web based application and does have a web interface for communication with end users, so it makes perfect sense that we could use the Google Maps JavaScript API interface to make the connection between Cognos reports and Google Maps! A few key elements support the integration:

• Google Maps JavaScript API allows web applications to use Google Maps services on a web page
• IBM Cognos is a web based application
• For any IBM Cognos report, running in HTML format produces a web page
• HTML item include JavaScript is supported within IBM Cognos Report Studio

Google Maps JavaScript API

Google Maps services are free web services. However to use them within your enterprise and commercial web applications please do check with Google for detailed license and terms of use, here is the link: http://code.google.com/apis/maps/terms.html.

The latest Google Maps JavaScript API is version 3 and doesn’t require an API key any more. Version 2 has been officially deprecated. If you have an application developed in Version 2, it is strongly recommended that you update it to version 3 as soon as possible.

In general you will deal with the following objects and functions with Google Map JavaScript API:

• Basic Map Objects
• Map Events
• Map Controls
• Map Overlays
• Map Services

Demo

Read for hands-on practice? Next I will show you an example of how to embed Google Maps in an IBM Cognos Report.

Note: this report is developed in IBM Cognos Report Studio, Version 10. You must have internet connection to access Google Maps Services; your internet service provider may have specific rules that may block Google Map Service.

Steps:

1. Launch Report Studio. When prompted, select the Go Sales(query) package and a new list report template.
2. Expand the Sales (query) namespace and drag Country, City and Address from the Branch query subject.
3. From the Insertable Objects tab, drag an HTML item next to the Address data item(unlock cell first)
4. Select “Report Expression” as HTML source, type in the following as Report Expression:
   ‘<script>
   AddMarker(“‘ + [Query1].[Address 2] + ‘, ‘+[Query1].[City] +’, ‘+[Query1].[Country]+ ‘”);
   </script>‘
5. Drag another HTML item before the list (NOT inside the list), choose “Text” as source type. Type in the following:
   <script type=”text/javascript” src=”http://maps.google.com/maps/api/js?sensor=false”></script>
   <div id=”map” style=”width: 700px; height: 400px”></div>
   <script type=”text/javascript”>
   var latlng = new google.maps.LatLng(40.756, -73.986);
   var options = {
   center : latlng,
   zoom : 1,
   mapTypeId : google.maps.MapTypeId.ROADMAP
   };
   // Creating the map
   var map = new google.maps.Map(document.getElementById(‘map’), options);
   var geocoder = new google.maps.Geocoder();
   function AddMarker(address) {
   geocoder.geocode( {‘address’ : address}, function(results, status) {
   if (status == google.maps.GeocoderStatus.OK) {
   map.setCenter(results[0].geometry.location);
   var marker = new google.maps.Marker( {
   map : map,
   position : results[0].geometry.location
   });
   var infowindow;
   if (!infowindow) {
   infowindow = new google.maps.InfoWindow();
   }
   infowindow.setContent(address);
   google.maps.event.addListener(marker, ‘click’, function()
   { infowindow.open(map,marker); });
   }
   });
   }
   </script>
6. Save report and run. You will see a live Google Map in your report!
   

- See more at: http://cognosfaq.blogspot.in/2012/12/embed-google-maps-in-ibm-cognos-reports.html#sthash.cy1aTWeB.dpuf

IBM Cognos 10 Bullet Charts - QuontraSolutions

Cognos 10 marks a significant improvement in a new charting engine. Charts are without a doubt, a very powerful tool to deliver information. The chart enhancements made in IBM Cognos 10 focus on improving the effectiveness of this communication. This article discusses the Bullet Chart.

What Are IBM Cognos 10 Bullet Charts?

Bullet charts are variation of bar charts. They compare a featured measure to a targeted measure. A bullet chart features a single primary measure, such as current revenue. It compares that measure to one or more other measures, such as the target or planned revenue, and displays it in the context of a qualitative range of performance, such as poor, satisfactory, or good.

A Bullet Chart Contains:

• A bullet measure, which appears by default as a blue bar in the chart.
• A target measure, which appears as a dash in the chart.
• The color ranges, which help to describe the qualitative state of the featured measure.
• Labels identifying measures and numeric scale.

Let’s take a look at the below chart. The blue bar indicates the actual revenue of a company and the short black bullet indicates the planned revenue. Colored backgrounds signify the qualitative aspect of the measure ‘revenue’. We can customize these colors and the ranges as per end user requirements.

They can be defined with respect to:

1. Percent on axis
2. Any numeric value
3. Mean
4. Percentile
5. Statistical maximum
6. Statistical minimum
7. Query Calculation
8. Layout Calculation

Calculations in Bullet Charts

In the diagram below, the colored backgrounds have been defined according to query calculations. This way we can add even more data and value to this simple and concise chart. This single chart can help us compare measures as well as display additional information.

Bullet charts can also be designed in groups: We can have numerous bullet charts comparing a single measure across a dimension. The diagram below shows a bullet chart describing the qualitative state of revenue with respect to planned revenue, gross profit and production cost for different product lines.

Advantages of bullet charts

• The layout communicates concisely in an easily digested format.
• Bullet Charts are a great alternative to gauge charts.
• The consumer can view the state of metrics against multiple areas of interest.

This is a great new tool in Cognos 10 for companies to enhance their dashboards, supplement their existing charting or replace some of their existing charts

IBM Cognos 10: Changing The Global Report Style Sheet

However sometimes you may find it is difficult to modify the pre-defined report style or there is need to make the changes across all reports. For instance, all your reports will be embedded in your organization’s web application, and one of the requirements is to have all hyperlinks colored in gray and without an underline in order to match the corporate style. In those scenarios, you should consider another approach: globally change report style sheets.

Which files need to be modified?

Similar to most modern web applications, IBM Cognos report styles are mainly defined by four global cascading style sheets. Specifically for IBM Cognos 10, we will modify the GlobalReportStyles_10.css files in the following directory (for windows):

• c10_location/bin
• c10_location/webcontent/schemas
• c10_location/reportstyles /
• c10_location/webcontent/reportstyles

Note: Make the changes in the GlobalReportStyles_10.css file, NOT the GlobalReportStyles.css as stated in the Administration and Security Guide. The guide introduced below, solely written based on this author’s personal working experience, it is provided “as is”. 

Tested Environment

• IBM Cognos 10.1 on Windows 7
• IE 8.0

Step by Step Demo Instructions – Globally Changing the Hyperlink Style

• Log into the IBM Cognos 10 application
• Launch Report Studio
• Select the sample Go Sales (query) package
• Select create a new list report
• Drag and drop Order method type, Product line, Quantity, Revenue, Gross profit from Sales(query) namespace
  
• Group the list by Order method type and Product line
• Add in total rows for Quantity, Revenue, Gross profit measures (see detailed instructions in Report Studio user guide for adding totals if necessary)
• Add a required filter
  [Sales (query)].[Order method].[Order method code]=?ordermethod?
• Save and name it as “Sales by Order Method – Details”
• Create another new report using the same Go Sales (query) package
• Drag and drop Order method type, Quantity, Revenue, Gross profit from the Sales(query) namespace
• Click on the first column and create a drill through to “Sales by Order Method – Details” report, passing order method code to the child report (please reference to Report Studio user guide on how to create a drill through report in Report Studio if necessary)
  
• Run the report and you will notice that by default the drill through link is colored in Blue and Underlined.
  
• Over the next in few steps we will change the hyperlink style to dark gray and not underlined.
  Go to your Cognos application server, locate GlobalReportStyles_10.css file in the c10_location/bin folder:
  Note: This can typically be found in the “C:\Program Files\ibm\cognos\c10\bin” folder in Windows.
• Create a copy of the GlobalReportStyles_10.css file first. Then OpenGlobalReportStyles_10.css file with a text edit such as Notepad.
• Search “hy /* hyperlink */” section(without quotes), you should see below CSS code:
  hy /* hyperlink */
  {
  color: blue;
  text-decoration: underline;
  cursor: pointer;
  }
• Change the color value to “#454545″, change the text-decoration attribute to “none”.
  hy /* hyperlink */
  {
  color: #454545;
  text-decoration: none;
  cursor: pointer;
  }

Hint: You can also add/edit other attributes if necessary

• Save your changes. Repeat step 10 to 17 to make changes for GlobalReportStyles_10.cssfile in other three folders:
  • c10_location/webcontent/schemas
  • c10_location/reportstyles /
  • c10_location/webcontent/reportstyles
• Restart IBM Cognos service (Optional but recommended).
• Re-run the report, notice that the drill through link has been changed to dark gray color (#454545) and non-underlined. When you mouse over any order method type, the hand shape will indicate that it’s a hyperlink/drillable.
  

Now with the above global change, the report author has the flexibility to style the hyperlink in Report Studio either with or without underline and format it to any web supported color!

Linking IBM Cognos Portal Pages through Prompts - QuontraSolutions

To demonstrate this technique, we will explain how to pass your prompt selections from one dashboard portlet to other report portlets within the same dashboard page.

Create the main portal tab

The first step in enabling this functionality is to fuse all the different dashboard pieces together by creating another, master dashboard page that will encapsulate two things:

1. The prompt.
2. The two report outputs.

To do this, start by navigating to the “Global Filters” folder, created in the previous article, in Cognos Connection and click the New Page button.

Name the page “Returns Analysis Dashboard” and click Next.

This time around, our page will have two columns, so we will have to select the appropriate radio button in the Number of columns: section at the top. Then, using the Columns width: drop-downs, set the left column to a width of 20% and the right column to 80%.

Click the Add… link in the left column and add a Cognos Viewer object just as we did in the previous step.

Then, in the right column, add a Multi-page object from the Dashboard entries.

Your portal page should now be laid out like the image below. If this is the case, click Finish.

From Cognos Connection, click the “Returns Analysis Dashboard” page and click the properties icon of the left

column.

Just like we did in Step 2, click the Select an entry… link and navigate to the “Prompt Report” (inside the “Global Filters” folder). Then, in the report’s properties, set it to communicate using the “promptChannel” channel.

Click OK twice.

Now click the properties button of the right column. Click the Select an entry… link and navigate to the “Detail Pages” folder as shown below.

Click OK.

The prompt and reports are displayed side-by-side.

Test out work by making a selection in the prompt.

To create a new tab in Cognos Connection using your new portal page, click the Add to my portal link (circled in red below).

A Tool to Standardize The Look and Feel of Your Reports - QuontraSolutions

Having reports with a common look and feel promotes user adoption as well as trust in the tools they are using. Have you ever noticed that some of your reports looks similar to others but may have some slight formatting variations? Text justified to the left instead of center? Font size or style not quite matching? Table formatting inconsistent? As is often the case with multiple report developers, over long periods of time you may find that your old reports are starting to look significantly different than your new ones.

Many developers use some sort of customized template to begin report development. While that is a great idea and a general best practice, templates may be too generic for every type of report, not to mention a big undertaking to maintain. Imagine having a collection of 50, 100 or even 300 reports made with a common template; Your legal team informs you that the disclaimer in your report footers require an immediate change. How would you go about accomplishing that in a time and cost efficient manner? The short answer is that you can’t!

A layout component reference is a widely underutilized item available in the tools menu. It allows one to create reusable objects within reports then store and maintain these objects all in one convenient location. Changes made to layout components are reflected in every report that refers to them. All of a sudden, changing the disclaimer in the footer for your 300+ reports went from a multi-resource, multi-day error prone low-level development nuisance to a 5 minute quick fix.

Customizing a layout reference is also easy and intuitive. A report developer can choose whether they want their objects to update automatically upon detected changes, or simply ignore future changes altogether. A developer is also able to nest objects, allowing them to create large multi-component objects with complex formatting. Child objects can be individually modified, especially useful in headers where a title may need to be changed per report.

While there is some cost (time) associated to converting all of your old reports to make use of this feature, the future benefit will outweigh the investment in the near term especially as the number of reports continues to grow. When time permits, schedule a time to convert your older reports. Next time you need to make broad changes you’ll be thankful you did.

Below is a screenshot of the Insertable Objects Pane where the Layout Component Reference can be found.

What’s new in IBM Cognos 10 Framework Manager - Quontra Solutions

While Framework Manager didn’t receive the dramatic changes in IBM Cognos 10 that some other areas did, there are still some interesting and useful new features, which might have slipped under the radar with the new release.

The main new features are:

• The ability to create “Durable Models”
• Model Design Accelerator
• Dynamic query Mode

There have also been several minor improvements in the way the IBM Cognos 10 handles SAP BW sources. If that’s an option that you are using, either contact Ironside Group and speak to one of our expert IBM Cognos/SAP consultants or check out the Framework Manager manual for details.

There is one notable feature that is deprecated in Cognos 10. Native Support for CVS and Microsoft Visual Source Save has been removed. Support for these and other code repositories is still available, but in a more generic fashion.

Let’s take a look that the major changes.

Durable Models

This is probably my favorite of the new features. The problem this feature addresses, is the situation where you want to rename Framework Manager objects, such as query subjects, query items, filters or namespaces after reports have already been written. If you change the name in the model and republish the package, the report will error out with references to data items which cannot be found. While there have been some workarounds for a while, this feature provides an elegant solution.

The key to making this work is to start with a new model, and select an unused language or dialect for the design language. I usually choose “English (New Zealand)” (EN-NZ). Then choose standard English (EN) as the active language. Finally, there is a new project property Use Design Local for Reference. You must also set this to true. This causes the report object names to be referenced internally in the report using the design language (EN-NZ or English–New Zealand) rather than the language of the report author (EN or base English). This is similar to authoring reports in multi-language environments, but requires no special actions on the part of the report author.

In this example, we added a data item called EXPENSE_GROUP_EN to the report

The Framework modeler never applied a user friendly name to the column. We add it to the report anyway, and save the report. The modeler then uses Framework Manager to correct the “Active Language” (EN) data item name to a business friendly name, such as “Expense Account Group”. The design language (EN-NZ) object name must not change.Republish the package. By simply running the report with the updated package, the renamed query item name and column heading is now shown in the report. This because the internal object reference in the report is set to the design language (NE-NZ or New Zealand ) name, which did not change. At run time, the report executes using the language of the user, and so displays the updated object name.

Looking at report studio, you will see that the report structure will reference the original EN-NZ description of the object (EXPENSE_GROUP_EN), which did not change. At run time, it will execute using the active language (EN or English base) name and column header, and therefore isolate the name change. Essentially, the report is built in one language (English – New Zealand) and run in a different language (English – base language). But this is all done without any special actions by the report author. And regardless of the language in which the report was authored, the object names are always stored internally using the model design language.

There are a couple of caveats to this method. Most obvious is that once you set the Design language name for an object, you must never change it. Therefore, you’ll need to have meaningful object names from the start. I suggest using the native database column names, where possible, as they are meaningful, not likely to change and are not required to be “business friendly” at this point.

Also, this techniques works for renaming objects only. It will not compensate for major structural changes, such as moving an object from one namespace to another.

Model Design Accelerator

The Model Design Accelerator is a graphical “wizard” which walks you through the design process for a relational model, complete with physical, logical, and presentation layers. Like most wizards, it doesn’t do much that you can’t do on your own, but does make the steps easier, while enforcing good design principles. You have the complete ability to modify your design manually, as usual.

You launch Model Design Accelerator from the Tools menu. The wizard presents you with a blank star schema model. You drag-and-drop items from your data source onto the fact and various dimension tables as needed. Simply add or delete as many new dimensions as you need. You can rename query subjects and items within the wizard, or later. When you are finished, Model Design Accelerator presents you with any warnings, then proceeds to generate the model, complete with the three layers, in separate namespaces.

Interestingly, it doesn’t automatically create a Dimensionally Modeled Relational (DMR) layer. That step is made easier, by directing you to think dimensionally about the model based on the star schema, but you still need to manually apply the various levels to the hierarchies, which can take some thought.

While this tool doesn’t technically add any functionality that didn’t already exist, it’s a nice feature, targeted towards the mid-level or beginner model designer. It will assist the occasional or new model designer think in dimensional terms and develop a solid three-layer model with minimal effort, and it does that admirably.

Dynamic Query Mode

Dynamic Query Mode (DQM) is an option to speed up query performance of certain cubes, by providing in-memory data caching. Dynamic Query Mode is limited to three specific OLAP data sources in the current release, namely TM1 version 9.5.1, Essbase versions 9 and 11, and SAP BW, version 7.1. Furthermore, the specific performance improvements you can expect differ according to the data source. In general terms, they support better null suppression, and improved repeatable performance, which is useful for the typical ad-hoc analysis style of query for which these cubes are often used.

Determinants – The Answer to a Framework Manager Mystery

Determinants can play a crucial role in the overall performance and consistency of your Framework Manager model but remain one of the most confusing aspects of the application to most developers. This article will attempt to end the confusion.

Determinants are used so that a table of one grain (level of detail) behaves as if it were another actually stored at another grain. They are primarily used for dimension tables where fact tables join to dimension tables at more than one level in the dimension. (There are other cases where you could use them, but they are less common and fairly specific situations.)

The Situation

Let’s use the example of a date dimension table with day level grain. If all the fact tables join at the day level, the most detailed level, then you do not need determinants.  But as many of us know from experience, this is not always the case. Fact table are often aggregated or stored at different levels of granularity from a number of reasons.

The Problem

The trouble arises when you wish to join to the dimension table at a level that is not the lowest level. Consider a monthly forecast fact table which is at the month level of detail (1 row per month). A join to the month_id (e.g. 2009-12) would return 28 to 31 records (depending on the month) from the date dimension, and throw off the calculations. Determinants solve this problem.

The SQL

Often when modeling, it’s useful to think about the SQL code you would like to generate. Without determinants, the incorrect SQL code would look something like this.

SELECT

F.FORCAST_VALUE,

D.MONTH_ID,

D.MONTH_NAME

FROM SALES_FORECAST F INNER JOIN DATE_DIM D ON

F.MONTH_ID = D.MONTH_ID

This code will retrieve up to 31 records for each of the sales forecast records. Applying mathematical functions, for example Sum and Count, would produce an incorrect result. What you would like to generate is something along the following lines, which creates a single row per month, AND THEN join to the fact table.

SELECT

F.FORCAST_VALUE,

D1.MONTH_ID,

D1.MONTH_NAME

FROM SALES_FORECAST F INNER JOIN

( SELECT DISTINCT

D.MONTH_ID,

D.MONTH_NAME

FROM DATE_DIM D ) AS D1

ON F.MONTH_ID = D1.MONTH_ID

As shown  above, the trick is to understand which columns in the dimension table are related to the month_id, and therefore are unique along with the key value.  This is exactly what determinants do for you.

Unraveling the Mystery in Framework Manager

Following Cognos best practices, determinants should be specified at the layer in the model in which the joins are specified.

Here we see a date dimension with 4 levels in the dimension, Year, Quarter, Month and day level.

This means we can have up to 4 determinants defined in the query subject depending on the granularity of the fact tables present in your model.  The first three levels, Year, Quarter, Month, should be set to “group-by” as they do not define a unique row within the table and Framework Manager needs to be made aware that the values will need to be “Grouped” to this level. In other words, the SQL needs to “group by” a column or columns in order to uniquely identify a row for that level of detail (such as Month or Year). 

The Day level (often called the leaf level) should be set to “Uniquely Identified”, as it does uniquely identify any row within the dimensional table. While there can be several levels of “group by” determinants, there is typically only one uniquely identified determinant, identified by the unique key of the table. The “uniquely identified” determinant by definition contains all the non-key columns as attributes, and is automatically set at table import time, if it can be determined.

The Key section identifies the column or columns which uniquely identify a level.  Ideally, this is one column, but in some cases may actually need to include more than one column.  For example, if your Year and Month values (1-12) are in separate columns.  In short, the key is whatever columns are necessary to uniquely identify that level.

Using our aforementioned table, the setup would look like this:

The Attributes section identifies all the other columns which are distinct at that level.  For example, at a month_id  (e.g. 2009-12) level , columns such as month name, month starting date, number of days in a month are all distinct at that level. And obviously items from a lower level, such as date or day-of-week, are not included at that level.

Technically, the order of the determinants does not imply levels in the dimension. However, columns used in a query are matched from the top down which can be very important to understanding the SQL that will be generated for your report. If your report uses Year, Quarter and Month, the query will group by the columns making up the Year-key, Quarter-key and Month-key. But if the report uses just Year and Month (and not the Quarter) then the group by will omit the Quarter-key.

How Many Levels Are Needed?

Do we need all 4 levels of determinants? Keep in mind that determinants are used to join to dimensions at levels higher than the leaf level of the dimension. In this case, we’re joining at the month level (via month_id). Unless there are additional joins at the year or quarter level, we do not strictly need to specify those determinants. Remember that year and quarter are uniquely defined by the month_id as well, and so should be included as attributes related to the month, as shown.

The Result

Following these simple steps the following SQL will be generated for your report. The highlighted section is generated by the determinant settings. Notice how it groups by the Month_ID, and uses the min function to guarantee uniqueness at that level.  (No, it doesn’t trust you enough to simply do a SELECT DISTINCT.)  The second level of group by is the normal report aggregation by report row.  So the result is that the join is done correctly, which each monthly fact record joined to 1 dimensional record at the appropriate level, to produce the correct values in the report.