PT Sterling Tulus Cemerlang
www.sterling-team.com

Phone
+62-877-8655-5055
+62 (21) 580-6055

Email
sales-enquiry@sterling-team.com

Address
Sampoerna Strategic Square
South Tower 18th
Jl Jend. Sudirman kav 45-46
Jakarta 12930 - Indonesia

300+ FREE Tips for you from Sterling Tulus Cemerlang (STEM) - SAP Indonesia Gold Partner, and Become SAP B1 Super User

Query for List of Top 5 Item by Sales

SAP Business One Tips Query Top Sales



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 :


This article was written by PT. Sterling Tulus Cemerlang, SAP Business One GOLD Partner in Indonesia.
More details about SAP Business One,
email to: sales-enquiry@sterling-team.com or call +6221-5806336 or wa +6287786555055

, , ,