Forum Discussion

Ole_S_P's avatar
Ole_S_P
New Contributor II
3 months ago

Drill down Chart in BI Viewer.

Hi

 I have a problem with dril Down in BI Viewer.

 Part of our UD1 is as follows:

 

BU1000 - EGMONT GROUP

        
 

BU1500 - Other Business line

      

 

 

 

 

 
   

BU6000 - HOLDING AND SERVICE

  
     

BU6100 - HOLDING COMPANIES
         
             

 

I have done the following:

1: Cubeview

 

 

 

Cubeview exportet to excel gives this

 

 

Please notice that top level BU1500 do not aggregate the data. And this is correct, totally 1.7.

 

2: Dataadapter:

 

Pivot in  BI Viewer looks like this:

 

 

 

My drill down in Charts i BI viewer  looks as follows:

Start: Total 5.1

 

 

Drill first level: Total 5.1

 

 

Drill second level: Total 3.4

 

The red box should not be there

 

Drill third level: Total 1.7

 

The red box should not be there

 

Drill forth level: Entity level (sums up to 1.7)

 

 

 

My questions are as follows, hopefully answering one solves all…

 

1: I can see in the pivot tablet has the amounts three times. (1.7+1.7+1.7=5.1) where it should be 1.7. This is also giving errors in the charts. How can I avoid this?

2: In the charts I have some ”Ghost” totals as they have one bar for the specification and one bar as a total. How can I avoid this. (the red markings)

3: If a subdivision has 5 sub-subdivisions then entity specifications is 5 click. If another subdivision only has 3 sub-subdivisions, the I still need to click 5 times. Any way to avoid this. (Yes this is icing on the cake)

BI Viewer
Cube View
Dashboards
  • Ole_S_P's avatar
    Ole_S_P
    2 months ago

    Hi

    I have finally found a solution that is working. Thanks for the help.

    Definition of Column in Cube View:

     

    It is important to use Base. I this case ”Where(Text7=’ ’)” is used to exclude joint ventures and associates.

     

    Definition of Row in Cube View:

     

    IMPORTANT: It does not work using TreeDescendantsInclusiveR

     Data adapter:

     

    As Columns is base only use Outermost Row. It will only work if I loop on UD1.

     

    Bi Viewer:

    I have made a calculated field with RecOK with the formula: iif([Amount]<>0,'Value','Zero'). By this I can remove all companies with no amount in the drill down.

    The chart has the following filter:

     

     

     

    The UD1 levels should be sorted on amount:

     

     

    After sorting in reverse orden the graph looks like this. I can now drill down to sublevels in UD1 and finally end in a specification on Entity.

     

     

     

  • Ole_S_P's avatar
    Ole_S_P
    New Contributor II
    2 months ago

    Hi

    I have finally found a solution that is working. Thanks for the help.

    Definition of Column in Cube View:

     

    It is important to use Base. I this case ”Where(Text7=’ ’)” is used to exclude joint ventures and associates.

     

    Definition of Row in Cube View:

     

    IMPORTANT: It does not work using TreeDescendantsInclusiveR

     Data adapter:

     

    As Columns is base only use Outermost Row. It will only work if I loop on UD1.

     

    Bi Viewer:

    I have made a calculated field with RecOK with the formula: iif([Amount]<>0,'Value','Zero'). By this I can remove all companies with no amount in the drill down.

    The chart has the following filter:

     

     

     

    The UD1 levels should be sorted on amount:

     

     

    After sorting in reverse orden the graph looks like this. I can now drill down to sublevels in UD1 and finally end in a specification on Entity.

     

     

     

  • ckattookaran's avatar
    ckattookaran
    VIP
    3 months ago

    The pivot table is not aware of financial functions, it is just like EXCEL pivot. I see three different levels of totals in that one. The same goes for Charts. What is the data adapter, and how does it look when you run it in Workspaces/Dashboards?

    • Ole_S_P's avatar
      Ole_S_P
      New Contributor II
      3 months ago

      Hi

      Data adapter:

       

      Loaded into dashboard it does not change anything:

       

      It is correct that it has subtotals, but they are important when drilling down.

      Regards

      Ole

       

       

       

  • ckattookaran's avatar
    ckattookaran
    VIP
    3 months ago

    You can run the data adapter from that screen and see if BU6000 is repeated. Is it the exact adapter you are using for the grid below? Also what are your arguments for the chart?

  • Ole_S_P's avatar
    Ole_S_P
    New Contributor II
    3 months ago

    yes, it is the data adapter used. What do you mean by arguments? This is the setup

     

    • Ole_S_P's avatar
      Ole_S_P
      New Contributor II
      3 months ago

      Hi

      It does not have an impact.

      Inspect data looking like this

      Drilling one step down giving this. And this should only show the amount column BU6100

      Inspect data look like this

      I do not think it should show an amount when UD1_Level_3 is blank.

      Raw data 

       

      In cube view I have moved the account from Advanced/Rows/Nested Member Expansion 3 to be a part of point of view. 

      With this result

      It did not change the result in Bi Viewer. It still ads BU6000 and BU6100 even though BU6000 is higher in the hierarchy

       

       

       

       

       

       

       

       

       

       

       

       

       

  • ckattookaran's avatar
    ckattookaran
    VIP
    3 months ago

    I think it is the leveling. When you level, it must still add those rows for the parent members. It is similar to the drill on the cube view. What if you change the UD1 to POV and use the loop to expand to the members you need?

    • Ole_S_P's avatar
      Ole_S_P
      New Contributor II
      3 months ago

      Hi 

      I realy appriciate your help. As I am quite new into OneStream I have tried several solutions without the wanted result. As I understand it now is that if I use TreeDecebtabtsInclusive in cube view I do not need to loop in dataadapter. I have tried to use AllUD1 in cube view and loop in dataadapter with no succes. 

      [EXTERNAL URL LINK REMOVED]

      Important places:

      - Starting with Cube View, and settings for UD1 and Entity.

      - After 20 seconds the dataadapter. No Loop but Dimension to Level set to Outermost Row

      - After 44 seconds BI Viewer with dummy values (no difference if it were running in a dashboard). As you can see in the orange box the total for BU1500=BU6000=BU6100 should be approx 12.6M kr.

      - After 1:16 I have two bars. The one with orange number 1 should not be there as it is a subtotal. BU6000 should be 12,6 but it adds BU6000+BU6100 and therefore giving 25,3.

      - After 1:30 I have drilled down and have two bars. The one with orange number 1 should not be there as it is a subtotal. BU6100 is correctly 12,6.

      - After 1:42 I am at the lowest level and it is giving me entity names.

       

       

       

       

       

      • MG's avatar
        MG
        Community Manager
        3 months ago

        Hi there.

        I would recommend you raise a ticket with the support team as they will be able to share a screen with you to take a look at the issue and provide some advice/ help as necessary. 

        Thanks