Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Sales for customer
jonathanchye
Member Posts: 34
in SQL General
Hi all,
I'm fairly new here so please bear with me
I'm writing TSQL for simple report which shows Sales (LCY) by customer. As a reference I'm using the Customer Statistics window to check if my values are correct.
Initially I went with the Value Entry table by summing the Sales Amount (Actual) column. The reason is the report also includes other fields for the report such as Item No. This method shows the correct Sales figure for most customers except some.
I've then replicated in TSQL the same logic as the Sales (LCY) flow field on the Customer table (summing the Sales (LCY) column from the Cust Ledger Entry table) which as expected consistently gives me the same figure as on the NAV window.
My question is why are the sales values different for some customers? Am I missing anything obvious here?
Below is a stripped down TSQL code I've used for testing. The results from Value Entry table seems to be less than expected.
select sum([Sales Amount (Actual)]) from [MYCOMPANY].[dbo].[MYCOMPANY$Value Entry]
where [Source No_] = '8127743'
and year([Posting Date]) = 2015
select sum([Sales (LCY)]) from [MYCOMPANY].[dbo].[MYCOMPANY$Cust_ Ledger Entry]
where [Customer No_] = '8127743'
and year([Posting Date]) = 2015
I'm fairly new here so please bear with me
I'm writing TSQL for simple report which shows Sales (LCY) by customer. As a reference I'm using the Customer Statistics window to check if my values are correct.
Initially I went with the Value Entry table by summing the Sales Amount (Actual) column. The reason is the report also includes other fields for the report such as Item No. This method shows the correct Sales figure for most customers except some.
I've then replicated in TSQL the same logic as the Sales (LCY) flow field on the Customer table (summing the Sales (LCY) column from the Cust Ledger Entry table) which as expected consistently gives me the same figure as on the NAV window.
My question is why are the sales values different for some customers? Am I missing anything obvious here?
Below is a stripped down TSQL code I've used for testing. The results from Value Entry table seems to be less than expected.
select sum([Sales Amount (Actual)]) from [MYCOMPANY].[dbo].[MYCOMPANY$Value Entry]
where [Source No_] = '8127743'
and year([Posting Date]) = 2015
select sum([Sales (LCY)]) from [MYCOMPANY].[dbo].[MYCOMPANY$Cust_ Ledger Entry]
where [Customer No_] = '8127743'
and year([Posting Date]) = 2015
0
Best Answers
-
jglathe
Member Posts: 639
Hi Jonathan,
Value Entries are only for amounts related to items (inventory). If you also sell services, or some revenue is posted via G/L accounts directly, then you can't have the full picture.
The Sales (LCY) field in the customer ledger entries should provide this information. There are issues, though:
1. If someone posts via general journal without the document type "invoice" or "credit memo", it won't be filled in.
2. If someone posts via general journal with multiple lines (not account - balance account), it won't be filled in, regardless of document type.
These issues can be resolved with some extra code in CU13. You can even correct this for existing postings.
With that in mind, Sales (LCY) is the better choice.
with best regards
Jens5
Answers
Value Entries are only for amounts related to items (inventory). If you also sell services, or some revenue is posted via G/L accounts directly, then you can't have the full picture.
The Sales (LCY) field in the customer ledger entries should provide this information. There are issues, though:
1. If someone posts via general journal without the document type "invoice" or "credit memo", it won't be filled in.
2. If someone posts via general journal with multiple lines (not account - balance account), it won't be filled in, regardless of document type.
These issues can be resolved with some extra code in CU13. You can even correct this for existing postings.
With that in mind, Sales (LCY) is the better choice.
with best regards
Jens
If not I guess it's not too much of a problem to change to Customer Ledger Entries. I will just have to figure out method to also include item level information in the report.