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