Solved

How can I create create a view that shows the most recent order date per customer?

  • 21 January 2022
  • 2 replies
  • 64 views

Userlevel 3
Badge

I need to create a report of most recent order date per customer by month. The problem is I need to count how many customers had a last order date in each month. I have been trying various ways around getting Report Designer to only include the maximum SOOrder.OrderDate against each customer by trying to use the Max function in the filters or the joins but that doesn’t seem to work. I finally decided the only way that would work for me would be to create a custom view that simply returns the Max(SOOrder.OrderDate) against each customer where the status is Complete and DocType is SO or WO.  This I can do in a GI easily enough but I am trying to translate that to a View that I can access from Report Designer.  I created a simple GI that returns Customer.AcctCD and Max(SOOrder.OrderDate) grouped by Customer.AcctCD which gives the values I need and then looked at the trace for the query to see what the SQL looked like:

SELECT TOP (19) [Customer_BAccount].[BAccountID] AS [Customer_BAccountID], MAX( [Customer_BAccount].[AcctCD]) AS [Customer_AcctCD], MAX( [Customer_BAccount].[AcctName]) AS [Customer_AcctName], MAX( [Customer_BAccount].[NoteID]) AS [Customer_NoteID], MAX( [SOOrder].[OrderType]) AS [SOOrder_OrderType], MAX( [SOOrder].[OrderNbr]) AS [SOOrder_OrderNbr], MAX( [SOOrder].[OrderDate]) AS [SOOrder_OrderDate], MAX( [SOOrder].[NoteID]) AS [SOOrder_NoteID], MAX( [SOOrder].[CuryID]) AS [SOOrder_CuryID], SUM( [SOOrder].[CuryInfoID]) AS [SOOrder_CuryInfoID] 
FROM (
[Customer] [Customer_Customer]
INNER JOIN [BAccount] [Customer_BAccount] ON ( [Customer_BAccount].[CompanyID] = 5) AND [Customer_BAccount].[DeletedDatabaseRecord] = 0 AND [Customer_Customer].[BAccountID] = [Customer_BAccount].[BAccountID]
)
LEFT JOIN [SOOrder] [SOOrder] ON ( [SOOrder].[CompanyID] = 5) AND ( [SOOrder].[BranchID] IS NULL OR [SOOrder].[BranchID] = 23) AND ( [Customer_BAccount].[BAccountID] = [SOOrder].[CustomerID])
WHERE ( ( [SOOrder].[OrderType] = 'SO' OR [SOOrder].[OrderType] = 'WO') AND [SOOrder].[Status] = 'C') AND ( [Customer_Customer].[CompanyID] = 5) AND [Customer_Customer].[DeletedDatabaseRecord] = 0
GROUP BY [Customer_BAccount].[BAccountID]
ORDER BY MAX( [Customer_BAccount].[AcctCD]), MAX( [SOOrder].[OrderType]), MAX( [SOOrder].[OrderNbr]) OPTION(OPTIMIZE FOR UNKNOWN)

 I used this as a basis for creating sql to create a view:

--[mssql: Native]
IF OBJECT_ID('dbo.custlastorder', 'V') IS NOT NULL DROP VIEW dbo.custlastorder
GO
CREATE VIEW custlastorder AS
SELECT B.[BAccountID] AS [Customer_BAccountID], MAX( B.[AcctCD]) AS [Customer_AcctCD], MAX( B.[AcctName]) AS [Customer_AcctName], MAX( B.[NoteID]) AS [Customer_NoteID], MAX( [SOOrder].[OrderType]) AS [SOOrder_OrderType], MAX( [SOOrder].[OrderNbr]) AS [SOOrder_OrderNbr], MAX( [SOOrder].[OrderDate]) AS [SOOrder_OrderDate], MAX( [SOOrder].[NoteID]) AS [SOOrder_NoteID], MAX( [SOOrder].[CuryID]) AS [SOOrder_CuryID], SUM( [SOOrder].[CuryInfoID]) AS [SOOrder_CuryInfoID]
FROM (
[Customer] C
INNER JOIN [BAccount] B ON ( B.[CompanyID] = 3) AND B.[DeletedDatabaseRecord] = 0 AND C.[BAccountID] = B.[BAccountID]
)
INNER JOIN [SOOrder] [SOOrder] ON ( [SOOrder].[CompanyID] = 3) AND ( [SOOrder].[BranchID] IS NULL OR [SOOrder].[BranchID] = 23) AND ( B.[BAccountID] = [SOOrder].[CustomerID])
WHERE ( ( [SOOrder].[OrderType] = 'SO' OR [SOOrder].[OrderType] = 'WO') AND [SOOrder].[Status] = 'C') AND ( C.[CompanyID] = 3) AND C.[DeletedDatabaseRecord] = 0
GROUP BY B.[BAccountID]
GO

and a DAC based on it just by adding it in a customization project, 

All appears fine when I use this in a GI linked to Customer on bAccountID and it returns what I would expect. However when I try to view the next page of data I get an error:

Incorrect syntax near 'OFFSET'.
Incorrect syntax near '18'.

Ultimately I was hoping to use this in a report but when I add it to the schema n report designer and just add CustLastOrder.Customer_AcctCD I don’t see the same thing as saw in the GI.

I am new to trying to customise this way and probably doing a lot wrong but if anyone has any advice I would be very appreciative.

icon

Best answer by Naveen Boga 26 January 2022, 10:52

View original

2 replies

Userlevel 7
Badge +12

Hi, @ppowell  I just worked on this requirement, and working as expected to me (Navigated to the multiple and observed all good with the GI). please find the details below.

I have created an SQLView and for that SQLView created GI with the below modifications.

  • While creating SQLView, I have removed all the underscores for that fields.
  • Changed BranchID, Company ID to fetch the data from my local machine.
  • While creating the DAC file, I have provided the IsKey= true for the KEY fields.

