SAP EPM10.1 Worksheet Generation in the report

[Former Member]

Hi

I'm facing the issue in EPM10.1 for Worksheet generation.

In EPM10.1 we are following the steps for worksheet generation as explained below.

We are in EPM10.1 SP27. Below are the steps.

1. Select Worksheet generation under Report actions in EPM10.1

2. Select Fundcentre

3. Remove existing members in “Member Selector”.

4. Select “Filter by properties” and select “TYPE = GROUP”

5. Select “ID” from Display

6. Select any Member for ex: “G_B_FINCE” and select "Descendants", and from pencil button, select only Level 2

Under the G_B_FINCE group, two FundCentres exist F130D and F132D, those two FundCentre data will spread across the sheet.

Please find the attached image.

Now the user not required to follow the steps as given above, in BPC 7.5 if press “REFRESH” button automatically sheets are generated, but in EPM10.1 we need to follow the steps.

Please let me know other alternative solution, either VBA code or any other alternative solution.

I searched in SDN and got one macro code, but this code is not giving each fundcentre in each sheet as shown in the image.

https://archive.sap.com/discussions/thread/3714959

[former_member186338]

Please correct your post - instead of "Insert File" use correct "Insert Image" to make your post readable.

[Former Member]

Hi Vadim

You are not doing complete my job, we are asking for help. Then my suggestion doesn't provide design steps, we all know these type of steps, if you are able to provide some technical help, please provide else ignore.

Anyhow thanks for your suggestion.

Not helpful answer.

Regards

Madhuvani

[former_member186338]

Sorry, but I see no attempts from your side to demonstrate your code. I have provided you the sequence of steps to implement. And...

[Former Member]

In BPC 7.5 used the below code, but in template they didn’t declare “GET_ID_HIREARCHY” anywhere, not sure, how it worked in BPC7.5, same BPC7.5 commands will work in EPM10.1 but still the code is not working

Instead of this, we gave in EPM10.1 the below steps

1. Select Worksheet generation under Report actions in EPM10.1

2. Select Fundcentre

3. Remove existing members in “Member Selector”.

4. Select “Filter by properties” and select “TYPE = GROUP”

5. Select “ID” from Display

6. Select any Member for ex: “SSSS” and select "Descendants", and from pencil button, select only Level 2

Under the SSSS group, two FundCentres exist F130 and F132, those two FundCentre data will spread across the sheet.

Our hierarchy levels are as given below

XXXX HLEVEL = 1

YYYY HLEVEL = 2

ZZZZ HLEVEL = 3

SSSS HLEVEL = 4

F130 HLEVEL = 5

F132 HLEVEL = 5

I passed in the template as =GET_ID_HIERARCHY(APP,"SSSS",3) is this correct passing the parameters, in above steps "DESCENDANTS" with LEVEL 2 given to generate worksheets, it is working fine, only reason user not to follow steps, just with REFRESH button automatically sheets need to spread with different Fundcentres (F130, F132).

I'm not sure about the macro code to write regarding spread sheet generation.

Macro code as given below:

Function Get_ID_Hierarchy(ByVal app As String, ByVal mdId As String, ByVal hlevel As Integer) As String

Dim curlevel As Integer

Dim jumplevel As Integer

curlevel = Val(Application.Run("EVPRO", app, mdId, "HLEVEL"))

jumplevel = curlevel - hlevel

If jumplevel > 0 Then

nextid = mdId

For ijump = 1 To jumplevel

nextid = Application.Run("EVPRO", app, nextid, "PARENTH1")

Next

End If

Get_ID_Hierarchy = nextid

End Function

Sub Refresh()

Dim isProtected As Integer

Dim currWS As String

currWS = ActiveSheet.Name

Application.ScreenUpdating = False

Application.EnableEvents = False

Application.Calculation = xlCalculationManual

For Each ws In Worksheets

If ws.Name Like "F*" Then

ws.Activate

isProtected = 0

If ActiveSheet.ProtectContents = True Then

ActiveSheet.Unprotect Password:="xxxx"

isProtected = 1

End If

Range("HIDE_RANGE").EntireRow.Hidden = False

If isProtected = 1 Then

ActiveSheet.Protect Password:="xxxx", DrawingObjects:=True, Contents:=True, Scenarios:=True _

, AllowFormattingColumns:=True, AllowFormattingRows:=False

End If

End If

Next ws

Sheets(currWS).Activate

Application.ScreenUpdating = True

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

BEFORE_EXPAND = True

Dim ref As New EPMAddInAutomation

Ref.RefreshActiveWorkBook

Dim isProtected As Integer

Dim currWS As String

currWS = ActiveSheet.Name

Application.ScreenUpdating = False

Application.EnableEvents = False

Application.Calculation = xlCalculationManual

For Each ws In Worksheets

If ws.Name Like "F*" Then

ws.Activate

isProtected = 0

If ActiveSheet.ProtectContents = True Then

ActiveSheet.Unprotect Password:="xxxx"

isProtected = 1

End If

If Range("HAS_DATA") = 0 Then

If Left(ws.CodeName, 4) = "MAIN" And _

Len(ws.CodeName) <> 4 Then

ws.Delete

End If

Else

With Range("HIDE_RANGE")

.EntireRow.Hidden = False

For Each cell In Range("HIDE_RANGE")

If cell.Value = "Y" Then

cell.EntireRow.Hidden = True

End If

Next

End With

End If

End If

Next ws

Sheets(currWS).Activate

Application.ScreenUpdating = True

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

End Sub

[former_member186338]

Sorry, but EVPRO is EVDRE function used in BPC7.5 reports. In BPC10 we use EPM reports with EPM functions!

Are you using EVDRE report in BPC10? EVDRE reports has to be recreated using EPM functions. Not all EVDRE functions are working correctly in BPC10.

You need to get some training in EPM reports and EPM API, at least read help.

https://help.sap.com/viewer/ec68e48b18a44a49abb12b8ee8ae306f/10.0.32/en-US/f9b366096faf1014878bae8cb...

[former_member186338]

P.S. If you want to get a list of children of some member in EPM API we have:

GetChildrenFromMember

[former_member186338]

Sorry, but you need some VBA training (or ask somebody).

1. Fund Center in the report header has to be referenced to some cell containing member ID.

2. VBA first has to provide member selection to select required Fund Centers.

3. In the loop you copy original sheet changing the name to the member from member list

4. Change content of the referenced cell

5. Refresh

6. Loop another member.

[Former Member]

Hi Vadim

Thank you for the steps provided.

I have knowledge in VBA macro, but still this one not able to achieve. Could you please provide me VBA code, Fundcentre reference to achieve this requirement.

Regards

Madhuvani

[former_member186338]

You want me to do your job? For sure I can do it, but not for free!

[former_member186338]

Sorry, but what is the issue?