We are new to Essentials and after watching the videos are still confused of the best way to write some reports.
I'd like to have a default way of working if possible but understand some reports will have to be different.
Bearing in mind I've already used the report writer to give me an Invoice list by customer that falls below a given %profit.
I'm writing a group of reports to give customer %profit by Month but would like to use the same data for Customer type %Profit by Month and also customer rating %Profit by Month.
I'd also like to see the report show the actual profit.
Jan Feb Mar
Customer 1 50% 40% 60%
Customer 2 48% 50% 50%
Do I use Table Builder for the data list and create a pivot table for each view I'd like to see or is there a better way of doing this in Essentials?
Chris
Nav 2013
Excel 2013
Date
Votes
3 comments
-
Jet Reports Historic Posts Hi,
For that kind of report, I'd use rows, columns and sums formulae.
For example in C6 I'd have an NL("Rows") giving all the customers you want.
In D3 I'd have an NL("Columns") on the date table with the total date range of the report, filtering on period type of month and giving the start date.
In D4 I'd use the date given in the cell above to build the date filter for date above it eg NP("DateFilter",D3, EOMONTH(D3))
In D5 I'd put TEXT(D3,"MMM") to give you the month names.
In D6 I'd write the jet function to get the %profit, filtering on the customer and the date range.
Hide rows 3 and 4.
When you run it, the rows will expand down, the columns will expand right and boom, report!
If that wasn't clear or you need help with any of it, just post again. -
Jet Reports Historic Posts To add, have you looked at the default reports, NAV003 - Customer Sales and Profitability Items would be a good starting point & also useful to see how Jet have approached this, but also try Teresas method to compare the results & you will get some good practice as well.
-
Jet Reports Historic Posts I started at what I thought the basics are but ended up getting confused.
I thought I'd get the sales per day working first so I could see how to use the function wizard properly with columns etc.
The report gives me exactly what I was looking for on this test.
When I tried to write a second results line for comparative days last year I could only see previous sales from days that had a sale in the filter dates.
Is there a way to put two dates in but give me the sales for the last 4 years split between 4 Lines for comparison?
Am I starting from the wrong table for this? I noticed that the NAV017 Accounts receivable uses a pivot table to show the data.
With a bit of learning should I be able to make this work just with Function Wizard or is A pivot a better way to go?
C2: Order Date D2: 01/01/2015..10/01/2015
C3: Document Type C3: Order
G4: =NL("Columns","Sales Header","Order Date","Order Date",D2)
G5: =NL("Sum","Sales Header","Amount","Document Type",$D$3,"Order Date",G4)
E7: =NL("Rows","Sales Header",,"Order Date",$D$2,"Document Type",$D$3)
Please sign in to leave a comment.