Creating Report of SAP Business One

[former_member264311]

Good Day Experts,

How can I get Sale report total in a Month and per customer, Example.

C.Code     Item1     Item2     Item3

Cus1     20kg     10kg     30kg

Cus2     20kg     10kg     30kg

Thanks

[Former Member · Accepted solution]

Hi,

Check this link

[former_member264311 · Accepted solution]

Hi Can you teach me how to apply that in SAP Business One? I dont know where can I put that code.

[Former Member · Accepted solution]

Hi,

Go to Tools-->Queries-->Query Generator 

Just click execute button , Now you will get error message and delete Select * command and paste your query there and click execute button and fill the parameters, Now you will get your desired results.

[former_member264311 · Accepted solution]

where can I put this??

) src

PIVOT (sum(Sal) for ItmsgrpNam IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

[former_member264311 · Accepted solution]

Select T0.CardName,T4.ItmsGrpNam,sum(-T1.LineTotal) as Sal from

ORIN T0 Inner join RIN1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OFPR T2 on T2.AbsEntry = T0.FinncPriod

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@ORIN+')

group by T0.CardName ,T4.ItmsGrpNam

) src

PIVOT (sum(Sal) for ItmsgrpNam IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

that code I will put in query generator?

[Former Member · Accepted solution]

Hi,

Paste this query

DECLARE @listCol VARCHAR(8000)

DECLARE @Query VARCHAR(8000)

DECLARE @OINV VARCHAR(8000)

DECLARE @ORIN VARCHAR(8000)

DECLARE @From varchar(15)

DECLARE @To varchar(15)

Set @From =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%0]',112)

Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%1]',112)

SET @OINV =  isnull( STUFF((Select  ',' + convert(varchar(10),Docentry) from OINV

  where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)

SET @ORIN =  isnull(STUFF((Select  ',' + convert(varchar(10),Docentry) from ORIN

  where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)

SELECT  @listCol = STUFF(( SELECT DISTINCT '],[' + ltrim((ItmsGrpNam)) from OITB

ORDER BY '],[' + ltrim((ItmsgrpNam)) FOR XML PATH('') ), 1, 2, '') + ']'

SET @Query = 'Select *  from (Select T0.CardName,T4.ItmsGrpNam,sum(T1.Quantity) as Sal from

OINV T0 Inner join INV1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@OINV+')

group by T0.CardName ,T4.ItmsGrpNam

union all

Select T0.CardName,T4.ItmsGrpNam,sum(-T1.Quantity) as Sal from

ORIN T0 Inner join RIN1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@ORIN+')

group by T0.CardName ,T4.ItmsGrpNam

) src

PIVOT (sum(Sal) for ItmsgrpNam IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

[former_member264311 · Accepted solution]

Can I do that in Crystal report? like that details???

Thank you so much

[former_member264311 · Accepted solution]

declare @column as varchar(max)

declare @Query as varchar(max)

set @column= STUFF((Select '],['+whsname from OWHS FOR XML PATH('')),1,2,'') +']'

set @Query='select * from (select w.ItemCode,m.Itemname ,s.WhsName ,sum(w.OnHand) Stock

from oitw w inner join oitm m on m.itemcode=w.itemcode inner join OWHS s on s.WhsCode=w.WhsCode group by w.ItemCode,m.itemname ,s.WhsName) fg

Pivot (Sum(Stock) for

Whsname in ('+@column+')) AS pvtb'

EXECUTE (@Query)

what is "pvtb"????

[former_member264311 · Accepted solution]

Do you have per Item not itemgroup?

Regards.

[Former Member · Accepted solution]

Yes you can do it in crystal report.If you are going to crystal report then use cross tab report.

[Former Member · Accepted solution]

Its just  column name . You can give any name in that place.

[Former Member · Accepted solution]

How many item master data records you have ?

[former_member264311 · Accepted solution]

I want all item I sold will display.. not all items in my masterdata

[Former Member · Accepted solution]

Try this,

DECLARE @listCol VARCHAR(8000)

DECLARE @Query VARCHAR(8000)

DECLARE @OINV VARCHAR(8000)

DECLARE @ORIN VARCHAR(8000)

DECLARE @From varchar(15)

DECLARE @To varchar(15)

Set @From =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%0]',112)

Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%1]',112)

SET @OINV =  isnull( STUFF((Select  ',' + convert(varchar(10),Docentry) from OINV

  where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)

SET @ORIN =  isnull(STUFF((Select  ',' + convert(varchar(10),Docentry) from ORIN

  where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)

SELECT  @listCol = STUFF(( SELECT DISTINCT '],[' + ltrim((t1.ItemCode)) from OINV T0 Inner join INV1 T1 on T0.DocEntry = T1.DocEntry

where t0.DocType='I' ORDER BY '],[' + ltrim((t1.ItemCode)) FOR XML PATH('') ), 1, 2, '') + ']'

SET @Query = 'Select *  from (Select T0.CardName,T1.Itemcode,sum(T1.Quantity) as Sal from

OINV T0 Inner join INV1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@OINV+')

group by T0.CardName ,T1.Itemcode

union all

Select T0.CardName,T1.Itemcode,sum(-T1.Quantity) as Sal from

ORIN T0 Inner join RIN1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@ORIN+')

group by T0.CardName ,T1.Itemcode

) src

PIVOT (sum(Sal) for Itemcode IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

[former_member264311 · Accepted solution]

Thank you so much,

Last question can we hide item column if 0 line total?

[Former Member · Accepted solution]

No we can't do it

[frank_wang6]

How many items do u have?

If it is not too many, u can use CTE to get sum result, and then pivot the result to achieve this.

Frank