Topic
9 replies Latest Post - ‏2013-02-11T14:17:21Z by DanielWagemann
igorM
igorM
302 Posts
ACCEPTED ANSWER

Pinned topic Cognos Viewer: How to preserve only the rows I need

‏2013-02-05T13:07:29Z |
Hi,
in Cognos BI v8.4.1 on Windows I have created a crosstab report in Report Studio. When report is run in Column there is a measure and in Rows there are several dimensions in each of column. In Rows first column there is a organization unit hierarchy from organization unit dimension, in second time dimension, third customer and forth product. By default (when report is run) the whole organization unit hierarchy is displayed in first Rows column and only single member from second, third and forth dimension. So far OK.

But now end-user only needs single member from first Rows column, so user would like to PRESERVE only single member from hierarchy and then drill down on second dimension.

I don't know how to preserve only single member from first dimension.
So when drilling down on second Rows column (time dimension) then all months by all organization unit hierarchy (first Rows column) are displayed. This is way to much data displayed.

Is there any way in Cognos Viewer to preserve only single member from first hierarchy and then drill-down on second dimension?

P.S. See attached print-screen for detailed info.
Regards
Updated on 2013-02-11T14:17:21Z at 2013-02-11T14:17:21Z by DanielWagemann
  • DanielWagemann
    DanielWagemann
    411 Posts
    ACCEPTED ANSWER

    Re: Cognos Viewer: How to preserve only the rows I need

    ‏2013-02-05T13:16:09Z  in response to igorM
    Are you using a relational, DMR or OLAP package?
    • igorM
      igorM
      302 Posts
      ACCEPTED ANSWER

      Re: Cognos Viewer: How to preserve only the rows I need

      ‏2013-02-05T14:07:00Z  in response to DanielWagemann
      I am using OLAP package "IBM Cognos TM1 9.5.2 cube".
      • DanielWagemann
        DanielWagemann
        411 Posts
        ACCEPTED ANSWER

        Re: Cognos Viewer: How to preserve only the rows I need

        ‏2013-02-05T15:18:37Z  in response to igorM
        This can be a bit tricky against TM1 as I do not know what, if anything you have defined for the level.

        Attached is a 10.1.1 example which uses the rolevalue('_levelNumber',member) to determine the current depth of a IBM Cognos PowerCube. It then uses the conditional style box type attribute to toggle the views.

        If this approach does not work for you, you may need to create a similar flag expression using the OLAP levels function to determine that you are beyond the first level.
        • igorM
          igorM
          302 Posts
          ACCEPTED ANSWER

          Re: Cognos Viewer: How to preserve only the rows I need

          ‏2013-02-06T06:36:58Z  in response to DanielWagemann
          For "organization unit" dimension I don't have any levels, because it is not logical to have levels on 'unbalanced' hierarchy. But I have levels on other dimensions like Time, Customer and Product which are all balanced dimensions and so levels makes sense.

          I tried to open your report in Report Studio, but getting and error: "The Report version 'http://developer.cognos.com/schemas/report/8.0/' is not supported. Expected report version 'http://developer.cognos.com/schemas/report/6.0/' " - this is probably due to your version of BI is higher then my and there is not backward compatible.
          • DanielWagemann
            DanielWagemann
            411 Posts
            ACCEPTED ANSWER

            Re: Cognos Viewer: How to preserve only the rows I need

            ‏2013-02-06T11:45:48Z  in response to igorM
            Try changing the the schema version from 8 to 6 and see if the report opens. It may not run but at least you can have a look at it.
            • igorM
              igorM
              302 Posts
              ACCEPTED ANSWER

              Re: Cognos Viewer: How to preserve only the rows I need

              ‏2013-02-07T13:29:50Z  in response to DanielWagemann
              Changing schema from 8 to 6 and "Open from Clipboard" and no error returned but nothing was displayed.
              • DanielWagemann
                DanielWagemann
                411 Posts
                ACCEPTED ANSWER

                Re: Cognos Viewer: How to preserve only the rows I need

                ‏2013-02-07T15:10:49Z  in response to igorM
                Boo! I really do not feel like back porting the spec. Can you see what kind of response you get from the roleValue calc.

                rolevalue('_levelNumber',member)
                


                OR if that does not work, what happens when you use the levels function.
                Updated on 2014-03-25T07:43:14Z at 2014-03-25T07:43:14Z by iron-man
                • igorM
                  igorM
                  302 Posts
                  ACCEPTED ANSWER

                  Re: Cognos Viewer: How to preserve only the rows I need

                  ‏2013-02-11T08:08:59Z  in response to DanielWagemann
                  I tried:
                  
                  rolevalue(
                  '_levelNumber',[2013])
                  

                  2013 is a year member of Time dimension.

                  When validating the expression I got warning (not error):
                  
                  "OP-ERR-0144 Grouping on a property or property expression: dataItem='test',expresion='rolevalue('_levelNumber',['2013'])' is valueSet='13' is not supported.
                  

                  Running a report it returns '2013'. If I understand correctly it should return 1, 2, 3 etc. so level number.
                  I also tried:
                  
                  levels ([MYCUBE].[TIME_DIMENSION].[TIME_HIERARCHY],1)
                  

                  validating OK, running it returns all of the years in Time dimension like: 2012, 2013 etc.

                  General syntax for levels function is:
                  
                  levels(hierarchy, index
                  

                  where index is a level number. Don't think this is a function that we are looking for.

                  I also tried level function with general syntax:
                  
                  level(member)
                  

                  but it returns all of the members in the level - so if member is 2013 then it returns 2012, 2013 etc.
                  • DanielWagemann
                    DanielWagemann
                    411 Posts
                    ACCEPTED ANSWER

                    Re: Cognos Viewer: How to preserve only the rows I need

                    ‏2013-02-11T14:17:21Z  in response to igorM
                    Have a look at the attached 8.4.1 report spec. Since I am not allowed to use any of the useful calculations, I had to do it with an if then else that provides a flag of 0 or 1 to determine if you are at the highest level. I don't like this approach but it works.