Forum Discussion

LisaC's avatar
LisaC
New Contributor II
11 months ago

Data flip sign when exporting to Excel

Dear community,  We encountered a reporting issue: when exporting a cube view to Excel, one line of data will flip the signage.  In cube view, we have the Budget amount of (10,763), and the delta...
Cube View
Reports
LisaC's avatar
LisaC
New Contributor II
10 months ago

Hi Jack, 

Thank you for sharing the idea!

I re-created the cube view and found what was causing the issue. 

Next to the delta column, we have another delta% column, in which the formula is: GetDataCell(CVC(delta)/ ABS(CVC(budget))). 

As soon as the ABS function is added, the data in the budget column flips, but I cannot understand why this would occur. 

Do you have any ideas of such behavior when using the ABS function?

Thank you!

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    10 months ago

    That function is a little gremlin, technically it's not even documented. You can bring it up with Support and see how it goes.

    A workaround would be to create your own AbsValue function, looking like this:

    Case Is = FinanceFunctionType.DataCell
	' Use: GetDataCell("BR#[MyBrFile, AbsValue~S#Budget]")
	Dim callELements As List(Of String) = StringHelper.SplitString( _
		args.DataCellArgs.FunctionName, "~", StageConstants.ParserDefaults.DefaultQuoteCharacter)
	' callElements(0) now contains "AbsValue"
	' callElements(1) now contains "S#Budget"

	If callElements(0).XFEqualsIgnoreCase("AbsValue") Then
		' start from the cell Pov
		Dim script As MemberScriptBuilder = api.Pov.GetDataCellPk.CreateMemberScriptBuilder(api)
		' if we have parameters, override the cell pov with them
		If callELements.Count > 1 Then
			script.AddMemberScript(callElements(1))
		End If
		' calculate abs
		Dim amount As Decimal = api.Data.GetDataCell(script.GetMemberScript()).CellAmount
		Return math.Abs(amount)
	End If

    You would then call it on the Cube View like this (assuming they are in a Finance rule called "MyBrFile"):

    GetDataCell("BR#[MyBrFile, AbsValue~S#Budget]")

    By using the member script, we can override any amount of dimensions:

    GetDataCell("BR#[MyBrFile, AbsValue~S#Budget:A#Something]")

    Unfortunately, you cannot mix this with other GetDataCell functions, so it would have to sit in its own column (hidden if necessary, by setting isColumnVisible to CVMathOnly in the Header Format clause)