SAP B1 Sales Analysis Invoice report

[former_member229757]

Hi Experts,

I need a report to match with existing sales analysis invoice report.

How do i include AR credit memo in the report to match with existing sales analysis report.

select t1.Name,t1.Today_Total,t1.Month_Total,t1.Current_Year_Total,t1.Prev_Year_Total,t1.[Perc_Changed] from(select 1 as Row,a.Name ,c.Today_Total,d.Month_Total,e.Current_Year_Total,f.Prev_Year_Total, ((e.Current_Year_Total-f.Prev_Year_Total)/f.Prev_Year_Total)*100 as 'Perc_Changed' from OCRY a left join ( select SUM(a.DocTotal)-isnull(Sum(a.VatSum),0) as Today_Total,b.Country from OINV a left join OCRD b on a.CardCode=b.CardCode where CAST(a.DocDate AS DATE)=CAST(GETDATE() AS DATE) and a.CANCELED = 'N' group by b.Country )c on a.Code=c.Country left join ( select SUM(a.DocTotal)-isnull(Sum(a.VatSum),0) as Month_Total,b.Country from OINV a left join OCRD b on a.CardCode=b.CardCode where month(a.DocDate)= month(GETDATE()) and Year(a.DocDate)= Year(GETDATE()) and a.CANCELED = 'N' group by b.Country )d on a.Code=d.Country left join ( select SUM(a.DocTotal)-isnull(Sum(a.VatSum),0) as Current_Year_Total,b.Country from OINV a left join OCRD b on a.CardCode=b.CardCode where Year(a.DocDate)= Year(GETDATE()) and a.CANCELED = 'N' group by b.Country )e on a.Code=e.Country left join ( select SUM(a.DocTotal)-isnull(Sum(a.VatSum),0) as Prev_Year_Total,b.Country from OINV a left join OCRD b on a.CardCode=b.CardCode where (CAST(a.DocDate AS DATE) BETWEEN CAST(CAST((Year(GETDATE()) - 1)AS Varchar) + '-01-01' AS DATE) AND DATEADD(YEAR,-1,GETDATE()))and a.CANCELED = 'N' --(Year(a.DocDate)= Year(GETDATE())-1 and MONTH(a.docdate) between 1 and month(getdate())) group by b.Country )f on a.Code=f.Country where a.Code IN('DE','GB','FR','IT') union all select 2 as Row,'Rest' as Name,sum(t0.Today_Total)Today_Total,sum(t0.Month_Total)Month_Total,sum(t0.Current_Year_Total) Current_Year_Total ,sum(t0.Prev_Year_Total)Prev_Year_Total, ((sum(T0.Current_Year_Total)-sum(T0.Prev_Year_Total))/sum(T0.Prev_Year_Total))*100 as 'Perc_Changed' from ( select c.Today_Total,d.Month_Total,e.Current_Year_Total,f.Prev_Year_Total from OCRY a left join ( select SUM(a.DocTotal)-isnull(Sum(a.VatSum),0) as Today_Total,b.Country from OINV a left join OCRD b on a.CardCode=b.CardCode where CAST(a.DocDate AS DATE)=CAST(GETDATE() AS DATE) and a.CANCELED = 'N' group by b.Country )c on a.Code=c.Country left join ( select SUM(a.DocTotal)-isnull(Sum(a.VatSum),0) as Month_Total,b.Country from OINV a left join OCRD b on a.CardCode=b.CardCode where month(a.DocDate)= month(GETDATE()) and Year(a.DocDate)= Year(GETDATE()) and a.CANCELED = 'N' group by b.Country )d on a.Code=d.Country left join ( select SUM(a.DocTotal)-isnull(Sum(a.VatSum),0) as Current_Year_Total,b.Country from OINV a left join OCRD b on a.CardCode=b.CardCode where Year(a.DocDate)= Year(GETDATE())and a.CANCELED = 'N' group by b.Country )e on a.Code=e.Country left join ( select SUM(a.DocTotal)-isnull(Sum(a.VatSum),0) as Prev_Year_Total,b.Country from OINV a left join OCRD b on a.CardCode=b.CardCode where (CAST(a.DocDate AS DATE) BETWEEN CAST(CAST((Year(GETDATE()) - 1)AS Varchar) + '-01-01' AS DATE) AND DATEADD(YEAR,-1,GETDATE()))and a.CANCELED = 'N' group by b.Country )f on a.Code=f.Country where a.Code Not IN ('DE','GB','FR','IT'))as T0)as T1 --order by T1.Row

Thanks

Vinoth

[former_member229757]

Hi

Please guide me in my existing report

Thanks

Vinoth

[former_member185296]

Hi,

Use "union all " sql function to pull data from both A/R invoice and A/R credit memo so the net sales quantity and value will match with sales analysis report

Regards,

vinoth