State Wise Sales Analysis

[Former Member]

Dear,

how can i manage State Wise Sales Analysis.

System Sales Analysis give me output for customers, Items.

But how can i view State Wise?

Thanks & Regards,

nikunj Mehta

[kothandaraman_nagarajan]

Hi,

You have to create customized query to get such report. Please refer below thread,

Re:Query for Statewise Sales Analysis Report in... | SCN

Thanks

[Former Member]

Dear Nagarajan,

I refer that thread.

But it remain Same. Instead of State Code I have to display State Name.

Thanks & Regards,

Nikunj Mehta

[Former Member]

Hi Nikunj,

You can achieve this through Query. You need to create Sales analysis query and set parameter by State.

Thanks,

Harshal

[Former Member]

Dear Harshal,

i generate this query:

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, T0.DocTotal, MONTH(T0.docdate) 'month' FROM dbo.OINV T0

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

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

UNION

SELECT T1.State, T0.DocTotal, MONTH(T0.docdate) 'month' FROM dbo.ORIN T0

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

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

PIVOT  (SUM(DocTotal) FOR [MONTH] IN

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

ORDER BY P.[State]

It give me output like:

State    Jan Feb

GJ     100     500

PN     0       200

but how can i view it like name wise instead of State Code:

Like

State     Jan    Feb

Gujarat   100    500

Pune   0        200

Thanks & Regards,

Nikunj Mehta

[Former Member]

Hi Nikunj,

You need to use case statement to convert all your state code to state name.

Thanks,

Harshal

[Former Member]

Dear Harshal,

I have no idea on it that how to use Case Statement for converting State Code To State Name.

Thanks & regards,

Nikunj Mehta