Forum Discussion

rchaturvedi27's avatar
rchaturvedi27
New Contributor II
12 months ago

How to add two dimension (UD2s) hierarchy in same row/column in cube view?

Hi All, I am trying to add two UD2 dimension members in the Cube view member filter and obviously , its not allowing me to enter them. I went through the approach of creating a dimension in UD8 and t...
Cube View
  • JackLacava's avatar
    JackLacava
    11 months ago

    I have a feeling Package and Provider should have been in two different UDs, but anyway...

    I'm still not entirely sure what your output should look like, maybe it would help if you created a mock in Excel with the desired final layout and showed it to us.

    Things I would keep in mind:

    • If you need to sum up values from multiple rows or columns, you can do it with the functions CVC (columns) and CVR (rows), even if the definition of these rows is actually on a single one. E.g. GetDataCell(CVR(1,1) + CVR(1,2)) would produce, on a new row, the sum of values from two output rows generated by the single definition U2#MemberA, U2#MemberB.
    • You can hide columns that you only need as intermediate steps in a calculation, by setting the Header Format property IsColumnVisible to CVMathOnly. So you could have a column with U2#MemberA, U2#MemberB, mark it as CVMathOnly, then have a second column with GetDataCell(CVC(1,1) + CVC(1,2)), and only the second column would be visible.
    • Creating a UD8 formula is an idea, but it will require a bit more information about the actual calculation that needs to be implemented.
    • If everything else fails, you can create a custom GetDataCell function. The general technique is illustrated in this blog post. At that point, you can do pretty much anything you want, again with a bit of code.
JackLacava's avatar
JackLacava
Honored Contributor
11 months ago

The system definitely allows multiple members in a single CubeView filter, like this: U2#MemberA, U2#MemberB. This will create two columns (or rows), one for each member. I don't understand what you're trying to do by duplicating the UD2 hierarchy in UD8.

By the way, that dynamic calc formula you posted is only printing out member names to the ErrorLog, not actually returning any value.

  • rchaturvedi27's avatar
    rchaturvedi27
    New Contributor II
    11 months ago

    Thanks for the tip ! Appreciate it. I am aware that OS  allows multiple members in a single CubeView filter, like this: U2#MemberA, U2#MemberB. However, In my case, Programs packagae and programs provider are related to each other in some cases. Hence, I wanted to use a column in which shows related program package for a program provider in the column. 

    Here you can see " AL_Jazzeera ) is a part of both Programs_Package and Programs_Provider.

     I wanted to show rolled up dimension TV Standard ( which is a part of Programs_Package shown against program provider cube view ( see first screenshot ). 

    Similary shown relevant programs_provider member against another cube view of programs_package. 

    • JackLacava's avatar
      JackLacava
      Honored Contributor
      11 months ago

      I have a feeling Package and Provider should have been in two different UDs, but anyway...

      I'm still not entirely sure what your output should look like, maybe it would help if you created a mock in Excel with the desired final layout and showed it to us.

      Things I would keep in mind:

      • If you need to sum up values from multiple rows or columns, you can do it with the functions CVC (columns) and CVR (rows), even if the definition of these rows is actually on a single one. E.g. GetDataCell(CVR(1,1) + CVR(1,2)) would produce, on a new row, the sum of values from two output rows generated by the single definition U2#MemberA, U2#MemberB.
      • You can hide columns that you only need as intermediate steps in a calculation, by setting the Header Format property IsColumnVisible to CVMathOnly. So you could have a column with U2#MemberA, U2#MemberB, mark it as CVMathOnly, then have a second column with GetDataCell(CVC(1,1) + CVC(1,2)), and only the second column would be visible.
      • Creating a UD8 formula is an idea, but it will require a bit more information about the actual calculation that needs to be implemented.
      • If everything else fails, you can create a custom GetDataCell function. The general technique is illustrated in this blog post. At that point, you can do pretty much anything you want, again with a bit of code.
      • rchaturvedi27's avatar
        rchaturvedi27
        New Contributor II
        11 months ago

        Thanks! for the detailed advice.  will have a look.