cancel
Showing results for 
Search instead for 
Did you mean: 

FORMATTED SEARCH FOR A SEGMENTED CHART OF ACCOUNTS

Former Member
0 Kudos

I'm using SBO 9.1 PL4 and a segmented chart.

I have added a udf field in my marketing document on which i want to populate the available balance using formatted search and the following query

SELECT SUM(T0.DebLTotal - T0.DebRLTotal - T0.FtrODRLSum ) AS 'Budget Balance'

FROM OBGT T0 WHERE T0.AcctCode = $[$39.94.0]

GROUP BY T0.AcctCode

The query does not pick the budget amounts as the code does not pick '-SYS......' codes.

Kindly help me amend it so that it picks the '_SYS.....' codes

I have attached screen shot of both segmented chart screen and normal chart screen

You assistance is highly appreciated

Regards

George Njuguna

Accepted Solutions (1)

Accepted Solutions (1)

former_member184146
Active Contributor
0 Kudos

Hi George

try the below FMS

SELECT SUM(T0.DebLTotal - T0.DebRLTotal - T0.FtrODRLSum ) AS 'Budget Balance'

FROM OBGT T0 WHERE T0.AcctCode = $[POR1.ACCTCODE]

GROUP BY T0.AcctCode

--Manish

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi George,

You may try this first:

SELECT SUM(T0.DebLTotal - T0.DebRLTotal - T0.FtrODRLSum ) AS 'Budget Balance'

FROM OBGT T0

JOIN OACT T1 ON T1.AcctCode=T0.AcctCode WHERE T1.FormatCode = $[$39.94.0]

GROUP BY T0.AcctCode

Thanks,

Gordon

Former Member
0 Kudos

Thank you Gordon. The new query you proposed is still not picking and populating the budget amount.

Further assistance is a highly appreciated.

Regards

George Njuguna

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query:

SELECT SUM(T0.DebLTotal - T0.DebRLTotal - T0.FtrODRLSum ) AS 'Budget Balance'

FROM OBGT T0

JOIN OACT T1 ON T1.AcctCode=T0.AcctCode WHERE T1.FormatCode = $[$39.94.Number]

GROUP BY T0.AcctCode

OR

SELECT SUM(T0.DebLTotal - T0.DebRLTotal - T0.FtrODRLSum ) AS 'Budget Balance'

FROM OBGT T0 WHERE T0.AcctCode = $[$39.94.Number]

GROUP BY T0.AcctCode

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Thank you Nagarajan. I'm getting an error with each of the two options you suggested. I have attached screenshot for the errors (named based on the unique parameters in the respective queries).

Further assistance is highly appreciated.

Regards

George Njuguna