Forum Discussion

Irina's avatar
Irina
Contributor
30 days ago
Solved

Text field of Entity dimension in cube view

Dear all,

Could you please advise how to show content of certain Text field of Entity dimension in cube view?

 

Cube View
  • BenEppel's avatar
    BenEppel
    28 days ago

    If your using text2 then you would need to change it. It is, you will just need logic to clean the string. Something like this should work.

    Dim textVal As String = api.Entity.Text(EntityID, 1, False, False)
Dim taxRateStr As String = ""

If Not String.IsNullOrEmpty(textVal) Then
    Dim startIndex As Integer = textVal.IndexOf("CIT=")
    Dim endIndex As Integer = textVal.IndexOf(",", startIndex)

    If startIndex >= 0 AndAlso endIndex > startIndex Then
        taxRateStr = textVal.Substring(startIndex + 4, endIndex - (startIndex + 4))
    End If
End If

Dim TaxRate As Decimal = taxRateStr.XFConvertToDecimal() / 100D

     

10 Replies

  • BenEppel's avatar
    BenEppel
    New Contributor III
    30 days ago

    XFMemberProperty(DimType=Entity, Member=YourEntityMemberName, Property=Text1, VaryByScenarioType=[], VaryByTime=[])

  • Irina's avatar
    Irina
    Contributor
    29 days ago

    Dear BenEppel, thank you for your answer! Could you please advise is it possible to use text field in cube view not only as header (instead of entity name) but also use it for calculations. In text field I have income tax rate different for each entity and need to use each tax rate for calculation of income tax by entities. Could you also please advise can I also add entity name in the same report.

    • DanielWillis's avatar
      DanielWillis
      Valued Contributor
      29 days ago

      Hi Irina. I just want to confirm because it doesn't sound right. Are you saying you're storing the tax rate in the text property of an entity and want to use it to calculate something on the report?

    • BenEppel's avatar
      BenEppel
      New Contributor III
      29 days ago

      Hi Irina,

      If you would like to create a stored calculation, you could create a new account member called Taxable_Income, and within the formula create a calculation like below:

      The Account's Formula setting would need to be set to a formula pass for this to run, which would be by running a consolidation or calculation.

      If (Not api.Entity.HasChildren) And (api.Cons.IsLocalCurrencyForEntity) Then
	
	Dim EntityID As Integer = api.Pov.Entity.MemberId
	Dim TaxRate As Decimal= api.Entity.Text(EntityID,1, False, False).XFConvertToDecimal
	
	api.Data.Calculate("A#Taxable_Income:F#Endbal_Input:IC#None:O#Import:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None = (A#Net_Income:F#Endbal_Input:IC#Top:O#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#Top:U7#Top:U8#None * " & TaxRate & ")", False)

End If

      You could also create a dynamically calculated Account member with a formula like below:

      The Formula setting on Taxable_Income would be DynamicCalc

      Dim Net_Income As Decimal = api.Data.GetDataCell("A#Net_Income:u8#None").CellAmount
	
	Dim EntityID As Integer = api.Pov.Entity.MemberId
	Dim TaxRate As Decimal = api.Entity.Text(EntityID,1, False, False).XFConvertToDecimal
	
Return Net_Income * TaxRate

      This calculation would be based off the POV of the Cubeview cell

      This function XFMemberProperty(DimType=Entity, Member=YourEntityMemberName, Property=Text1, VaryByScenarioType=[], VaryByTime=[]) could be used within Cubeview math, but I dont recommend this way.

      • Irina's avatar
        Irina
        Contributor
        28 days ago

        Dear Ben,

        Thank you! Am I right, that if tax rate is in Text2 field of Entity dimension, I need to change 1 to 2 in the nest string: api.Entity.Text(EntityID,2, False, False)?

        I have another issue with this text field. It contains not only tax rate, but also text like this: CIT=15, VAT=0, so I need to take only number after text "CIT=" and before ",". In Excel I use function like this: SUBSTITUTE(LEFT(cell with Text2,FIND(",",cell with Text2)-1),"CIT=","")/100. Is it possible to extract tax rate in OneStream from text field too?

    • DanielWillis's avatar
      DanielWillis
      Valued Contributor
      29 days ago

      How come you're not just storing the rate in the cube and also putting your calculation on a member to use in your report (and elsewhere)?

  • Irina's avatar
    Irina
    Contributor
    29 days ago

    Daniel, do you mean that I can use certain UD dimension for tax rates storing?