Cognos Report Studio: Cascading Prompts populating really slow

Question

I've a Cognos report in which I've cascading prompts. The Hierarchy is defined in the image attached.

The First Parent (Division) fills the two cascading child in 3-5 seconds. But when I select any Policy, (that will populate the two child beneath) it took around 2 minutes.

Facts:

• The result set after two minutes is normal (~20 rows)
• The Queries behind all the prompts are simple Select DISTINCT Col_Name
• Ive created indexes on all the prompt columns.
• Tried turning on the local cache and Execution Method to concurrent.
• I'm on Cognos Report Studio 10.1

Any help would be much appreciated. Thanks,

Nuh

Answer 1

There is an alternative to a one-off dimension table. Create a Query Subject in Framework for your AL-No prompt. In the query itself, build a query that gets distinct AL-No (you said that is fast, probably because there is an index on AL-No). Wrap that in a select that does a filter on ' #prompt('pPolicy')#' (assuming your Policy Prompt is keyed to ?pPolicy?)

This will force the Policy into the sql before it is sent to the database, but wrapping on the distinct AL-No will allow you to use the AL-No index.

select AL_NO from 
(
    select AL_NO, Policy_NO
    from CLAIMS
    group by AL_NO, Policy_NO
)
where Policy_NO = #prompt('pPolicyNo')#