Skip to end of metadata
Go to start of metadata



SAP Reports using SQL Queries

Link to Content's target Space :

https://wiki.sdn.sap.com/wiki/display/B1/Tips+and+Tricks 

Applies to:

SAP Business One Marketing Documents  

Summary

This articles shows some useful and frequently reqired reports as SQL queries. 

Author(s):  

Navneet Dhami
Company:     Sapphire Systems
Created on:    24/11/2010
Author(s) Bio

Navneet Dhami is currently working with Sapphire Systems as SAP SDK support consultant. 

Table of Contents


Monthly Sales Analysis report

 This report will give monthly Sales analysis for the partiular year entered.Run the below query in Query generator.

Monthly Sales Analysis Query

Declare @Year Numeric
Set @Year='2010'
SELECT @Year,T0.ItemCode, T0.ItemName, T0.OnHand,
                        sum(Case DATENAME(month,T2.DocDate) when 'January' then T1.Quantity   else 0 end) as January Qty,
                        sum(Case DATENAME(month,T2.DocDate) when 'February' then T1.Quantity   else 0 end) as February Qty,
                        sum(Case DATENAME(month,T2.DocDate) when 'March' then T1.Quantity   else 0 end) as March Qty,
                        sum(Case DATENAME(month,T2.DocDate) when 'April' then T1.Quantity   else 0 end) as April Qty,
                        sum(Case DATENAME(month,T2.DocDate) when 'May' then T1.Quantity   else 0 end) as May Qty,
                        sum(Case DATENAME(month,T2.DocDate) when 'June' then T1.Quantity   else 0 end) as June Qty,
                        sum(Case DATENAME(month,T2.DocDate) when 'July' then T1.Quantity   else 0 end) as July Qty,
                        sum(Case DATENAME(month,T2.DocDate) when 'September' then T1.Quantity   else 0 end) as September Qty,
                        sum(Case DATENAME(month,T2.DocDate) when 'October' then T1.Quantity   else 0 end) as October Qty,
                        sum(Case DATENAME(month,T2.DocDate) when 'November' then T1.Quantity   else 0 end) as November Qty,
                        sum(Case DATENAME(month,T2.DocDate) when 'December' then T1.Quantity   else 0 end) as December Qty
 
FROM dbo.OITM  T0
      INNER JOIN INV1 T1 ON T0.ItemCode = T1.ItemCode
      INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry
     
WHERE DATENAME(YEAR ,T1.DocDate )=@Year
 
GROUP BY DATENAME(month,T1.DocDate ),T0.ItemCode, T0.ItemName, T0.OnHand

or

SELECT P.CardCode,P.CardName,
 (SELECT SUM(Debit) - sum(Credit) FROM JDT1 WHERE ShortName= P.CardCode AND Year(DueDate) = 2009 AND TransType IN ('13','14')) '2009 Sales',
 (SELECT SUM(Debit) - sum(Credit) FROM JDT1 WHERE ShortName= P.CardCode AND Year(DueDate) = 2009 AND TransType IN ('13','14'))/12 '2009 Monthly Avg',
 (SELECT SUM(Debit) - sum(Credit) FROM JDT1 WHERE ShortName= P.CardCode AND Year(DueDate) = 2010 AND TransType IN ('13','14')) '2010 Sales',
 (SELECT SUM(Debit) - sum(Credit) FROM JDT1 WHERE ShortName= P.CardCode AND Year(DueDate) = 2010 AND TransType IN ('13','14'))/MONTH(GetDate()) '2010 Monthly Avg',
 ISNULL(1,0) as Jan,
 ISNULL(2,0) as Feb,
 ISNULL(3,0) as Mar,
 ISNULL(4,0) as Apr,
 ISNULL(5,0) as May,
 ISNULL(6,0) as Jun,
 ISNULL(7,0) as Jul,
 ISNULL(8,0) as Aug,
 ISNULL(9,0) as Sep,
 ISNULL(10,0) as Oct,
 ISNULL(11,0) as Nov,
 ISNULL(12,0) as Dec
FROM (SELECT T0.CARDCODE, T0.CARDNAME, (T1.Debit - T1.Credit) AS BAL,MONTH(T1.Duedate) as month FROM OCRD T0
          LEFT JOIN JDT1 T1 ON T1.ShortName = T0.CardCode AND Year(T1.Duedate) = 2010 AND T1.TransType in ('13','14')) S
          PIVOT  (SUM(S.BAL) FOR month IN
         (1,2,3,4,5,6,7,8,9,10,11,12)) P
Order By P.CardCode

Related Content

http://www.sdn.sap.com/irj/sdn/businessone

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/21815

Useful Information

Short quick summary to catch the reader's attention and of course the search engine