I want to have a report of city wise sales.

[former_member392795]

Hi Gurus

I want a report for city wise sales but issue is I cant put cities in Properties as they more then 64.

How can i achieve it?

Please advice a query for this report.

Your Prompt reply is needed.

Regards

Ghufran

[former_member212181]

Hi,

Instead of Business partner property, you can use Bill to City in Address Tab

     or  "Territory" in General Tab of BP Master data.

Then you can write a query.

Thanks

Unnikrishnan

[former_member392795]

what will be the query if i go for territory solution?

[KennedyT21]

You required to create customize query report..

Regards

Kennedy

[former_member392795]

Dear Kennedy what will be the query for that report. will you please guide me.

[KennedyT21]

Try this simple query

SELECT T1.State as [State],

docnum,cardcode,cardname, T0.DocTotal as [DocTotal]

FROM dbo.OINV T0

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

You can develop based on your requirment

Hope helpful

Regards

[former_member212181]

HI Ghufran,

You can use below query for detailed sales analysis- City wise. City is not mandatory in input criteria.

You can modify query as per your requirement

Declare @FDate DateTime, @TDate DateTime, @CardCode Varchar (20), @ItmGrpN Varchar (100),  @ItemCode Varchar (100)--,  @City Varchar (100)

Select @FDate = min(Q.Refdate) from OJDT Q where Q.Refdate >='[%0]'

Select @TDate = max(R.Refdate) from OJDT R where R.Refdate <='[%1]'

Select @CardCode = Min(S.CardCode) from OCRD S where S.CardCode like N'%[%3]%'

Select @ItmGrpN = Min(T.ItmsGrpNam) from OITB T where T.ItmsGrpNam like N'%[%4]%'

Select @ItemCode = Min(U.ItemCode) from OITM U where U.ItemCode like N'%[%5]%'

--Select @FDate, @TDate, @ItmGrpN, @ItemName

Select 'AR Invoice'[Type]

,H.descript  [City]

,case A.DocType When 'I' then 'Item' else 'Service' end[DocType]

,A.DocCur

,A.DocDate

, A.DocNum

,A.CardCode, C.CardName,D.GroupName[BP group]

,B.ItemCode

,Case A.DocType When 'I' then E.ItemName else B.Dscription end [ItemName/Description]

,B.Quantity[Quantity]

,B.Price

,B.Rate

, B.Currency[Price Currency]

,B.LineTotal- (B.LineTotal*isnull(A.DiscPrcnt,0)/100)[LineTotalLC],B.GrssProfit

,B.TotalFrgn- (B.TotalFrgn*isnull(A.DiscPrcnt,0)/100)[LineTotalFC]

,B.GTotalFC

,Case When B.SlpCode<>'-1' then F.SlpName

  When B.SlpCode='-1' and A.SlpCode<>'-1' then (Select M.SlpName from OSLP M where A.SlpCode = M.SlpCode) else '' end [Sales Person]

from OINV A

  Left Outer Join INV1 B on A.Docentry = B.DocEntry

  Left Outer Join OCRD C on A.CardCode = C.CardCode

  Left Outer Join OCRG D on C.GroupCode = D.GroupCode

  left outer join OITM E on B.ItemCode = E.ItemCode

  Left Outer Join OSLP F on B.SlpCode = F.SlpCode

  Left Outer Join OITB G on E.ItmsGrpCod = G.ItmsGrpCod

  Left Outer Join OTER H ON H.territryID = C.Territory

Where A.Canceled = 'N'

  and A.DocDate >=@FDate and A.DocDate  <=@TDate

  and D.GroupName Like N'%[%2]%'

  and C.CardCode  Like '%[%3]%'

  and (G.ItmsGrpNam Like N'%[%4]%' or G.ItmsGrpNam is null)

  and (B.ItemCode Like '%[%5]%' or B.ItemCode is null)

  and isnull(H.[descript],0) Like '%[%6]%'

Union All

Select 'AR Credit Note'[Type]

,H.descript[City]

,case A.DocType When 'I' then 'Item' else 'Service' end[DocType]

,A.DocCur

,A.DocDate

, A.DocNum

,A.CardCode, C.CardName,D.GroupName[BP group]

,B.ItemCode

,Case A.DocType When 'I' then E.ItemName else B.Dscription end [ItemName/Description]

, Case when B.NoInvtryMv ='Y' then 0 else -B.Quantity end [Quantity]

,B.Price

,B.Rate

, B.Currency [Price Currency]

,-B.LineTotal+ (B.LineTotal*isnull(A.DiscPrcnt,0)/100)[LineTotal],-B.GrssProfit

,-B.TotalFrgn+ (B.TotalFrgn*isnull(A.DiscPrcnt,0)/100)[LineTotalFC]

,-B.GTotalFC

,Case When B.SlpCode<>'-1' then F.SlpName

  When B.SlpCode='-1' and A.SlpCode<>'-1' then (Select M.SlpName from OSLP M where A.SlpCode = M.SlpCode) else '' end [Sales Person]

