Sure:
You can find more information on user defined functions in the help file under "How To->Creating and Using User defined functions", or view it online at:
http://www.stonefieldquery.com/OnlineHelp/GoldMine/_0OQ0RLAG4.htm
If you decide to go the multi detail band route, here is a guide written by one of our developers on how to do this, with some comments thrown in by me:
To create a report with a parent and multiple unrelated children in Stonefield Query, do the following:
1. Ensure the User Can Edit SQL SELECT configuration setting is turned on in the Configuration Utility (ignore this step)
2. In Stonefield Query, create a new Quick Report
3. In Step 2, select only fields from the parent table, and group on one of the fields (for example, Customer Name). Be sure to include the primary key field (for example, Customer ID) if it's reportable; if not, you can add it in the Advanced Report Designer as described later. The reason this field is required is to match up with child table records.
4. In Step 2, click the Advanced button and go to Step 6 (AfterData code). Enter code similar to the following in the edit box:
lcAlias = alias()
loDatabase = SQApplication.DataEngine.Databases.GetMainDatabase()
lcResults = loDatabase.ExecuteSQLStatement('select * from FIRST_CHILD_TABLE', , 'Child1')
if not empty(lcResults)
index on FIRST_CHILD_FOREIGN_KEY_FIELD tag Child1
lcResults = loDatabase.ExecuteSQLStatement('select * from SECOND_CHILD_TABLE', , 'Child2')
if not empty(lcResults)
index on SECOND_CHILD_FOREIGN_KEY_FIELD tag Child2
select (lcAlias)
set relation to PRIMARY_KEY_FIELD into Child1, PRIMARY_KEY_FIELD into Child2
endif
endif
select (lcAlias)
This code creates data sets from the two child tables and creates a relationship from the parent data set into these two data sets. Thus there are three data sets loaded: one that Stonefield Query created from the parent table and the two this code created from the child tables.
In this code, substitute the name of the first child table for FIRST_CHILD_TABLE, the name of the second child table for SECOND_CHILD_TABLE, the name of the foreign key for the first child table into the parent table for FIRST_CHILD_FOREIGN_KEY_FIELD, the name of the foreign key for the second child table into the parent table for SECOND_CHILD_FOREIGN_KEY_FIELD, and the name of the primary key field in the parent table for PRIMARY_KEY_FIELD. Here's an example using Customers as the parent table and Orders and Contacts as the child tables:
lcAlias = alias()
loDatabase = SQApplication.DataEngine.Databases.GetMainDatabase()
lcResults = loDatabase.ExecuteSQLStatement('select * from Orders', , 'Child1')
if not empty(lcResults)
index on CustomerID tag Child1
lcResults = loDatabase.ExecuteSQLStatement('select * from Contacts', , 'Child2')
if not empty(lcResults)
index on CustomerID tag Child2
select (lcAlias)
set relation to CustomerID into Child1, CustomerID into Child2
endif
endif
select (lcAlias)
5. Select Step 4 (AfterRun code) and enter code similar to the following in the edit box:
use in select('Child1')
use in select('Child2')
This code closes the two child data sets after the report has been completed. Omitting this code will cause an error if you run the report a second time since the AfterData code will try to create child data sets but those data sets are already open from the previous run.
6. Close the Customize Report Wizard. In Step 5 of the Quick Report Wizard, turn on Advanced Layout and click Edit.
7. In the Advanced Report Designer, if the primary key field from the parent isn't already in the report, add a field in the Group Header band with ParentTable.PrimaryKeyField (for example, Customers.CustomerID) as the expression. Regardless of whether you selected the field in the Quick Report Wizard or added it now, if you don't want this field displayed in the report, set its Print When expression to .F.
8. Choose Optional Bands from the Report menu and click Add to create a second detail band. Click OK.
9. Double-click the Detail 1 band bar and enter "Child1" (including the quotes) as the Target Alias Expression. Also, if you want to put column heading for the second detail band below the first, turn on the Associated Header and Footer Bands setting. Click OK.
10. Double-click the Detail 2 band bar and enter "Child2" (including the quotes) as the Target Alias Expression. Click OK.
11. Add the desired fields from the first child table to the Detail 1 band, ensuring they are aliased with Child1 (for example, use Child1.OrderID).
12. Add the desired fields from the second child table to the Detail 2 band, ensuring they are aliased with Child2 (for example, use Child2.ContactName).
13. If desired, add column headings for the Detail 1 band fields to the Group Footer band and column heading for the Detail 2 band fields to the Detail Footer 1 band.
14. Save the report.
|