Topic
13 replies Latest Post - ‏2013-11-18T06:09:40Z by timswiley1
mgibson
mgibson
592 Posts
ACCEPTED ANSWER

Pinned topic Level Specific Drill-Through

‏2011-04-28T01:09:48Z |
Hi Guys,

Does anyone know if it is possible to specify drill-through for specific levels in a DMR?

For example, Level 1-3 can drill-through to Report A
however, Level 4 is to drill-through to Report B

We have a hierarchy where this scenario presents the best usability option for users.

The Cognos KB tells me this is possible when using a PowerCube, but I'm curious to know if this can be done within Report Studio using a DMR?

Regards
Mick
Updated on 2011-05-06T01:37:38Z at 2011-05-06T01:37:38Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    15496 Posts
    ACCEPTED ANSWER

    Re: Level Specific Drill-Through

    ‏2011-04-28T06:10:41Z  in response to mgibson
    With DMR you can still use the roleValue function to get the level number:

    roleValue('_levelNumber', [DMR member or set])
    


    Then you can unlock the layout and put a second copy of the query item reference into the same crosstab node or list column. A string variable using the level number query item can then be used as the render variable for the two text items in the node/column. You would display one text item for variable values 1 to 3 and the other text item for value 4. A different drill through can be defined on each text item.
    Updated on 2014-03-25T08:09:14Z at 2014-03-25T08:09:14Z by iron-man
    • mgibson
      mgibson
      592 Posts
      ACCEPTED ANSWER

      Re: Level Specific Drill-Through

      ‏2011-05-04T00:58:08Z  in response to SystemAdmin
      Hi Phil,

      This is a brilliant idea - however, it's giving me grief...

      At first I created a simple expression...
      roleValue ('_levelLabel', [HP Service Manager].[ITSD Org Sturcture].[ITSD Org Structure])
      

      and got this error 'RSV-VAL-0010 Failed to load the report specification. CCL_ASSERT(sequentialFieldAccess < m_vars.size())'

      So I looked on the Kn and forum and saw some other methods and changed the expression to...
      roleValue ('_levelLabel',currentMember ([HP Service Manager].[ITSD Org Sturcture].[ITSD Org Structure]))
      

      and now I get a different Oracle error 'ORA-01722: invalid number RSV-SRV-0042'

      I'm sure it would work flawlessly with a PowerCube, but DMR with Oracle must be a different situation.

      Perhaps it's a bug in 8.4?

      Any ideas?

      Regards
      Mick
      Updated on 2014-03-25T08:08:41Z at 2014-03-25T08:08:41Z by iron-man
      • SystemAdmin
        SystemAdmin
        15496 Posts
        ACCEPTED ANSWER

        Re: Level Specific Drill-Through

        ‏2011-05-04T01:06:07Z  in response to mgibson
        I don't have an Oracle system to test at the moment but the _levelNumber and _levelLabel are working for me in a list with SQL Server using the samples.
        • mgibson
          mgibson
          592 Posts
          ACCEPTED ANSWER

          Re: Level Specific Drill-Through

          ‏2011-05-04T03:43:45Z  in response to SystemAdmin
          Hi Phil,

          It's odd because the sample works for me as well, and our sample DBs exist in Oracle.

          I might start with a completely new RS report and see if I can get it to work in the simplest fashion, as you've done with your sample report.

          Regards
          Mick
        • mgibson
          mgibson
          592 Posts
          ACCEPTED ANSWER

          Re: Level Specific Drill-Through

          ‏2011-05-04T03:48:29Z  in response to SystemAdmin
          I've created a new report using that package with only 2 columns, and I'm no longer getting that error - which suggests something else is playing havoc with it.

          BUT when I drill down to the second level on the first column, the Level still says 1 - it doesn't change to 2.

          Any ideas?

          Regards
          Mick
          • SystemAdmin
            SystemAdmin
            15496 Posts
            ACCEPTED ANSWER

            Re: Level Specific Drill-Through

            ‏2011-05-04T03:56:07Z  in response to mgibson
            You might have to get into member sets to configure advanced drill behaviours linking the calculation to the source item you are drilling down on. Take a look at the following:

            http://www.ibm.com/developerworks/data/library/cognos/page147.html

            It would also help to post your report spec from the samples with the drill down issue.
            • mgibson
              mgibson
              592 Posts
              ACCEPTED ANSWER

              Re: Level Specific Drill-Through

              ‏2011-05-04T04:10:57Z  in response to SystemAdmin
              Hi Phil,

              I'm not sure if it is useful, but I've attached 2 report specs...

              Original.txt - which does not include any attempt to include level information
              with_levelNumber.txt - which is as the name suggests

              Note that the 2nd report runs fine if I change '_levelNumber' to '_businessKey'.

              Regards
              mick
            • mgibson
              mgibson
              592 Posts
              ACCEPTED ANSWER

              Re: Level Specific Drill-Through

              ‏2011-05-04T04:20:37Z  in response to SystemAdmin
              Hi Phil,

              If I modify your original example to be;

              roleValue('_levelNumber',currentMember(......
              


              The Level number updates dynamically when I'm drilling down.

              So I might have to rebuild sections of the original report to prevent that Oracle error from appearing - or at least identify what's causing the contention.

              Thanks yet again for your help!

              Regards
              Mick
              Updated on 2014-03-25T08:08:32Z at 2014-03-25T08:08:32Z by iron-man
            • mgibson
              mgibson
              592 Posts
              ACCEPTED ANSWER

              Re: Level Specific Drill-Through

              ‏2011-05-06T01:15:00Z  in response to SystemAdmin
              Hi Phil,

              I've isolated the cause of my Oracle error. It's caused by the inclusion of 2 Percentage calculated columns. Eg.
              percentage([TotalBreached] auto))
              

              for some reason they produce an error when the Level function is included in the report.

              Now I just need to work out what variation of the Percentage function will avoid the error. I'll post the fix if I find one.

              Regards
              Mick
              Updated on 2014-03-25T08:08:20Z at 2014-03-25T08:08:20Z by iron-man
              • SystemAdmin
                SystemAdmin
                15496 Posts
                ACCEPTED ANSWER

                Re: Level Specific Drill-Through

                ‏2011-05-06T01:25:34Z  in response to mgibson
                I think there is a dimensional version of the percentage function. If you're going to be using a dimensional query approach with the roleValue function then you should steer clear of relational versions of the summary functions. Mixing the two makes it difficult for the query planner to make sense of the query definition.
                • mgibson
                  mgibson
                  592 Posts
                  ACCEPTED ANSWER

                  Re: Level Specific Drill-Through

                  ‏2011-05-06T01:32:52Z  in response to SystemAdmin
                  As it turns out, it's the same function, but different syntax...

                  The error was fixed when using this...
                  percentage([TotalBreached] within set [Group])
                  


                  Your pure genius has helped me yet again, while my unfamiliarity with the dimensional world has caught me out once again.

                  Gee it would be useful if the error was more helpful!

                  I should contact your management again to tell them how much of a lifesaver you are.

                  Regards
                  Mick
                  Updated on 2014-03-25T08:08:13Z at 2014-03-25T08:08:13Z by iron-man
                  • SystemAdmin
                    SystemAdmin
                    15496 Posts
                    ACCEPTED ANSWER

                    Re: Level Specific Drill-Through

                    ‏2011-05-06T01:37:38Z  in response to mgibson
                    Happy to help. You might consider raising the error with support to get something more meaningful put into the product but it likely will not go very far given the mix of dimensional and relational syntax in the query. In C8 the separation of the two approaches isn't that clear. C10 does this slightly better by regrouping the functions in the expression editor.
  • timswiley1
    timswiley1
    1 Post
    ACCEPTED ANSWER

    Re: Level Specific Drill-Through

    ‏2013-11-18T06:09:40Z  in response to mgibson

    Dimensional To Relational Drill Through With Multiple Levels - The final say...   (Cognos 10.1)

    Problem - Chart Drill Through

    1.  There are tons of so-called solutions out there almost all of which are half-way at best to get accurate drill through results from dimensional to relational data especially with more than 2 levels in a heirarchy. 

    2.  Many solutions think that by knowing your level and only the level of your immediate parent you can work some majic case/if logic in your drill through relational report to get what you need, but if you have more than 2 levels and your child member nodes aren't distinct within parent/grandparent levels then your drill through will not be accurate.    Example if you your 3 or 4 levels deep and your level caption is "John Smith" and your parent level name is "Miami"  this not enough info to accurately drill through on, because if your hierarchy is Country/State/City/Person because Miami is in Florida as well as Texas,  you will pull other un-wanted data as well. 

    Solution:   (This is my solution and if there is another please share because i have not found anything else that really does this right and clean)

    #1.  You will need the value of the member your clicking on in the drill through which is easy as well in the DT definitions but Caption will not due you will use the business key but only after you have prepared it in the backend...   It is irritating that you can't pass the full path of the intersection your clicking on so to really solve the problem you need to make sure your business key for any member in a heirarchy is actually a keyed full path to your member.  In the example above the busines key for John Smith in TX would be     'US|TEXAS|MIAMI|JOHN SMITH'   

    #3.  Just by having access to this full-path business key you know many things.... by counting the pipes or whatever delimiter you use, you know the current level, and with this multi-part key you could go at this many different ways.  Best Way - Split the columns so that a separate filter gets applied to each level in your business key:  [Country] = 'US' and [State] = 'TEXAS' and[ City]='MIAMI' and [Person]='JOHN SMITH'  (this can be done with clever use of the Cognos/Vendor string functions in the filter expression builder. 

    or you could just maintain the business key in your backend relational data and either use the passed leveluniquename to conditonally know what column to apply the businesskey filter to. 

    or you could just let Cognos do the filter away from the database which on large sets wouldn't be goood but you could just do something with a filter like  [Country] + '|' + [State] + '|' + [City] + '|' + [Person] = 'US|TEXAS|MIAMI|JOHN SMITH'  

    *  - In 10.1 i have tried all kinds of other options like creating a calculated item that i don't want to display on the chart but want to pass as a property in the drill through.  It doesn't work in 10.1 when using dimensional source without displaying the value on the chart which is very un-desired. 

    Its a real shame that this isn't easier in the product.  I would imagine 10.2 maybe better because of in memory cubes being closer to the FM dimensional model but even still this should be much easier than it is.