I am creating a GL account tree of sorts by using default dimensions in NAV 2016. My dimension codes are Rollup category 1, 2, etc. assigned to all GL codes. I am attempting to run a filter from the defualt dimension table based on the dimension value code to pull a string of GL numbers. No matter how I try it, I get back either value errors or blanks all the way down. Is it because the account has multiple dimensions associated with it?? Once I include the dimension value code into the mix, I can no longer retrieve data.
Date
Votes
15 comments
-
CascadiaDataworks When you are getting errors, what is the error returned? That may help determine the issue
-
Gino Martinez The return is either a value error or a blank. I have gotten it to work for the rollup1 dimension code but cannot for the rollup2. Currently using a link command to tie the GL and Dimension tables together and marrying the "No." fields. Either way if I try and filter by the dimension value (multiple criteria), it does not work.
-
CascadiaDataworks When you get the #VALUE and click on Check Error, what does it return? It may provide some useful information.
It doesn't seem like the G/L Account links to the Dimension table. https://dynamicsdocs.com/nav/2016/w1/table/gl-account
-
Gino Martinez Here is the string that works:
=@NL("Rows","G/L Account","No.","Link=","Default Dimension","No.","=No.","Dimension Value Code","OPERATING EXPENSES").
I don't know how to pull in the GL accounts categorized by multiple dimension value codes. If I use just one value under one dimension, I'm ok. But using multiple values under multiple dimensions does not seem to work.
-
CascadiaDataworks Since I don't have access to a NAV database any more, you might need to add another filter to the Default Dimension table to specify the Table ID to narrow it down to the G/L Account table. Since the Default Dimension table connects to multiple other tables in addition to the G/L Account Table, it may need that additional filter.
The only other thing I can think of is that "OPERATING EXPENSES" isn't the correct value. I've seen where the client thinks it is something like "DEPARTMENT NO" but it is really "DEPARTMENT" or "DEPT NO". Just wondering if the actual value is not exactly "OPERATING EXPENSES"?
-
CascadiaDataworks I've reread your problem and thought of a question. You showed an example with a single value, what about multiple? What is your formula when you are filtering for multiple criteria?
-
Gino Martinez Since GL accounts have been mapped to various dimension codes, I added the second dimension code as an additional filter in the same string. When doing this, the formula return a blank cell value instead of all GL account that are assigned to those two dimensions. Is this because it isn't recognizing it as an "and" for both filters. Here is the formula string when I am trying to specify the account belongs to "A" and "B" multiple dimension value codes (Dim1=OPEX; Dim2=Salaries & Benefits):
=@NL("Rows","G/L Account","No.","Link=","Default Dimension","No.","=No.","Dimension Value Code","OPERATING EXPENSES","Dimension Value Code","SALARIES & BENEFITS")
I don't seem to be having any success with the secondary dimension tiers, only the primary. Does the system view them as a required layered approach where I have to specify #1 to be able to filter on #2?
-
CascadiaDataworks Ah, that is what I ultimately thought was the issue after revisiting this topic. Putting multiple filters for the same field doesn't work and if you need an AND filter, you would have to change this up a bit because the way a filter of "Dimension Value Code" = THIS&THAT will always return nothing because of the structure of the database. If you assigned account 10000 a dimension of Operating Expenses and a dimension Salaries, the database will have that as two separate records to assign those dimensions. When you create a filter for AND in Jet, it is looking at each individual record and no single record will have a dimension value of both values so it is a little different than SQL.
Realistically, you will need to leverage Array Calculations.
- Create an NL(Filter) for all accounts with the Dimension Value Code = A
- Create an NL(Filter) for all accounts with the Dimension Value Code = B
- Create an NP(Intersect) of the two NL(Filter) functions - this will find all accounts that are found in both of the previous Filters
- Reference the NP(Intersect) as a filter for the accounts in your replicator.
Hopefully that makes sense. Where this becomes tricky to build is if you need different numbers of Dimension Values (if you sometimes need three or four Dimensions or sometimes only need one, etc.). The above solution assumes you always need exactly two Dimensions.
-
Gino Martinez Thanks for breaking down how the system methodology works. I am still getting blank values but think my replicator is incorrect. Sticking to the same scenario, here is what I have when attempting to retrieve accounts tied to Dim1-OPEX and Dim2=Salaries and Benefits:
- 1. "Create an NL(Filter) for all accounts with the Dimension Value Code = A" =@NL("Filter","Default Dimension","No.","Dimension Value Code","OPERATING EXPENSES")
- 2. Create an NL(Filter) for all accounts with the Dimension Value Code = B =@NL("Filter","Default Dimension","No.","Dimension Value Code","SALARIES & BENEFITS")
-
3. Create an NP(Intersect) of the two NL(Filter) functions
||"Intersect","||""Filter"",""Default Dimension"",""No."",""Dimension Value Code"",""OPERATING EXPENSES""","||""Filter"",""Default Dimension"",""No."",""Dimension Value Code"",""SALARIES & BENEFITS"""
4. Reference the NP(Intersect) as a filter for the accounts in your replicator =@NL("Rows","G/L Account","No.","No.",NP("Intersect",$B3,$C3)) (this is the formula I believe to be incorrect)
-
CascadiaDataworks Ah, part of the problem is the value of "SALARIES & BENEFITS". Jet is interpreting that as an AND statement, when it is both Salaries and Benefits.
Try =@NL("Filter","Default Dimension","No.","Dimension Value Code","@@SALARIES & BENEFITS")
https://support.jetglobal.com/hc/en-us/articles/219402277-Special-Characters-in-a-Filter
-
Gino Martinez Yes, that worked!!
Thank you so much. I owe you big time! This was holding me up from report development. First round is on me when you are in Orlando, FL!
Is there a similar way to tie in additional dimensions such as a three or four combination? Not as high priority but I will need to figure it out eventually to create cost center (utilized via default dimensions) level P&L reports.
-
CascadiaDataworks You can get creative by setting up multiple NL Filters and for all additional NL Filters where it is not needed, you can enter is * as the filter value.
You might have something set up like:
=@NL("Filter","Default Dimension","No.","Dimension Value Code","@@SALARIES & BENEFITS")
=@NL("Filter","Default Dimension","No.","Dimension Value Code","OPERATIONS")
=@NL("Filter","Default Dimension","No.","Dimension Value Code","*")
=@NL("Filter","Default Dimension","No.","Dimension Value Code","*")
You will need to get creative with the Intersects since you can only intersect two arrays but you just keep embedding them further and further like:
=NP("Intersect",B3 ,NP("Intersect",C3, NP("Intersect", D3,E3))) where the values referenced have the NL Filters.
It's kind of a wacky report build but leveraging * values can help with having 1, 2, 3 or more dimension filters but should work since you would say "all accounts where the dimension is anything". Best to set up report filters so you can enter them at runtime.
-
Gino Martinez Hmm, I've tried this with two additional blank filters and identical formulas but am getting blank cells again. Any ideas?
-
Gino Martinez Also, this works at a line level but doesn't seem to work when attempting to rollup categorically. When trying to reflect total balance across a date range using the intersect function, I get #VALUE errors. When trying to pull all OPEX and Salary Dimensions by total balance and total budget by fiscal year (need to specify a start and an end date due to multiple open FYs at the same time), It pulls the sum without a date filter. I only want to pull in the balance & budget for these two dim codes from 10/1 thru 9/30. Using the GL function doesn't seem to work when pulling in the intersect formula. Here are my formulas:
Actuals by Dim1: =@NL("Sum","G/L Account","Balance","No.",$G16,"Date Filter",$L$2:$L$3)
G16 is the intersect formula; the L2-3 range is a FY beg/ending date range
Budget by Dim1: =@NL("Sum","G/L Account","Budgeted Amount","No.",$G16,"Date Filter",$M$2)
G16 is the intersect formula; the L2-3 range is a FY beg/ending date range
-
CascadiaDataworks Can you use the GL function? That might be better since you can leverage up to 4 dimensions. Otherwise, you are going to have to use NL(SUM) against the G/L Entry table so you can filter on the dimensions which can affect run times. Usually not so terrible for an income statement or report that is focused on a range of dates but is terrible for balance sheets (since it needs to determine Amount from beginning of time to date).
Please sign in to leave a comment.