multiple value validation in epmDimensionOverride

[Former Member]

Hi experts,

is there anyway that i could do to validate multiple value in parameter member in epmDimensionOverride function?

for example:

dimension Account

ID     IS_CALC

A     X

B     X

C     X

D     Y

E     Y

F     X

G     X

C29 is cell that user can select multiple value from all available data (no restriction) by using epmContextMember

C29 contains A, B, C, D, E, F, G

EPMDimensionOverride("000", "ACCOUNT",C29)

what i want to do is filter a set of data in C29 which ONLY the value of member property IS_CALC = X

expected result is only show: A, B, C, F, G

thanks.

[bishwajit_das]

Use...

Cell C29 =EPMContextMember(,"ACCOUNT","IS_CALC = X")

then EPMDimensionOverride("000", "ACCOUNT",C29)

this will filter a set of data in C29 which ONLY the value of member property IS_CALC = X

so your expected result is only : A, B, C, F, G

Regards,

Bishwajit

[former_member186338]

Hi Bishwajit,

Please don't post that you don't check. The =EPMContextMember function returns a single member, the filter is set only for the user selection.

Vadim

[gunesbt]

Hi Vadim,

I write =EPMDimensionOverride("000","COST_CENTER","BUS_AREA="&C6) and C6 contains 21,22,25.But the result in rows is the cost centers having 21 as bus_area not 21 or 22 or 25. The formula considers only the first business area not all 3 bus areas seperated by comma in C6.I also tried ";" instead of "," .

What is wrong with the formula?

[former_member186338]

Hi Gunes,

Your question is not related to this discussion, but property criteria in EPMDimensionOverride doesn't support list of properties. But you can use:

=EPMDimensionOverride("000","COST_CENTER","BUS_AREA=21,BUS_AREA=22,BUS_AREA=25")

Vadim

[former_member190501]

Hi,

How can you select multiple values using EPMContextMember? Using EPMContextMember user can select only one value.

Regards,

Raju

[Former Member]

Hi Vara,

ups i am sorry. good catch

i mean in epmOlapMember function with multiple selection checkbox is ticked or using macro.

actually i am using macro in my input template.

the code is like below

"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim dimension As String

    If Target.address = "$C$29" Then

        dimension = "ACCOUNT"

        Call Member_Select(dimension, Target.address)

    End If

End Sub

"

thank you.

[former_member190501]

Hi,

Except Page axis selection you can not select multiple values .

Can you attach screen dump of your Member selector screen.

Thanks,

Raju

[former_member186338]

Hi Yoki,

For the page axis you can generate the EPMOlapMultiMember with the following VBA code:

Public Sub GenMulti()

ThisWorkbook.Worksheets("Sheet1").Range("A4").Formula = "=EPMOlapMultiMember(""1110,1117"",""000"",""[TITLES].[PARENTH1].[1110]"",""[TITLES].[PARENTH1].[1117]"")"

End Sub

Members have to be defined twice:

""1110,1117""

""[TITLES].[PARENTH1].[1110]"",""[TITLES].[PARENTH1].[1117]""

If you have a list of members you can generate the correct string.

Vadim