Force Cognos portal to give more feedback to users

I don't see the numbers for yesterday. Has the data been loaded? Is all OK? These questions start getting asked every time data load goes wrong. And they pop up in our users heads every day before they run their reports. We have to shape up and be more proactive in giving feedback to our users so we get less questions and get bonus points in user-friendliness.

One way to give data load status feedback is to create a special report (maybe even push it out as the starting portal tab, if you dare). However that would not be cool at all. Having to run a report forces the user to go and look for this information. In real life this type of information can only be consumed if it is in the users sight at all times and without any extra effort.

Currently there is no built-in functionality helping dedicate a small piece of the portal to provide a system health-check report. It is time to hack the Cognos portal! Amm.. I mean customize.

So, how about we force Cognos to show a small report in the top most toolbar, just like the screenshot above?

Start with the report

It is a bit up to you how you make your report and what logic you choose to base the data load status on. My favorite is when a table in the DB stores a row for each of my applications. I use a status value between 0 and 2. 

0 = Succeeded; 

1 = In Progress;

2 = Failed.

The statuses should be updated via which ever ETL tool you are using.

If I have a bunch of applications listed in my table, I need to aggregate the result to show my total status. For that Maximum aggregation can be used. So if any of the rows in this table return 2, then I set my total status to Failed, if max=1, then something is loading, and only if it is 0 then all is fine and I don't need to double-check what is going on with my data.

You can also take it a step further and join the application status table with security group table, that way identifying which AD groups have access to which applications in your BI environment. Together with this one(Application)-to-many(AD groups) join and the row-level security of Framework Manager model you will be only showing data load status which is RELEVANT to every individual user. A very important feature, IMO.

Your filter function to show only rows relevant to users account would look similar to this:

[Model].[App Status].[UserGroupColumn] in (#CSVIdentityNameList()#)

It basically checks if row returned from the database is within the credential array of current AD user. Read more about row level security here.

One thing to note about the layout of this report is that you can't have any headers or footers. And, obviously, we are working with very limited height, so be prepared to squeeze your text quite a lot.

Prepare the portal

OK, so we have our report. Time to place it into the header of Cognos portal. We are able to insert elements directly into different areas of the portal by modifying Istall_path\templates\ps\portal\system.xml on all application servers. Custom OEM header section has to contain:

<style styleFolderName="YourThemeName">

          <table style="">

                             <tr>

                             <td class="headerTitle" style="padding-right:2px;white-space:nowrap"> Your Business Intelligence Portal </td>

                             <td>

                                                          <div id="load_status"></div>

                             </td>

                             </tr>

          </table>

</style>

<div> with ID “load_status” is the container that will hold the report output. The reason why we are not referencing the report directly is that it would kill the service and nothing would work. Adding a div element was as far as this customization agreed to go while I was figuring this out.

Once you have added the div, restart your cognos application servers and they are ready.

Inject status report into the header

When all is up and running again we can finally pick up our report and put it into the portal header.

Find the url of your report and make sure that all of these parameters are included in the end of it:

&run.outputFormat=&run.prompt=false&cv.header=false&cv.toolbar=false","","width=600,height=500,menubar=no,scrollbars=no,toolbar=no

This will turn off all the toolbars and buttons that surround every cognos report. Perfect, since we don't have space for all of that anyway.

Now we need a way to put the output of this report into our header. JavaScript is the magical solution for the task.

function changestatus () {

document.getElementById("load_status").innerHTML = "<iframe src='PLACE YOUR REPORT URL HERE' width='200px' height='36px' frameborder ='0' marginheight='0' marginwidth='0' style=\x22padding=0;margin=0;background-color=#50a443;overflow:hidden;\x22 scrolling=\x22no\x22></iframe>";

}

if(window.attachEvent) {

    window.attachEvent('onload', changestatus);

} else {

    if(window.onload) {

        var curronload = window.onload;

        var newonload = function() {

            curronload();

            changestatus();

        };

        window.onload = newonload;

    } else {

        window.onload = changestatus;

    }

}

These two functions should be placed into gateway server by modifying the banner.js file located in install_path\webcontent\ps\portal\js

This JavaScript code replaces the div element which we added to portal header via OEM customization with an Iframe container. The iframe has our report as it's source. As soon as the page is loaded, JavaScript injects this iframe, which in turn runs our little report. 

The data load status is loaded after everything else arrives on the page and via separate process, so it does not inflict any portal performance degradation.

BAM

And there you have it! Every user can now see their own custom data load status report without having to go searching for it anywhere. You can add drill-through functionality to the header report- pop up a new window which shows details behind the aggregate value.

Bonus

As a little bonus to the header report, you might want to add a HTML element somewhere within the content. It should contain this JavaScript which would force a reoccurring reload of the page. Something like this:

<script type="text/javascript">

function refresh (timeoutPeriod){ 

refresh = setTimeout(function(){window.location.reload(true);},timeoutPeriod); 

} 

window.onload = refresh(300000);

</script>

Now your users not only have a customized report for data load status, but they don't have to refresh it either!

Have fun!

M.