Stonefield Query SDK Version 8.6

We are pleased to announce the release of Stonefield Query SDK version 8.6. There are several new features in this version.

• The new Display as Tooltip setting allows point labels for charts to be displayed when you hover the mouse over a data point rather than all the time. This is handy for charts with a lot of data points or where point labels would otherwise overlap if always displayed.

• You can now output each group to separate tabs rather than separate files when outputting to Excel data format.

• The application now supports large data files and queries that result in more than 2 GB of data.

• You can now roll in one report at a time rather than a set of reports in a dashboard.

• Gauges now have a header so they can show the report name and filter conditions.

• You can now specify that a custom SQL statement is sent directly to the database engine as is rather than being processed by the reporting engine first by prefixing the SQL statement with *=. This is necessary for some complex custom SQL statement that the reporting engine may parse incorrectly.

• The new Maintenance Utility provides functions to clean up the reports table, reset the ADMIN password, contact the activation server to refresh licenses, and fix security issues that could cause some users to not have access to some reports.

• You can now specify ACTION=REFRESH on the command line to contact the activation server to refresh licenses.

Stonefield Query SDK Version 8.5

Stonefield Software is pleased to announce the release of version 8.5 of the Stonefield Query SDK. There are several new features in this version.

Report Designer

• A new report type, Tiled Dashboard, is now available.

• The new Edit button in step 2 of the Dashboard Wizard allows you to edit the selected report included in the dashboard.

• Dashboards now have a toolbar with buttons to pause/restart refreshing or move to the previous or next set of reports.

• Dashboards no longer display a blank cell while a report is being refreshed.

• You can now add a watermark to a report when you print or output to PDF.

• The new Include Totals output setting determines whether totals are included in Microsoft Excel data format documents.

• The Use Saved Email Settings option in the Schedule Report Wizard allows you to specify whether or not to use saved email settings.

• The Advanced Diagnostics setting in the Options dialog creates additional diagnostic files helpful for tracking down certain types of issues.

• The Other Data Sources dialog now has a Refresh button.

• Charts no longer show point labels for 0 values.

• The Commonly Used functions list in the Expression Builder and the list of function when you choose Is as the operator for a filter now both have fiscal year functions.

• You can now ignore certain filter conditions when scheduling a report.

Stonefield Query Studio

• The new DashboardTile property of the Application object indicates which tile of a tile dashboard is currently displayed.

• The new IncludeTotals property of Report objects can be set to True to include totals in Microsoft Excel data format documents.

• MD5.fll is no longer deployed.

• You can hide the Show Password button on the Email page of the Options dialog by setting oApp.lDisplayShowPassword to false in a script, such as Application.AfterSetup or Application.AfterLogin.

Stonefield Query SDK Version 8.4

Stonefield Software is pleased to announce the release of version 8.4 of the Stonefield Query SDK. This is mainly a maintenance release but there are a few new features.

• A report can now be scheduled to repeat every so many hours.

• You can now specify that scheduled tasks be synced across time zones.

• The Schedules dialog now has an Import function to allow you to import schedules, such as when moving Stonefield Query from one machine to another.

• The Print Setup dialog now allows you to choose a different paper size and source, overriding the template settings.

• If a formula starts with "=", it's output as a formula to Excel.

• The email settings in effect at the time a report was scheduled was created are used to email the report when the schedule runs.

Stonefield Query SDK Version 8.3

Stonefield Software is pleased to announce the release of version 8.3 of the Stonefield Query SDK. There are several new features in this version.

Report Designer

• You can now specify additional files to attach to an email in addition to the file for the report.

• Cross-tab reports now support Highest and Lowest total types for date and datetime fields in the Data area.

• You can now specify the horizontal orientation—left, right, or center, with center being the default—for a chart.

• If you add another data source using the Other Data Sources dialog and that data source isn't available (for example, the other data source is a Microsoft Excel document located on someone's local drive), you no longer get an error message and Stonefield Query will still start up.

• You can now specify that a table added in the Other Data Sources dialog is non-reportable.

• There are several DataGrid enhancements:

  • The new Options dialog allows you to turn on "zebra stripes" (alternate row coloring) and specify what the alternate color is, display zero as blank, display a "splitter" between the grid and any text box for memo fields, auto-fit the grid to the column widths when the form opens, and select the font and size for the grid.
  • The mouse pointer displays as an up arrow (sort ascending), down arrow (sort descending), or "no" symbol (column cannot be sorted) when it's over a column header.
  • Logical (yes/no) fields now display as check boxes.

• You can now choose to not display a row field in a cross-tab report by setting its width to 0.

• The recipient of an emailed report is now logged to the diagnostic file.

• The tool tip for a field in step 2 or 3 of the report wizards now displays if a dynamic color or style is used for the field.

Stonefield Query Studio

• A DataEngine.GetObjectVersion script can check which database the objects belongs to with the ObjectDatabase variable.

• A new BeforeProcessTables event in SQConfig.sqs allows you to execute custom code before tables are processed when the data dictionary is created or updated from a data source.

• The new Application.AfterLogin script is called right after the user either successfully or unsuccessfully logs in.

Stonefield Query SDK Version 8.2

We are pleased to announce the release of version 8.2 of the Stonefield Query SDK. This is mainly a maintenance release but there are a few new features.

• Stonefield Query now supports Modern Authentication for email.
• You can now indicate that a stored filter is available for all reports or just the current report.
• More Avery label types are now supported.
• Diagnostic files more than 30 days old are now automatically deleted.
• Spreadsheet output was removed since it was almost never used and is superceded by new Microsoft Excel output formats.
• Choosing the new Yes to All option when importing multiple reports means you won't be prompted for each one whether it should overwrite the existing report.

Stonefield Query SDK Version 8.1

 We are pleased to announce the release of version 8.1 of the Stonefield Query SDK. Here are the new features.

Report Designer

• The new Data Grid button in the ribbon and toolbar output a report to a data grid window.

