That worked! Thanks!!!
What is the best way to return total payments. As I have it set up, I am only getting each individual payment. I tried in Jet Designer to move over the Amount with Sum marked at the bottom, but that didn't work.
Thanks,
Beth
Date
Votes
6 comments
-
Jet Reports Historic Posts I am brand new to Jet Reports and am using version 7.1.2 with Navision 5.0. I am trying to write a report that will give me all of the entries for a certain G/L account and branch. Here is what I have so far:
=NL("Rows","G/L Entry",,"Posting Date","01012009..09292009","G/L Account No.",272000,"Global Dimension 1 Code",94)
I need the report to give me the Posting Date, Vendor No. (Source No.), Vendor Name and Total Payments.
Most of my information is coming from the G/L Entry table, but I also need the Vendor name, which is in the Vendor table. I've tried =Link and Filter, but I must be doing something wrong. I've tried to debug with no success.
Thanks for your help!
Beth -
Jet Reports Historic Posts You can use this statement:
=NL("First","Vendor","Name","No.",NF(B2,"Source No.")
Assuming that your record key is in B2. Since you will already have source no on your report, you could just reference it like this:
=NL("First","Vendor","Name","No.",B3)
Assuming that source no is in B3. -
Jet Reports Historic Posts I'm not totally sure what you are looking for, but here's a couple of things you can try:
=NL("Sum","G/L Entry",,"Posting Date","01012009..09292009","G/L Account No.",272000,"Global Dimension 1 Code",94)
will give you just the sum in a cell, no replicated lines.
If you want a sum, after the replicated lines, you should use either =SUM or =SUBTOTAL. Usually =SUBTOTAL is better since it won't double count sums if you use it more than once. The one "trick" is be sure to include one row below your replicator so that the subtotal formula will expand properly.
So if your NL("Rows=1") is on row 3, use this: =SUBTOTAL(9,D3..D4),
if your NL("Rows=3") is on row 3, use this: =SUBTOTAL(9,D3..D6) -
Jet Reports Historic Posts I want only one row for each vendor to be returned with Total Payments for a certain time period. For my report, I added the following columns:
Vendor Vendor No. Total Payments
My formula for Vendor is: =NL("First","Vendor","Name","no.",D3)
My formula for Payments is: =NL("sum","Vendor","Payments ($)","no.",D3,"Date Filter","01012008..12312008")
My main formula is as follows:
=NL("rows","G/L Entry",,"Posting Date","01012008..12312008","G/L Account No.",291100,"Global Dimension 1 Code",23)
When I change the "Rows" to "Sum" I get the following error: "Invalid Field Argument. Fields being summed must be numeric."
When I run the report with "Rows", I get a duplicate list of payments for the same vendor, all the same amount (I'm assuming there is one row for each actual G/L entry).
I know I must be making an obvious mistake, but as a newbie to JetReports, I can't figure out what I'm doing wrong. It seems like it should be such a simple report. Perhaps I'm making it harder than it needs to be.
I even tried starting with the Vendor Table and Linking in the G/L entry table so that it can be filtered by G/L Account No. and Global Dimension 1 Code, but that didn't work either.
Thanks for your help!
Beth -
Jet Reports Historic Posts How about this:
B2 =NL("Rows","G/L Entry","Source No.","Posting Date","01012009..12312009","G/L Account No.",272000,"Global Dimension 1 Code",94)
(This will give you a unique list of vendor nos)
B3 =NL("First","Vendor",,"No.","@@"&B2)
(This will give you the vendor record for the vendor no. in B2 - you may wish to hide this column by entering "Hide" in A3)
B4 =NF(B3,"Name")
B5 =NF(B3,"Payments ($)","Date Filter","01012009..12312009","Global Dimension 1 Filter",94)
(this will calculate the amount paid in a certain period for a certain branch)
A few tips:
Use the Jet Reports Options function to enter your filter values (e.g. date filter and dimension 1) - this makes the report more flexible
Use the Jet Reports Documentation and Help to learn more about the different functions - sometimes they do things differently than you think ;)
Build a report step by step (run it after each step so that immediately know where things don't go in the direction you want
Good luck! -
Jet Reports Historic Posts In your suggested scenario, Vendor No. and the Vendor Name come back fine, but the Payments that are returned include all payments for these vendors during the time period, regardless of whether they were coded to the G/L Account 272000 or not.
I've tried =NF(B3,"Credit Amount"), but that returns ALL payments for that vendor, regardless of the date, but it won't let me filter that statement.
Any other suggestions?
Thanks!
Beth
Please sign in to leave a comment.