on 2024 Feb 21 9:26 AM
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.
Request clarification before answering.
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'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@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'
@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
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
5 | |
4 | |
4 | |
3 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.