IBP - Editable key figure only editable on lowest (base) planning level. Restrict Editability.

[vincentverheyen]

Let's say in IBP, we have a stored editable Key Figure on base planning level Month + Product + Location.

Can you restrict the editability of a key figure to only allow edits on the lowest (base) planning level.

That is: to restrict editability on higher levels, for example on Month + Product level.

[riyazahmed_ca2]

Hi Vincent,

There are no standard ways to my knowledge at Excel Addin level. Of course there are procedures at Data Integration App level by file and CI-DS methods using maintenance in Global Configuration App.

Now, I can see only one way out using VBA Hooks. You can make use of IBPBeforeSend Hook in which you can write code to make it send the changes only if the KF is available in Base Level, else you can return the error(say 'KF not updated in base planning level') and stop the data from writing into cloud.

Use the link IBPBeforeSend Application help to help yourself with the complete detailing of the IBPBeforeSend Hook with the relevant example that may help you.

Best Regards,

'Riyaz'

[vincentverheyen]

Hi @riyazahmed_ca2. Agreed with you that CI-DS seems the only way. With regards to VBA hooks, it will still be highly dependent on the restrictions on the Planning Views. If they have any other planning view or access to edit planning view, then they could add the key figure in another planning view et cetera. I am familiar with the IBPBeforeSend. However, with regards to the specifics of the code that could check whether or not the view is at the base planning level of a Key Figure, if you would have a sample, it would be much appreciated.

[riyazahmed_ca2]

@vincentveheyen,

Please check below code. This wasn't tested, but expected to be working well with my VBA confidence. Let me know if successful.

Private IBPAutomationObject As Object

Function IBPBeforeSend(callMode As String) As Boolean 
	If callMode = "SAVE" or callMode = "SIMULATE" or callMode = "CREATE_SIMULATION" Then 

		‘declare the variables 
		Dim loc As Integer 

		'show a message box if location not found in the workbook and stop the save
		
		On Error GoTo ErrorHandling:
			If IBPAutomationObject Is Nothing Then Set IBPAutomationObject =
			Application.COMAddIns("IBPXLClient.Connect").Object

		'Check if the cell includes the Location Id
		loc = Target.Formula2
		If InStr(1, loc, "=@ EPMOlapMemberO(""[LOCID].[].[") = 0 Then
			MsgBox "Keyfigure not editable without Location", vbOKOnly
			IBPBeforeSend = False
		Else
			IBPBeforeSend = True
		End If
	End If
End Function 

Best Regards,

'Riyaz'

[vincentverheyen]

@riyazahmed_ca2

Please find the following code at the bottom of this post.

Would you know how to adapt it such that it specifically checks whether or not there have been any specific changes (edits) to that particular editable Key Figure.

It currently only checks whether or not the Planning View is being simulated / saved ... and if that certain Key Figure is present.

Private IBPAutomationObject As Object

Function IBPBeforeSend(callMode As String) As Boolean
If callMode = "SAVE" Or callMode = "SIMULATE" Or callMode = "CREATE_SIMULATION" Then

Dim Target As Range
Dim cell As Range
Dim searchString As String
Dim cellValue As String
Dim keyFigureFound As Boolean
Dim locIDFound As Boolean
Dim prdIDFound As Boolean
Dim errorMessage As String

Set Target = ActiveSheet.UsedRange

For Each cell In Target
If Not IsEmpty(cell) Then
cellValue = cell.Formula
searchString = searchString & cellValue
' Check if Key Figure "ZZZEXAMPLEOFAKEYFIGURE)" is present.
If InStr(1, cellValue, "EPMOlapMemberO(""[KEY_FIGURES].[].[ZZZEXAMPLEOFAKEYFIGURE]") > 0 Then
keyFigureFound = True
End If
' Check if Location ID attribute is present
If InStr(1, cellValue, "EPMOlapMemberO(""[LOCID].[].[") > 0 Then
locIDFound = True
End If
' Check if Product ID attribute is present
If InStr(1, cellValue, "EPMOlapMemberO(""[PRDID].[].[") > 0 Then
prdIDFound = True
End If
End If
Next cell

' If Key Figure is present
If keyFigureFound Then
' Check if attributes "Location ID" and "Product ID" are present in the Planning View.
If Not locIDFound And Not prdIDFound Then
errorMessage = "s ""Location ID"" and ""Product ID"""
ElseIf Not locIDFound Then
errorMessage = " ""Location ID"""
ElseIf Not prdIDFound Then
errorMessage = " ""Product ID"""
End If

' Display the error message
If errorMessage <> "" Then
MsgBox "Please add the Attribute" & errorMessage & " to the Planning View." _
& vbNewLine _
& vbNewLine & _
"Error:" _
& vbNewLine _
& vbNewLine & _
"Edits on Key Figure ""ZZZEXAMPLEOFAKEYFIGURE"" should not be made on a higher level. " & _
"Both the attributes ""Location ID"" and ""Product ID"" should be present in the Planning View.", vbOKOnly
IBPBeforeSend = False
Else
IBPBeforeSend = True
End If
Else
' If Key Figure is not present
IBPBeforeSend = True
End If

End If
End Function

[Rigor]

Hi @vincentverheyen, another idea would be to create:

- Dummy simple master data entry for the base planning level (Location in your example)
- Planning objects for the dummy MD entry on the base PL
- Permission filter with option Location <> DUMMY and assign to users

When changing values on Month + Product, IBP will try to disaggregate them to Month + Product + Location and give authorization issue. This proposal depends on KF disaggregation settings (can confirm for Equal Distribution / Same Key Figure - Stored Values).

[vincentverheyen]

@Rigor Interesting. Have you ever used this before? With what process did you create the Planning Objects, and also: will this work for 1 particular Key Figure only? We do not want to restrict the entry on higher level for other Key Figures that use this base Planning Level.

[Rigor]

@vincentverheyen, actually I learnt this from an issue. One of our users had authorization issue, he didn't have access to one of the lowest level planning objects when changing data on a higher level. So I thought this error-driven behaviour could help with your requirement. And yes, this would affect other key figures as well.