Solved

Drill down Chart in BI Viewer.

Forum|Forum|1 year ago
September 24, 2024
7 replies
4 views

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)

Best answer by Ole_S_P

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.

C

ckattookaran

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?

O

Ole_S_PAuthor

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

C

ckattookaran

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?

O

Ole_S_PAuthor

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

C

ckattookaran

From the left side, what if you remove Entity from Arguments of the chart object

O

Ole_S_PAuthor

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

C

ckattookaran

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?

C

ckattookaran

hmm, why are you looping on UD1 if you already got all UDs in the cubeview?

O

Ole_S_PAuthor

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.

M

MG8

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

O

Ole_S_PAuthorAnswer

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.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded