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 then copying all the data from the UD2 dimension to UD8 dimension in order to show the two columns. However, it is not working for me.

I used the following code in dynamic calc formula for UD8: B2C_PGC_Provider so that I can reference it to UD2 ( all member dimensions and included data): 

Dim U2Filter As List(Of Member)=api.Members.GetBaseMembers(api.Pov.UD2Dim.DimPk, api.Members.GetMemberId(dimtype.UD2.Id, "Programs_Provider"))
For Each memberObj In U2Filter
BRApi.ErrorLog.LogMessage(si, memberObj.Name)
Next.

Please let me know what is wrong and how this can be resolved.

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.
  • CarlosAlvear's avatar
    CarlosAlvear
    Contributor
    12 months ago

    Hi there,

    If the original question is to add 2 members that belong to the same dimension, you can create a technical member parent that has as children the 2 members you want to add. You can create this in a parallel hierarchy to avoid messing the current structure.

    Another way to implement is to set up rows (or columns) for every base member to add and then use a CVR (CVC) function to make the sum of the previous 2 rows (columns)

    Best regards

  • CarlosAlvear's avatar
    CarlosAlvear
    Contributor
    12 months ago

    Hi,

    I think it's a better approach if you create a parallel hierarchy in a UD2 dimension taking the members from the other UD2 dimension.

    Regards

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

      Thanks for the idea! However, these dimensions are used in a lot of other places, hence a parallel hierarchy approach wouldn't help me in this case.

  • CarlosAlvear's avatar
    CarlosAlvear
    Contributor
    12 months ago

    Hi, why wouldn't help? You can set a weight of 0 so there's no impact on the sums that you have already implemented in the 1st dimension.

    BR

    Carlos

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

    Maybe I am not understanding you correctly. I created/copied a child under Programs_Package as" Programs_Provider" and put the aggregation to 0. However, it is still not working out for me when I use a cube view. Can you please explain to me in detail how to create this hierarchy. so that it shows both the UD2s

     

    • CarlosAlvear's avatar
      CarlosAlvear
      Contributor
      12 months ago

      Hi, could you share some images? When you use a member in another dimension, this member is shown as grey to indicate that the original dimension is elsewhere. If it's black, you're creating a new member. 

      Good luck!

  • VishalPai's avatar
    VishalPai
    Contributor
    12 months ago

    rchaturvedi27 , as per my understanding of your above question, I have tried putting across solution as below hope this helps.

    If Hierarchy expansion and Suppressions are required, then Expansions and Filters Like Keep, Remove can applied on below

    ABC and DEF can be replaced with your members

    Column Format

    Row Format

     

  • CarlosAlvear's avatar
    CarlosAlvear
    Contributor
    11 months ago

    Hi Rchaturvedi,

    Thank you for explaining further the issue. I think that the idea of a cube is that you have sorted data, meaning that the data cells are easily identifiable by the coordinates defined by the selected members for each dimension. In this case it seems that you have to search for a cell which has 2 different values for UD2 which is not allowed. By default, I'd try to move one of the branches to another dimension, maybe to a UDx different to UD3.

    Just by curiosity, what do you expect to recover when you have as input "Programs_provider/Al_Jazzeera" x "Programs_SPP/Al_Jazzeera"?

    Thank you.

    BR, Carlos