EPM Add-in. How to add members in RowAxisMembers by API

[former_member440277]

Hi, experts.

There is array with members, which are need to add into row axis (two dimensions in axis).

I was tried add members in VBA cycle by FPMXLClient.EPMAddInAutomation and AddMemberToRowAxis, but if count of members is big (near 1000) the VBA-procedure is not responding.

Is anybody knows another way to create custom row axis or launch guided member recognition by API?

[former_member186338 · Accepted solution]

In general you want to have report with many-to-many relationship between dimension1 and dimension2!

Please read my blog how to achieve it without VBA:

https://blogs.sap.com/2017/03/04/report-or-input-form-with-many-to-many-relationship-between-2-dimen...

[former_member440277 · Accepted solution]

It`s interesting way. I'm not sure that this fully solves my problem, but thank you very much for good idea.

[former_member186338 · Accepted solution]

"I'm not sure that this fully solves my problem" - why not?

In the matrix you can establish all possible relations!

If you already have property filled you can use script to fill the matrix automatically!

[former_member186338]

Try to enable member recognition by:

SetSheetOption(ThisWorkbook.Worksheets("Sheet1"),2,True)

Then fill the cells in a loop.

Disable member recognition:

SetSheetOption(ThisWorkbook.Worksheets("Sheet1"),2,False)

P.S. Strange requirement to add 1000 members in VBA...

Can you explain the logic you want to achieve?

[former_member440277]

Hi, Vadim!

Thank you for answer.

Good idea to use SetSheetOption, but it doesn`t not work on my report yet. may be need some other options of SetSheetOption.

my end purpose is report, which contains in row axis two dimensions. members should be grouped like this:

[dim1].[member1] [dim2].[member1]

[dim1].[member1] [dim2].[member2]

...

[dim1].[member1] [dim2].[memberConst1]

[dim1].[member1] [dim2].[memberConst2]

[dim1].[member2] [dim2].[member5]

[dim1].[member2] [dim2].[member6]

...

[dim1].[member2] [dim2].[memberConst1]

[dim1].[member2] [dim2].[memberConst2]

i created two reports in othe sheets. each report contains two dimensions in row axis.

first report contains members of dimension1, which selected by property, depends on member (different dimension3), selected by user. members of dimension2 are fixed list, for each member of dimension1.

in second report members of dimension1 linked to property of dimension2 and members of dimension2 selected by properties, depends on the user choice.

and now i want to combine the members of these two row axes in third report.

may be this task has a different way.

[former_member186338]

"but it doesn`t not work on my report yet." - sorry, but you have to show your code! Never had issues with SetSheetOption.

Explanation is not clear, it's better to provide screenshots!

P.S. In my sample the syntax has to be corrected due to SetSheetOption is Sub:

epm.SetSheetOption ThisWorkbook.Worksheets("Sheet1"), 2, True

where

Dim epm As New FPMXLClient.EPMAddInAutomation

[former_member440277]

Dim EPM As New FPMXLClient.EPMAddInAutomation

Set Sht = ThisWorkbook.Sheets(3)

ConnectionReport = EPM.GetConnections(Sht)

Set StartRangeReport = Sht.Range("D8")

Set StartRangeAxisRows = StartRangeReport.Offset(1, -1) ' first cell of range row axis

Cnt = UBound(ArrReportNewIds)

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

EPM.SetSheetOption Sht, 2, True

For i = 0 To Cnt Step 2

StartRangeAxisRows.Offset(i / 2, 0).Value = ArrReportNewIds(i)

StartRangeAxisRows.Offset(i / 2, 1).Value = ArrReportNewIds(i + 1)

Next i

EPM.SetSheetOption Sht, 2, False

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True

format of members in array ArrReportNewIds looks like [Dimension].[PARENTH1].[Member]

[former_member186338]

With

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

member recognition will not work to my mind!

Start with simple code, try to add single member with member recognition enabled!

[former_member440277]

yes, without

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

it`s working, but very slowly (about one record per second).

may be there is a posibility to Launch Guided Member Recognition by VBA?

[former_member186338]

Sorry, but what do you mean by "Guided"??

There is no way to programmatically affect the member recognition.

Still not clear what are you trying to achieve. Not sure that VBA is the right solution for your requirements, but can't help without clear specification and business logic explanation.

[former_member440277]

i mean this optin:

[former_member186338]

There is no API to this menu function!

Have to ask third time - what are you trying to achieve?

[former_member440277]

I`ll try to explain.

In report must be two dimensions in row axis. for each member of Dimension1 need to show linked members of Dimension2 (linked by property) and for the same member of Dimension1 need to show fixed list members. i.e. for each member of Dimension1 need to show dynamic list and fixed list of members of Dimension2.

[former_member186338]

Sorry, but you what property you are talking about??? "linked by property" in what dimension?

Please provide real sample and real screenshot!

[former_member440277]

Id of member of Dimension1 = property of member of Dimansion2

[former_member186338]

Read my blog in another answer.

If you started you question with this picture I was able to show this blog immediately...