Sales by State report

[Former Member]

Hi

Wondering if anyone can help me with a query to extract sales information from B1. I'm trying to build a report to show all sales by state in SAP Business One. I have come across this code and while it is close to what I need it doesn't go the whole way.

Along with the sales from each state I need to include the wareshouses that stock was sold from. I also need to sum each column (I know that you can press Ctrl and Click to get totals at the bottom of each column but would like them to appear automatically on the final report). Can anyone help?

Here is the query so far:

SELECT T0.State1 AS 'Bill-to State',

(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)

INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

WHERE MONTH(T1.DOCDATE) = 1 AND T2.State1 = T0.State1

AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN Amt',

(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)

INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

WHERE MONTH(T1.DOCDATE) = 2 AND T2.State1 = T0.State1

AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB Amt',

(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)

INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

WHERE MONTH(T1.DOCDATE) = 3 AND T2.State1 = T0.State1

AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR Amt',

(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)

INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

WHERE MONTH(T1.DOCDATE) = 4 AND T2.State1 = T0.State1

AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR Amt',

(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)

INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

WHERE MONTH(T1.DOCDATE) = 5 AND T2.State1 = T0.State1

AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY Amt',

(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)

INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

WHERE MONTH(T1.DOCDATE) = 6 AND T2.State1 = T0.State1

AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN Amt',

(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)

INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

WHERE MONTH(T1.DOCDATE) = 7 AND T2.State1 = T0.State1

AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUL Amt',

(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)

INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

WHERE MONTH(T1.DOCDATE) = 8 AND T2.State1 = T0.State1

AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'AUG Amt',

(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)

INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

WHERE MONTH(T1.DOCDATE) = 9 AND T2.State1 = T0.State1

AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'SEP Amt',

(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)

INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

WHERE MONTH(T1.DOCDATE) = 10 AND T2.State1 = T0.State1

AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT Amt',

(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)

INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

WHERE MONTH(T1.DOCDATE) = 11 AND T2.State1 = T0.State1

AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV Amt',

(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)

INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

WHERE MONTH(T1.DOCDATE) = 12 AND T2.State1 = T0.State1

AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'DEC Amt'

FROM dbo.OCRD T0

LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode

GROUP BY T0.State1

ORDER BY T0.State1

Thanks in advance

Cathal

[kothandaraman_nagarajan]

Hi,

Try this query:

Select [S] as State, [W] as warshouse,[Jan]= sum([1]), [Feb]= sum([2]), [Mar]= sum([3]), [Apr]= sum([4]),  [May]= sum([5]), [June]= sum([6]), [July]= sum([7]), [Aug]= sum([8]), [Sept]= sum([9]), [Oct]= sum([10]), [Nov]= sum([11]), [Dec]= sum([12]), total = sum (isnull([1],0)+ isnull([2],0) + isnull([3],0) + isnull([4],0) +  isnull([5],0) + isnull([6],0) + isnull([7],0) + isnull([8],0) + isnull([9],0)+ isnull([10],0) + isnull([11],0) + isnull([12],0))

from(

SELECT T0.[State1] as S, T2.[WhsCode] as W,month(T1.[DocDate]) as month, sum(T1.[DocTotal]) as T FROM OCRD T0  INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode INNER JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry WHERE year(T1.[DocDate]) = year(getdate()) GROUP BY T0.[State1], T2.[WhsCode],T1.[DocDate] ) s

Pivot
(sum(t) for month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

group by [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[S],[W]

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan

Thanks for the response.

I tired to run the script you sent to me but got the following error:

"Msg 325, Level 15, State 1, Line 22

Incorrect syntax near 'Pivot'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE."

I looked to see what the compatibility options were on the SQL Server 2008 and they only go to 100. I'm guessing that this code would work with the right compatibility version but I can't change it.

Do you know of another way?

Many thanks

Cathal

[kothandaraman_nagarajan]

Hi,

Tested in 8.82 PL04 and there is no such error message and also tested in SQL server management studio, there is no error message.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan

I am running 8.82 PL14 and the same error appears if I try to run it within B1 and also from SQL Server 2008.

Thanks

Cathal

[kothandaraman_nagarajan]

Hi,

Please post screen shot of above error message.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan

I have attached 2 screen shots showing the error that I get in both B1 and SQL Server.

Thanks

Cathal

[kothandaraman_nagarajan]

Hi,

Please advice SQL server version. I am using SQL server 2008 R2.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan

I'm using the same SQL version i.e. 2008 R2.

Thanks

Cathal

[kothandaraman_nagarajan]

Hi,

Sorry. My one SQL server 2012.

I read some help files to set comparability level for R2 is 100.

View or Change the Compatibility Level of a Database

Thanks & Regards,

Nagarajan

[Former Member]

Hi

That seems to work now.

Do you know if there are any issues associated with changing the compatibility version of a database? I'm not a dba so don't want to cause problems. I have tried changing the levels on a test db and it seems fine but would like to know what else to look for.

Thanks

Cathal

[Former Member]

Hi

Changed the compatibility to 100 and ran the script. It runs but I noticed that the figures that are pulled out are wrong when compared to what I was working with (script in origianl post). Do you know why this may be?

E.g. Original script - sales from AK = $684.58, new script from AK = $10,268 (see image attached)

Thanks

Cathal

[kothandaraman_nagarajan]

Hi,

Need to check the effect of changing level. I am also not DBA.

Is this query giving expected result?

Thanks & Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

I will check and reply by tomorrow.

Thanks & Regards,

Nagarajan