Profit Centre Report by Job Wise

[Former Member]

Hi Experts

I would like to know whether we can generate Profit Centre Report by "Job Wise". We are using SAP B1 8.8 PATCH 10

REGARDS

KARTHIK

[Former Member · Accepted solution]

Hi Karthik,

     Try this,,,,

Declare @FromDate as Datetime

Declare @ToDate as Datetime

Declare @From as nVarchar(8)

Declare @To as nVarchar(8)

Set @FromDate = (Select min(S0.RefDate) from OJDT s0 where S0.RefDate >='[%0]')

Set @ToDate = (Select max(S1.RefDate) from OJDT s1 where S1.RefDate <='[%1]')

Set @From = (Select MAX(S2.PrJCode) from OPRJ S2 Where S2.PrJCode ='[%2]')

Set @To = (Select MAX(S2.PrJCode) from OPRJ S2 Where S2.PrJCode ='[%3]')

--Select * from OPRC

Select B.[PROJECT Code] , B.[PROJECT Name] ,

sum(B.Expense) [Expense] , -1 * Sum(B.Revenue) [Revenue] ,

-1 * Sum(B.Revenue)- sum(B.Expense) [ Total]

From (

Select

A.[PROJECT Code] , A.[PROJECT Name],

CASE A.Type

When 'E' then A.Total

End as [Expense],

Case A.Type

When 'I' then A.Total

End as [Revenue]

From (

Select J1.PROJECT [PROJECT Code],

(Select MAX(M0.PrJName) from OPRJ M0 Where M0.PrJCode = J1.PROJECT) [PROJECT Name],

A0.ActType  as [Type],

SUM(J1.Debit) - SUM(J1.Credit) [Total]

from OJDT J0,JDT1 J1,OACT A0

Where

J0.TransId = J1.TransId and

J1.PROJECT >=@From and J1.PROJECT <=@To and

J0.RefDate >=@FromDate and J0.RefDate <=@ToDate and

A0.AcctCode = J1.Account

Group By

J1.OcrCode2,A0.ActType ) A ) B

Group By

B.[PROJECT Code] , B.[PROJECT Name]

Regards,

Priya

[Former Member · Accepted solution]

Priya

Thanks a lot for your response.

When i execute this query it gives the following error

"column jd1.project is invalid in the select list because it is not contained either in the aggregate function or the group clause"

Can u pls guide me where the error will be??

regards

karthik

[Former Member · Accepted solution]

Hi,

   Now try this....

Declare @FromDate as Datetime

Declare @ToDate as Datetime

Declare @From as nVarchar(8)

Declare @To as nVarchar(8)

Set @FromDate = (Select min(S0.RefDate) from OJDT s0 where S0.RefDate >='[%0]')

Set @ToDate = (Select max(S1.RefDate) from OJDT s1 where S1.RefDate <='[%1]')

Set @From = (Select MAX(S2.PrJCode) from OPRJ S2 Where S2.PrJCode ='[%2]')

Set @To = (Select MAX(S2.PrJCode) from OPRJ S2 Where S2.PrJCode ='[%3]')

--Select * from OPRC

Select B.[PROJECT Code] , B.[PROJECT Name] ,

sum(B.Expense) [Expense] , -1 * Sum(B.Revenue) [Revenue] ,

-1 * Sum(B.Revenue)- sum(B.Expense) [ Total]

From (

Select

A.[PROJECT Code] , A.[PROJECT Name],

CASE A.Type

When 'E' then A.Total

End as [Expense],

Case A.Type

When 'I' then A.Total

End as [Revenue]

From (

Select J1.PROJECT [PROJECT Code],

(Select MAX(M0.PrJName) from OPRJ M0 Where M0.PrJCode = J1.PROJECT) [PROJECT Name],

A0.ActType  as [Type],

SUM(J1.Debit) - SUM(J1.Credit) [Total]

from OJDT J0,JDT1 J1,OACT A0

Where

J0.TransId = J1.TransId and

J1.PROJECT >=@From and J1.PROJECT <=@To and

J0.RefDate >=@FromDate and J0.RefDate <=@ToDate and

A0.AcctCode = J1.Account

Group By

J1.project,A0.ActType ) A ) B

Group By

B.[PROJECT Code] , B.[PROJECT Name]

Regards,

Priya

[Former Member · Accepted solution]

Priya

Thanks a Lot. Now i get the results.

regards

Karthik

[Former Member · Accepted solution]

Hi Karthik,

        Then close the thread with correct or Helpful answer.

Regards,

Priya

[Former Member]

Hi,

Create Project as a Profit Centre (Dimension) then you can able to take the report.

[Former Member]

Hi Karthik,

When you say "Job wise", do you refer to Project? If not, do you have any add-on running?

Thanks,

Gordon

[Former Member]

Gardon

Yes. It refers to Job Wise only

regards

karthik

[Former Member]

Gardon

Sorry. It refers to Project Wise only.

regards

karthik