Topic
  • 12 replies
  • Latest Post - ‏2012-05-02T14:12:24Z by MalathiM
MalathiM
MalathiM
6 Posts

Pinned topic Sectioning multi query report

‏2012-05-01T14:03:27Z |
Hi,

We have a single page report in Cognos 8.4 with multiple lists based on multiple queries (8) with in the same page.
I need to section the report based on the product name coming from a single query. Meaning, we will have all the lists containing information for a single product separately in each page.
Is there a way to achieve this. Any help appreiated.

Thanks,
Malathi
Updated on 2012-05-02T14:12:24Z at 2012-05-02T14:12:24Z by MalathiM
  • DanielWagemann
    DanielWagemann
    411 Posts

    Re: Sectioning multi query report

    ‏2012-05-01T14:30:39Z  
    Here is a example which uses Page Sets and Master-Detail relationships against the IBM Cognos Samples.
  • MalathiM
    MalathiM
    6 Posts

    Re: Sectioning multi query report

    ‏2012-05-01T14:39:34Z  
    Here is a example which uses Page Sets and Master-Detail relationships against the IBM Cognos Samples.
    Hi Daniel,

    Thank you for the response.
    I have product details in a single query and other queries do not have common data items related to product to incorporate a master detail between the product query and other queries feeding the lists to make it to work.

    Thanks,
    Malathi
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Sectioning multi query report

    ‏2012-05-01T14:49:50Z  
    Here is a example which uses Page Sets and Master-Detail relationships against the IBM Cognos Samples.
    Hi Daniel.
    Thanks for the sample attached.
    I have quick question regarding this post.
    How can we find out how many time query send it to the data base to retrieve the data? I generate the sql for each query but I can tell by looking at it. (Maybe I am lack of sql understanding).

    Ex: before u do the master detail or pageset to split the page if we capture the sql vs then with the pageset and the master detail apply.
    I am trying to understand the sql being send it to the database.
    because We have few reports being take so long to produce the output with the pageset with the master detail on it. (it takes up to 3 hrs to run the report) I never got the chance to figure it out (again maybe I am lack of experience).

    So that report which I have with out pageset and the master detail relationship it take about 30 min. With MD and pageset it take about 3 hrs. users not happy and not sure how can I tune this report to at least run with in reason amount of time let say 1 hr or so .

    Please provide some help regarding this matter.

    Thanks,
    Winson.
  • DanielWagemann
    DanielWagemann
    411 Posts

    Re: Sectioning multi query report

    ‏2012-05-01T15:04:19Z  
    Hi Daniel.
    Thanks for the sample attached.
    I have quick question regarding this post.
    How can we find out how many time query send it to the data base to retrieve the data? I generate the sql for each query but I can tell by looking at it. (Maybe I am lack of sql understanding).

    Ex: before u do the master detail or pageset to split the page if we capture the sql vs then with the pageset and the master detail apply.
    I am trying to understand the sql being send it to the database.
    because We have few reports being take so long to produce the output with the pageset with the master detail on it. (it takes up to 3 hrs to run the report) I never got the chance to figure it out (again maybe I am lack of experience).

    So that report which I have with out pageset and the master detail relationship it take about 30 min. With MD and pageset it take about 3 hrs. users not happy and not sure how can I tune this report to at least run with in reason amount of time let say 1 hr or so .

    Please provide some help regarding this matter.

    Thanks,
    Winson.
    @Winson11 I think you may find the following article helpful: http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html
  • DanielWagemann
    DanielWagemann
    411 Posts

    Re: Sectioning multi query report

    ‏2012-05-01T15:06:03Z  
    • MalathiM
    • ‏2012-05-01T14:39:34Z
    Hi Daniel,

    Thank you for the response.
    I have product details in a single query and other queries do not have common data items related to product to incorporate a master detail between the product query and other queries feeding the lists to make it to work.

    Thanks,
    Malathi
    If you do not have a common product within each of the detail queries, how are you currently linking the two? Meaning, how do you determine which details belong to which product?
  • MalathiM
    MalathiM
    6 Posts

    Re: Sectioning multi query report

    ‏2012-05-01T15:15:18Z  
    If you do not have a common product within each of the detail queries, how are you currently linking the two? Meaning, how do you determine which details belong to which product?
    Hi Daniel,

    We have filter on product in each of the queries. So, the queries fetch data related to the product at various level of detail.

    Thanks,
    Malathi
  • DanielWagemann
    DanielWagemann
    411 Posts

    Re: Sectioning multi query report

    ‏2012-05-01T15:18:53Z  
    • MalathiM
    • ‏2012-05-01T15:15:18Z
    Hi Daniel,

    We have filter on product in each of the queries. So, the queries fetch data related to the product at various level of detail.

    Thanks,
    Malathi
    So the product does or could exist in each of the queries to satisfy the master-detail relationship. Maybe you could post your report specification to provide further clarity on what you are up against. Please of course.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Sectioning multi query report

    ‏2012-05-01T15:20:58Z  
    @Winson11 I think you may find the following article helpful: http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html
    Thanks Daniel.

    Is it possible to tell me about how to trace sql?

    Thanks,
    Winson.
  • DanielWagemann
    DanielWagemann
    411 Posts

    Re: Sectioning multi query report

    ‏2012-05-01T15:34:26Z  
    Thanks Daniel.

    Is it possible to tell me about how to trace sql?

    Thanks,
    Winson.
    @Winson11 The technique provided in the link earlier should allow you to tune your master-detail report without actually tracing the SQL. That being said, you can either trace the SQL on the database side, if you want to do a trace within the IBM Cognos product, you could implement the following UDA trace. http://www-01.ibm.com/support/docview.wss?uid=swg21373137
    I would also like to kindly ask you to create your own posting on issues you need help with. Although this is remotely related to the original proposed solution to the post. The SQL tracing is taking away from the direction of the original posting.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Sectioning multi query report

    ‏2012-05-01T16:44:05Z  
    @Winson11 The technique provided in the link earlier should allow you to tune your master-detail report without actually tracing the SQL. That being said, you can either trace the SQL on the database side, if you want to do a trace within the IBM Cognos product, you could implement the following UDA trace. http://www-01.ibm.com/support/docview.wss?uid=swg21373137
    I would also like to kindly ask you to create your own posting on issues you need help with. Although this is remotely related to the original proposed solution to the post. The SQL tracing is taking away from the direction of the original posting.
    Thanks Daniel.

    In the future I will open up a new thread for it.

    Thanks for the document.

    Winson.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Sectioning multi query report

    ‏2012-05-01T20:31:34Z  
    • MalathiM
    • ‏2012-05-01T15:15:18Z
    Hi Daniel,

    We have filter on product in each of the queries. So, the queries fetch data related to the product at various level of detail.

    Thanks,
    Malathi
    @MalathiM
    To follow on Daniel's message, the master-detail relationship would be the way to link one query for the section to other detail queries. There are two ways to create the master-detail relationship:

    1. Add the required data items for filtering to the query definition of the detail queries. These items do not need to be displayed on the report layout; they must simply exist in the query definition. The presence of the data item will allow you to match an item from the master/section query to the corresponding item in the detail queries.

    2. Modify the filters in the detail queries to use a parameter. The master-detail relationship will allow you to send a value from the master query to a parameter in the detail query. This is useful in more complex master-detail relationships where a simple matching of values in a data item is not sufficient to establish the relationship and the value from the master query must be used in calculations within the detail query.

    If your current reports already contain filters in the detail queries for product information then the second approach is probably the easiest one to implement. One thing to be aware of when applying this technique is that a parameter used for a master-detail relationship should be different than parameters used for filtering in other scenarios. For example, if your main section query is being filtered based on a parameter for product information then your detail queries should use a different parameter name when setting up the master-detail relationship. Personally, I like to prefix the parameters I use in master-detail relationships with "MD_" to easily identify their usage later on.
  • MalathiM
    MalathiM
    6 Posts

    Re: Sectioning multi query report

    ‏2012-05-02T14:12:24Z  
    @MalathiM
    To follow on Daniel's message, the master-detail relationship would be the way to link one query for the section to other detail queries. There are two ways to create the master-detail relationship:

    1. Add the required data items for filtering to the query definition of the detail queries. These items do not need to be displayed on the report layout; they must simply exist in the query definition. The presence of the data item will allow you to match an item from the master/section query to the corresponding item in the detail queries.

    2. Modify the filters in the detail queries to use a parameter. The master-detail relationship will allow you to send a value from the master query to a parameter in the detail query. This is useful in more complex master-detail relationships where a simple matching of values in a data item is not sufficient to establish the relationship and the value from the master query must be used in calculations within the detail query.

    If your current reports already contain filters in the detail queries for product information then the second approach is probably the easiest one to implement. One thing to be aware of when applying this technique is that a parameter used for a master-detail relationship should be different than parameters used for filtering in other scenarios. For example, if your main section query is being filtered based on a parameter for product information then your detail queries should use a different parameter name when setting up the master-detail relationship. Personally, I like to prefix the parameters I use in master-detail relationships with "MD_" to easily identify their usage later on.
    Thanks to Daniel & Phil!

    I have added the product info to all queries used in the report and then estabilshed Master detail relationship between these on product and it worked :)

    Malathi