Hacking Datasets in 11.1 – and some wonderful news!

The previous article I wrote on the subject doesn’t work on 11.1. It looks like IBM is wisening up to my tricks. So here’s the new way to do it.

This is actually significantly easier than the way I posted before. To start, create a list report as normal. The list should be standard: no groups, no footers or headers, no subtotals at all. Save and close the report.

Next, reopen the report. This gives us the correct URL parameters we need:

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

Change that to isDataset=true and press enter. The page will reload with the report in the dataset skin of report studio. Save it as a new dataset – this new object will be a regular dataset, you can refresh it from the portal screen as normal.

Now for the wonderful news. IBM has for some reason decided to grant me IBM Champion status! Ignoring the immediate instinct to question their sanity, I want to assure everyone that this accolade won’t change anything about my blog. I certainly won’t let this go to my head, and my ego shall remain carefully grounded. In other, completely unrelated news, I will be selling opportunities for selfies with me in Think 2019!

Creating rows from a prompt

Here’s an interesting request. A developer wants to populate a query set using a prompt. The values don’t exist in a table, so a simple filter won’t work. How can we get the query to have the rows? Turns out there’s a really easy way.

Cognos SQL is based on the SQL-92 standard, which gives us a few very nice features. One of them is the values() keyword.

values() let’s us define a set, sort of like a union query. Plenty of info here: https://modern-sql.com/feature/values.

An example query might be:

SELECT
    parameterValue
FROM (VALUES 
    ('Scenario 1')
  , ('Scenario 2')
  , ('Scenario 2')
  )  query(parameterValue)

That would create a list with 3 rows, Scenario 1-3.

So how can we use this in Cognos? A prompt macro with a split and join works wonders.

SELECT
    parameterValue
FROM (VALUES 
  (#join('),(',split(',',promptmany('Scenarios','string',sq('N/A'))))#)
    ) query(parameterValue)

The values passed to the parameter will appear as ‘Scenario 1′;’Scenario 2′;’Scenario 3’, the join/split functions will effectively replace the semicolons with ),(. This ultimately makes the SQL match the standard.

Check out the report here: params-to-rows.txt (5036 downloads)

Every month or so I will be publishing a list of list of RFEs (Request For Enhancements) that have been submitted to IBM. Some of these are enhancements that I believe should be instituded ASAP, and others just sound interesting.

This is the second month doing this, please take a look at November 2018’s list here.

This time we’ll start with a couple of RFEs relating to data modules. Data modules are a great addition to Cognos. It’s an ETL job, it’s a data source, it’s a modeling tool. I love it to death, but that doesn’t mean it can be improved.

1. Object Level Security at Table and Field Level for Data Modules

Under Consideration with 8 votes.

Data security is possible, to a small degree, but object security still isn’t. There are some columns that I don’t want users to see, ever. If they run a report that uses it, I want it to fail hard with an unfriendly error message. We can do it in FM, but not yet in data modules.

2. Data Module created from Data Server with multiple connections should be able to use any connection

Under Consideration with 3 votes.

With regular data sources it’s possible to create multiple connections to a single database, or even different databases. Reports built on FM models work very gracefully with this. Data modules just aren’t there yet.

3. Data Module – Select Unmatched Column Values

Under Consideration with 5 votes.

For the most part a standard equals join is pretty much all I ever need, except for that one time in a year where I absolutely need to have a non-standard join. I haven’t had that problem yet, but it looks like someone already did.

Now let’s look at a couple others

4. RAVE2 SDK

Under Consideration with 60 votes.

Story time! A few years ago I was giving a lecture, at BACon, on RAVE. How to use it, how to work with it, and most importantly a walkthrough of the grammar. It was a great success and everyone loved it. Then a certain IBMer who will remain anonymous, had his session. Where he said that RAVE 1 was being dropped in favor of RAVE 2. It was like I got punched in the stomach. However RAVE 1 is still going strong. That being said, having a modern kit for customizing or creating our own visualizations would be awesome. It’s interesting to note that the RFE linked here has an update from IBM: While we would like to have an extensible viz API, it is unlikely to be RAVE 2 based.

5. Improve Look, Flexibility and augment the properties on Interactive Dashboard and Visualization (IDnV) Prompts

Submitted with 9 votes.

Recently there’s been an upsurge on people demanding changes to the prompting GUI. The bottom line is the prompt options are aging, and competing tools are much “sexier”.

Did I miss any important RFEs? Got an important idea that you want in Cognos? Drop a note in the comments.