need to change dimension member in context based on the member property

[Former Member]

Hi,

When i select the one particular entity i.e. E_1100 , it having the currency property is INR, this currency has to change  the RPTCURRENCY dimension member i.e. INR. in the context member.

Context is locked for the users.

for example:

In context i have Entity dimension, RPTcurrency dimension

                           E_1100               INR.

In report user having the select parameter for the Entity Dimension. Now he has selected the E_1200  in the report , but it has currency property is USD

now the RPT Currency dimension should have to display the  USD in the Context.

Please anybody help me.

Regards.

Surya

[Former Member]

Hi Surya the code that you have posted

epm.SetContextMember CONNE, "CURRENCY", Range("A1")

works with all version of EPM?

I'm using EPM 10.0

setcontextmeber seems that are not present in the API section

could you advise me a different instructions?

thanks a lot

Francesco

[meenubansal87]

Hi Surya,

As you said when you are selecting entity E_1100 whose property currency is INR so this INR should change the context of RPTCurrency dimension.

To achieve this, you can filter RPTCurrency based on the property value of Entity dimension (i.e. INR in your example).

=epmcontextmember(,"RPTCurrency", "ID="&A1)

where A1 is the cell having the currency property of Entity dimension which you can extract through EPMMemberProperty formula.

Regards,

Meenu

[Former Member]

Hi Meenu,

It not working.

Can please share example with the screen shot.

[Shrikant_Jadhav]

You can not override / change context member depending on other member of dimension until and unless dimension is in any axis.

All possible options are discussed ....

Shrikant

[meenubansal87]

Hi Surya,

Please follow below steps-

1. Take EPMContextMember for Entity dimension in Cell A1.

2. Retrieve the currency property of Entity dimension in Cell A by writing EPMMemberProperty(,A1,"<propertyname>").

3. Now type =epmcontextmember(,"RPTCurrency", "ID="&A1) in cell A3.

4. Refresh the sheet.

Regards,

Meenu

[Former Member]

Hi meenu

I think your approach is correct only when you do refresh manually. But author want it should be done dynamically.

[Former Member]

Hi surya,

Did you try using VBA that already shared in my previous post? i have already shared two ways one is manual trigger and second is automatically.Below code will run whenever there is change in context. But i think you can achieve this only using VBA. In my system it is working perfectly.Try this and let me know if there are any problem.

Dim epm As New FPMXLClient.EPMAddInAutomation

Function AFTER_CONTEXTCHANGE()

Dim CONNE As String

CONNE = epm.GetActiveConnection(ActiveSheet)

'To set context

epm.SetContextMember CONNE, "CURRENCY", Range("A1")

End Function

[meenubansal87]

Hi Surya,

You can check "Automatic Refresh on Context Changes" from User options but that refresh the sheet whenever you change context member.

Or you can use VBA instead.

Regards,

Meenu

[Former Member]

Hi Meenu,

Is it working in your system? Because in my system it is not working, it is not changing my context automatically. When i double click on cell that contains context member of currency only single currency is available for selection, and i have to select that currency manually then it is updating.

[meenubansal87]

Hello,

By default it will not select and it is not possible also. You have to double click and select that particular currency in Currency dimension forcefully.

Regards,

Meenu

[Shrikant_Jadhav]

Hi Surya,

Kindly close the old thread if quires are resolved.

How to close a discussion and why and The SCN Rules of Engagement

About your query I am not sure that whether we can change/ override the context depending on other dimension member selection.

You can add currency dimension in row axis and use "Dimension property" in edit report member selector. Hide that dimension in template

Shrikant

[Shrikant_Jadhav]

refer the following screen shot

Shrikant

[Former Member]

Hi shrikant,

Dimension property is missing my report editor selection.

I am using epm version 10.0 sp18 .net 4,

Regards.

Surya

[Shrikant_Jadhav]

Hi Surya,

Do not select any member as you selected context member in member selector.

Shrikant

[Former Member]

Dear sir,

Please under stand my requirement,

