view the POV of the datacell executing BusinessRule.DashboardStringFunction XFBR business rule?

Davy
Contributor

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)

 

 

1 ACCEPTED SOLUTION

ChristianW
Valued Contributor

It is an UD8 member formula and it has to be used together with a text view member like annotation:

ChristianW_2-1662113225224.png

 

ChristianW_1-1662113144985.png

 

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

 

View solution in original post

10 REPLIES 10

ChristianW
Valued Contributor

Yes, there is a faster way: api.Pov.GetDataCellPk.GetMemberScript(si)

This only works for financial business rules and member formula.

ChristianW
Valued Contributor

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?

 

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!! 

 

 

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"

ChristianW
Valued Contributor

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.

I have  a very simple CubeView - cells under "Reason code" column have these dimensions:

U8#CommentAvB  , V#ANNOTATION

Davy_0-1661961001722.png
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.


 

 

ChristianW
Valued Contributor

Would something like this work as well:

ChristianW_0-1662044485727.png

It is a dynamic calc member formula showing the message, if a required cell hasn't a comment.

 

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

 

ChristianW
Valued Contributor

It is an UD8 member formula and it has to be used together with a text view member like annotation:

ChristianW_2-1662113225224.png

 

ChristianW_1-1662113144985.png

 

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

 

ChristianW
Valued Contributor

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.