Has this ever happened to you?

It’s happened to me several times. There was also that one time I worked on a model for hours, and left it for the night. Can you guess which operating system decided to update and restart overnight without warning? It was like a punch to the gut when I realized I lost all that work.

Fortunately, there’s a really easy way to retrieve all that work.

First, don’t panic. In the model folder there are two vitally important files.

The file session-log.xml is a record of every action you’ve taken while framework is open. Every object you move, every expression you write, every property you change, session-log is watching.

The next time you open Framework Manager, session-log.xml will be renamed session-log-backup.xml, overwriting the old one, and a new blank session-log.xml will be created. Copy that file! If it disappears because FM screws up renaming it, or if you have a brain fart and open the model twice, you’re stuck.

Now that we have a backup of all the work we’ve done, and forgot to save, we can start the reconstruction. Open the Project menu and select “Run Script”

When you press the run button, Framework will perform every action in the list. It pauses on error, allowing you to check what’s going on before proceeding.

Once the script has run it’s course, you can accept the changes, or revert back to the way it was.

And there we go. Hours of worked gone and back again.

One last note, it may be worth enabling the auto save from the Project->Options menu item. It makes the entire premise of this article superfluous.

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