Capturing Authentication Properties in Cognos 8

If you’ve done much metadata modeling in IBM Cognos’s Framework Manager, you’re probably aware that you can use attributes of the user from the authentication source (Active Directory, LDAP, etc.) to enhance security, through the use of session parameters and macro functions.  Most authentication sources contain a wealth of information about the user.  Common attributes include default name, last name, surname, phone number, and email address,  All of this can be accessed in Framework Manager, or even in a Report Studio report.

This is great if you need to know something about the current user, but what if you need to know details about all of your users?  A couple of examples of where this would be useful are in audit reports (listing report usage), or for email bursting.  These session parameters are of no use then, because they only exist while the user is logged in, and only for that session.

One option could be to extract the LDAP (export to a text file) and then transform and load it into your database.  If you have a cooperative network security administrator, this may be the cleanest way to go.  But if this is not practical (for whatever reason) then you could try capturing the data yourself as users log into Cognos 8.

The basic technique involves creating a database report that all of the users automatically run at login (I chose to use a “Warehouse Status Report”). Then using the Open Session Commands in the data source, running a stored procedure (passing in session parameters) that updates information about the user.

Add Custom Properties to Session Parameters

The default session parameters may work for you (if so, skip this section). But if not, Cognos 8 allows you to add custom properties and map them to your LDAP attributes.  This is done in IBM Cognos Configuration on the application server.

  1. Start IBM Cognos Configuration.
  2. Navigate to Local Configuration -> Security -> Authentication -> (your authentication source).
  3. Scroll down to the bottom of the list and edit the Custom Properties.
  4. Add any custom properties you may need, including the name of the LDAP attribute that it maps to.
  5. Click OK to save the custom properties.
  6. Click the Save button to save your configuration changes.
  7. Stop, and then restart Cognos 8 to apply the changes.

Storing the User Information

The first thing you’ll want to do is create a table that will hold your user information.  I call my table User_Profiles. The example here is in Oracle 10g, but it should be easy to adopt this to your database.

CREATE TABLE USER_PROFILES(
   USER_NAME varchar2(256) PRIMARY KEY NOT NULL,
   DEFAULT_NAME varchar2(256),
   GIVEN_NAME varchar2(256),
   SURNAME varchar2(256),
   DEPARTMENT_NAME varchar2(256),
   OFFICE_NAME varchar2(256),
   STATE_NAME varchar2(256),
   BUSINESS_PHONE varchar2(256),
   EMAIL varchar2(256),
   RUN_LOCALE varchar2(256),
   LAST_LOGIN_DATETIME date
);
CREATE UNIQUE INDEX USER_PROFILES_PK ON USER_PROFILES(USER_NAME);

Then you will create a stored procedure that takes in all the session parameters and performs an insert into the table if the user does not exist, or an update if the user does exist (sometimes  referred to as an “upsert”).  I called mine UPSERT_USER_PROFILES.

PROCEDURE UPSERT_USER_PROFILES (
     p_USER_NAME USER_PROFILES.USER_NAME%type,
     p_DEFAULT_NAME USER_PROFILES.DEFAULT_NAME%type,
     p_GIVEN_NAME USER_PROFILES.GIVEN_NAME%type,
     p_SURNAME USER_PROFILES.SURNAME%type,
     p_DEPARTMENT_NAME USER_PROFILES.DEPARTMENT_NAME%type,
     p_OFFICE_NAME USER_PROFILES.OFFICE_NAME%type,
     p_STATE_NAME USER_PROFILES.STATE_NAME%type,
     p_BUSINESS_PHONE USER_PROFILES.BUSINESS_PHONE%type,
     p_EMAIL USER_PROFILES.EMAIL%type,
     p_RUN_LOCALE USER_PROFILES.RUN_LOCALE%type,
     p_LAST_LOGIN_DATETIME USER_PROFILES.LAST_LOGIN_DATETIME%type
     ) AS
