Defining GMail as the SMTP Mail Server in Cognos Connection

In anticipation of some upcoming articles, I needed to set up an email provider. Because it’s so ubiquitous, and because I’m lazy, I’m going to use GMail as my SMTP server. Here’s what to do.

First, make sure you enable IMAP in the GMail settings. Click on the gear icon, settings, Forwarding and POP/IMAP, Enable IMAP, and Save Changes.

gmail settings

Now on GMail settings screen there a configuration instructions links for your applications, but for some reason Cognos isn’t listed. So here they are, in Cognos Configuration, open the Notification settings and set the following:

SMTP mail server:
Account and password: Google account credentials
Default Sender: your own email
SSL Encryption: True

cognos connection. Now remember, you should use YOUR email, not mine. Mine is reserved.

Restart Cognos.

Now let’s set up a quick report to test the email.
quick and dirty report in Query Studio. A true masterpiece. Also, did I really need to tell people to restart? That should be obvious, right?

And run it…

Cognos report run page. I'm actually taking these screenshots as I'm writing the article. Hope it works.

Fingers crossed…

It works! I was actually worried for a moment. How embarrassing would it be if it DIDN'T work? I would have had to scupper the entire article! And since I have so many planned, this would have been horrible.

Obviously this shouldn’t be used for a production environment. There are also a few Google set limitations, the biggest of which is a 99 email limit per day – so no crazy burst jobs. This is really good for a quick and dirty example.

If I find the time this year (no promises), I’ll be writing about setting up dynamic email subject lines, and different report outputs in a single email. Another article I’m planning is a combination of the data entry method and bursting.

Different Drillthroughs for Graph Elements

I recently received an interesting problem. A multi bar graph needed to have drillthroughs pointing to separate reports. The requirement is to have it seamless for the end-user, no transitional screens. If the user clicks on the revenue bar, it needs to go to the revenue report. If they click on the planned revenue bar, it goes to the planned revenue report.

As the product is currently built, drillthroughs are defined on the graph level, not a measure level. Let’s take a look at the actual HTML being generated:


  dttargets="<drillTarget drillIdx=\"2\" label=\"Planned revenue\"/><drillTarget drillIdx=\"3\" label=\"Revenue\"/>" 
  title="Year = 2010 Revenue = 914,352,803.72" 
  coords="157, 309, 157, 174, 118, 174, 118, 310, 157, 310" 
  class="dl chart_area" 

In this example, I have a chart with two bars. In the area of each bar, the dttargets is defined with both drills. The drills themselves I’ve named the same as the data item of the measure. We can then use JavaScript to extract the dttargets string, match the label of the drill to the data item name, and place the correct one in there.

 * This will loop through every chart and replace multiple drill definitions with one. 