• Everywhere a password can be entered, such as the login dialog or the Email page of the Options dialog, now has a "show password" button that toggles whether the password is shown as plain text or "*****" (it's always stored encrypted regardless of the setting of that button).

• You can now control what folder scheduled tasks are placed in rather than always being in the "root" folder in the Task Scheduler.

• The Test Syntax button in the Customize SQL Statement step of the Customize Report Wizard now tests whether the SQL statement works with the database engine you're connected to; previously, it only tested whether it was valid in Stonefield Query.

• If Shutdown.txt exists at startup (created either by checking for updates or clicking the Shutdown button in the Displayed Logged in Users dialog), you are now prompted whether maintenance has been completed and if you respond yes, that file is deleted and startup continues.

Stonefield Query Studio

• The View Table Contents and View Field Contents windows now have search, filter, and export to Microsoft Excel functionality.

• Project encryption is no longer supported.

• VFP9R.dll, VFP9RENU.dll, and ISXDL.dll are no longer installed since they are no longer required.

• The Generate Setup function now requires Inno Setup version 6.1 or later.

• The installer has a more modern icon and appearance.

Stonefield Query SDK Version 8.0

We are pleased to announce the release of version 8.0 of the Stonefield Query SDK. Here are the new features.

Report Designer

• You can now choose to display a Microsoft Office-like ribbon rather than a toolbar and menu at the top of the Reports Explorer.

• The Microsoft Excel - Data Only and Microsoft Excel - Data-Only (Fast) output types were replaced with a single Microsoft Excel - Data Format output type. Any reports or scheduled tasks using Microsoft Excel - Data Only or Microsoft Excel - Data Only (Fast) now automatically use Microsoft Excel - Data Format instead.

• You can now specify that a field should be output as a bar code in quick and label reports.

• You can now use multiple page columns in a quick report. This is useful for reports that don't have many fields: rather than having the right side of the page empty, another column of records can be output there.

• A new date format is available: Abbrev. Month/Year. This displays a three-letter abbreviation for a month, such as "Jan 2021" for "January", which allows columns in a report, especially a cross-tab report, to be narrower.

• You can now use a dynamic expression for both the foreground (text) and background colors of a field in a report. Also, dynamic colors and font styles are now supported when you output to Microsoft Excel - Data Format.

• You can now specify an email address to be notified when a scheduled report fails due to an error.

• The Scheduled Tasks dialog has three new features: you can stop a running task, you can disable a task without deleting it, and you can select multiple tasks and delete or disable/re-enable them.

• The Preview window has some new features:

  • The new left panel, which can be expanded or collapsed, contains bookmarks, page thumbnails, and search results.
  • The mouse wheel move to the next or previous page when zoom is set to 100% or less, or scrolls within the page when it's more than 100%.
  • There are more choices for Pages to Display: all the way up to 11x5.
  • You can now zoom a report up to 6,000%.

• You can now get the CC email address to use for a report from a field.

• Fields in the group header of a quick report are now left-aligned with each other if they appear on separate lines.

• Turning on the Auto-Fit to Page setting for a cross-tab report now causes the font to be reduced automatically if it can fit all of the columns on one page.

• The GetValueForParameter function now accepts a format parameter.

• Hovering your mouse over the file name in the Output page of the Reports Explorer now shows the type of output as a tooltip. This is helpful because, for example, several output types use XLSX as a file extension.

• The SQL statement generated for a report can now be up to 16,000 characters; previously, the limit was 8,000.

Stonefield Query Studio

• A new file has to be distributed with Stonefield Query: VFPAR.dll. Be sure to update or regenerate your installer script.

• The new GetSQL script for a table returns the SQL statement to use for the table rather than a simple SELECT SomeFields FROM TableName. The benefit of this is that if a virtual table simply needs a single SQL statement, such as a UNION between two real tables, you can avoid creating a Select script for the table. Select scripts cause a query to be split into individual tables when a report is run, which can make a report run slower, especially with tables with a lot of records. A GetSQL script can potentially be significantly faster since the query isn't split in that case but instead the returned SQL statement is used as a subquery for the main SQL statement for the report.

• You can customize the ribbon displayed in the Reports Explorer in the Application.ReportsExplorerReady script.

• A new Build button appears when you choose Connection String for an ODBC connection to a database. Clicking this button displays the Connection String Builder which makes it easier to enter a connection string.

• Online help generated by Stonefield Query Studio has much better searching so there's no need for Google Custom Search. As a result, the Google Custom Search ID configuration setting was removed.

• The Application object has a new RibbonVisible property.

• The Report object has a new CCEMailField property.

• The DataEngine object has a new GetWhereFromSQLStatement method.

• The GetValueForParameter function now accepts a format parameter.

Stonefield Query SDK Version 7.6

We are pleased to announce the release of version 7.6 of the Stonefield Query SDK. Here are the new features.

Report Designer

• You can add a new data source to report on, such as another database or a Microsoft Excel document. Note: you have to be an Administrative user with an Ultimate license to use this feature.
• Related to the previous item, if there is no relationship between the tables used in a report when you add a field in step 2 of the report wizard, you are now prompted to create one rather than just being given a warning.
• You can now visually see the relationships for the tables in a SQL statement by clicking the View button in the Customize SQL Statement step of the Customize Wizard.
• You can now prevent a user running a report from ignoring an ask-at-runtime filter condition.
• The Reports Explorer now shows how long the last run of a report took. This is also available in Usage Reporting.
• You can now force all users to log out by choosing Display Logged in Users from the Tools menu and clicking the Shutdown button. Users have three minutes to finish what they're doing and exit the program or it is terminated. This is handy if you need to install a new version or perform some maintenance that requires all users to be out of the program.
• You can now use Microsoft SQL Server or some other database to host Stonefield Query's data tables, such as Users and Reports, rather than them being DBF files on disk.
• You no longer get a warning message if the Everyone group doesn't have any data sources selected in the Maintain Users and Groups dialog. That allows you to turn off all data sources for all users and then turn it on group by group.
• By default, the Login dialog displays the name of the last user who logged in. You can prevent that by adding AutoLoginName=N to Data.ini.
• Previously, the Login dialog showed a list of data sources available to the system, whether the user who logs in can access them or not. Now, it doesn't display any data sources until the user name is entered and then only shows the data sources that user can access.
• The Logged In Users menu item is now only available for Administrator users.
• You can now specify whether you want to support newer email protocols or not (some mail servers require it) in the Options dialog. You can also now turn on email logging, which is helpful if emails cannot be sent for some reason. You can also determine which of several SSL/TLS settings should be used rather than just on or off.
• You can now customize the subtotals line in the group footer of a report using a template.
• The new SpelledOut function spells out a numeric value. For example, SpelledOut(5.25) returns "Five dollars and 25 cents".
• You can now specify whether a cross-tab report with a page field has column totals or not.
• A message is now displayed if login fails.
• The Windows account a scheduled report runs under no longer has to have run the application to specify email settings. Instead, the email settings for the user who scheduled the report are used.
• The image in the Sample page of the Reports Explorer is now scrollable.

Stonefield Query Studio

• Three new files have to be distributed with Stonefield Query: SMTPLibrary2.dll, Library32.fll, and WinGraphViz.dll. Be sure to update or regenerate your installer script.
• You can now visually see the relationships for a table by clicking the View Relations button in the Properties pane for a table.
• ODBCDataSource has a new property: ConnectionFailureMessage, which is the text of any error that occurs when connecting to the database.
• The new ReportEngine.OnFinishWizard script allows you to execute code when the user clicks the Finish button in a report wizard.
• The new Quiet property of the Application object allows you to determine when Stonefield Query is running without displaying any user interface, such as when running a scheduled report.
• The Log method of the Application object can now handle format strings. This saves having to concatenate strings together for more complex log entries.
• The SaveEmailSettings method of the Application object now accepts Timeout, UseMailKit, and Logging parameters.
• Field captions are now read from IBM DB2 databases.
• The Options.Settings script can now access the Options dialog using the OptionsForm object. This allows you, for example, to programmatically remove a page from the Options dialog.
• The GetReportHeader method of the Report object now accepts a parameter indicating that only the header should be returned, omitting the filter.

Stonefield Query SDK Version 7.5

We are pleased to announce the release of version 7.5 of the Stonefield Query SDK. Here are the new features.

Report Designer

• Clicking a data point in a chart in a dashboard may now refresh other charts in the dashboard. This allows you, for example, to have a chart showing sales by year and another chart showing sales by month in a specific year. Clicking a year in the first chart changes the year used in the second one.
• Related to the previous feature, you can have a chart in a dashboard automatically cycle through its X-axis values as filter values for other reports in the dashboard.
• You can now specify that a chart included in a quick or cross-tab report should appear on its own page.
• Charts now show "(None)" instead of a blank series.
• The Output each group as a separate file option for outputting to file or email is now supported for chart reports.
• The paging control in a chart now only appears if there's more than one page of data points.
• Column heading objects no longer appear on the last page of a report containing a chart at the bottom.
• Negative numbers formatted to display with parentheses are now right-justified on the last numeric position rather than on the closing parenthesis.
• You can now specify vertical alignment for objects—top, middle, or bottom—in the Advanced Report Designer.
• Microsoft Excel documents created by outputting a report using Excel Data-Only (Fast) output now support the desired font and size for each column and display fields formatted as % properly.
• Tooltips are now supported. You can specify the tooltip for a field in the Properties dialog and that tooltip appears when you hover the mouse over a value for that field in the Preview window, in a PDF file, and in a HTML file.
• You can now enter comments for a folder when you create or edit it and the comments are shown on the Information page of the Reports Explorer.
• The default value for the Weeks starts on setting in the Options dialog is now the same as your Windows Regional Settings.
• You can now report on several fields in the reports table, including Comments, Created By, and Created At, in the usage reporting module.
• Preview functionality in the Advanced Report Designer that was removed in version 7.2 was restored.
• The default for the Display grid lines setting is now turned on.
• Cross-tab reports output to Excel Data-Only (Fast) now handle numeric row fields as numeric and don't include subtotals.
• You can now optionally sum a numeric field in the row of a cross-tab report.
• You can now specify a list of values for the user to choose from in the GetValuesForParameter function.
• You can now create and populate a ValuesCollection object using the new ValuesCollection function.
• Emailing using SMTP now handles email servers that use a newer protocol for SSL.

Stonefield Query Studio

• You can now specify comments for database, table, and relation objects in Studio. These comments aren't used anywhere but are output when you use the Export to Excel function.
• To better support email servers using newer SSL protocols, three new files have to be distributed with Stonefield Query: MimeKit.dll, MailKit.dll, and BouncyCastle.Crypto.dll. Be sure to update or regenerate your installer script.
• TaskScheduler.dll is no longer deployed since it was only used on Windows XP and earlier systems and Stonefield Query now requires Windows Vista and later.
• There's a new ReportRunCancelled property for ReportEngine, a new ProcessID property for Application, and a new Tooltip property for ReportField.
• You can now specify a list of values for the user to choose from in the GetValuesForParameter function.
• You can now create and populate a ValuesCollection object using the new ValuesCollection function.

Stonefield Query SDK Version 7.4

We are pleased to announce the release of version 7.4 of the Stonefield Query SDK. Here are the new features.

Report Designer

• There's a new option when emailing a report: Embed report in email. Turn this on to include the report as HTML in the body of the email.

• There's a new output type: Microsoft Word Merge to a PDF file.

• Output to Microsoft Excel Full Format (XLSX file) and Microsoft Word (DOCX file) is now supported from a scheduled task since those formats no longer need the application (Excel or Word) to be installed on the system and run during output.

• Output to HTML is now supported when emailing.

• Users who have the Advanced setting turned off can no longer view snapshots for report runs nor turn that setting on in the Options dialog.

• Grouping formulas that contain ask-at-runtime filter conditions now put the values of those conditions into the Parameters collection of the report object so they can be used if necessary, such as in a report header.

• You can now change the Data group or table for an existing formula when you edit it from the Formulas dialog. You still can't change those when you edit the formula from step 2 of the report wizards.

• If you want to manually create the result set for a report in the AfterData script of the report, enter "*:" for the SQL statement in the Customize Report Wizard to indicate that Stonefield Query should not retrieve any data itself.

• You can now pass a single default value rather than a collection of values as the default parameter for the GetValueForParameter and GetValueForField functions if you aren't using the between or is one of operators.

• The Check for Updates function now works with our recently changed web server.

• A better message is displayed if testing the email settings in the Options dialog fails.

• <img> tags are now supported in HTML content when the Display as setting in the Field Properties dialog is set to "Formatted HTML".

Stonefield Query Studio

• More properties of the Report object are now documented and displayed in IntelliSense.

• The new ReportEngine.AfterSendEmail script allows you to execute code after a report was emailed, whether successfully or not.

• Stonefield Query now installs version 4.5.2 of the .NET framework if it isn't already installed.

• Reporting on CSV files now handles more types of date formats.

Stonefield Query SDK Version 7.3

We are pleased to announce the release of version 7.3 of the Stonefield Query SDK. Here are the new features.

Report Designer

• Cross-tab reports have a new engine that is significantly faster and more flexible than the older engine. New cross-tab reports default to using the new engine while existing reports use the older engine. You can change which engine a specific report uses with the Use older engine setting in step 5 of the Cross-Tabulation Wizard.

• You can now specify that the chart included in a quick or cross-tab report appears at the end of each group (page field in the case of a cross-tab report), showing just the data for that group, rather than at the end of the report. The Display chart after each group setting in the Other page in step 2 of the Chart Wizard controls that.

• Cross-tab reports now have an Auto-fit to page setting that if turned on causes columns to be spaced across the page if necessary.

• The new Display totals page setting in step 5 of the Cross-Tabulation Wizard allows you to specify whether a totals page appears at the end of the report or not when the report has a page field.

• Turning on the new No summary at the first grouping level setting in the Format page of the Field Properties dialog for a quick report allows you to have a summed parent field in the detail band that only shows the parent value in the group footer but the sum in higher level groups and the report summary band. For example, suppose you have a report that's grouped by Customer Name (from the Customer table) first then by Invoice Number (from the Invoice Header table) and includes Quantity Ordered, Extended Price (both of those from the Invoice Details table), and Freight (from the Invoice Header table). If you sum on Freight, the total in the group break for each invoice will be too high because it sums Freight (which is a field in Invoice Header, which is the parent table for Invoice Details) for each detail record. Turning this setting on for Freight tells the report to not include the sum for Freight in the group footer for Invoice Number but do include it in the group footer for Customer Name and to only sum up at that level.

• If you turn on the new Use "Other" for non-top records setting in the Customize Report Wizard, records outside the top values are combined into a single "Other" value. For example, if you turn on Select only top, choose 10 for the number of records, and turn on Use "Other" for non-top records, rather than displaying only the top 10 records, the report shows the top 10 plus an Other record with all other records combined.

• Formulas are no longer automatically tested for correctness; you are prompted whether you want the expression tested or not. It might be useful to choose No if the expression takes a long time to evaluate.

• Formulas using complex SQL expressions are now better supported.

• You can now schedule a report on the last day of the month.

• You can now specify which database to use when scheduling a report.

• The Reports Explorer now shows on the Information page the number of reports and folders contained in a folder when you select that folder.

• Turning off the new Check for new or modified report files at startup setting in the Options dialog speeds up startup performance.

• You can now specify the increment and minimum/maximum values for the right-axis when two data fields are used in a chart.

• The Continue running reports if cannot connect or one report fails setting now applies to all users.

• Fields with Show percentage of total turned on now show "100%" in the summary band.

---

Stonefield Query Studio

• Configuration settings were reorganized to be in a more logical order, and a new Branding category with branding-related settings (moved from Application) was added. Also, the Support Fax configuration setting was removed for obvious reasons.

• The new Add Delimiters to All Names setting tells Stonefield Query Studio to add delimiters (such as [ and ]) to all names when the data structures are read from a database.

• The New Project Wizard and Refresh dialog have a new Advanced Diagnostics setting. Turn this on to create advanced diagnostic files that can help track down problems reading data structures.

• The new Generate Resource File function in Studio generates an XML file containing the captions for all tables and fields in the data dictionary, making it easier to localize reports.

• TaskScheduler.dll is no longer installed because that's only required for Windows XP which is no longer supported.

• A column was added to the data dictionary table (RepMeta.dbf by default) so when you open a project in Studio, it notifies you that the structure will be changed.

Stonefield Query SDK Version 7.2

We are pleased to announce the release of version 7.2 of the Stonefield Query SDK. Here are the new features.

Report Designer

• A new script is available for reports in the Customize Report Wizard: BeforeOutput. This script allows you to change output settings for the report before it's output to file or email.

• If a subreport has an ask-at-runtime filter condition that matches a filter condition in the main report, the value of that filter condition for the main report is used for the subreport and you are no longer prompted for the value (similar to the way this works for linked reports).

• Cross-tab reports can now have a page field, which appears in the page header and causes a page break when its value changes.

• You can now specify the number of points per page in a chart that's part of a quick or cross-tab report.

• You can now specify whether the Y-axis scale of a chart is determined automatically and, if not, what the minimum and maximum values are.

• Gauges are now centered in their preview window.

• The Scheduled Tasks dialog has a Diagnostic button that displays the content of the most recent diagnostic file for the selected task.

• You can now append to an existing Microsoft Excel document when outputting to Excel full format.

• You can now specify whether an Excel document has gridlines.

• You can now choose the merge type, Letter or Directory, when outputting to Microsoft Word Merge. This affects where pages break in the generated Word documents.

• The new Shadowed setting allow you to specify whether the data shapes in bar, column, pie, and donut charts are shadowed.

• A chart in a quick or cross-tab report can now be different than the main report. For example, a cross-tab with Country in the row, Order Date (displayed as Year) in the column, and Sales Amount in the data can have a chart has Order Date (displayed as Quarter) as the x-axis and Sales Amount as the Y-axis (that is, Country isn't used in the chart).

• You can now turn on Subtotal on This Field for a date field formatted as an aggregate date (such as Quarter or Year) in the rows of a cross-tab report.

• You can now create a template that displays only the page header rather than both the page header and report filter.

• You can now press Enter to add a line break in the Expression Builder for report headers and footers, the message of an email, and the Customize Report Wizard.

• The Expression Builder button is now available for the custom description of a filter condition.

• In the Expression Builder, clicking the Values button to display the Values dialog and selecting a value now inserts that value into the expression.

• You can now use loReport.GetReportHeader(.T.) in a report template to display the page header without the report filter.

• If you import a template with the same name as an existing one, you're asked if you want the imported template to overwrite the existing one or be imported with a new name.

• The last page of the Formatting Options step in the Chart Wizard is now labelled "Other" because it contains other settings.

• You can no longer use the Edit Chart button for a chart that's part of a quick or cross-tab report because it doesn't work properly.

• You can no longer use the Print Preview function in the Advanced Report Designer since it isn't reliable.

Stonefield Query Studio

• A new DLL has to be deployed with Stonefield Query: InTry.dll. Be sure to add it to your installer script.

• The Report object has new DisplayGridLines and MergeType properties.

• The Field object has a new DisplayExpression property.

• A report with a link action on a field that's output to HTML now includes the anchor tag for the linked field. This allows you to do something when the HTML document is displayed in a Web Browser control in a form (obviously it won't work when the document is displayed in a browser).

• You can now implement the Google Custom Search Engine into deployed HTML help files for better searching. To support this, a new configuration setting, Google Custom Search ID, was added.

• You can now pass a blank string for the INI file when calling the GetINIValue and SetINIValue methods of the Application object to use Data.ini in the Data subdirectory.

• The CreateObject method of the Application object can now accept a single parameter: the class name to instantiate. This requires that the class be one of the built-in Stonefield Query classes.

Stonefield Query SDK Version 7.1

We are pleased to announce the release of version 7.1 of the Stonefield Query SDK. This is mainly a maintenance release but there are a few new features.

• The Stonefield Query SDK is now available with two license types: Ultimate and Pro. Ultimate is the full-featured version of the SDK and is designed for developers creating reporting solutions for their customers while Pro is a less expensive license intended for consultants, IT staff, or technical people creating a reporting solution for internal use or a single customer.

• The new Content setting for fields allows you to specify the type of content for a field, which determines how it's rendered in a report.

• You can now open multiple instances of Stonefield Query Studio.

• The new BusinessDays function returns the number of business days between the two specified dates, skipping Saturday and Sunday and optionally holidays.

• There are new This Calendar Year and This Fiscal Year choices for the Is operator for filter conditions on date fields.

• Grand totals for subreports no longer appear at end of the main report since they are rarely wanted.

• All dialogs have help buttons so help is even easier to access than before.

• The Scheduled Tasks dialog now automatically refreshes itself every five seconds while a schedule is being run, saving you having to manually click the Refresh button periodically to see if the task finished.

• You can no longer change the Data group or table for an existing formula since doing that causes the formula to fail. If you need to change the table, recreate the formula in the correct table.

• The cross-tab and label field properties dialogs now have an Enclose negative values in parentheses option.

Stonefield Query SDK Version 7.0

We are pleased to announce the release of version 7.0 of the Stonefield Query SDK. There are lots of new features in this release.

Report Designer

• Multiple license types are now available. This allows you to decide what set of features you would like to have.

• You can now evenly spread the columns across the page in a quick report by turning on the Auto-fit to Page setting. This setting adds extra space between the columns so the fields take up the entire width of the page.

• You can now sort a cross-tab report by a data field. This can be used to, for example, show customer sales by country and product with the largest customers (by sales) at the top and smallest at the bottom.

• You can now specify the number of columns to display in a dashboard. If the total number of cells (rows multiplied by columns) is less than the number of reports, the reports are cycled through in sets at the specified interval. For example, if you specify two rows and two columns, only four reports can be displayed at a time, so if the dashboard contains six reports, the first four are displayed, then the next two, then the first four again. The interval is now in seconds rather than minutes. Also, you can now add more than 9 reports to a dashboard.

• You can now specify the minimum and maximum values for a gauge. The goal value is now optionally shown as a goal marker on the gauge.

• Fields containing HTML and rich text (RTF) content can now be displayed as formatted text.

• The performance of outputting a report with a large number of records (tens of thousands) to Microsoft Excel Data-Only (Fast) was greatly improved (90% faster) and produces smaller XLSX files.

• You can now format a field to display negative numbers in parentheses rather than with a minus sign (for example, (123.45) instead of -123.45).

• You can now specify the title settings for the right axis in a chart that has a field plotted on the right axis.

• You can now create a stacked chart when there is no series field but there is more than one value field.

• You can use the new built-in variable ChartTotal to display the total of a chart, such as in the title of the chart.

• A new type of formatting is available for date fields: Day, which displays Sunday, Monday, Tuesday, etc.

• When you compare a field to another field in the Filter Condition dialog, the drop-down list of fields to compare to now includes fields from other tables in the report, not just the ones from the selected table.

• You can now drill down from a report using an aggregate date (such as Month/Year) to one using a filter on a date range.

• You can no longer sort a chart by values if there's more than one values field or if there's a series field because the chart doesn't support sorting on more than one set of values so the sets don't match with the x-axis.

• The Select Values dialog, which appears when you click the Values button in the Filter Condition dialog or the Expression Builder, now has a Search option so you can search for values.

• The value for a filter condition can now be used in the custom description of the condition.

• The Preview window has new zoom levels: 1000%, 800%, and 500%.

• Expression builder buttons are now available for the header and footer of a report and for the subject and message of an email, making it easier to enter expressions for these things.

• The GetValueForField and GetValueForParameter functions now accept a parameter indicating which value for "is between" and "is one of" conditions is returned.

• You can no longer schedule a report to output to any Microsoft Excel format other than Microsoft Excel Data Only (Fast) or any Microsoft Word format because the other formats require running Excel or Word behind the scenes and they cannot be reliably run from a scheduled task as Microsoft themselves admit.

• The Format function can now use < and > as delimiters for placeholders in addition to { and }. This allows it to be used in expressions, which use { and } as the expression delimiters; for example, {Format('<0:c2>', MyValue)} works but {Format('{0:c2}', MyValue)} doesn't because of the confusion of the two uses of { and }.

• The default Add Missing Values setting for date fields in a chart is now turned off.

• You can now include commas in values passed to filter conditions in a parameters file by escaping them (prefixing them with a backslash); for example, <value value="My Company\, LLC"</value>.

• Cross-tab reports now use weighed averages rather than average of averages when Average is used for a data field.

Stonefield Query Studio

• The DataEngine object has a new UserCancelled property that contains True if the user clicked Cancel when prompted for a parameter value. It also has a new CancelReport method you can call from script code that stops the run of a report if, for example, the user clicks Cancel when GetValueForField or GetValueForParameter is called.

• The User object has a new CanCreateReports property that indicates whether the user can create reports or not.

• The Application object has new License and BatchReportRunning properties. Also, changing the value of CanEditReports is ignored if the user has a Basic license.
  

Issue Accessing Microsoft Excel Spreadsheets

A Windows update on October 10, 2017 caused the Microsoft Excel ODBC driver (specifically, the “Microsoft Excel Driver (*.xls)” driver) to stop working correctly. The solution is to use the “Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)” driver instead. However, this requires installing the latest Microsoft Access Database Engine components, which you can download from https://www.microsoft.com/en-ca/download/details.aspx?id=13255

Stonefield Query SDK Version 6.1

We are pleased to announce the release of version 6.1 of the Stonefield Query SDK. There are lots of new features in this release.

Report Designer

• A new type of output is available for chart reports: Microsoft Excel - Chart, which outputs to an Excel chart rather than an Excel spreadsheet containing an image, which is what Microsoft Excel - Full Format does.

• The Find Reports by Name and Find Reports Using Field functions have been combined into a single Find Reports function in the Tools menu. The dialog displayed when you choose this function allows you to find all reports having a keyword in the name or comment, being last modified on or after a certain date, or containing a specified field.

• Many optional settings that had to be set by manually editing Data.ini are now maintained in the Options dialog.

• The new No Group Footer option for a grouped field specifies that you don't want a group footer for that field.

• You can now fine-tune label dimensions in step 5 of the Label Wizard.

• The new Add group to file name output option allows you to determine when the group name is added to the file name. This also allows you to do Microsoft Word Merge output to separate documents.

• You can now use HTML tags in the body of a email.

• You now only get a warning when a summary report containing ungrouped and unsummarized fields is previewed in the Quick Report Wizard, not when it's run any other time.

• You can now select Highest and Lowest for Summary of a non-numeric grouping formula. This makes it easy to find the oldest or earliest invoice date by customer, for example.

• There is better support for group recalculation formulas. You can now sort a report on such a formula. These formulas are now significantly faster than before, especially in summary reports. You can now use calculated fields in the numerator or denominator or for the grouping of these formulas.

• The new Create bookmarks on this field setting in the Group Header Band Properties dialog in the Advanced Report Designer does the same thing that setting does in the Field Properties dialog in the report wizard, but you can now control it for advanced layout reports.

• More complex SQL statements (those with more terms in parts of the statement) are now supported.

• The AfterData script of a report now has access to the result set for a cross-tab before it's pivoted. It also has access to the result set used for a chart in a quick or cross-tab report that includes a chart through the ChartResultSet variable.

• You now get a warning when creating a formula that does division that doesn't check if the denominator may be zero to avoid "division by zero" errors.

• Blank row fields now display "(None)" for the value in a cross-tab report.

• Bookmarks on grouped fields in summary report are now supported.

• The ask-at-runtime filter dialog now shows which condition of the total number of conditions you're currently on.

• The default setting for the Run report with no records option for new quick and cross-tab reports is now turned on. That means unless you turn it off, when you run a report that has no records matching the filter conditions, the report runs but displays a blank report.

• The Commonly used expressions list in the Expression Builder has new items for First Day of Last Year, Last Day of Last Year, and This Day Last Year.

• The Elegant template has better support for subreports.

• There are new templates: Elegant With Subreport Spaces and Elegant With Indented Group Headers.

• If a scheduled report can't be emailed, the error code is now -35 instead of the generic "some error occurred" -12.

Stonefield Query Studio

• The new Cleanup Project function in the File menu packs the project files to minimize space.

• Stonefield Query Studio now supports reading relationships from Microsoft Access accdb databases.

• There are new ReportEngine.ImportReport, ReportEngine.AfterAddField, and ReportEngine.AfterCreateFile events.

• The Report object has new AddGroupToFileName, BatchItem, and FilterDescription properties. Also, the Folders property is now writable.

• The ReportField object has a new NoGroupFooter property.

• The new URL for Customer Portal configuration setting specifies the URL for a customer portal.

• The new Use "with (nolock)" configuration setting allows you to tell Stonefield Query to use "with (nolock)" on each table when querying a SQL Server database.

• Studio now shows a progress dialog when reading a database structure.

Stonefield Query SDK Version 6.0

We are pleased to announce the release of version 6.0 of the Stonefield Query SDK. There are lots of new features in this release.

Report Designer

• You can now add subreports to quick reports.

• A new type of formatting is available for date fields: Fiscal Year. For example, if the Year Starts In setting in the Options dialog is set to November, a date of 12/06/2016 displays 2017 (since it's in the 2017 fiscal year) when the date is formatted as Fiscal Year.

• You can now include reports that have ask-at-runtime filter conditions in a batch report. When you run the batch report, it now prompts you for all unique conditions for all reports at the start of the run and uses those values for the conditions in all reports. That way, you aren't asked more than once for the same value.

• You can now determine whether scheduled reports run even if there are no records that match the filter conditions for the report so you don't have to wonder why the schedule appears not to run when there's no output.

• You can now link a gauge report to another report.

• Microsoft Excel data-only (fast) output now outputs to XLSX rather than XLS files. Also, it now supports appending to an existing file.

• The Calculate value at group level setting in the Formula Editor was changed to a Group recalculation button which brings up a dialog allowing you to specify the numerator, denominator, and expression to use when recalculating the formula's value at the group level. This was necessary because the application couldn't always parse complex expressions for the recalculation.

• You can now turn on Append to existing file when outputting to CSV and text files.

• You can now determine whether the group value is added to the file name when outputting each group to a separate email. This can also be controlled with the AddGroupValue command line setting and the lAddGroupToFileName property of report objects.

• The new Format function makes it easy to create expressions combining text and non-character values.

• The Reports Explorer now displays the Favorites folder first in the list.

• The Analyze Database function was renamed to Analyze Reports. Rather than looking for fields containing distinct values in the database, it now marks as "favorite" any table and field used in a report.

• There are several new colorful templates available: Stylish Blue, Stylish Green, Stylish Mixed Colors, and Stylish Red.

• You can now preview a template with sample records by choosing Preview Report when in the Advanced Report Designer for the template.

• The Scheduled Tasks dialog now displays the date and time a task is scheduled to run at next and allows you to sort the list of tasks by next run date.

• The Cross-Tab and Chart wizards now display balloon tips for the field under your mouse pointer in step 3.

• You can now specify a "sync" folder in Data.ini. This allows you to copy reports to a server and have all workstations download them.

• You can specify the location of temporary files when a schedule is run if necessary.

• You can now display a marker at the goal value in a gauge and specify the color of the marker.

• You can now set the timeout value for the email server in the Email page of the Options dialog.

• A scheduled report or command line now returns code -33 if the schedule is supposed to email the report but email settings haven't been set up. It returns code -34 if the folder for the specified output file doesn't exist.

• If you import a report using a folder that has the same name as an existing folder but a different structure (for example, you have a folder named Daily Reports that's inside the Management Reports folder and the imported report belongs in a folder also named Daily Report but that's inside the My Reports folder), you're now prompted if you want the report to go into the folder that's on your system (continuing this example, the Daily Reports that's inside the Management Reports folder).

• Double-clicking an SFX file now imports that file if necessary. This resolves the issue of sending an updated SFX file to someone and they double-click it instead of importing it so they run the old one.

• You are now warned if you run a summary report with fields that won't appear because they aren't grouped or summed.

• Reports now have an AfterPreview script where you can code what happens when the Preview window is closed for the report.

• When you save a report in the wizard, any existing snapshot for the report is removed since the layout may have changed.

• Report objects have a cMergeFrom property you can use to programmatically set the Merge From setting for Microsoft Word merge output.

• You can now specify in the Advanced Report Designer that an image with an absolute position (such as a watermark) should appear on top of rather than behind the text in the report.

Stonefield Query Studio

• The new Export to Microsoft Excel function allows you to easily document your data dictionary.

• The Values Method controls in the Calc page of Stonefield Query Studio were moved to the Comments page (which was renamed to Other) to make room for a new field property: Display Expression. This property is similar to Output Expression except it allows the result set for a report to contain data in a proper sorted order but the values displayed in a report to be different. For example, using an expression like ICASE(Value = 1, 'Low', Value = 2, 'Medium', 'High') means that the result set will contain 1, 2, or 3, possibly sorted in that order, but the values are displayed as Low, Medium, and High, which wouldn't sort properly if the ICASE expression was used for the Output Expression.

• The new ReportEngine.PreviewReport script allows you to do something when the report is previewed, such as add a button to the Preview window's toolbar that performs an action when clicked.

• Report objects have a MergeFrom property you can use to programmatically set the Merge From setting for Microsoft Word merge output.

• The Application object has new CreateObject and OpenForm methods.

• Database objects have a NameDelimiters property.

• The new Go Back button in Studio's status bar is a quick way to jump back to a previous object.

• Stonefield Query Studio can now create relationships for foreign keys in any database engine, not just the ones (Microsoft SQL Server, Access, and Oracle) that were supported before.

• Stonefield Query Studio now requires version 5.0 of West Wind HTML Help Builder if you use HTML Help Builder to edit the generated help.

• You are now prompted for the name of a new virtual or subtable rather than having the name be assigned as VIRTUALtable or SUBtable.

• The new Use TOP 10 When Testing Relations option allows you to determine whether Studio uses a TOP 10 clause in the SQL statement used to test relations or limits the result set to 10 records with an ODBC function call. The latter can cause problems with some ODBC drivers so turn this on if that's the case for you.

• The default setting for the Enable Table/Field Favorites configuration setting was changed from False to True since Stonefield Query now treats any table and field used in a report as a favorite.

• Using a text file for a data source now supports more date formats, handles long text such as memos, and allows you to ignore header and footer lines in the file.

• You can now tell Studio to ignore the schema for tables when refreshing the data dictionary. Otherwise, if the schema for a table changed, it doesn't refresh.

• New error code -33 means the parameters specify the report is supposed to be emailed but email settings haven't been set up. New error code -34 means that the folder for the specified output file doesn't exist.

Stonefield Query SDK Version 5.2.5868

We are please to announce the release of version 5.2.5868 of the Stonefield Query SDK. There are several new features in this release.

Report Designer

• A new type of output is available: Microsoft Word Mail Merge. This allows you to perform a mail merge from a report to create form letters.

• A formula can now use an expression that doesn't retrieve data from the database, such as " ", which allows you to add a blank column to a report.

• The SQLookup function can now return an expression instead of just a field. The expression must be one the database engine can understand rather than a Stonefield Query expression.

• The GetConditionValue function now accepts a parameter indicating which value for "is between" and "is one of" conditions is returned.

• You can now turn off usage reporting by adding Usage=N to the [Options] section of Data.ini.

• If none of your data fields contain null values, you can make processing slightly faster by adding HandleNull=N to the [Options] section of Data.INI.

• Grouping formulas can now be used as data fields in a cross-tab report.

• Cross-tab reports now use the text for the Summary Total Template field in the template if it exists; this allows you to customize the text displayed for the totals line by changing the text in the template used by the report.

• Help now displays correctly even when running from a server without having to install the help file on the workstation.

• The Calculate Value at Group Level setting in the Formula Editor is now disabled if the field is a grouping formula since a formula can't be both.

• The GetConditionValue, GetValueForField, GetValueForParameter, GoMonthDay, SQLookup, STOD, and RunSQL functions now appear in the Built-in Functions list in the Expression Builder.

• The Print button in the Preview window is now disabled for a label report using a Dymo label size since it works better to print those labels directly rather than previewing first.

• You can now specify "login" on the command line to prompt the user to log in when running a dashboard from a shortcut. Also, theDashboard Wizard now has a "Require login" setting in step 2.

Stonefield Query Studio

• The new ReportEngine.BeforeSendEmail script allows you to change the settings of a report before it's emailed to someone.

• You can now customize what happens when refreshing the data dictionary for a database that has tables or fields removed. For example, you can use this to not remove tables or fields from modules that may not be in the database being refreshed from.

• You are now prompted before Stonefield Query Studio removes tables from the data dictionary after refreshing from a database that has tables or fields removed.

• Stonefield Query Studio now checks that you've selected the correct name delimiters (the reverse apostrophe: `) when adding a MySQL database to the data dictionary.

• Stonefield Query now handles two tables with the same name but different schemas.

• The default for the Support Online Updates and Support Maintenance Reminder configuration settings for new projects is now False.
  

Stonefield Query SDK Version 5.2

We are please to announce the release of version 5.2 of the Stonefield Query SDK. There are lots of new features in this release.

Report Designer

• There are two new settings on the Grouping page of the Properties dialog for fields in step 2 of the Quick Report Wizard: Retrieve all records and Number of records per group. Turn off Retrieve all records and enter a value for Number of records per group to limit the number of records per group. You can combine this with sorting on one of the fields in the detail for the group to see the oldest or newest record. For example, suppose you want to see only the most recent activity for a customer. To do that, group on customer name, set Number of records per group to 1, and sort on the activity date in descending order.
• There's a new setting for formulas: Calculate value at group level. This setting allows certain formulas to be re-calculated at the group level rather than just being summed, such as profit margin or other types of ratio calculations for which summing and averaging don't work. To indicate that the formula should show group values in a report, set the Summary option for the formula in the report to a new choice: Group Calculation.
• A new type of filter operator is available for date fields: Is. When you choose this operator, you can select a date type to compare the field to, such as This Week, Last Year, or This Month to Date, from a drop-down list.
• Dashboards can now include all types of reports, not just charts and gauges. Note, however, that the dashboard window doesn't scroll so it's really only appropriate for short, informative reports.
• Step 2 of the report wizards now shows information about the field under the mouse pointer in a "balloon" tip. For the Available fields list, the balloon shows any comments about the field. For the Selected fields list in a quick report or label, the balloon shows how the field is formatted in the report: whether it's grouped or summed, what numeric or date format is used, and so on. This allows you to see at a glance information about the field without having to click the Properties button.
• You can now select more than one grouping field for a grouping formula. This is necessary for a formula that, for example, show sales for a certain date range by customer and product.
• A new type of output is available: Microsoft Excel - Data Only (Fast). This is similar to Microsoft Excel - Data Only but is faster and doesn't require that Excel is installed on the system. This format works better that Microsoft Excel - Data Only with scheduled reports because Excel doesn't always work correctly when run from a scheduled task.
• You can now specify whether category and series fields are sorted in ascending or descending order in a chart.
• You can now specify a background color for fields in a report in the Properties dialog. Sample text shows what the colors of the field will look like in the report. You can also revert to the default color for the field by turning on the new Use default color setting.
• You can now export and email a report layout to someone in one step: right-click the report in the Reports Explorer and choose Email Report File, enter their email address, and click Send.
• The Filter page in the Reports Explorer and report wizards has a new setting: Warning if no filter when report is run. This used to be a global option in the Options dialog but is now available on a report-by-report basis. The setting in the Options dialog was changed to Default warning if no filter when a report is run and it now determines what the default is for that setting for new reports.
• You can now specify the font for X-axis, Y-axis, and point labels in charts.
• When you output a cross-tab to a Microsoft Excel PivotTable, the PivotTable now supports the Show percentage of total, Show difference from previous column, Show percentage change, Column totals, Row totals, Display date as, and numeric format settings for the cross-tab report.
• The Options dialog no longer has a Date Format setting; the application now always uses the date format specified in the Windows Control Panel.
• If no output settings are specified when a report is run from the command line, the saved output settings for the report are used if there are any.
• If an advanced layout report has any images, such as a company logo, those images are included in the SFX file for the report. If you export the report and send the SFX file to someone but forget to send them the image files used by the report, the Import process they use to add the report to their reports list creates a new folder called Images and puts the images imbedded in the SFX file there. The benefit of this is that you no longer get an error about a missing image in this case.
• Some settings in the Options dialog now affect all users on a network: Create and display report snapshots, Send summary queries to database engine, Week starts on, and Year starts in. These settings are saved in Data.ini in the Data subdirectory of the program folder rather than in the Windows Registry where other settings are saved.
• The Show totals in group headers setting for quick reports is now available for advanced layout reports.
• The positions of the Advanced Layout, Chart, and Edit buttons in step 5 of the Quick Report and Cross-Tab Wizards was changed a little to accommodate other controls.
• The Alignment setting in the Properties dialogs for fields now displays icons for left, right, and center alignment.

Stonefield Query Studio

• The Tag for News Feed configuration setting can now contain multiple tags if they are separated with commas.
• A new step 2 of the New Project Wizard allows you to specify that core Stonefield Query files should be named differently when installed on a user's system, such as naming SFQuery.exe as MyApp.exe. This is easier than the manual steps you previously had to use to create a branded version of Stonefield Query.
• The Application object has a new Log method.
• A new set of files, IsFunctions.DBF and IsFunctions.FPT, must be distributed with Stonefield Query so be sure to regenerate your setup script or update your existing one accordingly.
• Editing the West Wind HTML Help Builder project created with the Create Help Files function now requires West Wind HTML Help Builder version 5.0.
• If the user was editing a report and an error occurred, the SFX file attached to the email sent with the error report includes the user's changes.