The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

EGM's avatar
EGM
New Contributor II
1 year ago
Solved

Help Needed with Confirmation Rule to Validate Annotations for Thresholds

Hello OS Community.

I'm working on a confirmation rule that is supposed to check for the presence of annotations when thresholds are defined. The intended behavior is:
If thresholds are present, but no annotation/comment is provided, the rule should fail validation.
However, the logic isn't working as expected. Even when no comment is given, the rule still passes and acts as if a comment is provided.
Here’s what I need help with Ensuring that the rule correctly identifies missing comments and fails as intended. Identifying what might be wrong in my current logic it seems to incorrectly detect comments even when none exist.

Any insights or examples of similar logic would be much appreciated!

 

dashboards
Finance
Parsing
XFBR
  • RobbSalzmann's avatar
    RobbSalzmann
    1 year ago

    Also, consider organizing your decision logic using named variables and using named strings for easier readibility.  This will save you confusing which quarter you're referencing and make your code easier to read and maintain:

    ' Constant suffix for all POVs except the view and time
Const povBase As String = ":E#100:C#Local:S#Actual:A#1014:F#Top:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None"

' Time periods
Dim currQtr As String = "2024Q1"
Dim prevQtr As String = "2023Q4"

' Compose POVs with data type (Periodic/Annotation) as prefix
Dim povCurrent As String = $"V#Periodic:T#{currQtr}{povBase}"
Dim povPrior As String = $"V#Periodic:T#{prevQtr}{povBase}"
Dim povAnnotation As String = $"V#Annotation:T#{currQtr}{povBase}"

' Get current and prior values
Dim qtdCurrent As Decimal = api.Data.GetDataCell(povCurrent).CellAmount
Dim qtdPrior As Decimal = api.Data.GetDataCell(povPrior).CellAmount
Dim variance As Decimal = qtdCurrent - qtdPrior

' Show calculated variance
args.ConfirmationRuleArgs.DisplayValue = variance
args.ConfirmationRuleArgs.Info1 =
    $"Current QTD: {qtdCurrent:N2}, Prior QTD: {qtdPrior:N2}, Variance: {variance:N2}"

' Check for annotation
Dim annotation As String = api.Data.GetDataCellEx(povAnnotation).DataCellAnnotation
Dim hasComment As Boolean = Not String.IsNullOrWhiteSpace(annotation)
Dim varianceExceedsThreshold As Boolean = Math.Abs(variance) > 15000000D

' Rule enforcement
If Not varianceExceedsThreshold Then
    args.ConfirmationRuleArgs.Info2 = "Variance within threshold. No comment required."
    Return True
ElseIf hasComment Then
    args.ConfirmationRuleArgs.Info2 = "Variance exceeds 15,000,000 and comment is provided. Rule passed."
    Return True
Else
    args.ConfirmationRuleArgs.Info2 = "Variance exceeds 15,000,000. Comment is required in the Comments column."
    Return False
End If

     

13 Replies

  • EGM's avatar
    EGM
    New Contributor II
    1 year ago

    Thank you. Now I have the opposite problem. The comment exist, but the confirmation rule says "comment is required" and wont pass. 🤯

    • MarcusH's avatar
      MarcusH
      Valued Contributor
      1 year ago

      Post the code you have now. Please use the Insert Code method - click on {;} at the bottom of the dialogue.

  • EGM's avatar
    EGM
    New Contributor II
    1 year ago
    ' Get current QTD value for Entity 100 (T#2024Q1)
Dim qtdCurrent As Decimal = api.Data.GetDataCell("Cb#Group:E#100:C#Local:S#Actual:T#2024Q1:V#Periodic:A#1014:F#Top:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None").CellAmount

' Get prior QTD value for Entity 100 (T#2023Q4)
Dim qtdPrior As Decimal = api.Data.GetDataCell("Cb#Group:E#100:C#Local:S#Actual:T#2023Q4:V#Periodic:A#1014:F#Top:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None").CellAmount

' Calculate variance
Dim variance As Decimal = qtdCurrent - qtdPrior

' Display variance in amount column
args.ConfirmationRuleArgs.DisplayValue = variance

