cancel
Showing results for 
Search instead for 
Did you mean: 

Re:Query for Statewise Sales Analysis Report involving two years..!!

nvs_revathy
Participant
0 Kudos

Dear  Members,

I need query for sales analysis report that involves two years.As Our company fiscal year starts from April to March(i.e.,April 2011-March2012).I need query for state wise sales analysis report from march to april rather than January to December.It is prefer to have a query than that of std reports available in SAP.

With Regards,

Revathy

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Revathy,

Try:

SELECT P.[STATE],

  [4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec],

[1] as [Jan],

[2] as [Feb],

[3] as [Mar]

FROM (SELECT T1.State as [State],

T0.DocTotal as [DocTotal],

MONTH(T0.docdate) as [month]

FROM dbo.OINV T0

INNER JOIN dbo.INV12 T1 ON T1.DocEntry=T0.DocEntry

WHERE T0.docdate BETWEEN '04/01/2011' AND '03/31/2011'

UNION

SELECT 1.State as [State],

-T0.DocTotal as [DocTotal],

MONTH(T0.docdate) as [month]

FROM dbo.ORIN T0

INNER JOIN dbo.RIN12 T1 ON T1.DocEntry=T0.DocEntry

WHERE T0.docdate BETWEEN '04/01/2011' AND '03/31/2011')  S

  PIVOT  (SUM(DocTotal) FOR [month] IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

ORDER BY P.[State]

Thanks, Gordon

nvs_revathy
Participant
0 Kudos

Hi Gordon,

Thanks for your query.It works fine.Whether it is possible to have total as another column which will sum the value of a each cell in corresponding month.

I.e..,

State     Apr     May     Jun     Total

AP          10     20          30     60

It is possible in the same query itself..?

With Regards,

Revathy

Former Member
0 Kudos

Yes. You can add columns such as [4]+[5]+[6] 'Q1 Total' in between [6] and [7]

nvs_revathy
Participant
0 Kudos

Hi Gordon,

Thanks for your Query.It is working well.

With Regards,

Revathy

0 Kudos

This query is not working in my setup. please help me out.

regards,

nadia

Answers (1)

Answers (1)

jitin_chawla
Advisor
Advisor
0 Kudos

Hi,

Check this for a start and see if you can modify and bring it to ur requirement:

http://scn.sap.com/thread/1897769

Kind Regards,

Jitin

SAP Business One Forum Team