BEGIN
     MERGE
          INTO USER_PROFILES
          USING DUAL ON (USER_NAME = p_USER_NAME)
     WHEN MATCHED THEN
          UPDATE SET
               DEFAULT_NAME = p_DEFAULT_NAME,
               GIVEN_NAME = p_GIVEN_NAME,
               SURNAME = p_SURNAME,
               DEPARTMENT_NAME = p_DEPARTMENT_NAME,
               OFFICE_NAME = p_OFFICE_NAME,
               STATE_NAME = p_STATE_NAME,
               BUSINESS_PHONE = p_BUSINESS_PHONE,
               EMAIL = p_EMAIL,
               RUN_LOCALE = p_RUN_LOCALE,j
               LAST_LOGIN_DATETIME = p_LAST_LOGIN_DATETIME
     WHEN NOT MATCHED THEN
          INSERT (
               USER_NAME,
               DEFAULT_NAME,
               GIVEN_NAME,
               SURNAME,
               DEPARTMENT_NAME,
               OFFICE_NAME,
               STATE_NAME,
               BUSINESS_PHONE,
               EMAIL,
               RUN_LOCALE,
               LAST_LOGIN_DATETIME
          )
          VALUES(
               p_USER_NAME,
               p_DEFAULT_NAME,
               p_GIVEN_NAME,
               p_SURNAME,
               p_DEPARTMENT_NAME,
               p_OFFICE_NAME,
               p_STATE_NAME,
               p_BUSINESS_PHONE,
               p_EMAIL,
               p_RUN_LOCALE,
               p_LAST_LOGIN_DATETIME
          );
          COMMIT;
END;

(Note, this stored procedure uses Oracle’s  relatively new MERGE statement, but there are a number of ways to perform an upsert)

Create a Data Source That Calls the Stored Procedure

