Adding an External Report

The list of reports displayed can include reports other than those created in Stonefield Query. For example, you may have some existing Visual FoxPro reports you want to run from within Stonefield Query. To add these to the list of available reports, choose New in the Reports Explorer toolbar and when asked for the type of report, choose the External Report Wizard. You can also click the down arrow to the right of the New button in the toolbar and choose External Report from the menu that appears.

Using the External Report Wizard, select the existing report you want added to the list by either typing the path and name of the report or clicking the small button beside the file name and selecting it in the Open File dialog that appears. After you've selected the report file, Stonefield Query reads the report definition to determine which fields appear in the report. If it can't determine the fields used, you are warned that you cannot filter or sort this report in Stonefield Query.

After entering the report file name, enter the same information you would enter when creating a new report in Stonefield Query:

• Report name: the name of the report. If you enter the name of an existing report, an error message displays. You cannot go to the next step until you have entered a valid report name.

• Folder: the folder this report belongs in. The default is the current folder in the Reports Explorer. You can select the desired folder by clicking the down arrow beside the folder name and choosing the folder in the list that appears. To expand a folder, click the + sign in front of the folder name; the + sign changes to a - sign. To collapse a folder, click the - sign in front of the folder name; the - sign changes to a + sign. To select the desired folder, either click it and click the down arrow again to close the list or double-click it.

  

• Comments: any comments entered here appear in the Reports Explorer when the report is selected.

Step 2 allows you to specify certain options for the report.

The options in this step are:

• Allow report to be filtered: turn on this option if someone can specify a filter for the report in the Reports Explorer. This is normally checked, but is unchecked and disabled if Stonefield Query cannot determine which fields appear in the report.

• Allow report to be sorted: turn on this option if someone can specify a sort order for the report in the Reports Explorer. If you grouped on any field, or specified a sort order in the report, you may want to uncheck this option. This option is unchecked and disabled if Stonefield Query cannot determine which fields appear in the report.

• Edit report using Quick Report Wizard: this option, which is only available for Visual FoxPro reports, specifies whether you can edit the report using the Quick Report Wizard. With this option turned off, the report must be edited in Visual FoxPro (choosing Edit for this report brings up the External Report Wizard). With this option turned on, editing the report brings up the Quick Report Wizard, and you can use the Advanced Report Designer to edit the report. Note that turning this option on causes the report to be read into and stored in the Stonefield Query reports file, so making changes to the Visual FoxPro FRX file outside Stonefield Query has no effect on how the report appears within Stonefield Query.

• Create data set for this report: if this option is turned on, Stonefield Query creates a data set for the report to run against. Turn this option off if the report creates its own data set, such as in the DataEnvironment Init or BeforeOpenTables method. See below for some issues regarding this option. , This option is only available for Visual FoxPro reports and only if Stonefield Query can determine which fields appear in the report.

• Use custom SQL statement: this option is only available if Create data set for this report is turned on. Turning this option on means you want a specific SQL statement executed for a report to create the data set, rather than the SQL statement that Stonefield Query normally uses. After turning this on, enter the SQL statement in the text box below this option.
  

The options in Step 3 allow you to determine who can access your report and what they can do with it.

The options in this step are:

• Available groups: this shows which user groups do not have access to this report.

• Selected groups: this shows which user groups have access to this report. By default, the Everyone group has access to a new report, so all users can see it. If you want only certain users to see it, remove the Everyone group from the Selected list and add the appropriate groups from the Available list. If no groups have access to the report, the report is essentially private, available only to you.

• Add: selecting this button adds the selected group in the Available list to the Selected list. You can also add the group by double-clicking it in the Available list or selecting it and pressing Enter. The group is removed from the Available list.

• Add all: selecting this button adds all groups from the Available list to the Selected list.

• Remove: selecting this button removes the selected group from the Selected list. You can also remove the group by selecting it and pressing Enter. The group is added to the Available list.

• Remove all: selecting this button removes all groups from the Selected list, and adds them to the Available list.

• In addition to running the report, users in the selected group may modify or delete this report: turn this option on if the selected group in the Selected list is able to edit or delete this report; if this item is turned off, only you are able to do so.
  

When you are finished, click the Finish button to save the report and add it to the reports list in the Reports Explorer. If you do not want to save the report, click the Cancel button instead.

Visual FoxPro Report Issues

There are several issues specific for Visual FoxPro reports:

• Fields in the report should either be aliased with the table name (for example, CUSTOMER.COMPANY rather than just COMPANY) or you must put *:SFREPORTS EXPRESSION TABLE.FIELDNAME (where TABLE is the name of the table and FIELDNAME is the name of the field; for example, *:SFREPORTS EXPRESSION CUSTOMER.COMPANY) into the Comment for the field in the report. This allows Stonefield Query to determine which fields it must retrieve from the database when running the report. If fields aren't aliased or are aliased with a table name that doesn't exist in the data dictionary, you'll get the warning "Stonefield Query cannot read the fields used in this report. You will not be able to filter or sort this report."
  
  
• The "private data session" setting for the report is ignored if the Create data set for this report option is turned on. Also, any tables or views that appear in the DataEnvironment of the report are removed before it's run (they're removed in a copy of the report that's made just before the report is run so the original isn't altered). Also, any code that creates a cursor in the Init or BeforeOpenTables methods of the DataEnvironment is executed, but the cursor that's used for the report is the one Stonefield Query creates, not the one created by such code.
  
  
• If the Create data set for this report option is turned off, you won't be able to filter and sort the report in Stonefield Query, because the report handles its own data retrieval.