Tuesday, January 30, 2018

Calculating Profit Margin and Other Expressions with Division

An expression that does division, such as calculating profit margin, is trickier than it looks. The problem is how do you calculate a group or end-of-report value?

For example, you can create a formula to calculate the profit margin of an item using something like "(SaleAmount - Cost)/SaleAmount * 100." That expression works correctly for detail items but not for groups. Suppose an invoice has one item sold for $2.00 that you paid $1.00 for (a 50% margin) and another item for $20.00 that you paid $15.00 for (a 25% margin). The total sale amount for the invoice is $22.00 and the total cost of the items is $16.00 for an overall margin of 27%. For a group footer for that invoice, you clearly can't sum the margins (25 + 50 = 75%), nor can you average them ((25 + 50)/2 = 37.5%). What you really need the report to do is to total the sales amounts, total the cost amounts, and then calculate the margin at the group level from those totals.

Fortunately, Stonefield Query has a way to do that: formulas with group level recalculation. See the help topic for “Creating Your Own Formulas” for a discussion of how to use that feature.

If you’re doing the calculations manually in the Advanced Report Designer rather than with formulas, you need to calculate the margin with variables. In the Advanced Report Designer, choose Variables from the Report menu, click Add, enter a name such as GroupPrice, and use these settings:

Value to store: the name of the field containing the price, such as EXTPRICE
Initial value: 0
Reset value: choose which group to restart the calculation on
Calculation type: Sum

This tells Stonefield Query to sum up the price field at the group level. Create another variable named TotalPrice with the same settings but Reset Value should be Report so it sums the price field for the whole report.

Create two more variables, GroupCost and TotalCost, that are similar but use the name of the cost field instead.

Finally, use these variables in fields in the Group Footer band and Summary band to display the overall margin. For example, for the one in the Group Footer band, use:

iif(GroupPrice = 0, 0, (GroupPrice - GroupCost) / GroupPrice * 100)

That way, the calculation is done the same way you'd do it by hand: total up the price, total up the cost, and do the calculation on the totals.

Monday, January 22, 2018

ClickBase Competitive Upgrade

With the ending of support for ClickBase for AccountMate, finding a replacement may be difficult. Stonefield Query is that replacement.

Stonefield Query for AccountMate provides a step-by-step method to create professional business reports from information stored in AccountMate. Anyone can create detailed reports, high level overview, drill-down, charts/graphs, pivot tables, and more in minutes rather than hours. Stonefield Query for AccountMate works with the latest version of AccountMate SQL and earlier versions.

Stonefield Software is offering AccountMate users a 50% off competitive upgrade from ClickBase to Stonefield Query for AccountMate until June 30, 2018.  That includes one Report Designer license and five Report Viewer Subscription licenses. Contact sales@stonefieldquery.com for more details.

Thursday, January 11, 2018

Stonefield Query for AccountMate webinars

Stonefield is presenting a webinar for AccountMate Business Partners on Stonefield Query for AccountMate and our new ClickBase competitive upgrade on Wednesday, January 17, 2018 at 1:00 Eastern. Register today on the AccountMate Business Partner web site.

We are also presenting a webinar for AccountMate users on Stonefield Query for AccountMate and our new ClickBase competitive upgrade on Wednesday, February 7, 2018 at 1:00 Eastern. Register today at https://www.accountmate.com/education/webcasts.asp

Wednesday, December 13, 2017

Stonefield Query for GoldMine Version 6.1.6543

We are pleased to announce the release of version 6.1.6543 of Stonefield Query for GoldMine. This release adds compatibility with the new GoldMine 2018.1, along with some other minor updates and bug fixes.

Monday, November 6, 2017

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

Tuesday, October 10, 2017

Stonefield Query for Act! Version 6.1.6492

We are pleased to announce the release of version 6.1.6492 of Stonefield Query for Act!. This release adds compatibility with the new Act! v20.

Thursday, September 21, 2017

Stonefield Query for Sage 300 Version 6.1.6467.

This version adds compatibility with Sage 300 2018 and is also a maintenance release. It has the following new features:

  • 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).
  • The Order Entry Sales History (OESHHD) and Sales History Details (OESHDT) tables now have Net Amount (Functional) and Net Amount (Source) fields (FNETAMT and SNETAMT, respectively), which are sales amount less returns amount.
  • There's now a relationship between the GL Posted Transactions table (GLPOST) and the AP Posting Journal Entries table (APPJH) so you can drilldown from a GL entry to the matching AP entry.

Thursday, July 6, 2017

Stonefield Query for ALERE Version 6.1

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

  • 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 for HEAT Version 6.1

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

  • 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 for AccountMate Version 6.1

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

  • 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.