The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
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?
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
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.
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 IfYou 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 * TaxRateThis 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.
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?
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() / 100DDear Ben,
Thanks a lot for solution!