Query vs Sales Analysis Report

[Former Member]

I wrote a query that does virtually the same thing as a sales analysis report -- add up all invoice totals and calculate a gross sales number.

In the sales analysis report ItemXXX has a total sales of $1456.94

My query ItemXXX has a total sales of $1457.73

A $.79 discrepancy which I assumed was a result of the fact that my query doesn't include A/R Credit Memos. Only some items have this discrepancy.

The problem is that if I do an inventory posting list on the item there are no A/R Credit Memos listed. Only A/R Invoices, A/P Invoices, and A/P Goods Receipts.

What other documents could be affecting this number? Any other things that could be causing this?

Thanks,

-Steve

[Former Member · Accepted solution]

Hi Steve,

This discrepancy is caused by rounding. If you have many line items to add up, you have to use exactly the way system use to match the number.

Thanks,

Gordon

[Former Member · Accepted solution]

Gordon,

That makes sense. Is there a way to find out what rounding method the sales analysis report uses, and then apply it to my query?

Thanks Again,

-Steve

[Former Member · Accepted solution]

That is determined by so many factors. The simple way would be by selected scenario test run and check each run result to compare.

Thanks,

Gordon

[jitin_chawla · Accepted solution]

Hi Steve,

A possible cause of the difference can be rounding. When you run the Sales Analysis report then in the Business One the amounts are displayed rounded to the decimal settings mentioned in the settings.

However, when you run the same from the Query, the same will take the decimals to 6 places and the same is giving you a difference between the two reports.

Regards,

Jitin Chawla

[Former Member · Accepted solution]

HELLO GORDON,

I WAS LOOKING TO YOU FOR ASKING ABOUT A QUERY OF SALES ANALYSIS IN SAP BO, BECAUSE I NEED TO MIMIC THE SAME REPORT INTO THE COMPANY I APPRECIATE YOUR EFFORT REALLY

MY EMAIL ES jorgea_glezh@hotmail.com

THANKS

[kothandaraman_nagarajan · Accepted solution]

Hi,

This is SAP Business one reporting and printing forum. Please find correct forum and repost your requirement to get quick assistance.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi Nagarajan,

Im user of Sap Business One, i need the completly query for obtain the Sales Analysis Report

i have this but in some items dont match

SELECT  ItemCode,   

        Sum(TotFac) - SUM(TotNC) as  'FacturadoNeto',   

        Sum(GBrutaFac)-SUM(GbrutaNC) as  'GBruta',   

        Round((Sum(GBrutaFac)-SUM(GbrutaNC))/case when  (Sum(TotFac) - SUM(TotNC))= 0 then 1 else (Sum(TotFac) - SUM(TotNC)) end  *100,2) as '% Margen'   

FROM (   

SELECT    

ItemCode = T1.ItemCode, 

Marca = (Select T2.FirmName from OMRC T2 JOIN OITM T3 on T2.FirmCode = T3.FirmCode where T3.ItemCode=T1.Itemcode), 

TotFac = Sum(T1.Linetotal),    

GBrutaFac = SUM(T1.GrssProfit),   

TotNc= 0,   

GbrutaNC = 0   

FROM OINV T0 (NOLOCK) INNER JOIN INV1 T1 (NOLOCK) ON T0.DocEntry = T1.DocEntry                                    

WHERE (T0.[DocDate] >='20140101' AND  T0.[DocDate] <='20141231') 

  and T0.DocType  = 'I' -- solo facturas por articulos   

Group by  T1.ItemCode  

Union   

SELECT    

ItemCode = T1.ItemCode,   

Marca = (Select T2.FirmName from OMRC T2 JOIN OITM T3 on T2.FirmCode = T3.FirmCode where T3.ItemCode=T1.Itemcode), 

TotFac = 0,   

GBrutaFac = 0,   

TotNc= Sum(T1.LineTotal),    

GbrutaNC = SUM(T1.GrssProfit)   

FROM ORIN T0 (NOLOCK) INNER JOIN RIN1 T1 (NOLOCK) ON T0.DocEntry = T1.DocEntry                                    

WHERE (T0.[DocDate] >='20140101' AND  T0.[DocDate] <='20141231')

  and T0.DocType  = 'I' -- solo NC por articulos   

Group by T1.ItemCode 

Union

SELECT    

ItemCode = T1.ItemCode,   

Marca = (Select T2.FirmName from OMRC T2 JOIN OITM T3 on T2.FirmCode = T3.FirmCode where T3.ItemCode=T1.Itemcode), 

TotFac = 0,   

GBrutaFac = 0,   

TotNc= Sum(T1.LineTotal),    

GbrutaNC = SUM(T1.GrssProfit)   

FROM ODPI T0 (NOLOCK) INNER JOIN DPI1 T1 (NOLOCK) ON T0.DocEntry = T1.DocEntry                                    

WHERE (T0.[DocDate] >='20140101' AND  T0.[DocDate] <='20141231')     

  and T0.DocType  = 'I' -- solo Fact Anticipos por articulos   

Group by T1.ItemCode

) T   

Group by  ItemCode, Marca

[kothandaraman_nagarajan · Accepted solution]

Hi Jorge,

Create as new discussion for above requirement. This is standard forum rule.

Thanks & Regards,

Nagarajan

[Former Member]

HELLO STEVE,

WOULD YOU SHARE YOU QUERY WITH ME PLEASE,

BECAUSE I NEED TO MIMIC THE SAME REPORT INTO THE COMPANY

I APPRECIATE YOUR EFFORT REALLY

THANKS