Cognos Analytics

 View Only

 Cognos Analytics 12.1.3: PERIODID prompt shows values ascending although the database returns them descending (worked in 11.x)

Anand kushwaha's profile image
Anand kushwaha posted 10/09/26 01:19 PM

Hi everyone,

Here's one that's been bugging us since we moved to Cognos Analytics 12.1.3 GA. I'm curious whether anyone else has seen it.

The setup

We have a set of reports where the user must pick a PERIODID before any data shows up. The filter is a plain required parameter ([PERIODID] in ?PERIODID?). There's no authored prompt page, so Cognos builds the default (generated) prompt page for us.

The values for that prompt come straight from the database, and the query sorts them descending, newest period first:

2570
2569
2568
2567
2566
...

We checked the native SQL in the Cognos logs: it really does carry ORDER BY ... DESC. Run directly against the database, it returns exactly the list above.

What the prompt shows

On the generated prompt page, the same list comes out ascending:

0000
0001
0002
0003
0039
0062
...

So the oldest period sits at the top, and the one everybody actually wants is at the very bottom of a long list. Users have to scroll all the way down every single time.

The part that surprised us

These same reports worked fine on Cognos 11.x: the prompt kept the database order and the newest period was on top. Nothing changed in the reports or the query. Only the Cognos version changed.

What we've checked so far

  • The sort on the query / data item is descending, and the native SQL confirms it.
  • It only happens on the generated prompt page. With an authored prompt page and an explicit sort, the order is right.
  • We tried a Customization Extension with a small script to reorder the list. It loads in the portal, but it never seems to reach the prompt page, so it made no difference.

Over to you

  1. Has anyone else seen generated prompts in 12.x re-sort values ascending and ignore the query order? Is this a known change from 11.x?
  2. Is there a setting, advanced property, or supported way to keep the query order on a generated prompt? We have a lot of reports with this filter, so adding an authored prompt page to each one isn't really practical.
  3. If you've worked around something similar, how did you do it?

Open to any suggestions. We're testing a workaround on our side and I'll share it here once it's solid.

Thanks!

Anand kushwaha
https://www.linkedin.com/in/anandkushwaha064/

Senior Software Engineer
AMVARA CONSULTING S.L.
Barcelona

cognostechie's profile image
cognostechie

Not a solution without adding a Prompt in the report but explaining the problem.

1> A native SQL does not mean that same SQL will be sent to the database. Cognos can, and most probably will, rewrite that SQL. Did you check the report XML and the SQL running in the database? A native SQL does not get treated the same way as a pass-thru SQL.

2> The native SQL is only for the data query, not for the prompt and it's possible Cognos will generate another SQL for the prompt itself. Few changes are always made in newer versions.

This is why I like to have full control over the report and create Prompts in the reports itself, either in the Prompt page or the report page. 

Anand kushwaha's profile image
Anand kushwaha

Hi ,

Thanks for looking into this, both points were worth checking.

Iitialnly I didn't stop at the native SQL. I picked the SQL out of the data service log file, the statement that was actually sent to the database, and ran that directly on the database same query. It comes back newest first, exactly as expected.

So the order is right up to the database result, and it only flips to ascending when the generated prompt page draws the list. Same reports kept the database order on 11.x.

The other problem for us is the number of reports, We have more than 300 of them use this PERIODID filter, so adding a prompt page to each one is a lot of rework, and every new report would need the same thing.

What we have working for now: the generated prompt page is drawn in an iframe that loads webcontent/bi/pat/rsapp.htm,

We dropped a small JS file next to rsapp.htm and added one script tag before </body>.

The script only touches generated prompt pages and only PERIODID prompt, and it re-sorts the list descending after Cognos has drawn it, without touching the selection. Back up rsapp.htm first, it's an IBM file. With that, all 300+ reports show the newest value on top and we didn't change a single report.

Two things that bit us: the browser caches rsapp.htm, so it took a Ctrl+F5 (or an incognito window) before the new order showed up, and the change has to go on every node that serves webcontent. A fix pack replaces rsapp.htm, so we keep the backup and recheck after upgrades.

Happy to drop this solution for any permanant and better solution.

Anand Kushwaha
https://www.linkedin.com/in/anandkushwaha064/

Senior Software Engineer
AMVARA CONSULTING S.L.
Barcelona