How to use Universe create new measure to compute percent ?

[Former Member]

HI Frieds,

I need create compute percent measure to universe.

Exemple:

Universe had Plant, QTY fields.

My BW Query datas:

Plant QTY

1000 100

1200 200

5500 300

6000 400

I need datas:

Plant QTY Percent TotalPercent

1000 100 0.1 0.1

1200 200 0.2 0.3

5500 300 0.3 0.6

6000 400 0.4 1

How to create percent,totalpercent measure for Universe?

Thank you.

Regards,

Howard.

[Former Member · Accepted solution]

Hi,

Here are the syntax that compute the percentages for a given measure and a given characterstic/hierarchy.

Percent over parent:

<EXPRESSION>[Measures].[0D_OORQTYBM] / ([Measures].[0D_OORQTYBM], [0D_CO_CODE].parent)</EXPRESSION>

Total percent:

<EXPRESSION>Aggregate( [0D_CO_CODE].firstsibling:[0D_CO_CODE].currentmember, [Measures].[0D_OORQTYBM])  / ([Measures].[0D_OORQTYBM], [0D_CO_CODE].parent)</EXPRESSION>

Didier

[Former Member · Accepted solution]

HI Didier,

Thank you very much.

I ask question:

How to find Dimension. " firstsibling" , " parent ","CurrentMember" ..... ?

Regards,

Howard.

[Former Member · Accepted solution]

HI Didier,

IF I had two char fileds:index and plant.

My BW Query datas:

Index Plant QTY

1 1000 100

2 1200 200

3 5500 300

4 6000 400

I need datas:

Index Plant QTY Percent TotalPercent

1 1000 100 0.1 0.1

2 1200 200 0.2 0.3

3 5500 300 0.3 0.6

4 6000 400 0.4 1

How to create totalpercent measure for Universe?

<EXPRESSION>Aggregate( [ZINDEX].firstsibling:[ZINDEX].currentmember,[0PLANT].firstsibling:[0PLANT].currentmember,[Measures].[D7WS0KH6EUHJYTLW4M46F6JGZ]) / ([Measures].[D7WS0KH6EUHJYTLW4M46F6JGZ], [ZINDEX].parent, [0PLANT].parent)</EXPRESSION>

THat is error.

Thank you.

Regards,

Howard.

[Former Member · Accepted solution]

Hi,

Theses expression are MDX expressions.

They describe relative postion based on a member of a given dimension.

- Ciurrentmember is the current member ...

- Parent is the parent of the current member

- First sibling is the first member in the list of memebers where the current member seats.

I recommend you to have a look to Microsoft documentation related to MDX: [MDX documentation|http://msdn.microsoft.com/en-us/library/aa216775.aspx]

Regards

Didier

[former_member793810]

Go through chapte 5 and 9.

[http://help.sap.com/businessobject/product_guides/boexir31/en/xi3-1_designer_en.pdf]

You can also see "OLAP universes best practices" for syntax help to create measures at universe level.

Regards,

Bashir Awan

[Former Member]

HI Bashir,

I have read, which means no I want to.

Thank you.

Regards,

Howard.

[Former Member]

Hi Howard,

Its been a task creating percent measure at the universe.

Mostly it is preffered to be done at reporting end.

But in XI3, we have a feature of smart measures that can help you achieve this.

Check the R3 designer guide for smart measures or visit the site www.dagira.com that might help you.

Regards,

Learner

[Former Member]

HI learner007 ,

I could to do the return value for bw query, but universe can't sustain .

Thank you.

Regards,

Howard.