Report Studio in Cognos 11.1 and making drilldown breadcrumbs

I’ll admit it. Since I’ve started working at PMsquare, I’ve been a bit remiss on updating this blog. What’s even worse, I’ve started cheating on my own blog and I’ve been writing periodically for the PMsquare one!

My last two posts there are well worth the time to read.

The first is an overview of Report Studio. With Cognos 11.1 released for cloud customers, and an on-prem version being released imminentlyish (November or December maybe?) my article goes into the nitty gritty of the changes in Report Studio. With lots of nice animations of me fooling around with it. There are some really nice features, and one annoying change. Read it here: http://www.pmsquare.com/main/blog/cognos-11-1-reporting/

The second is a technique for making breadcrumbs with advanced drillthrough options. It works beautifully in every version of Cognos I’ve tested in, but only in non-interactive mode. Read it here: http://www.pmsquare.com/main/blog/drilldown-breadcrumbs-in-cognos-analytics

Excuse the clickbait, couldn’t resist. I submitted to Analytics University but it looks like it wasn’t accepted. Maybe IBM doesn’t want you to know about this one amazing trick.

Datasets are amazing. I can’t fully express my love for them. In-memory local data. Fast and, when modeled with data modules, incredibly versatile. The only downside is the development environment for the datasets. It’s basically a cut down report studio. You can drag in model items, and maybe add a few detail filters. But what if you want to do something more complex? Let’s say you want to build a dataset over a cube, and use some advanced MDX functions? What if you want to union a few queries? Or what if, for some really crazy reason, you want to write a pure SQL statement?

Let’s take a look at the basic structure. This is on the latest release version:

,
It looks simple, and you can double click on the individual items to perform simple calculations. Personally it’s not enough. I want full control over the dataset. How can we do this? Turns out that the dataset GUI is really report studio that’s been cut down. Crippled. Mutilated. Let’s fix this. In this example I want to add some advanced calculations to the dataset. I want to count the orders and find the total days between close and ship date. Then I’ll want to make another query and join the two queries locally.

First we need to find the storeID of the dataset I want to modify. Navigate to the dataset, click on the ellipsis or the three dots, and click properties.

That’s the ID we need. An interesting note, sometimes the ID has an extra 0 length space character immediately after that first “i”. If it has that, you need to remove it before it will work with this trick.

Now let’s take a look at the URL to open reports in Report Studio

https://SERVER/bi/?perspective=authoring&id=i8B1B499D47484395A991D046ABB75437&isViewer=false&isNewFromModule=false&isNewFromPackage=false&isNewDataSetFromModule=false&isNewDataSetFromPackage=false&isTemplate=false&isDataset=false&UIProfile=Titan&cmProperties%5Bid%5D=i8B1B499D47484395A991D046ABB75437&rsFinalRunOptions%5Bformat%5D=HTML&rsFinalRunOptions%5Ba11y%5D=false&rsFinalRunOptions%5Bbidi%5D=false&rsFinalRunOptions%5BrunInAdvancedViewer%5D=false&rsFinalRunOptions%5BDownload%5D=false&rsFinalRunOptions%5Bprompt%5D=true&rsFinalRunOptions%5BisApplication%5D=false&isPreview=false&promptParameters=%5B%5D


Lots of useless flags, but one stands out to me. That isDataset can be toggled to true to make the saved file a dataset! Let’s cut down that URL and use the URL from our dataset:

First, let’s take a look at the URL to open report studio.

https://SERVER/bi/?perspective=authoring&id=i0CDF1E1C6B4E4CAAAAA39CD0DCFCD242&isDataset=true&UIProfile=Titan&cmProperties%5Bid%5D=i0CDF1E1C6B4E4CAAAAA39CD0DCFCD242

That’s a much more reasonable URL and look what happens when we open it!

It’s beautiful! We have the page explorer, we have the query explorer. It’s all there! Let’s make the changes we want. I go to the query and add a new data item, close to ship _days_between([Sales (query)].[Time (close date)].[Date (close date)],[Sales (query)].[Time (ship date)].[Date (ship date)]) with the aggregation of total. And then I drag order number in and set the aggregation to count distinct. We can perform the calculation in the data module later. Finally I add a new data item called Previous Year Month which is [Month key] – 100. This will allow me to build an identical query and pull the same measures for last year.

Finally, I can go back to the report page, set the query to that joined query, and add in the new data items.

But there’s a problem! Take a close look at that screenshot a few paragraphs above. It’s missing the data items in the insertable objects pane! Oh the humanity. Fortunately it’s nothing a little skullduggery can’t fix. Press F12 to open the dev toolbar. Use the element selector to select that space next to “Source” and take a look at the HTML. The data items tab is actually there, but it’s set to display:none! Change that to display:block or remove that style, and it will come back!

Important note. before you attempt to add the items, make sure to turn off “Automatic group and summary behavior for lists”. With that option on you’ll get a weird and unhelpful error message when attempting to add the new data items.

Let’s add the item, and save. Go back to the welcome page and let’s try to run it.

It works! And now let’s see if the new things we added work in dashboards.

And the dashboard works exactly as I would expect. In the next version of Cognos there is going to be an easier way to handle relative time periods, but there are plenty of other use cases for doing local joins in datasets.

It’s been a hectic week for me, so my apologies for not posting this earlier.

Thursday was the last day of the conference, and had the absolute best session so far. While the sessions so far have been mostly introductory or soft, Thursday finally had a hard technical session.

Sadly I missed most of the morning sessions, so I only have two to report on.

Merck Pharmaceuticals upgraded to Cognos 11, Series 7 authentication to Active Directory. While this would normally be a nightmare-and-a-half, Merck brought in the IBM AVP group to help handle the migration. Apparently they have tools that will ease the process, and map from one auth source to the other. Sadly the cost of these tools are bundled with hiring AVP to come and help. It’s not something I can download and play with.

Baxter wins the best technical session of the week. Alex used the Cognos audit model, modified it to his needs, and then built a data set on top of it. In the presentation, which I’m helpfully providing a link to, he added a number of calculated fields to better group the data. Furthermore, he did a count distinct on the request ID. As the audit package stands, it counts the timestamp of the run, which changes for each prompt selection. Once that was done, he wrapped the entire model in a data set, giving tremendous improvements in speed and usability. The presentation can be found here: https://1.dam.s81c.com/m/374561c83aa32d1c/original/Think_2018_Session7034_Pataky_20180322v2-pdf.pdf

Ultimately the conference was somewhat disappointing this year. The technical sessions were few and far between. The few Cognos sessions there were tended to emphasize the same, albeit good, lesson – “When upgrading Cognos, plan twice, implement once”. Due to the extreme number of attendees, coupled with the dearth of BA sessions, rooms were overcrowded, and occasionally impossible to enter. Several people I talked to had to choose between eating lunch, or staying in the room to make sure they could attend the next session.

The roadmap was good, as was the session with the designers. Wonderful news on that front, we’ll be able to “pin” the popover toolbar to the top. Basically we’re getting the toolbar back! I’m hoping to get early access, in some way or another, so I can write about the awesome new features.