' Display value breakdown
args.ConfirmationRuleArgs.Info1 = "Current QTD: " & qtdCurrent.ToString("N2") & ", Prior QTD: " & qtdPrior.ToString("N2") & ", Variance: " & variance.ToString("N2")

Dim StringAnnotation As String =	api.Data.GetDataCellEx("Cb#Group:E#100:C#Local:S#Actual:T#2024Q1:V#Annotation:A#1014:F#Top:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None").DataCellAnnotation

' Check if any annotation (comment) exists
Dim hasComment As Boolean =	api.Data.HasDataAttachments("Cb#Group:E#100:C#Local:S#Actual:T#2023Q4:V#Annotation:A#1014:F#Top:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None")

' Logic: Reordered for clarity and corrected threshold condition
If Math.Abs(variance) <= 15000000D Then
    args.ConfirmationRuleArgs.Info2 = "Variance within threshold. No comment required."
    Return True
ElseIf hasComment Then
    args.ConfirmationRuleArgs.Info2 = "Variance exceeds 15,000,000 but comment is provided. Rule passed."
    Return True
Else
    args.ConfirmationRuleArgs.Info2 = "Variance exceeds 15,000,000. Comment is required in the Comments column."
    Return False
End If

    Thank you for looking at this. 

     

    • RobbSalzmann's avatar
      RobbSalzmann
      Valued Contributor II
      1 year ago

      change line 19 to:
      Dim hasComment As Boolean = Not String.IsNullOrWhiteSpace(StringAnnotation)

      Try this - it checks for a comment in the current (2024Q1) period:

      ' Get current QTD value for Entity 100 (T#2024Q1)
Dim qtdCurrent As Decimal = api.Data.GetDataCell("Cb#Group:E#100:C#Local:S#Actual:T#2024Q1:V#Periodic:A#1014:F#Top:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None").CellAmount

' Get prior QTD value for Entity 100 (T#2023Q4)
Dim qtdPrior As Decimal = api.Data.GetDataCell("Cb#Group:E#100:C#Local:S#Actual:T#2023Q4:V#Periodic:A#1014:F#Top:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None").CellAmount

' Calculate variance
Dim variance As Decimal = qtdCurrent - qtdPrior

' Display variance in amount column
args.ConfirmationRuleArgs.DisplayValue = variance

' Display value breakdown
args.ConfirmationRuleArgs.Info1 = "Current QTD: " & qtdCurrent.ToString("N2") & ", Prior QTD: " & qtdPrior.ToString("N2") & ", Variance: " & variance.ToString("N2")

Dim StringAnnotation As String =	api.Data.GetDataCellEx("Cb#Group:E#100:C#Local:S#Actual:T#2024Q1:V#Annotation:A#1014:F#Top:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None").DataCellAnnotation

' Check if any annotation (comment) exists
Dim hasComment As Boolean =	api.Data.HasDataAttachments("Cb#Group:E#100:C#Local:S#Actual:T#2023Q4:V#Annotation:A#1014:F#Top:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None")

Dim hasComment As Boolean = Not String.IsNullOrWhiteSpace(StringAnnotation)
Dim varianceExceedsThreshold As Boolean = Math.Abs(variance) > 15000000D

If Not varianceExceedsThreshold Then
    args.ConfirmationRuleArgs.Info2 = "Variance within threshold. No comment required."
    Return True
ElseIf hasComment Then
    args.ConfirmationRuleArgs.Info2 = "Variance exceeds 15,000,000 and comment is provided. Rule passed."
    Return True
Else
    args.ConfirmationRuleArgs.Info2 = "Variance exceeds 15,000,000. Comment is required in the Comments column."
    Return False
End If



    • MarcusH's avatar
      MarcusH
      Valued Contributor
      1 year ago

      The code is looking for the comment in the Prior period - is that correct?

    • RobbSalzmann's avatar
      RobbSalzmann
      Valued Contributor II
      1 year ago

      Also, consider organizing your decision logic using named variables and using named strings for easier readibility.  This will save you confusing which quarter you're referencing and make your code easier to read and maintain:

      ' Constant suffix for all POVs except the view and time
