Here is the skinny, i wrote a report that uses posted transactions from the G/L Entry table. It creates individual sheets for each Global Dimension 2 Code. And within each sheet, it groups by Global Dimension 1Code and then fills in GL entries for each Global Dimension 1 Code. The report works fine but…..
If a combination of Global Dimension 2 Code, Global Dimesnion 1 Code, and GL account doesn't have a posted transaction, it doesn't show up in the report, but it does have a budget, so i need it to show up in the report because it has a budget value.
So what i am saying is if it has a budget entry, AND OR Posted GL ledger entery, then show up in the report.
So i have to search both the GL Budget Entry table and the GL Entry table to see if the combinations exist, and if they do, show up on the report, i have been trying a union NP, but i am having problems…here are my NL's below to give me the sheets and groupings and data for my report.
=NL("Sheets","g/l entry","Global Dimension 2 Code","Posting Date",$D$3,"Global Dimension 1 Code",$D$4,"Global Dimension 2 Code",$D$5,"G/L Account No.",$D$6)
=NL("Rows=4","g/l entry","Global Dimension 1 Code","Posting Date",$D$3,"Global Dimension 1 Code",$D$4,"Global Dimension 2 Code","@@"&$E14,"G/L Account No.",$D$6)
=NL("Rows","g/l entry","G/L Account No.","Posting Date",$D$3,"Global Dimension 1 Code","@@"&$G15,"Global Dimension 2 Code","@@"&$E15,"G/L Account No.",$D$6)
Does anyone have an idea how i can rewrite my formulas?
Date
Votes
7 comments
-
Jet Reports Historic Posts What you can do with Jet Reports is to build an array of values from one table, build an array of values from another table, and combine / compare them before presenting them. Sounds cryptic? I know, sorry about that, so let me try to explain it with your report in mind:
I suppose Cell E14 contains your NL("Sheets") function. But I need 2 more free cells for some extra functions - I will call them B3 and B4 - but you could put them elsewhere if you wish.
Cell B3 will be used to build a list of all G/L account nos in the G/L Entry table without listing them in the report:=NL("Filter","g/l entry","Global Dimension 2 Code","Posting Date",$D$3,"Global Dimension 1 Code",$D$4,"Global Dimension 2 Code",$D$5,"G/L Account No.",$D$6)
Cell B4 will be used to build a list of all G/L account nos in the G/L Budget Entry table without listing them in the report:=NL("Filter","g/l budget entry","Global Dimension 2 Code","Posting Date",$D$3,"Global Dimension 1 Code",$D$4,"Global Dimension 2 Code",$D$5,"G/L Account No.",$D$6)
These 2 functions generate a list of account nos, which you cannot see in your report. In Cell E14 you will combine and present them as sheets:=NL("Sheets",NP("Union",$B$3,$B$4))
Does this work for you report? Then the next step would be to do the same trick for the dimensions. Please let us know if it worked for you. -
Jet Reports Historic Posts Even before your post i was trying that out, and i thought i had it but now i have an error. To make this easier, i have attached the report to look at…The first grouping works fine, the second grouping, i couldn't use a cell reference because i needed to reference my original grouping. So i wrote it out, and this is now causing the error in cell H14.
I just think i need to make the report less 'involved' for the client.
I you think you may have a solution, let me know, thank you very much.
Craig -
Jet Reports Historic Posts Craig,
You formula is cell H14 is too long. You will need to put the NL("Filter") functions in their separate cell and references them in you NL("Rows") function. Just like hans suggested in his posting. -
Jet Reports Historic Posts Cell H14 has to reference E14 so the grouping works properly, i can't make a reference to it when i use the "Filter" option. I do have the cell references in B3 and B4. I am stuck..i have attached my report again with the union in cell H14
-
-
Jet Reports Historic Posts Thanx Sherman, I think i got it now. I also had to add in another NL Filter for the GL account too.
I am going to post it on the site so everyone can take a look at it. :) -
Jet Reports Historic Posts i have uploaded my report to the reports page for NAV. Go there and take a look.
it works for me, but you may want to change it. It came from a highly customized database…so i have modified it so it should work with a standard NAV database.
craig
Please sign in to leave a comment.