08-30-2022 10:14 PM - last edited on 05-25-2023 06:10 AM by JackLacava
How do I view the POV of the datacell executing BusinessRule.DashboardStringFunction XFBR business rule?
I think I can run this statement:
Dim viewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId)
BRapi.ErrorLog.LogMessage(si, "view = " & viewMember.tostring)
Is there a faster way to view all 10 dimesions in POV of the datacell ?
These do not work:
Dim POV As POV = ViewMember.GetItem(api.Pov.MemberPk.MemberId)
BRapi.ErrorLog.LogMessage(si, "view = " & POV.tostring)
Solved! Go to Solution.
09-02-2022 06:10 AM
It is an UD8 member formula and it has to be used together with a text view member like annotation:
The member formula might look like this:
' Test if the cell is a text-based dataCell.
If api.View.IsAnnotationType() Then
'Has the cell a cell text or an attachement?
If Not api.Data.HasDataAttachments("O#Forms:V#Periodic:Ud8#None") Then
'Retrieves the cells to test
Dim amount As DataCell = api.Data.GetDataCell("O#Top:V#Periodic:UD8#None")
Dim budget As DataCell = api.Data.GetDataCell("O#Top:V#Periodic:UD8#None:S#BudgetV2")
'Calculate the variance percent
Dim variance As Decimal = (amount.CellAmount - budget.CellAmount ) / budget.CellAmount
'Is there a negative variance?
If variance < 0 Then
Return "<== Explantion Required"
Else
Return Nothing
End If
Else
Return Nothing
End If
End If
Return Nothing
08-31-2022 09:57 AM - edited 08-31-2022 10:01 AM
Yes, there is a faster way: api.Pov.GetDataCellPk.GetMemberScript(si)
This only works for financial business rules and member formula.
08-31-2022 10:09 AM
The XFBR function in a cubeview, will be resolved before the system retrieves the datacells, so there is no way for the rule to get the POV of a cell (because it doesn't exist yet).
The problem are dynamic queries like A#IS.descendants, when the xfbr is executed, the query isn't resolved yet.
There are several ways to work around this limitation, can you help us to understand, what you try to achieve?
08-31-2022 10:47 AM - edited 08-31-2022 10:47 AM
Wow!! I have no idea what the differences are between XFBR vs using formula and financial business rules. Obviously, you are some big shot developer at 1S. Or are you a consultant? If yes, which company do you work for?
I was studying some XFBR code written over 5 years ago, that I inherited. I suspected there was some issue with XFBR not being able to read the cell's POV. I think what you wrote is true- which means that case, 1S consultants' use of XFBR to do conditional formatting is flawed.
I guess my first question is - why would anyone use XFBR, except to process something before retrieving datacells!!
08-31-2022 11:21 AM
I am asked to color a cell yellow if a user added annotation into that cell. This cell stores annotations of users who enter an explanation for why there is a variance on the row of that cell.
You just told me that I have to use a formula or a finance rule as I need the cell POV before I can retrieve the contents of the cell to see if it has text.
Koemets wrote this:
Annotations are attachments by nature, so they can't be treated as regular data cell in your code. So you need to attach them to the respective datacells> Koemets
Someone recommended the following lines of code. Do you have a better suggestion?
Dim yourText As String = api.Data.GetDataCellEx("A#variance:U8#CommentAvB").DataCellAnnotation
api.Data.SetDataAttachmentText("A#variance:U8#CommentAvB",yourText,False) ‘I don’t even know what this line does
If yourText.Equals(String.Empty) Then
' color cell pink to show that user entered text into this cell
Return "Pink"
08-31-2022 11:42 AM
Why are you not just showing the annotation in a column next to the data?
You solution with a pink cell is probably possible, but I need to know the dimensions in the rows and the columns. Today is a little late in Europe to start such a project, but I will try to build something this week.
08-31-2022 11:56 AM
I have a very simple CubeView - cells under "Reason code" column have these dimensions:
U8#CommentAvB , V#ANNOTATION
Each row is a dept - they have to explain why there is a large variance.
Please don't bother to build something for me -if you are very busy. I learned from news that Europeans value their life-after-office hours, which I totally agree with ; so please don't work overtime for me.
09-01-2022 11:05 AM
Would something like this work as well:
It is a dynamic calc member formula showing the message, if a required cell hasn't a comment.
09-01-2022 12:01 PM
You are a life-saver!! Thanks. May i have the code to this formula and it's settings? Also, if you can give me a screen shot of how this cubeview calls the formula, that will be great.
My I will change your formula to suit my needs - by making it returning a text (e.g. color pink) to my cubeview.
Thanks again for your hard work!!
Maybe I can pay you back by recommending some great movies? I usually watch only movies based on real events of incredible people
09-02-2022 06:10 AM
It is an UD8 member formula and it has to be used together with a text view member like annotation:
The member formula might look like this:
' Test if the cell is a text-based dataCell.
If api.View.IsAnnotationType() Then
'Has the cell a cell text or an attachement?
If Not api.Data.HasDataAttachments("O#Forms:V#Periodic:Ud8#None") Then
'Retrieves the cells to test
Dim amount As DataCell = api.Data.GetDataCell("O#Top:V#Periodic:UD8#None")
Dim budget As DataCell = api.Data.GetDataCell("O#Top:V#Periodic:UD8#None:S#BudgetV2")
'Calculate the variance percent
Dim variance As Decimal = (amount.CellAmount - budget.CellAmount ) / budget.CellAmount
'Is there a negative variance?
If variance < 0 Then
Return "<== Explantion Required"
Else
Return Nothing
End If
Else
Return Nothing
End If
End If
Return Nothing
08-31-2022 11:15 AM
There are plenty of great use cases for xfbr, including for conditional formatting, but you need to understand how it works. 5 years ago, we hadn't conditional formatting, but for some years now, it is a function of cubeviews.
There might be a much simpler solution for your problem.
I work for Onestream.