Hi All,
I am trying to write a report where i list all the G/L accounts and sum all G/l ledger entries in a certain period by their ledger entry dimensions (global 1, global 2, shortcut 3 and shortcut 4 in my case).
Global 1 and global 2 are in the G/L ledger entries but additional ones need to be pulled from the ledger entry dimension table.
In the attachment, it is partially working just using the 2 global dimensions but is repeating some of the dimensions.
I don't know a better way or how to get this filter right.
If anyone can help me solve this problem then i can try the next step of getting the ledger entry dimensions pulled through as well.
The reason for trying to summarise this way is that there are too many gl entries to pull through each line individually so i need to summarise per Gl account, per Dimension combination which must include blanks if any.
Many thanks
Tom
Date
Votes
7 comments
-
Jet Reports Historic Posts Hi Tom,
I don't think you need to use NP(Union) in this case. Since you already are using Link= to link from the Dimension Value to the G/L Entry table and all the dimension values will be in the Dimension Value table, there's no need to union with the G/L Entry as well. Also, getting the unique dimension values from the G/L Entry and unioning them will be really slow so getting rid of that should make this a lot faster.
Could your problem here be that when you get the Global Dimension 2 Codes, you're not filtering for only those codes that have entries with the Global Dimension 1 Code for that account? I'll attach an example of what I mean. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Tom,
Does your Dynamics NAV version and license allow you to define an Analysis View? In the Classic menu you may find this in Financial Management, Setup, Dimensions.
In an Analysis view you define how G/L Entries should be compressed to do a fast analysis. Options are a.o. G/L Accounts, Date Compression and up to 4 Dimensions.
Reading your posting I think this would be really helpful for you to use.
Jet is able to read the Analysis View Entries (e.g. with NL(Rows) or NL(Table)) and it will be much easier for you to define the report you're looking for.
Does this help? -
Jet Reports Historic Posts Hi Hughes, Hans,
Sorry for the late reply, been on training.
I worked out what you were saying Hughes and this worked really well and fast so thank-you very much - what was taking a couple of hours to do on my large dataset now works in 3 minutes :D
I do however still need to understand how to go the step further to link to the ledger entry dimension table because although i can setup analysis views for the GL (License allows this - thanks for suggestion Hans), i need to still work out how to get to this next level as i need to analyse other areas in NAV where analysis views are not detailed or specific enough - ie. posted sales invoices by 4 dimensions where type = Item or GL or Both etc so the ability to pull through additional dimensions from the ledger entry dimension table is a must and again needs to be done this way as the number of transactions are huge.
The new sample attached is my attempt at getting the ledger dimension entries for the 2 global dimensions.
Global 1 works fine on its own from what i can see but getting global 2 is more difficult as the results need to be further filtered by Global 1 else global 1 gets repeated.
That is where i got stuck again as the structure of the ledger entry dimension table does not seem to allow me to filter by global 1 and global 2 etc within the same NL function in the filters.
You will also notice that i have added the columns for shortcut 3 and 4 and the Amount calc but this is incomplete and for illustration of desired outcome only for now.
I tried adding a filtered list again for the entry numbers (J13) but this partially works and repeats global 1 on my larger dataset (Cronus does not seem to have enough transactions to demo this properly for the example) and i get "empty filter not allowed".
Any additional assistance would be greatly appreciated.
Thanks
Tom -
Jet Reports Historic Posts Hi Tom,
Jet does all this for you. Jet will actually link to the Ledger Entry Dimension (or Posted Document Dimension or whatever the correct dimension table is for the table you are on) and retrieve the dimension values. You can just specify the dimension code (or caption in newer versions of Jet) as a field. In your report you can just use an NF function with the dimension code like this:=NF(E13,"Department") =NF(E13,"Project")
I don't know what the codes for your dimension 3 and 4 are, but you should be able to do the same thing for those. Jet actually figures out the links and gets the values from the correct dimension table for you. This is part of the beauty of Jet Reports.
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
I made the changes which works well if i want to return each entry with its dimensions (this is very useful for some other reports i have in mind and did not know this could be done this way - thank-you) but i am still back to my problem of summing them per combination of dimension code to reduce the number of records returned.
If you look at the latest attachment you will notice i have highlighted 2 sets of entries.
The yellow ones should only show 1 line with a total of 6000.
The green ones should also only be 1 line with 5600.
I havent worked out the filters to sum them either properly yet (only for the first 2 dimensions so any assist here also would be great)
Any further assistance is greatly appreaciated.
Many thanks
Tom -
Jet Reports Historic Posts Tom,
Ah, ok, so for each G/L Account, you want to replicate the unique combinations advanced dimensions which have entries in the G/L Entry table, is that right? The easy way is just to do an NL(Rows) for each dimension in the G/L Entry and return each dimension out of the G/L Entry table. This isn't going to perform extremely well since it will spin through every record that matches your filters in the G/L Entry table several times in order to compile unique lists of advanced dimension values, but it should work. I will attach an example. Does this work for you?
Regards,
Hughes -
Darryl Schroeder None of the Excel attachments are available. How can we obtain a copy of the sample workbooks?
Please sign in to leave a comment.