Quickie: Top 5 or Top 4 with “Others”

Here’s a puzzle for you. A set, depending on filters, may have anywhere between 1 and 100 members. If there are more than 5 members, we should see 4 with an aggregated “Other” member. If there are 5 or fewer we should just see them.

Top 4

We could try to do it with an insane combination of head and topCount and pull end up pulling out far too much hair. Instead, Cognos has a couple built in function that makes this a snap.

_firstFromSet is a function that will return the first N members from a set, with an optional overflow. Let’s say that Product Line has 5 members: Camping Equipment, Golf Equipment, Mountaineering Equipment, Outdoor Protection, and Personal Accessories. If we were to do _firstFromSet([Product Line],4,1) it would return all 5 members. Order Method, on the other hand, has 7 members. _firstFromSet([Order Method],4,1) will return only the first 4.

So this gives us either 4 or a max of 5 members, but how do we get that “Other” member? There is another, similar, function called _remainderSet. If the set in question contains more than a specified number of members, it will return a specified member. If the referenced set contains fewer, it will return an empty set. So let’s see an example:

_remainderSet (
      currentMeasure - total(currentMeasure within set _firstFromSet([Dim],4,1))
    , 'Other'
    , 'Other'
    , hierarchy([Dim])
  , [Dim]
  , 5

So in this case, if [Dim] contains more than 5 members, it will create a calculated member that finds the total for the remaining members.

Putting it together we get:

  _firstFromSet (
    , 4
    , 1
  ,  _remainderSet (
        currentMeasure - total(currentMeasure within set _firstFromSet([Dim],4,1))
      , 'Other'
      , 'Other'
      , hierarchy([Dim])
    , [Dim]
    , 5

The report is, as usual, attached below.
Top 5 (17 downloads)

3 Reports in 1 email

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: ReportOutput variable with PDF and spreadsheetML options.

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.
Conditional Layouts option in the file menu

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.
A list of pages in the conditional layout

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

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

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.
HTML Output in the email

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 (36 downloads)

And the spreadsheet:
Example Excel Output

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

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 (30 downloads)

Exposing Cognos content to the outside world

One of the greatest strengths of Cognos has always been the ease in which data can be managed, queried and presented. With a well designed data model, the barrier to building accurate reports is very low. For all of the fancy interactive features, neat tricks, and convoluted JavaScript techniques, the actual process of building a simple report to important information is very easy.

Now what happens if you want to build a portal to take that information and present it to end users. With a tool as complex and as extensible as Cognos, there are many ways of exposing Cognos to the world. What follows is a few, but Important Note! I am not factoring in licensing in this article. Licensing costs can often choose your solution for you. Letting the public use report studio will probably cost more than saving a report to PDF and manually attaching it to the website, but it’s ultimately down to your license agreement.

Schedule report runs to save to a folder. This is a simple idea. You build a report, schedule it to save to a folder, and run a process to upload it to your website. I’ve seen complex examples where a process scans the folder for new reports every few seconds and dynamically updates the website when it finds the output, to simple examples where the output report is dumped into an FTP site for people to download at their leasure.

Some sites simply expose Cognos directly to the internet granting visitors anonymous access. A simple google search will find you many examples of this. One example is The Office of Personel Management (https://www.fedscope.opm.gov/). Click on a data cube and you’ll find yourself using PowerPlay studio. No actual predefined reports to speak of here, but people who use it are probably more interested in using the slice and dice capabilities provided. The benefit here is obvious. It’s Cognos, pure and simple. All of the benefits, and drawbacks, of using Cognos are here.
FEDSCOPE allows anonymous users to use PowerPlay to research their data. Seriously, who users PowerPlay anymore? The Feds!

iFrames. You might be a government organization presenting offering reports through a complicated series of generated iFrames, such as found at the New Hampshire Department of Health and Human Services. Whan interactive dashboard is run (such as https://wisdom.dhhs.nh.gov/wisdom/#Topic_00FD0704951145F793A8C5424D352FBF_Anon), it loads several widgets talking to predefined reports. Each widget contains parameter information for Cognos to run and return the report, specifically for that widget. You could have one widget showing hospitalizations by day as a line graph, and another one showing fatalities by month; with both widgets pointing to the same report. This is a brilliant and extensible solution. It allows the dashboard developer to use the same reports multiple times. As each widget is an iFrame, the user has all of the capabilties in native Cognos. Drillthoughs, prompting, and any other feature report authors could put in. The drawback of this technique is that each widget does run a separate report, with all the overhead associated with it. If you anticipate a large audience, with thousands of hits an hour, this will cause system stability issues.
DHHS of New Hampshire has a pretty cool solution - each dashboard is a series of iframes that point to existing reports. URL params control what measures and dimensions appear, and those can be modified through prompts.

Mashup Services. I’ve been using Cognos Mashup Services (CMS) quite a bit lately. It’s an extension of the SDK that simplifies exposing Cognos reports as an API. There are many ways to use CMS to build your portal. A simple REST call and your pixel perfect reports, with graphs and formatting, can be returned in fully formed HTML and embedded directly in your webpage. The issue here is that all native Cognos interactivity is gone. No prompts, no drill downs. Any interactive features have to be built by default. But this may not necessarily be a bad thing. The Cognos outputs, while it can be coerced into looking nice, do not live up to modern web design standards. Features like sticky headers when scrolling down, or client side table sorting, or more info drawers or popovers, are difficult to build. By using CMS to generate your data in a compressed format, like JSON, you can merge the powerful querying engine with modern web design. Of course this is entirely predicated on you having web developers on your staff, or an application already set up to work with Cognos data. And speaking of which, I’ll revisit this one in the future.

So to summarize.
Scheduling a report – Simple, non-interactive, low cost. You need someone to set up the automation to get the output into the webpage, and then it’s fire and forget.
iFrames – Build the reports as normal, and use URL parameters to load it. As long as you have a report developer, this is the easiest solution. Some overhead on the server when running reports.
Mashup – medium to difficult. Any interactivity on the page needs to be coded in. Expertise will be needed to embed the output into the report. If you’re pulling HTML you can expect some unreasonably large results. Datset outputs, like JSON or ATOM, will need to have additional post processing.