i need to change the context member dynamic it has to change.

Please...........

Regards.

Surya

[bhagyesh_ravange]

Hello Surya,

In screen shot which you have posted, you have selected one member and hence you are not able to see the options. Try not to select any member of Currency dimension and then select the drop down option.

Regards

Bhagyesh Ravange

[Shrikant_Jadhav]

As per my understanding its not possible.

Wait for other expert advice .

Shrikant

[bhagyesh_ravange]

Hello Surya,

Try the this method,

1) Through EPMMemberProperty formula call the property value of ENTITY dimension in one cell. For this maintain Currency property in Entity Dimension. In the below case I have call the Currency property value of Entity Dimension in Cell E1 (i.e EUR).

2) Give the Cell reference in the EPMOlapMemberO formula. In the below case I have Currency Dimension in Column axis and had given the reference of E1. Then Hide the particular Row.

Regards

Bhagyesh Ravange

[Former Member]

Hi Surya,

I think it is not possible to change context member dynamic depending on the selection of other dimension's member.

But for your requirement you can use dimension override functionality. please refer below screen shot

Regards,

Sushant

[Former Member]

Hi Surya,

I think there are some mistakes in the above screen shot.

You can hide column D instead of column C. or simply change the sequence of the dimension in the report as shown in below screen shot.

Regards,

Sushant

[Former Member]

Hi Sushant Patil,

Befor it self I have done it by dimension override, but the management want " context has be  change dynamically".

If any suggesion

Thxs for you input.

Regards.

Surya

[Former Member]

Hi,

Thxs for you input.

Please understand my requirement.

You have taken RPTCurrenc dimension in column axis, that is not acceptable for me.

i am not considering the RPT Currency in column axis. I am considering only the Category and Time Dimension in the Column axis.

By using Entity dimension the RPT curency dimension member has to change.

My management not accepting to consider Currency dimension in column axis.

I Knew that what u have done. but dimension- RPTCurency has to change dynamically in CONTEXT

Please help me.

[bhagyesh_ravange]

"RPTCurency has to change dynamically in CONTEXT", you mean to say in EPMContextMember formula.

Regards

Bhagyesh Ravange

[Former Member]

Sir,

No In CONTEXT bar.

i have only one select parameter i.e ENTITY

[bhagyesh_ravange]

I think this requirement can't be achieved. If possible you can achieve your requirement by above given solution and hide the row containing Currency Dimension. Further hide the same Currency dimension from EPM context bar.

[Former Member]

Hi Surya,

you can achieve this using simple VBA code. Use below vba code for overriding the context member. assign this code to button. it will change the context member.

Copy paste below code in one module and assign this code to the button.

First Enable FPMXLClient

Dim epm As New FPMXLClient.EPMAddInAutomation

Sub change context()

Dim CONNE As String

CONNE = epm.GetActiveConnection(ActiveSheet)

'To set context

epm.SetContextMember CONNE, "CURRENCY", Range("A1")

'To refresh

epm.RefreshActiveWorkBook

End Sub

where CURRENCY==> use your dimension name whose context want to change

Range("A1") ==> In A1 cell Populate property using EPMMemberproperty()

Basically we will set context member and refresh the workbook.After selection of the entity click on button so macro will run and context member will gets override in the Context Bar.

Regards,

Sushant

[Former Member]

Hi Surya,

You can also use another approach whenever you change the context member (entity) value of the cell A1 gets changed according to property that you have maintained. whenever you change context below vba code will execute and the context member  will changed accordingly automatically.

You can use AFTER_CONTEXTCHANGE function

First Enable FPMXLClient references and use below VBA code to change context automatically.

Dim epm As New FPMXLClient.EPMAddInAutomation

Function AFTER_CONTEXTCHANGE()

Dim CONNE As String

CONNE = epm.GetActiveConnection(ActiveSheet)

'To set context

epm.SetContextMember CONNE, "CURRENCY", Range("A1")

End Function

Use your dimension names.

Hope this will help you.

Regards,

Sushant