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.