>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