By using this website, you agree to our Terms of Use (click here)
I'm attempting to pull a row value from a specific UnitCode in order to allocate costs amongst all the UnitCodes. Here's what I have tried so far in my RowSet:
=Value('0109',@UnitCode)/Value('0109','TOTAL'). Where 0109 is total sales value and TOTAL is the UnitCode that combines all Units. Goal is to return % of sales for each Unit.
=Value('0472',@UnitCode)/*-Value('0420','HQQ'). Where 0472 is the row where the formula above is , 0420 is the row value to allocate and HQQ is the specific UnitCode to pull row 0420 value from. Goal is to apply the % of sales for each UnitCode to the 0420 value for UnitCode HQQ.
In addition, my column set has multiple columns: Budget,Actual,Prior Year, Budget Variance and Prior Year Variance.
Neither of these formulas work.
I was able to get this done in a different report using @ColumnCode where I was only referencing the value in one particular column and was not using Unit Codes, but I'm not sure how to get the value into the row set from one particular UnitCode.
Thanks for your help!
I don't think you can pull from a specific Unit Set using the Value formula. The Value formula takes two arguments: the Row Code and the Column Code (not a Unit Set Code).
The only way that I can think of at the moment to do what you are looking for would be to pull the individual values that you want into hidden rows. Then reference the hidden rows in your division calculation. Does that make sense?
That does make sense, but can I pull the individual values from a specific Unit Code into the hidden rows? Another possibility I just thought of would be to create a hidden column for the Unit Code and then use the Value Fromula to bring the values into the row set - can I reference a Unit Code value in the column set? In this case, the Unit Code refers to a specific sub account segment so could use that, but I'm curious if I can reference a Unit Code value in the Column Set.
There is a Unit Group field on the Row Sets (CS206010) screen that you might look into, but I don't think that it will do what you are looking for.
The hidden row idea is a good idea, but I think that you will need to duplicate your Unit Set logic into the Data Source field on the hidden rows because I don't know of a way to reference the Unit Set directly. Will that be difficult to do? Is your Unit Set logic complex?
Online Members
