This is a repost of an article I wrote for the PMsquare Journal. A wonderful publication, with articles written by the finest minds in the business. Make sure you sign up here today!
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.
Fortunately, Cognos allows us to do this 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.
3 output email PDF (844 downloads)
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?
Report XML:
3 reports 1 email specs (799 downloads)
Elegant solution for that task/requirement
Hey Paul, did you ever solve the challenge of making the subject line dynamic? Just curious. Great solution and still applicable today!
The trick is to use Event Studio here. Run the report and send out the email from there – you can use any query item in the email header.
You can also burst the report to the content store and attach the output to the email. Just reference the burst key in the email (such as the subject line) and it will attach the correct output to the email.