There are many times when an end user might want to see the same data in multiple formats. A case in point, a manager wanted a detailed weekly PDF with lots of graphs and tables and all sorts of bells and whistles. At the same time, the body of the email needed to be populated with a high level summary. He also wanted to see raw data in a spreadsheet. All of this in one email.  Let’s take a look at this kind of Cognos Report Customization together.

Fortunately for us, Cognos report customization like this can be done fairly easily. To begin, let’s consider what he’s asking for as separate pages in a single report.

The high level summary is simply a couple of graphs. If we really wanted to we could right conditional sentences to give it the appearance of a hand-written analysis (which is actually what I did in the real world, lots of ‘In the past week, the sales have ‘ + case when [Measure Variance] >0 then ‘seen growth’ when [Measure Variance] <0 then ‘experienced a decline’ else ‘remained stable’ end.)

The PDF output provides a detail description of each line of business, handled with pagesets. It has a cover letter, and a table of contents and has color coded headers so the toner providers stay in business.

And finally the excel output has all of the data feeding the report. In theory it would be possible to recreate all of the graphs using the spreadsheet. Personally I am not a big fan of Excel, as I find self-driven analyses tend to bend to the end users personal biases. Nevertheless, this output will give the manager the ability to see exactly what is causing that dip in Q3 for his product line.

The implementation is actually quite simple. The first thing to do is to create a variable based on the report output:

With the expression ReportOutput(), this will return PDF for PDF, spreadsheetML for Excel, and the HTML output will be dumped into Other.

Next, use File > Conditional Layouts, associating that to the previously created variable.

Each of the variable options, including Other, will create a section for pages in the report. Populate those with the pages you want to appear as PDF, Excel, and the email body.

Once the report is set up, it’s time to send a test email.

In this case we are sending the same report in HTML, PDF and Excel 2007. The body of the HTML should be blank.

As it says in the note at the top, leaving the body blank will attach the report as the body, and since we’re using conditional layouts, it will only be the summary that we wanted.

Notice the two attachments. One drawback with this technique is the attachment names are the same across the board. Excel, PDF, CSV, they will all have the same name.

The PDF is difficult to show in a simple screenshot, so I’ll just attach it here. 

And the spreadsheet:

Ultimately this seemingly difficult requirement is a simple matter of using conditional layouts.

Challenge

Now a few challenges to the reader. How can we ensure this works if the user selects the wrong version of Excel? How can we extend this technique to make the subject line dynamic? How can attach multiple, different PDFs or spreadsheets to the same email?

Conclusion

I hope you found this article on Cognos Report Customization useful.

Next Steps

If you have any questions or would like PMsquare to provide guidance and support for your analytics solution, contact us today.