If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

List Report

Started by ghostraider, 04 Apr 2008 05:23:45 PM

Previous topic - Next topic

ghostraider

Hi All,

I am trying to do a simple list report with items from four query subjects. Lets call the four query subjects are Audit, Request, User, Delivery. Audit is inner joined with Request and User, Request is inner joined with Delivery and Delivery is left outer joined join with User. When i am pulling query items of the any two above subjects the sql generated is fine. But when i pull a query item from the remaining query subjects all the items are being used in the native sql query. And since all the items are being pulled out and subqueried later to get the required items the sql generated is very very long. I compared this native sql with sql generated by impromptu and it just had few lines with the right result. On the other hands this long query is giving me and oracle temp space error. 

Please, can someone help me with this. Thanks!!


blom0344

Hard to  pinpoint this, cause this could be the result how your query subjects are defined. In any case, Cognos 8 has it's problems with generating efficient SQL in many cases.
Are these model query subjects or query subjects based on imported tables /views?
If model query subjects check out whether the sql generated is set to ' minimize sql ' or set to 'treat as view'.

Cognos 8 habit of creating more joins than strictly needed is the worst aspect of the tool..

ghostraider

There query subjects are model query subjects. I thought there was a problem with joins and when i played a little bit with joins and try to create the same list report as if i were doing a new query subject the query in framework manager is very small. But when i do it in Report Studio it seems to generate the same lengthy query. I am not able to comprehend how framework manager generates one sql and report studio generates a different one.

blom0344

FM shows only the sql for 1 join between 2 subjects or the sql generated for a model query subject.
It cannot generate the SQL for a complete report which takes the whole model in consideration..