Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
12 replies Latest Post - ‏2012-05-02T14:12:24Z by MalathiM
MalathiM
MalathiM
6 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: Sectioning multi query report

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

      Re: Sectioning multi query report

      ‏2012-05-01T14:39:34Z  in response to DanielWagemann
      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
      • DanielWagemann
        DanielWagemann
        411 Posts
        ACCEPTED ANSWER

        Re: Sectioning multi query report

        ‏2012-05-01T15:06:03Z  in response to MalathiM
        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
          ACCEPTED ANSWER

          Re: Sectioning multi query report

          ‏2012-05-01T15:15:18Z  in response to DanielWagemann
          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
            ACCEPTED ANSWER

            Re: Sectioning multi query report

            ‏2012-05-01T15:18:53Z  in response to MalathiM
            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
            ACCEPTED ANSWER

            Re: Sectioning multi query report

            ‏2012-05-01T20:31:34Z  in response to MalathiM
            @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
              ACCEPTED ANSWER

              Re: Sectioning multi query report

              ‏2012-05-02T14:12:24Z  in response to SystemAdmin
              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
    • SystemAdmin
      SystemAdmin
      15496 Posts
      ACCEPTED ANSWER

      Re: Sectioning multi query report

      ‏2012-05-01T14:49:50Z  in response to DanielWagemann
      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
        ACCEPTED ANSWER

        Re: Sectioning multi query report

        ‏2012-05-01T15:04:19Z  in response to SystemAdmin
        @Winson11 I think you may find the following article helpful: http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html
        • SystemAdmin
          SystemAdmin
          15496 Posts
          ACCEPTED ANSWER

          Re: Sectioning multi query report

          ‏2012-05-01T15:20:58Z  in response to DanielWagemann
          Thanks Daniel.

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

          Thanks,
          Winson.
          • DanielWagemann
            DanielWagemann
            411 Posts
            ACCEPTED ANSWER

            Re: Sectioning multi query report

            ‏2012-05-01T15:34:26Z  in response to SystemAdmin
            @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
              ACCEPTED ANSWER

              Re: Sectioning multi query report

              ‏2012-05-01T16:44:05Z  in response to DanielWagemann
              Thanks Daniel.

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

              Thanks for the document.

              Winson.