Stonefield Query for HEAT Version 3.3

We released version 3.3 of Stonefield Query for HEAT today. The list of new and enhanced features in version 3.3 is extensive; see the online help for complete details. Here are the major new features.

Report Designer

• The new Formula Editor allows you to create your own formulas without using the Advanced Report Designer or customizing the data dictionary.
• The new Show percentage of total option in the Format page of the Field Properties dialog automatically adds a new column to the report showing the percentage that the field is of the subtotal in detail lines, of the next level of grouping in subgroup footers, and of the grand total for group footers.
• Turn on the new Show totals in group headers option in Step 5 of the Quick Report Wizard and the Options page of the Reports Explorer to display group totals in the group header rather than the group footer.
• Stonefield Query now supports Crystal Reports reports. You can add them to Stonefield Query using the External Report Wizard.
• The Preview window is no longer modal, meaning you can open multiple instances. Also, the new Preview reports in tabbed pages setting in the Options dialog allows you to determine whether different preview windows are used when you run more than one report or whether the reports appear as different tabs in the same preview window.
• There are several new output options available. You can specify that a timestamp is added to the file created to prevent overwriting existing files, that the file emailed to someone shouldn't be deleted after the email is sent, and what filename to use for the emailed file.
• Previous versions of Stonefield Query create a report as a PDF file when emailing the report to someone. You can now specify the type of file used as an attachment.
• You can now specify that each group in a report should be output to a separate file or email and that the email address should come from a field in the report itself. This can be used, for example, to email invoices to customers; the report is grouped by customer name and includes the customer's email address, so each customer's records are emailed to them.
• You can now sort fields in database order or alphabetical order in Step 2 of the report wizards.
• You can now tell Stonefield Query to display in the Tables drop-down list only those tables related to tables already appearing in the selected list.
• Reports are now stored as export files with an SFX extension in a folder structure that exactly matches the folders in the Reports Explorer.
• The new Send summary queries to database engine setting in the Options dialog allows you to specify whether Stonefield Query or the database engine does data summarization. This may significantly improve the performance of certain types of reports.
• The new Run report with no records option in Step 5 of the report wizards and the Options page in the Reports Explorer allows you to run a report when there are no records that match your conditions.
  
  
• You can now report on HEAT "First Level Support" tables.
• Added new "Latest Group Assigned" field to the "Call Log" table that will give you the name of the most recent Group that a Call has been assigned to. This is available so that you do not need to include the "Assignments" table in the report as this would give you multiple records for each Call, one for each assignment that the Call has.
• Added a new "Latest Assignee" field to the "Call Log" table that tells you who the latest assignee is for that particular call.
• Table captions are now of the form: TABLEDESC[TABLETYPE]. Where TABLEDESC is the table caption from the HEAT database and TABLETYPE is the table type (both set by the HEAT administrator). This is to avoid the confusing issue of table listings that have the exact same caption for different tables. You can further narrow down the list of available tables by turning on the "Exclude Supplementary Tables" option in the Tools->Options menu.

Web-Based Technical Support

Due to an excessive amount of spam (as much as 2500 per day), we're no longer using support@stonefieldquery.com for technical support.

Instead, we now offer Web-based technical support to all of our customers. This service is included with your Software Maintenance and will allow us to track and respond to your Stonefield Query issues in a timely and efficient manner.