paulScripts.fixChartDrills = function(chartName){
  var oCV = window['oCV'+'_THIS_']
  , areas = paulScripts.getElement(chartName).parentNode.previousSibling.getElementsByClassName('chart_area')
  , areasLen = areas.length
  , areaDataItemName
  , drills=[]
  , dtargets=[]

for (var i=0;i<areasLen;++i){
  if(!areas[i].getAttribute('dttargets')) continue;

  drills = areas[i].getAttribute('dttargets');
  dtargets =drills.split('>');

  for (var j=0;j<dtargets.length;++j){
    var regexp = /label...(.+?)."/g;
    var match = regexp.exec(dtargets[j]);

    if(match&&match[1] == areaDataItemName) areas[i].setAttribute('dttargets',dtargets[j]+'>');


First we’re finding the chart that we want to do this on, and finding the area map. We loop through the areas, skipping the ones that don’t have any dttargets (like the legend or labels).
For each area with a dttarget, we get the source data item name. Fortunately for us, there’s a useful Cognos JavaScript function to do it! Then a little hackey JS magic to get the label for each individual dttarget we can finally match and replace the dttargets attribute.

Let’s see it in action!

Now it’s very important that the drillthroughs have exactly the same names as the data items. If they don’t do that, this script won’t work – but of course that wouldn’t stop you from using different logic. I built this in Cognos 10.2.2, but I have no reason to think it’s not backwards compatible. The full JavaScript, including the paulScripts.getElement can be found in the report XML below.

separateDrills.txt (315 downloads)

A few Report Studio tricks

Building reports in Cognos can be a fairly arduous process. A mistake early on in the development can cause some headaches later on. Quickly and efficiently building reports is best, but what if you have to go back and fix something?

Fixing Extended Data Items

One of the most common problems I run into is people using extended data items to build a dimensional report. Once the data item has been created, it can’t easily be changed. What if you need to change the level, or even hierarchy, the item is based on? You’ll need to delete and rebuild it from scratch. Instead it would be easier to convert it to a full expression.

There is a function that allows this, but it is normally inaccessible. We can easily reveal it by using the developer toolbar in Report Studio. I prefer using Firefox, that’s the browser in the animation. Simply copy the following code into the console and run it.


Convert to V5 Data Items

As you can see in the animation, the option magically appears under tools, and will convert every extended data item into a normal data item. It’s worth mentioning that this is not supported by IBM, and it is not possible to convert data items back into Extended Data Items, so tread carefully with this.

Avoid Extra Master/Detail relationships by using list headers

I’m not a big fan of using section functionality. It works by creating another list, a list in a list, connected with master/detail. In some cases Cognos can handle this efficiently; with master detail optimization and efficiently built queries. In most cases however, Cognos will generate an extra query for each separate detail.

How can we avoid this? By careful grouping and setting of list headers/footers, we can replicate the appearance of a sectioned report. In the image below, we have two identical looking lists. One is using the default section function, and one is some very clever formatting. Can you guess which is which?

List Headers vs Sectioning

Year grouped, removed from the list, headers and footers set, list header set to “start of details”, and extra rows/fields included for the padding effect. All small details that go a long way into making things look right.

The report xml is attached below, but can you figure it out yourself?

Caching charts to speed up run time

This is actually something I’m not too proud of. At my current client we have a few reports with embedded graphs. Hundreds upon hundreds of embedded graphs. Each graph can take around 300 ms to render. This increases the runtime of the report from 30 seconds, without graphs, to around 9 minutes. How can we get around this? Well, in this case, the graphs were fairly simple – a colored bar showing 0-100% against a light gray background. There are a few different variations – a 12 pixel tall vs a 6 pixel tall, and depending on the type it can be blue, green, or purple. In total there were 606 possible graphs (it’s actually 602 as the 0% graphs all look identical, but let’s not get technical here).

To start, the names of the graphs had to be consistent. Bar55B.png would be a bar set to 55% and is colored Blue. We can then use an image item with the source set to layout expression. ‘\images\charts\Bar’+number2string(floor([Measure]*100))+’B.png’. Each row would generate the image tag, and wouldn’t have to take the time to actually build the graph image. But how can we generate those images?

The easiest way is to use a numbers dimension. Don’t have a numbers dimension? Use a date dimension – you just need to massage a date dimension to work the way you want. For example, on the samples I’m using _days_between([Sales (query)].[Time].[Date],2010-01-01) and filtering that <=100. Build the graph in each row, and run it to make sure. The output should look like this: graphs

But saving each of those images would be tedious, so how can we automate it? Modern web browsers have a number of options. I’m using an addon called Save Images which will automatically save each image from the tab to a specified folder. This addon will number the images starting at 1, so it’s important to sort the list so the 0 is at the very end. This way you can modify Bar101 to Bar0, instead of having to subtract 1 from each graph. The addon saves and names the images in the order they appear in the output, so it works well for this purpose.

save images

Once the images have been saved, we need to do a bit of post processing.

First, rename Bar101 to Bar0. Now let’s notice something interesting the height of the image is 16, but we need 6 or 12. To fix this, we can use a batch image processor. For this we can use an application like XnConvert. With XnConvert we can modify the size and colors of the graph. The color picker offers a number of ways of selecting the desired colors, including a color picker.

Simply select the color to change and the color you want.
xnconvert colors

Next we resize
xnconvert resize

Finally, renaming:
xnconver rename
This will take the original name, BarN, append GL (green large).

And when we press the convert button…
xnconver rename

We can repeat, very quickly, for every variation we need. In my case it only took 6 rounds of changes, about a minute in total.
xnconvert done

Now instead of relying on Cognos to generate hundreds (if not thousands) of graphs, we simply use an image item with the source set to a report expression.
‘..\images\Bar\ipr\Bar’+number2string(floor([Query1].[Percentage]*100))+[Color letter] +[Large or Small]+’.png’

The difference in runtime is phenomenal. First we don’t have a master/detail relationship any more. So that’s an additional N db connections that aren’t being run (yes, I know M/D optimization negates that, but that only works in DQM and it’s spotty). And most importantly, that’s thousands of images Cognos doesn’t have to generate (300 ms * 1500 images = 7 minutes).

section-or-formatting.txt (272 downloads)
graphs.txt (211 downloads)