Hi cognostechie,
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