Everyone loves BaCON!

June 20, 2017 | cognospaul

I’m sure many of you are devastated at the news of IBM cancelling their big Vegas World of Watson conference this year. Well, dry your tears because PMsquare is hosting a Business Analytics conference in Chicago! And, best of all, I’ll be running a workshop!

This August I will presiding over a Cognos Analytics workshop. My workshop will be on the new JavaScript contol in the interactive mode. Did you know we can use jQuery and bootstrap and all of these wonderful JS libraries now? Did you know we can pass data directly to the JavaScript from a query, instead of having to embed stuff in repeaters or loop throuhg a hidden list? We’ll go over how to use a couple of controls that I wrote, and how we can pass data from a query directly to the control.

Finally, we’ll get into the code itself. A dive into the API. How can we write the code to use data from queries? What is this JS Control configuration all about?

With the cancellation of WoW we’ve been expanding the scope of the conference so the other sessions and keynotes are still in the works. What I can say is that we will have more than one workshop, we will have a Question and Answer session with Business Analytics professionals, and we will have a lot of really good food.

And, because my readers are so awesome, I’ve managed to secure a code to get you 30% off the admission fee.

So click on this link:
https://events.r20.constantcontact.com/register/eventReg?oeidk=a07ed3bwu7o48a4d1eb

and make sure to apply the promo code: 1VN1AVDABCN

If securing budget will be difficult, please contact me. I may be able to request some kind of scholarship via Marketing.

Personally, I would love to meet my readers and discover how you’ve used the techniques from my articles. What works, what doesn’t? Any suggestions for new posts? Need to smack me for destroying your Cognos environment? This is the best opportunity for us to connect.

OLAP

Quickie: Top 5 or Top 4 with “Others”

March 6, 2017 | cognospaul

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 (
  member(
      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:

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

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

Uncategorized

3 Reports in 1 email

February 21, 2017 | cognospaul

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

And the spreadsheet:
Example Excel Output

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)