Tips SAP Indonesia – As we know, SAP Business One provide a feature / facilities to make us easier retrieving data from the system using queries to database.
In this example, we want to make a report to show the Top 5 Items that are most profitable for the company based on sales made in a certain period.
Below is one of the example to show List of Top 5 Sales Items in certain date period :
/* Top 5 Sales Items */
SELECT TOP 5
S.ItemCode,
MAX(S.Description) as ‘Item Description’,
SUM(S.LineTotal) as ‘Amount(LC)’
FROM
(SELECT
T1.ItemCode AS ‘ItemCode’,
T1.Dscription AS ‘Description’,
T1.LineTotal AS ‘LineTotal’
FROM dbo.OINV T0
INNER JOIN dbo.INV1 T1 ON T1.DocENtry = T0.DocENtry
WHERE T0.docdate >= [%0]
and T0.docdate <= [%1]
AND T0.doctype = ‘I’
UNION
SELECT
T1.ItemCode AS ‘ItemCode’,
T1.Dscription AS ‘Description’,
-T1.LineTotal AS ‘LineTotal’
FROM dbo.ORIN T0
INNER JOIN dbo.RIN1 T1 ON T1.DocENtry = T0.DocENtry
WHERE T0.docdate >= [%0] and T0.docdate <= [%1]
AND T0.doctype = ‘I’) S
GROUP BY S.ItemCode
ORDER BY SUM(S.LineTotal) DESC
When we execute the query, SAP Business One will ask for the period of date of the Top 5 Sales Items that we want to retrieve
And when we click the “OK” button, SAP Business One will execute the query and show the result as below :
You can see other tips related with query manager in SAP Business One in the link below :
- How to Use Custom Parameter on Query Manager
https://www.sap-business-one-tips.com/how-to-use-custom-parameter-on-query-manager/ - Easier Way to Search Query on Query Manager
https://www.sap-business-one-tips.com/easier-way-to-search-query-on-query-manager/ - How to Display Total for a Column in SAP Business One Query Manager
https://www.sap-business-one-tips.com/how-to-display-total-for-a-column-in-sap-business-one-query-manager/
