I am trying to obtain a list of Shortcut Dimension Codes 3 & 4 used from either the G/L Entry Table and the G/L Budget Entry Table where the entries match the filters for posting date, Global Dimension 1, Global Dimension 2, and G/L Account No. I was able to create a formula to list Shortcut Dimension Code 3 but am unable to properly create a formula to list Shortcut Dimension Code 4, which is also dependent on Shortcut Dimension Code 3. I'd appreciate any help!
Thanks,
Kelly
If you simply need a list, using a field cache would be the easiest, and you would make one for each table.
Something like:
NL("Rows",{"Shortcut Dimension 3.","Shortcut Dimension 4"},"G/L Entry", Filter1, Filter, Filter2, Filter, Filter 3, Filter)
NL("Rows",{"Shortcut Dimension 3.","Shortcut Dimension 4"},"G/L Budget Entry", Filter1, Filter, Filter2, Filter, Filter 3, Filter)
Then use the NF function to pull the values. Those replicators will pull back every combination of SD3 and SD4 that it finds within the filter set.
Hi Bryan,
Thank you for responding. I guess I don't quite understand how this will give me one concise list. Won't your response give me 2 lists?
This is true, you will end up with two lists. I don't know the full extent of your report so it's hard to provide a lot of insight. You just mentioned you needed a list. You could take these two lists, create an Concat function in Excel to combine, then highlight duplicates for the rows to manually remove.
If you really need this as a report, you will probably need to do some sort of grouping report where you use array calculations for Dim3 from both tables, then in the secondary group, another array calculation to get all the Dim4 associated with the Dim3. Could be a slow report since you are doing multiple array calculations but that is the only way I could think would solve your question.
Hi Bryan,
I appreciate your assistance. I finally figured out how to get what I needed by using the Union function, which looks at both the G/L Entry table and the G/L Budget Entry table to give me a list of all GD1 (Dept Code) & GD2 (Lob Code) combinations for specific date filters and a G/L Account No. filter:
GD1 List:
=NL("Rows",NP("Union",NL("Filter","G/L Entry","Dept Code","G/L Account No.",$F17,"Dept Code",$D17,"Posting Date",G$5),NL("Filter","G/L Budget Entry","Dept Code","G/L Account No.",$F17,"Dept Code",$D17,"Date",G$6,"Budget Name",G$4)))
GD2 List:
| =NL("Rows",NP("Union",NL("Filter","G/L Entry","Lob Code","G/L Account No.",$N18,"Dept Code","@@"&$O18,"Posting Date",P$5),NL("Filter","G/L Budget Entry","Lob Code","G/L Account No.",$N18,"Dept Code","@@"&$O18,"Date",P$6 "Budget Name",P$4))) |
Kelly