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
10 Replies
XFMemberProperty(DimType=Entity, Member=YourEntityMemberName, Property=Text1, VaryByScenarioType=[], VaryByTime=[])
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. 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?
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?
Hi Daniel,
Yes. You are right.
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)?
Daniel, do you mean that I can use certain UD dimension for tax rates storing?