Const povBase As String = ":E#100:C#Local:S#Actual:A#1014:F#Top:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None"

' Time periods
Dim currQtr As String = "2024Q1"
Dim prevQtr As String = "2023Q4"

' Compose POVs with data type (Periodic/Annotation) as prefix
Dim povCurrent As String = $"V#Periodic:T#{currQtr}{povBase}"
Dim povPrior As String = $"V#Periodic:T#{prevQtr}{povBase}"
Dim povAnnotation As String = $"V#Annotation:T#{currQtr}{povBase}"

' Get current and prior values
Dim qtdCurrent As Decimal = api.Data.GetDataCell(povCurrent).CellAmount
Dim qtdPrior As Decimal = api.Data.GetDataCell(povPrior).CellAmount
Dim variance As Decimal = qtdCurrent - qtdPrior

' Show calculated variance
args.ConfirmationRuleArgs.DisplayValue = variance
args.ConfirmationRuleArgs.Info1 =
    $"Current QTD: {qtdCurrent:N2}, Prior QTD: {qtdPrior:N2}, Variance: {variance:N2}"

' Check for annotation
Dim annotation As String = api.Data.GetDataCellEx(povAnnotation).DataCellAnnotation
Dim hasComment As Boolean = Not String.IsNullOrWhiteSpace(annotation)
Dim varianceExceedsThreshold As Boolean = Math.Abs(variance) > 15000000D

' Rule enforcement
If Not varianceExceedsThreshold Then
    args.ConfirmationRuleArgs.Info2 = "Variance within threshold. No comment required."
    Return True
ElseIf hasComment Then
    args.ConfirmationRuleArgs.Info2 = "Variance exceeds 15,000,000 and comment is provided. Rule passed."
    Return True
Else
    args.ConfirmationRuleArgs.Info2 = "Variance exceeds 15,000,000. Comment is required in the Comments column."
    Return False
End If

       

      • EGM's avatar
        EGM
        New Contributor II
        1 year ago

        Thank you so much! this if very helpful. Ultimately, the rule needs to be dynamic. But I'm just trying to get it to work with one period and one entity. 

         

  • MarcusH's avatar
    MarcusH
    Valued Contributor
    1 year ago

    I think the problem is where you are getting the DataCellAnnotation (line 78?). The result of that called is used as a POV for the HasDataAttachment function. If you change line 78 so that the variable StringAnnotation holds just the POV then I think that will work.

    • EGM's avatar
      EGM
      New Contributor II
      1 year ago

      Thanks for the reply. I'm not sure I'm following. What would it look like?

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    1 year ago

    The method (function) api.Data.HasDataAttachement takes a memberscript as an argument.  You appear to be passing it the comment (StringAnnotation) returned by the line above it.

    try:

    Dim StringAnnotation As String =	api.Data.GetDataCellEx("Cb#Group:E#100:C#Local:S#Actual:T#2024M1:V#Annotation:A#1014:F#Top:0#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None").DataCellAnnotation

' Check if any annotation (comment) exists
Dim hasComment As Boolean =	api.Data.HasDataAttachments("Cb#Group:E#100:C#Local:S#Actual:T#2024M1:V#Annotation:A#1014:F#Top:0#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None")

    Or

    Dim StringAnnotation As String =	api.Data.GetDataCellEx("Cb#Group:E#100:C#Local:S#Actual:T#2024M1:V#Annotation:A#1014:F#Top:0#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None").DataCellAnnotation

Dim hasComment As Boolean = 
    Not String.IsNullOrWhiteSpace(StringAnnotation) AndAlso    api.Data.HasDataAttachments("Cb#Group:E#100:C#Local:S#Actual:T#2024M1:V#Annotation:A#1014:F#Top:0#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#Top:U8#None")

     

  • EGM's avatar
    EGM
    New Contributor II
    1 year ago

    Excellent question I hadn't thought of. It is looking for a comment in a current period. 

     

  • EGM's avatar
    EGM
    New Contributor II
    1 year ago

     Thank you for your help and input RobbSalzmann and MarcusH