You can submit a Technical Support Ticket at our Online Support Center (http://www.stonefieldquery.com/Support). Alternatively, you can post messages in our Support Forums(http://www.stonefieldquery.com/Support/SupportForums.aspx).

Please note that you must register on our site (registration is free) in order to submit or review your technical support issues. If you are already a member of our site, please sign-in to submit a support ticket.

Antivirus Software Causing Errors in Stonefield Query

We've received several error reports from customers over the past couple of days about errors when running reports in Stonefield Query. The most common error is "Cannot load XFRXLIB.FLL". It turns out that a recent change in the definition files for the Trend Micro anti-virus software was erroneously flagging one of our DLLs, HNDLIB.DLL, as a suspect file and quarantining it. Since XFRXLIB.FLL relies on that DLL, it fails to load so Stonefield Query can't run reports.

Apparently Trend Micro has fixed the problem, so updating to the latest definition files should resolve the problem, although you'll have to manually remove HNDLIB.DLL from quarantine.

Even if you're not using Trend Micro, it's likely a good idea to configure your anti-virus software to not scan the Stonefield Query program folder.

Update: see http://esupport.trendmicro.com/support/viewxml.do?ContentID=EN-1036613&id=EN-1036613 for instructions on configuring Trend Micro to allow or block specific programs.

Stonefield Query 3.2 in Beta

Stonefield Query 3.2 is now in beta. Here are some additional changes we made this week:

• The Desktop Image configuration property was removed since Stonefield Query no longer has a background window to display it in.
• The SQApplication object has a new CanEditReports property you can set to False to prevent the user from creating or editing reports under certain conditions (for example, perhaps demo users should only run existing reports).
• The Import Report function now handles invalid files better.
• You now get a warning message if you open a report in the Advanced Report Designer that has an image file that no longer exists or has been moved to a different folder. The image is replaced with a placeholder that you can edit to correct the file location.
• The Configuration Utility no longer terminates after you've activated it.
• Changing the connection settings for a database from ODBC to VFP no longer gives a Locate DBCXREG dialog when you refresh the database.
• The properties page for a database in the Configuration Utility now includes settings for Add All Tables to the Data Dictionary, Include Views, and Prompt for Subtables. Formerly, these settings were only available in the Add Database dialog, making it difficult to use different settings when you refresh the database.
• Including two enumerated fields with the same name (for example, RECTYPE from two different tables) in a report no longer displays blank values for the second field.

The Latest Build

We added several new features and fixed a number of bugs in the last three weeks.

• The Cross-Tab, Label, and Chart wizards now have an Advanced button that brings up the same dialog available in the Quick Report Wizard to specify TOP value, customize the SQL statement for a report, or specify code to execute at certain places for the report.
• You can now specify that a field should go into the group header for another field. For example, suppose you group on City from the Customers table but want to show the Region in the group header as well. If you turn on the Include all fields in same table in group header option in the Group page in the Field Properties dialog, all fields in the Customers table will appear in the group footer, which isn't what you want. Instead, turn the new Include this field in the group header for setting on and select City from the drop-down list to place just City and Region in the group header. Since all fields that appear in group headers now appear in summary reports, Region will no longer be excluded from such a report.
• The Options dialog has a new Locations page where you can easily change the location where reports are stored.
• The Setup dialog now prompts you for your email settings. These are needed to email reports. However, many people don't realize they need to set these settings in the Options dialog so they can't email reports.
• You can now select specific reports to export or export all or some of the reports in a specific folder. The Export All Reports function in the File menu was renamed to Export Reports; selecting it now displays a dialog from which you can select which specific reports or folders to export. The Export Report function in the File menu was renamed to Export Selected Report.
• You can now edit the security properties for a folder by selecting the folder and clicking Edit in the Reports Explorer.
• The Information page in the Reports Explorer now show what groups the selected report or folder is available to.
• Importing a report that uses a data group that doesn't exist any more (for example, it was renamed) no longer makes the report invisible but instead resets the data group to All.
• Renaming a user in the Maintain Users and Groups dialog automatically moves all their reports to the new name.
• The Commonly Used Expression drop-down in the Expression Builder now only shows common expressions that match the data type of the expression being built. For example, the common date expressions now only appear for date expressions. Also, a new First Day of Previous Month expression was added.
• A splash screen can now appear when Stonefield Query starts up if you create an image named SPLASH.BMP, JPG, JPEG, or PNG that you want displayed in the splash screen.
• You can now distribute customized Standard and Elegant templates in the Sample Templates folder. Previously, the internal versions of these templates were always used.
• Returning False from the DataEngine.GetCustomMetaData script now terminates Stonefield Query so you can close the application if you detect a problem in such a script.
• Grand totals at the end of the report no longer appear on a second line if they will fit on the first one.
• The Generate Setup function in the Configuration Utility no longer gives an "Inno Setup is not installed" error if you installed only the Inno Quick Start Pack and not the full Inno Setup.
• A bug that gave an error when running a report with a filter from a text data source was fixed.
• A bug that prevented case-insensitive filters when the filter Compare to setting is set to Field was fixed.
• A bug that sometimes caused the login dialog to appear off-screen so you couldn't see it was fixed. For details, see http://doughennig.blogspot.com/2008/01/dont-save-if-minimized.html.
• A bug that caused a field needed by a link action to appear in both the available and selected lists in Step 2 of the Quick Report Wizard was fixed.
• A bug that sometimes caused a filter condition to be mangled if it contained a single quote (for example, SomeField = 'Barney's Store') or a pair of empty quotes (for example, SomeField = '') was fixed.
• The cFilter property of the loReport object is now set to the filter for the report.

Displaying Company Header Information

 

Yesterday, a customer asked me how to handle a single record "Company" table. The single record in this table contains information about the Company that is using the software, like the Company Name and the Address. So, if we here at Stonefield were looking at our entry in that table, the Company Name field would contain "Stonefield Software Inc.", and the Address field would be "2323 Broad Street".

The question becomes, "How do we get this information into our reports?". When I first ran across this situation, my initial reaction was to simply create a join/relationship with that table and every other table in the database. There are a couple of problems with doing it this way:

• We don't have a field in the table to join on, so the join would need to be an expression that evaluates to true.
• If the database has a large number of tables, creating a relationship with all of them would be impractical.
• Having to include this company information in the data selection step of Stonefield Query's report wizards would be very clumsy. The information would repeat itself over and over again at the detail level. Information like this generally belongs in the header of a report.

A better way to handle this situation is to use a script that looks this information up for us and stores it in a global variable. Here is an example of such a snippet from a GetCustomMetaData script:

*variable declarations 
public gcAddress, gcCompanyName 
local lnSelect, loDatabase, lcSelect 
*store the current work area 
lnselect = select() 
*get a reference to the database we are currently connected to 
loDatabase = SQApplication.DataEngine.Databases.GetMainDatabase() 
lcSelect = "select CompanyName, Address from Customers" 
*send the SQL statement to the database 
loDatabase.ExecuteSQLStatement(lcSelect, .NULL., 'curCompanyCursor') 
*store the address and company name we just retrieved 
gcAddress = curCompanyCursor.Address 
gcCompanyName = curCompanyCursor.CompanyName 
*restore the workarea 
select (lnselect)

Once this is done, we have two global variables (gcAddress and gcCompanyName) that we can reference from the output expression of a field object in the advanced report designer. We could even reference these from a template, and have company information like name and address appear in the header of every report by default.

This Week's Build

Here are some new features implemented in the past couple of weeks:

• If you turn on the Include All Fields in Same Table in Group Header setting in the Field Properties dialog, you can now specify that all fields in the group header should appear on the same line rather than each on its own line by turning on the new Place All Fields in Group Header on Same Line setting.
• You can now specify multi-line headings for fields, both in the Configuration Utility as a default for the field and in the Field Properties dialog for a field in a particular report.
• You can now press the Esc key to stop a report run at any point rather than just while the pages are being rendered as was the case in earlier versions.
• You now get a warning if you run a report that has no filter, with an option to run the report anyway. This prevents a long-running report because you forgot to create a filter. An new setting in the Options dialog allows you to turn this warning off.
• You now get a warning if you have a custom SQL statement for a report and filter on a field in a table that isn't specified in the SQL statement. The reason is because the filter condition can't be just added to the WHERE clause of the SQL statement since its table isn't specified in the FROM or JOIN clauses.
• The View Field Contents function in the Configuration Utility is now disabled for calculated fields with the Stonefield Query Expression setting turned on, but now works properly if that setting is turned off.
• A new GetReportFilter method of the loReport object can be used to display the filter conditions for the report.