from ORIN A

  Left Outer Join RIN1 B on A.Docentry = B.DocEntry

  Left Outer Join OCRD C on A.CardCode = C.CardCode

  Left Outer Join OCRG D on C.GroupCode = D.GroupCode

  left outer join OITM E on B.ItemCode = E.ItemCode

  Left Outer Join OSLP F on B.SlpCode = F.SlpCode

  Left Outer Join OITB G on E.ItmsGrpCod = G.ItmsGrpCod

  Left Outer Join OTER H ON H.territryID = C.Territory

Where A.Canceled = 'N'

  and A.DocDate >=@FDate and A.DocDate  <=@TDate

  and D.GroupName Like N'%[%2]%'

  and C.CardCode  Like '%[%3]%'

  and (G.ItmsGrpNam Like N'%[%4]%' or G.ItmsGrpNam is null)

  and (B.ItemCode Like '%[%5]%' or B.ItemCode is null)

  and isnull(H.[descript],0) Like '%[%6]%'

Order BY 3

Thanks

Unnikrishnan

[former_member212181]

Hi,

If you need only summary, then you can try below query.

-------------------------------Starts----------------

Declare @FDate DateTime, @TDate DateTime

Select @FDate = min(Q.Refdate) from OJDT Q where Q.Refdate >='[%0]'

Select @TDate = max(R.Refdate) from OJDT R where R.Refdate <='[%1]'

Set @FDate =  '[%0]'

Set @TDate =  '[%1]'

--Select @FDate, @TDate

; WITH  Sales as (

Select 'AR Invoice'[Type]

,H.descript  [City]

,case A.DocType When 'I' then 'Item' else 'Service' end[DocType]

,A.DocCur

,A.DocDate

, A.DocNum

,A.CardCode, C.CardName,D.GroupName[BP group]

,B.ItemCode

,Case A.DocType When 'I' then E.ItemName else B.Dscription end [ItemName/Description]

,B.Quantity[Quantity]

,B.Price

,B.Rate

, B.Currency[Price Currency]

,B.LineTotal- (B.LineTotal*isnull(A.DiscPrcnt,0)/100)[LineTotalLC],B.GrssProfit [GP]

,B.TotalFrgn- (B.TotalFrgn*isnull(A.DiscPrcnt,0)/100)[LineTotalFC]

,B.GTotalFC

,Case When B.SlpCode<>'-1' then F.SlpName

  When B.SlpCode='-1' and A.SlpCode<>'-1' then (Select M.SlpName from OSLP M where A.SlpCode = M.SlpCode) else '' end [Sales Person]

from OINV A

  Left Outer Join INV1 B on A.Docentry = B.DocEntry

  Left Outer Join OCRD C on A.CardCode = C.CardCode

  Left Outer Join OCRG D on C.GroupCode = D.GroupCode

  left outer join OITM E on B.ItemCode = E.ItemCode

  Left Outer Join OSLP F on B.SlpCode = F.SlpCode

  Left Outer Join OITB G on E.ItmsGrpCod = G.ItmsGrpCod

  Left Outer Join OTER H ON H.territryID = C.Territory

Where A.Canceled = 'N'

  and A.DocDate >=@FDate and A.DocDate  <=@TDate

   and isnull(H.[descript],0) Like '%[%6]%'

Union All

Select 'AR Credit Note'[Type]

,H.descript[City]

,case A.DocType When 'I' then 'Item' else 'Service' end[DocType]

,A.DocCur

,A.DocDate

, A.DocNum

,A.CardCode, C.CardName,D.GroupName[BP group]

,B.ItemCode

,Case A.DocType When 'I' then E.ItemName else B.Dscription end [ItemName/Description]

, Case when B.NoInvtryMv ='Y' then 0 else -B.Quantity end [Quantity]

,B.Price

,B.Rate

, B.Currency [Price Currency]

,-B.LineTotal+ (B.LineTotal*isnull(A.DiscPrcnt,0)/100)[LineTotal],-B.GrssProfit [GP]

,-B.TotalFrgn+ (B.TotalFrgn*isnull(A.DiscPrcnt,0)/100)[LineTotalFC]

,-B.GTotalFC

,Case When B.SlpCode<>'-1' then F.SlpName

  When B.SlpCode='-1' and A.SlpCode<>'-1' then (Select M.SlpName from OSLP M where A.SlpCode = M.SlpCode) else '' end [Sales Person]

from ORIN A

  Left Outer Join RIN1 B on A.Docentry = B.DocEntry

  Left Outer Join OCRD C on A.CardCode = C.CardCode

  Left Outer Join OCRG D on C.GroupCode = D.GroupCode

  left outer join OITM E on B.ItemCode = E.ItemCode

  Left Outer Join OSLP F on B.SlpCode = F.SlpCode

  Left Outer Join OITB G on E.ItmsGrpCod = G.ItmsGrpCod

  Left Outer Join OTER H ON H.territryID = C.Territory

Where A.Canceled = 'N'

  and A.DocDate >=@FDate and A.DocDate  <=@TDate

  and isnull(H.[descript],0) Like '%[%6]%'

)

Select @FDate[From Date], @TDate[To Date], P.City, Sum(P.Quantity)[Qty],Sum(P.LineTotalLC)[LineTotalLC], Sum(P.GP)[Gross Profit]  from Sales P

Group By P.City

-----------------------------Ends--------------

Thanks

Unnikrishnan