I have created a report using the Navision Sales Invoice Header and Sales Invoice Line tables to give me sales by salesperson for a specific period. However we also need to see any credits for the same period and salesperson.
I know the credits are kept in the Sales Cr.Memo Header and Sales Cr.Memo Line tables. The problem is I don't know how to combine these tables so I get all of the data coming up in the same report.
I could put the credits at the end of the report as a separate report, but I would prefer for the credits to come up against each customer within the body of the main report. Is there a way to do this or does it have to be done separately? I have a few reports that I run that I need to be the same.
I would appreciate any assistance with this!
Nicole
Date
Votes
10 comments
-
Jet Reports Historic Posts I'm going to try to give a short answer, but you will have to figure somethings out while trying, since I can't "regionalise" your report:
In C5 you have the formula:
=NL("Rows=10";"Sales Invoice Header";"Salesperson Code";"Document Date";Options!$D$5;"Salesperson Code";Options!$D$6;"No.";NL("Filter";"Sales Invoice Line";"Document No.";"Posting Group";Options!$D$7))
the "Rows=10" defines that, for every salesperson, you have 10 rows that will be copied, these are the 10 rows you see in designer, and can become hundreds in reporting mode.
Basically, if you were to make this "Rows=20", and mimic the formulas used for the Sales Inv., but make them for the Sales CM below, you would get the result.
The main thing there is that the lines need a good reference to the salesperson they are next to in the report.
So: expand the "range" per salesperson and put in the formula's for CM's underneath the formula's for Invoices.
Remark: The formula from C5 gets the salespersons that made Invoices within the filters, this would mean that a salesperson has a CM in the filters but no Invoice, he would not be shown. You can solve this using the "Union" function (check the help for reference). -
Jet Reports Historic Posts Thanks for your help Jan. I tried your way but couldn't seem to get it to work. I tried the Union of the two tables and that worked! Much appreciated.
-
Jet Reports Historic Posts Hi again,
Well I thought this was fixed but it is still not right. The data is coming through ok for both invoices and credit notes, but it seems whenever a customer has multiple invoices and credit notes, it repeats the same credit note after each invoice row. Does anyone know how to fix this? I've been searching in the knowledgebase but can't find anything yet. I even tried creating a brand new report that was much more basic than the last one with only the Sales Invoice Header and Sales Cr.Memo Header in it (no line details) but it still does it - see attached.
Thank you! -
Jet Reports Historic Posts The problem is you're trying to put the cm's and the inv on the same line, while I was refering to increase the number of rows and put it underneath your current formula's.
The thing you did with the "union" makes the report act very weird. Also, Sherman stated a couple of days ago that using more than two "rows" in one line can really make Jet act weird.
See attachment for solution.
Regards,
Jan -
Jet Reports Historic Posts Im also having the same trouble, I have attached a very simple report in order to test formulas, my ultimate aim is quite a complex report, but the end result should be the same, basically I just want all posted Invoices and Posted Credit notes on the same report, when I work out how to do that I'l be able to work the formula into my report, any help will be appreciated.
Also can someone explain the Union function, I've used Link but what I've read about Union sounds like it does the same thing?
Thanks in advance -
Jet Reports Historic Posts Sorry, forgot to attach the report
-
Jet Reports Historic Posts Hello,
NP(Union) wasn't being used quite right in the previous report. Basically NP(Union) can be used to union 2 lists of data together into one big list. So if you wanted the union of the customer numbers from both the Sales Invoice Header and Sales Cr. Memo Header tables, you could do something like this:
B3:=NL("Filter","Sales Invoice Header","Sell-to Customer No.")B4:=NL("Filter","Sales Cr. Memo Header", "Sell-to Customer No.")B5:=NL("Rows",NP("Union",$B$3,$B$4))
This would give you one long, sorted list of the customer numbers from both tables without duplicates. You could probably use the last report Jan posted on this thread except substitute her function in D5 for a union of the customer numbers from the 2 tables. I don't think you want to use NP(Union) in place of your 2 rows functions from the Sales Invoice Header and Sales Cr. Memo Header because then you wouldn't know which table to get the details from. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts First of all thank you for your help, but it seems that I was wrong, I've worked out how to join the 2 tables together on my simple report, but putting it into the complex one, I am flummoxed (Cool word huh?!)
Anyway, I have attached my report for you to have a go at, the open sheet is the one Im trying to put the formula into, I think the problem is that I already have a "Link" in E5 and E9, and subtotals per Doc no. I've left the union formula out of this sheet as I'm really not sure where to put it, do I have to have 2 unions, one for column E and one for column F?
Thanks
(Confused Jetter) -
Jet Reports Historic Posts And again with the lack of attachment!…
-
Jet Reports Historic Posts Anyone… pretty please?
Please sign in to leave a comment.