Hello,
I am trying to create a report that summarizes the sales amount per product number per Parentco. The parentco is in the Customer Table and the product info is in the Sales Invoice Line Table. The only suject that links them is the Customer No. however there can be several Customer Nos. per parentco and i dont want it filtered like that. I just want the total product number sales amounts per Parentco. Is there any way to do this, maybe be combining NL functions or is this impossible because the Parentco is only in one table. Please help!
Date
Votes
7 comments
-
Jet Reports Historic Posts As I don't have Excel nor Jet on my home computer (it's 9PM over here) I have to do this by heart…
- create a list of parent cos numbers in e.g. D4: NL("Rows","Customer","Parent co")
- create an array of customer nos per parenct co in e.g. E4: NL("Filter","Customer","No.","Parent co","@@"&$D4)
- create the total sales amount in e.g. F4: NL("Sum","Sales invoice line","Sales amount","Customer no.","@@"$E4)
You may have to adjust the table and field names I used. Please let us know if this worked for you.
(P.s. As I was not quite sure what you meant with "per product info" I left that part out of my explanation) -
Jet Reports Historic Posts Hello,
Thanks for your response. My problem is that I do not want the customer no. in my table. I just want the Parentco -Product No -Total Sales.
For example, my report right now is similar to this format:
PARENTCO CUSTOMER NO. CUSTOMER NAME PRODUCT NO. TOTAL SALES
COCA COLA 012 ATLANTIC 10500 $5000
COCA COLA 045 COKE MEXICO 10500 $10500
I want to eliminate the customer number and have it look more like this:
PARENTCO PRODUCT NO. TOTAL SALES
COCA COLA 10500 $15,500
My problem is that the customer number is the only thing that connects the Customer Table and the Sales Invoice Line (where the product information is) but i do not want my report to be filtered by customer number. I hope this make sense. Thank you for your help! -
Jet Reports Historic Posts Yes, but what i am saying is i do not want the Sum of the product sales per Customer Number. i want it for the Parentco and do not want it separated by customer numbers so what you are giving me will generate the same report i have now, which is filtered by customer number.
-
Jet Reports Historic Posts I had typed up a reply yesterday but bad things happened to my computer. I've attached an excel sample that should demonstrate a technique I use for some reports. It may or may not help here. Its not perfect as it requires maintenance of Jet with changes to the Customer details, but it works.
Notice I've hidden a few columns and also used conditional hide to not show rows where there are no sales.
You may also want to add posting date filters, etc too.
Hope this helps get you on your way. -
Jet Reports Historic Posts Hey. I think this one might actually work. thanks! If i wanted to create a date filter, should i create another NL function or add it to another one in the report. Thanks for your help!
-
Jet Reports Historic Posts Nevermind markl, i just added it to the NL function with the Sales invoice line which works! This was what i needed-thanks so much for your help!
-
Jet Reports Historic Posts Not a problem. Happy to help.
Please sign in to leave a comment.