Of course, you could use any data source for the report, but I chose to create a new data source just for the login report.  The reason for this is, I didn’t want to update the USER_PROFILE table every time I ran a report, just when they first logged into Cognos 8.

  1. Open up Cognos 8 and launch IBM Cognos Connection.
  2. Click on the Configuration tab.
  3. Create the data source that will connect to the database containing the data for your login report.  This assumes that this is the same database that will hold your USER_PROFILES table and UPSERT_USER_PROFILES procedure.
    (You’re on your own here)
  4. After you’ve created the data source, edit the properties of the data source.
  5. Click on the Connection tab
  6. Edit the Open session commands (more on this below*)
  7. Add a command block that will call the stored procedure, passing in the session parameters
     <commandBlock>
         <commands>
              <sqlCommand>
                   <sql>BEGIN DW.UPSERT_DW_USER_PROFILES(#sq($account.personalInfo.userName)# , #sq($account.defaultName)#, #sq($account.personalInfo.givenName)#, #sq($account.personalInfo.surname)#, #sq($account.parameters.departmentName)#, #sq($account.parameters.officeName)#,  #sq($account.parameters.stateName)#, #sq($account.personalInfo.businessPhone)#, #sq($account.personalInfo.email)#, #sq($runLocale)#, TO_DATE( substr( #sq($current_timestamp)#, 1, 19), 'YYYY/MM/DD HH24:MI:SS' ) ); END;
                   </sql>
              </sqlCommand>
         </commands>
    </commandBlock>

Why use the Open Session command as opposed to the Open Connection command?  Cognos 8 shares connections among it’s users.  One user may create the connection, but there could be hundreds of reports using that connection before it times out through inactivity.  So if you were to use the Open Connection command, only that first user who started the connection would be recorded.

Create the Package and the Login Report

If you’ve read this far, I assume you know how to create a package, add a query subject to it and develop a report in Report Studio or Query Studio.  The important thing to remember her is that this report should be the only place where you use the data source we just created.

Create a Page

I’m also assuming you know how to create pages in Cognos 8.  Put whatever you need into the page, but be sure to add a Cognos Viewer portlet to the page, and set the portlet’s Entry to the Login report you created.

Make Sure They See the Login Report

There are a number of ways to make sure that your users see the login report (none of them perfect). But if this is a new Cognos 8 installation, it’s easy to set the page to be the default tab.  This way, each new user will see the page when they first log in.

  1. Open up Cognos 8 and launch IBM Cognos Connection.
  2. Click on the Security tab.
  3. Click on the Cognos namespace.
  4. Click the Edit Default User Profile button.
  5. Click the Portal Tabs tab.
  6. Add the page you created to the list of tabs.
  7. Click Modify the sequence, and move it to the top.

If this is an existing installation, then the above method will not work as it only applies to new users.  Alternatives include:

  • Sending a broadcast email to the users with a link to the page.
  • Manually going through the authentication source namespace and changing each user.
  • I have also heard of third-party metadata managers that can change user portal pages in bulk (sorry, but I have no experience with these).
Advertisements

How To Display Data From a Cognos 8 Report in Google Maps

IBM’s Cognos 8 Report Studio already has built-in maps that allow you to create geographic representations of your data, but they are not without limitations, the most notable being that there are only about a half dozen maps included.   The world-level maps contain every location, but not down to the level of granularity you often need (zip-code level for example).  There are U.S.-only maps that go down to the zip code, but they don’t include U.S. Posessions (ex: Puerto Rico) You can develop custom maps, but the software required to generate the MapInfo files is quite expensive and outside of the range of many project budgets.

One solution is to integrate your reports with Google Maps via the Google Maps API.  The API is capable of marking any location in the world, by a variety of means, including zip-codes, city names, or even latitude and longitude.  The interactivity is about as intuitive as it gets, and you can add markers to highlight specific map points or polygons to outline and color areas of any shape (a state for example)

This article follows-up on the example provided by Rayudu Vecha and Sridhar Munireddy in their developerWorks article; Integrate Google Maps with Cognos 8.  In it, they explain how to call the Google Maps API from within a Cognos 8 Report Studio report and insert hard-coded markers.  Here, I will show you how to expand that to dynamically populate markers on top of the Google Map.

The basic technique is fairly simple.  I take the single HTML Item that Vecha and Munireddy use (enhanced a bit for my particular needs) and then divide it into four separate HTML Items, with one of them nested inside a repeater object to handle an unknown number of rows from a query. Optionally, the third HTML can be embedded inside a singleton to allow it to retrieve from the database a value that will be put into a JavaScript variable.

When the report is run, the four separate HTML items act as a single block of HTML code, but displayed within the Cognos 8 report viewer.

What You’ll Need:
  • A Cognos 8 Business Intelligence server
  • A Google Maps API Key  for the Cognos 8 BI server’s domain (directions for obtaining this are below)
  • Basic development skills for Cognos 8 Report Studio
  • A fundamental understanding of JavaScript
  • A database that contains the latitude and longitute of the locations you wish to mark on the map.

The Steps:
  1. You are going to need a Google Maps API key.

    1. Go to http://code.Google.com/apis/maps/signup.html
    2. Enter the domain name at which you access Cognos 8 (ex: http://cognos.mycompany.com)
    3. Click the Generate API Key button.This should create a key that looks similar to this:

      ABQIAAAAN28cRTOPRc8HwC3fHtsmKhTtWTE_GChODK0XVX0wNep29RbXARRXke_fSEx94NZZnhOisv8S0tZqVg
    4. Copy the API key displayed and save it for future use.
    5. If you can, store this key somewhere in your reporting database.  This will help if you plan to later migrate the report to another server (ex: from development to production)
  2. Open Report Studio and create a new report using the blank template.
  3. From the insertable objects toolbox, add a Repeater object to the body of the report.
  4. Right-click on the new repeater object and select Go To Query.
  5. Add to the query the Data Items you want to display.This data will be used to assemble a Javascript array (in a subsequent step).You will also need to include two columns containing the latitude and longitude of each marker.

  6. If you plan to have more than one row in the query (i.e. more than one marker on the map) you will need a column that contains a comma for all but the first row.  This comma will be used as a separator for the elements in the Javascript array.  To Do this:
    1. Add a Data Item to the query named Row. Set the expression to:

      rank ([Location])

      (replacing “[Location]” with a column you’ll be sorting on)

    2. Add another Data Item named ElementSeparator. Set the expression to:

      IF ( [Row] =  1 ) THEN
      ( ” )
      ELSE
      ( ‘, ‘ )


  7. Return to the page (Page1).
  8. Click on the repeater and edit the Data->Properties.Click check-boxes by each data item that you will want to use.  This will allow you to use them in a report expression.

  9. Change the repeater’s Data->Rows Per Page to 99999(otherwise, Report Studio will try to create a new page for every 20 rows in your repeater)

  10. You may also need to define the Data->Grouping and Sorting of the repeater.
  11. Add an HTML Item immediately to the left of the repeaterThis will contain the start of the Javascript

    1. The source type will be the default: Text
    2. Set the HTML text to the following:
      <html>

      <body>

      <head>

      <title>Static -> Dynamic Google Map with Markers</title>

      </head>

      <style type=”text/css”>

      .message {

      font-size: 80%;

      padding: 2px;

      font-weight: bold;

      text-align: center;

      width: 550px;

      height: 450px;

      background-color: #ffcc00;

      border: 1px solid white;

      position: relative;

      }

      </style>

      <script type=”text/javascript”>

      var locations = [


  12. Add a second HTML Item inside the repeaterThis will be the assembly of the array containing your data

    1. Set the source type to: Report Expression
    2. Set the report expression to the following, modifying it to fit your data:

      [Query1].[ElementSeperator] + ‘{name: ”’ +

      [Query1].[Location] + ”’, url: ”’ + [Query1].[URL] +  ”’, headcount: ‘ + number2string ( [Query1].[Headcount] ) + ‘, hires: ‘ + number2string ( [Query1].[Hires] ) + ‘, terminations: ‘ + number2string ( [Query1].[Terminations] ) + ‘, promotions: ‘ + number2string ( [Query1].[Promotions] ) + ‘, lat: ‘ + number2string ( [Query1].[Latitude] ) + ‘, lng: ‘ + number2string ( [Query1].[Longitude] ) + ‘} ‘

      The resulting data would look something like this (line breaks and indentation added for readability):

      {
      name: ‘Location 1’,
      url: ‘http://url.for.location.1&#8217;,
      headcount: 123,
      hires: 1,
      terminations: 2,
      promotions: 3,
      lat: 38.88944,
      lng: -77.035341

      } ,

      {

      name: ‘Location 2’,

      url: ‘http://url.for.location.2&#8217;,

      headcount: 456,

      hires: 7,

      terminations: 8,

      promotions: 9,

      lat: 43.878996,


      lng: -103.459811

      }



  13. Immediately to the right of the repeater, add a third HTML Item
    (Note: if you stored the API Key in your database, add a Singleton with a query that retrieves it and put the HTML Item inside the singleton, and use a report expression to define the text)

    1. The source type will be the default: Text
    2. Set the HTML text to the following:

      ];

      var strMapKey = ‘

      ABQIAAAAN28cRTOPRc8HwC3fHtsmKhTtWTE_GChODK0XVX0wNep29RbXARRXke_fSEx94NZZnhOisv8S0tZqVg‘;

      (substituting your own API Key)

  14. Add a fourth HTML Item immediately to the right of the third one.This will contain the end of (and the bulk of) the Javascript

    1. The source type will be the default: Text
    2. Set the HTML Text to the following:

      var map = null;

      var bounds = null;

      var currentMarker = null;

      var mapDiv = null;

      var containerDiv = null;

      var clickedX = 0;

      var clickedY = 0;

      var isLoaded = false;

      /**

      * Called after script is asynchronously loaded in.

      * Creates the GMap2, GMarker objects and performs actions according to

      * what the user did to trigger the map load (search, zoom, click etc).

      */

      function loadMap() {

      if (GBrowserIsCompatible()) {

      mapDiv.style.background = ‘#fff’;

      mapDiv.style.cursor = ”;

      map = new GMap2(mapDiv, {logoPassive: true});

      bounds = new GLatLngBounds();

      for (var i = 0; i < locations.length; i++) {

      bounds.extend(new GLatLng(locations[i].lat, locations[i].lng));

      }

      var latSpan = bounds.toSpan().lat();

      map.setCenter(bounds.getCenter(), map.getBoundsZoomLevel(bounds));

      map.addControl(new GLargeMapControl());

      //map.setZoom(2);

      var mapControl = new GMapTypeControl();

      map.addControl(mapControl);

      var newBounds = map.getBounds();

      var newLatSpan = newBounds.toSpan().lat();

      if (latSpan/newLatSpan > .90) { map.zoomOut(); }

      for (var i = 0; i < locations.length; i++) {

      var marker = createMarker(i);

      var latlng = marker.getLatLng();

      var pixel = map.fromLatLngToDivPixel(latlng);

      if (Math.abs(pixel.x -clickedX) < 12 && Math.abs(pixel.y -clickedY) < 20){

      //GEvent.trigger(marker, ‘click’);

      }

      map.addOverlay(marker);

      map.setCenter(bounds.getCenter(), map.getBoundsZoomLevel(bounds));

      }  } }

      function zoomToAll() {

      map.setCenter(bounds.getCenter(), map.getBoundsZoomLevel(bounds));

      }

      /**

      * Creates a marker for the given location.

      * @param {Number} ind

      * @return {GMarker}

      */

      function createMarker(ind) {

      var value= 103037;

      var location = locations[ind];

      var marker = new GMarker(new GLatLng(location.lat, location.lng));

      /**

      * When the user clicks on the marker while zoomed out, it zooms in (in case there is

      * another marker nearby that would not be visable when zoomed out)

      * When they click on the marker while zoomed in, it opens an Info Window that displays

      * details about the location

      */

      GEvent.addListener(marker, ‘click’, function() {

      marker.html = [‘<b>’, location.name, ‘</b>’,

      ‘<ul>’,

      ‘<li>Headcount: ‘, location.headcount, ‘</li>’,

      ‘<li>New Hires: ‘, location.hires, ‘</li>’,

      ‘<li>Terminations: ‘, location.terminations, ‘</li>’,

      ‘<li>Promotions: ‘, location.promotions, ‘</li>’,

      ‘</ul>’,

      ‘<a  href=”‘ + location.url + ‘”  target=”_blank “> Detail Listing</a>’].join(”);

      currentMarker = marker;

      if ( map.getZoom() <= 12 ) {

      map.setCenter( marker.getLatLng(), 15 );

      }

      else {

      marker.openInfoWindowHtml(marker.html);

      }

      });

      return marker;

      }

      /**

      * Formats location info into a URL-friendly version for maps url.

      * @param {Object} location

      * @return {String}

      */

      function formatAddressForMaps(location) {

      var address = location.street + ‘ ‘ + location.city + ‘ ‘ + location.state + ‘ ‘

      + location.zip;

      return escape(address.replace(‘ ‘, ‘+’));

      }

      function _cel(elementType, id) {

      var element = document.createElement(elementType);

      element.id = id;

      return element;

      }

      /**

      * Loads in the Maps API script. This is called after some sort of user interaction.

      * The script loads asynchronously and calls loadMap once it’s in.

      */

      function loadScript() {

      if (!isLoaded) {

      isLoaded = true;

      var div = document.createElement(‘div’);

      div.className = ‘message’;

      div.innerHTML = ‘Loading…’;

      div.style.left = (800/2 – 53) + ‘px’;

      div.style.top = 800/2 + ‘px’;

      mapDiv.appendChild(div);

      var script = document.createElement(‘script’);

      script.type = ‘text/javascript’;

      script.src = ‘http://maps.google.com/maps?file=api&v=2&#8217; +

      ‘&async=2&callback=loadMap&key=’ + strMapKey;

      document.body.appendChild(script);

      }

      }

      /**

      * Sets up the gadget by setting CSS and click events.

      */

      function loadMapGadget() {

      containerDiv = document.getElementById(‘container’);

      mapDiv = document.getElementById(‘map’);

      mapDiv.onclick = function (e) {

      clickedX = (window.event && window.event.offsetX) || e.clientX;

      clickedY = (window.event && window.event.offsetY) || e.clientY;

      loadScript();

      };

      loadScript();

      mapDiv.style.cursor = ‘pointer’;

      var urlString = [‘http://maps.google.com/staticmap?markers=’%5D;

      var markerString = [];

      for (var i = 0; i < locations.length; i++) {

      markerString.push(locations[i].lat + ‘,’ + locations[i].lng + ‘,red’);

      }

      urlString.push(markerString.join(‘|’));

      urlString.push(‘&size=800×600’);

      urlString.push(‘&key= ‘ + strMapKey);

      mapDiv.style.background = ‘url(\” + urlString.join(”) + ‘\’)’;

      }

      </script>

      </head>

      <body onload=”loadMapGadget();”>

      <div id=”container”>

      <div id=”map” style=”width: 800; height: 600; overflow:hidden”></div>

      </div>

      </body>

      </html>



  15. Save and test the report.

Conclusion:

Integrating Cognos 8 Report Studio data into Google Maps might not be quite as straight-forward as using the built in maps, but it’s not too difficult and the results are sure to impress your report consumers.  This is also a great starting point for learning to integrate Cognos 8 with other web services.

Finding Shortcuts That Point To A Report In Cognos 8

If you’ve worked with Cognos 8 for any length of time, you’ve undoubetedly had it happen; You’re changing a report and need to know all the shortcuts that point to it within the Cognos Connection portal.

To date, there’s no way to find this within Cognos Connection, however a little digging into the content store database should do the trick.

This technique will get you the names of the shortcuts, but not the locations in Cognos Connection.  You’ll need to do a search to locate the shortcuts.

The Steps:

  1. Create a new shortcut pointing to the target report in question and give it a unique name (ex: My_Temp_Shortcut)
  2. Execute the following SQL Statement against the content store database:
SELECT
  ONB1.NAME
FROM
  CMOBJNAMES_BASE ONB1
  INNER JOIN CMOBJPROPS6 OP1 ON ONB1.CMID = OP1.CMID
WHERE
  OP1.TARGET IN
  (
    SELECT OP2.TARGET
    FROM CMOBJPROPS6 OP2
         INNER JOIN CMOBJNAMES_BASE ONB2 ON ONB2.CMID = OP2.CMID
    WHERE ONB2.NAME =  'My_Temp_Shortcut'
  );