>>>>I could use some help thinking through how to create a fairly complex report. There are many tanles involved but it basically boild down to a budget and expense report for a collection of projects. Each project has an ID that looks like this. RTA-12, RTA-23, RTA-22a, RTA-22b, etc. The RTA- prefix is uniform with all the projects. The numeric portion of the ID represents a group that the project belongs to. Group 12, Group 22, etc. The parent table in this case identifys the project and there are child tables for the budget and expenses. The budget table is a one to one, and the expense table is a one to many. The report needs to produce a single line that summarizes the total budget and total expenses like this:
>>>>
>>>>Group Budget Expenses
>>>>1 400 250
>>>>22 800 650
>>>>
>>>>I have been able to deal with the expense portion by creating a cursor with a record for each expense and grouping the report on the Project Group. Then using the group footer to subtotal the expenses. But I am not at all clear on how to add the budget information.
>>>>
>>>>Any thoughts would be appreciated.
>>>
>>>If you are "driving" the report from the Expense cursor, you could set relations to the budget table/cursor based on Group. Then in the Group Footer you can reference BudgetTable.BudgetField.
>>>
>>>Another option is to build one cursor that joins the data and just print the data in the detail band.
>>>
>>>

>>>SELECT Group.GroupID, NVL(Budget.BudAmt, 0) AS BudAmt, NVL(ExpSumData.ExpAmt, 0) AS ExpAmt ;
>>>  FROM Group ;
>>>    LEFT JOIN Budget ON Budget.GroupID = Group.GroupID ;
>>>    LEFT JOIN (SELECT GroupID, SUM(ExpAmt) AS ExpAmt FROM Expenses GROUP BY GroupID) ExpSumData ;
>>>        ON ExpSumData.GroupID = Group.GroupID ;
>>>   ORDER BY GroupID
>>>

>>
>>Hi Cathy,
>>
>>Just in case I would add CAST(NVL(Budget.BudAmt, 0) as Numeric(9,2)) as BudAmt, etc.
>
>Good point ... I do this in my apps but I forgot about it when writing up this off-the-cuff example.

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.