>>Thanks Cathy and Naomi. My understanding of SQL select continues to grow. As an aside, I understand from another thread that it is unwise to use a UDF in a select. In this example the only way to assemble the budget is with a UDF because it is spread over 60+ fields, each of which must be tested to make sure it meets certain criteria before it is added to the total. (This is the result of poor table design that originated 15+ years ago.) Nevertheless I have a function that iterates the fields and sums up the budget. So, given this, would it be best to create an empty field with the select and then run the function afterward? That's the only way I can think of except using the function directly in the SELECT but I was advised against this.
>
>
>Hi Don :)
>
>Since you already have some procedures/functions which already calculate some values you need,
>I can propose you another aproach;
>You can actually reuse them by enclosing them into an object based on datasession which would simply return value
>you need to show on report.
>
>So instead of building complicated SQL statements and/or complicated FRX you can build your report on
>a simple temporary cursor which contain all data needed for report, and it is suitable for presenting via FRX.
>
>Idea is folowing;
>
>At first you create an empty temporary cursors with all fields you need to show on report
>If driving alias will be projects then you build it to the same field structure as projects table
>
>

>select projects &&Table which holds projects codes etc
>        local aStru(1)
>        afields(aStru)
>        create cursor rProjects from array aStru
>        alter table alias() add column income N(12,2)   **Add other fields you need
>        alter table alias() add column expences N(12,2)
>        alter table alias() add column result N(12,2)
>        index on projcode tag tag1
>        set order to projectcode tag1
>

>
>You can populate it with dummy values just to build FRX layout (tip: you can copy it on hard disc and use
>to build report by builder or drag & drop)
>
>Once you have layout you want, you are building supporting code to fill it up with real values at run time.
>Therefore; you create object (class) based on session object
>
>

>
>define class  ProjectIncomeAndExpencesServer  as session
>
>procedure init
>this.OPenTables()
>
>procedure OpenTables
>* copy paste opening and relation of tables that existing function (or new methods inhere) uses,
>open table projexpences in 0 shared
>open table IncomeDetails in 0 shared
>open table budgets ...
>set relation to ...
>endproc
>
>
>
>**Now claculation methods/functions which return single value as result
>**Here you reuse whatever you can from old code
>
>**First Income;
>
>function CalculateIncome && new or existing code which calculate income figure per project
>lParameters cProjectCode
>local nIncome
>.
>select IncomeDetails
>=Seek(cProjectCode)
>scan while IncomeDetails.ProjectCode=cProjectCode
>.
>*Calculations
>.
>endscan
>
>return nIncome
>
>
>**Then expences side
>**Calculate sum of all expences for specific project code
>
>Function CalculateExpences && new or existing code which calculate expences figure
>lParameters cProjectCode
>.
>A+B+C-D=nExpences
>.
>
>return nExpences
>
>
>enddefine
><pre/>
>
>So now you run all this in folowing way
>
><pre>
>
>**Insert here tmp cursor creation code from above
>
>local oBalances
>oBalances=createobject('ProjectIncomeAndExpencesServer')
>
>select projects
>
>Scan for &cSomeCriteria   &&You can rise ranges form before this to establish some report criteria
>
>   scatter memvar memo
>
>   m.income  = oBalances.CalculateIncome(projects.ProjCode)  &&Pass project code / get result figure
>   m.Expences= oBalances.CalculateExpences(projects.ProjCode)  &&Pass project code / get result figure
>
>   m.result=m.Income-m.Expences
>
>   insert into rProjects from memvar
>
>
>endscan
>
>oBalances = null  &&Releases supporting session object (private sessions automatically closes all open tables)
>
>
>So you are left with cursor which have all results you need
>
>select rProject
>*browse normal  &&At design time to verify results you are getting
>go top
>report form myReport to printer preview
>
>select rProjects
>use &&relese temporary cursor as well
>
>

>
>Make sure that report runs in default datasession and that DE object does not open any tables.
>Report will use rProject cursor which you created right before execution
>
>Now if you manage to assemble cursor like this in a series of SQL statements, then forget all this
>because it will be obviosely much less code then this, but if calculations are to complex and
>you don't have SQL idea that will yield resulting cursor you want, then this aproach offers quiet a bit of flexibility.
>All complexity is basically moved from FRX (and SQL area) into procedural code within session object
>Session object is environmentaly friendly as well, as it automatically closes all his tables when released.
>
>
>BTW, Have look at
>
>www.report-sculptor.com
>
>which can help you create some very complex report layouts freely in code and/or using form objects, text files etc.
>
>HTH
>Sergio

Thanks Sergio. I was able to build it using a combination of SELECT followed by a SCAN loop to populate it with the additional values I couldn't get with the SELECT. The SCAN uses the UDFs so they are outside of the SELECT. Most of the calcs are removed from the frx. I agree its simpler that way.