Here is the GI.

SQL View:

DROP VIEW IF EXISTS VWGetCustomerSOData

GO
Create view VWGetCustomerSOData
as


SELECT B.[BAccountID] AS [CustomerBAccountID], MAX( B.[AcctCD]) AS [CustomerAcctCD], MAX( B.[AcctName]) AS [CustomerAcctName], MAX( B.[NoteID]) AS [CustomerNoteID],
MAX( [SOOrder].[OrderType]) AS [SOOrderOrderType], MAX( [SOOrder].[OrderNbr]) AS [SOOrderOrderNbr],
MAX( [SOOrder].[OrderDate]) AS [SOOrderOrderDate], MAX( [SOOrder].[NoteID]) AS [SOOrderNoteID], MAX( [SOOrder].[CuryID]) AS [SOOrderCuryID],
SUM( [SOOrder].[CuryInfoID]) AS [SOOrderCuryInfoID]
FROM (
[Customer] C
INNER JOIN [BAccount] B ON ( B.[CompanyID] = 2) AND B.[DeletedDatabaseRecord] = 0 AND C.[BAccountID] = B.[BAccountID]
)
INNER JOIN [SOOrder] [SOOrder] ON ( [SOOrder].[CompanyID] = 2) AND ( [SOOrder].[BranchID] IS NULL OR [SOOrder].[BranchID] = 16)
AND ( B.[BAccountID] = [SOOrder].[CustomerID])
WHERE ( ( [SOOrder].[OrderType] = 'SO' OR [SOOrder].[OrderType] = 'WO') AND [SOOrder].[Status] = 'C') AND
( C.[CompanyID] = 2) AND C.[DeletedDatabaseRecord] = 0
GROUP BY B.[BAccountID]

DAC File:

using System;
using PX.Data;

namespace GetCustomerSOData
{
[Serializable]
[PXCacheName("VWGetCustomerSOData")]
public class VWGetCustomerSOData : IBqlTable
{
#region CustomerBAccountID
[PXDBInt(IsKey = true)]
[PXUIField(DisplayName = "Customer BAccount ID")]
public virtual int? CustomerBAccountID { get; set; }
public abstract class customerBAccountID : PX.Data.BQL.BqlInt.Field<customerBAccountID> { }
#endregion

#region CustomerAcctCD
[PXDBString(30, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Customer Acct CD")]
public virtual string CustomerAcctCD { get; set; }
public abstract class customerAcctCD : PX.Data.BQL.BqlString.Field<customerAcctCD> { }
#endregion

#region CustomerAcctName
[PXDBString(255, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Customer Acct Name")]
public virtual string CustomerAcctName { get; set; }
public abstract class customerAcctName : PX.Data.BQL.BqlString.Field<customerAcctName> { }
#endregion

#region CustomerNoteID
[PXDBGuid()]
[PXUIField(DisplayName = "Customer Note ID")]
public virtual Guid? CustomerNoteID { get; set; }
public abstract class customerNoteID : PX.Data.BQL.BqlGuid.Field<customerNoteID> { }
#endregion

#region SOOrderOrderType
[PXDBString(2,IsKey = true, IsFixed = true, InputMask = "")]
[PXUIField(DisplayName = "SOOrder Order Type")]
public virtual string SOOrderOrderType { get; set; }
public abstract class sOOrderOrderType : PX.Data.BQL.BqlString.Field<sOOrderOrderType> { }
#endregion

#region SOOrderOrderNbr
[PXDBString(15,IsKey = true, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "SOOrder Order Nbr")]
public virtual string SOOrderOrderNbr { get; set; }
public abstract class sOOrderOrderNbr : PX.Data.BQL.BqlString.Field<sOOrderOrderNbr> { }
#endregion

#region SOOrderOrderDate
[PXDBDate()]
[PXUIField(DisplayName = "SOOrder Order Date")]
public virtual DateTime? SOOrderOrderDate { get; set; }
public abstract class sOOrderOrderDate : PX.Data.BQL.BqlDateTime.Field<sOOrderOrderDate> { }
#endregion

#region SOOrderNoteID
[PXDBGuid()]
[PXUIField(DisplayName = "SOOrder Note ID")]
public virtual Guid? SOOrderNoteID { get; set; }
public abstract class sOOrderNoteID : PX.Data.BQL.BqlGuid.Field<sOOrderNoteID> { }
#endregion

#region SOOrderCuryID
[PXDBString(5, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "SOOrder Cury ID")]
public virtual string SOOrderCuryID { get; set; }
public abstract class sOOrderCuryID : PX.Data.BQL.BqlString.Field<sOOrderCuryID> { }
#endregion

#region SOOrderCuryInfoID
[PXDBLong()]
[PXUIField(DisplayName = "SOOrder Cury Info ID")]
public virtual long? SOOrderCuryInfoID { get; set; }
public abstract class sOOrderCuryInfoID : PX.Data.BQL.BqlLong.Field<sOOrderCuryInfoID> { }
#endregion
}
}

 

 

Userlevel 3
Badge

Hi, @ppowell  I just worked on this requirement, and working as expected to me (Navigated to the multiple and observed all good with the GI). please find the details below.

I have created an SQLView and for that SQLView created GI with the below modifications.

  • While creating SQLView, I have removed all the underscores for that fields.
  • Changed BranchID, Company ID to fetch the data from my local machine.
  • While creating the DAC file, I have provided the IsKey= true for the KEY fields.

 

 

 

I really appreciate your reply.  This fixes the problem I was running into.  I’m guessing it was due to the key not being defined as I just used the DAC as generated without editing it. Working perfectly now in a GI and Report Designer.

Thanks,

Phil

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved