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.
Best answer by Naveen Boga
View original