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 is calculated with GetDataCell(CVC(Actual column) - CVC(Budget column)), which should be 700, but the delta is showing (20,825) instead. 

When exporting this cube view to Excel, the Budget amount becomes 10,763 (which is incorrect), but the delta is calculating correctly as 700.

For delta, the temporary solution is to calculate with GetDataCell(S#ACTUAL - S#BUDGET); however, the Budget amount would still flip the sign when exporting to Excel. 

We use the Text1 property in the cube view to determine the expansion, TreeDescendantsInclusiveR. 

For the formatting, we use the below parameters in the cube view: BackgroundColor = White, NumberFormat = [#,###,0 ;(#,###,0);"0 "], Scale = 3, ExcelNumberFormat = [#,##0,], ExcelUseScale = False

This happens in Budget for EntityA (a parent) in the last row, and all the descendants below Entity A behave the same. EntityA and all its descendants are set up just as all the other entities in the dimension. 

At the moment, we are running out of ideas to check where the issue is coming from, and we want to understand the root cause of it.

Has anyone faced a similar issue before and would share the experience with us?

We appreciate any help you can provide.

Cube View
Reports
  • JackLacava's avatar
    JackLacava
    Honored Contributor
    11 months ago

    One should try to do all calculations inside the calculation (eh) engine, to avoid problems. If GetDataCell(S#Actual - S#Budget) is a positive 700, I would use that calculation rather than going through CVC, because that number is more likely to be correct and you have more control over the POV.

    More specifically, I have a feeling there are stray Override formattings kicking in. If I were you, I'd rebuild the CV from scratch, one column at a time, keeping in mind the above-mentioned principle; once numbers look good, only then apply the minimum amount of formatting you absolutely need. The first rule of troubleshooting is isolating components; so isolate numbers (by putting them together without formatting rules), and only move on once you know they look ok.

  • 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)