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

400+ 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
SHARE ON



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 :

Run Your Business With The Piece of Mind Using SAP Business One

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

, , ,