Thursday, April 16, 2015

Creating an Aging Report Using Stonefield Query

An aging report is one that shows totals broken down by age range. The most common example is an accounts receivable aging report, which shows how much each customer owes by period, typically within the past 30 days, 31 – 60 days, 61 – 90 days, and over 90 days. This type of report allows you to make business decisions, such as which customers to increase collection efforts with and which to cut off sales until they pay up (no point in continuing to sell products or do work for customers who won’t pay for them).

aging

You’ve always been able to create this type of report in Stonefield Query, but until version 5.0, it was a bit of work. There were two ways to do it:

  • Create a formula for each aging range that decides whether to include the amount or not using the IIF function that checks the date. For example, the expression IIF(BETWEEN(Invoices.DateDue, DATE(), DATE() – 30), Invoices.OutstandingAmount, 0) checks whether the due date for the invoice is between the current date and 30 days ago. If so, it takes the unpaid amount; if not, it takes 0. The report is grouped on customer and the formula fields are summed.
  • Using the Advanced Report Designer, add fields to the report that do similar calculations to the formula approach.

A better way

A new feature added in version 5.0 called grouping formulas makes this a much simpler task. Here are the steps to creating an aging report using the sample data that comes with the Stonefield Query SDK; for other versions, such as Stonefield Query for Sage 300 ERP, the steps are very similar but you use the appropriate tables and fields instead of the ones mentioned here.

The first step is to create a quick report. Add the Customer Name field to the report, then click the New Formula button. Select the Customers table because that’s where we want the formula to go. Enter “0 – 30” for the Name and Heading since this first formula is for the first aging. Click the Expression Builder button (the one with the three dots) and select the Total Price field from the Order Details table because that’s the field we want to sum up.

formula1

Now here’s the key: we want this to be a grouping formula. A grouping formula is similar to a regular formula but it automatically summarizes the values, grouping on a certain field, and optionally for only a certain range of records. (Those of you who are technically oriented may guess that a grouping formula results in a SQL statement like SELECT SUM(SomeField) FROM SomeTable WHERE SomeConditions GROUP BY SomeGroupField.) In our case, we want to sum the total price, group the sums by customer, and only for records that are 30 or fewer days old. (Since the sample database doesn't have payments, we'll just assume that all orders are outstanding for demo purposes.)

To make this a grouping formula, click the Grouping button. Select Sum for Summary and Customer ID for Grouping Field. Click the Filter button, add a condition, select the Order Date field from the Orders table, and choose “is between” for the operator. The values to use are a little tricky: we don’t want to hard-code them to something like 05/01/2015 and 05/31/2015 because every time we run the report, we’d have to edit the formula and change the date range. We could turn on Ask at Runtime to prompt for the date range, but since we’ll ultimately have four formulas (one for each aging range), we don’t want to be asked for four different ranges of values; we really just want to put in a single value, which is the “as of” date for the aging calculations. To do that, click the More button, change Compare To to “Expression”, and enter the following expressions for the two values:

GetValueForParameter('As of date', 'D') – 30
(for the first value)

GetValueForParameter('As of date', 'D')
(for the second one)

GetValueForParameter is a built-in function that asks the user running the report for a value. The first parameter for the function is the text to display (“As of date” in this case) and the second is the data type for the value (“D” means we want a date; see the documentation for GetValueForParameter for what codes to use for different data types). The first expression tells Stonefield Query to ask the user for a date value and then subtract 30 days from that value. You may think from the second expression that Stonefield Query asks the user a second time, but GetValueForParameter has a nice feature: if the prompt is the same as a previous instance, the function doesn’t ask the user a second time but instead just returns the value they entered when they were asked. Since the two expressions both use “As of date” for the prompt, the user is asked only once. In fact, you’ll use a similar expression for the other formulas, such as 31 – 60, but subtract a different number of days, and since all of them use “As of date” for the prompt, the user is still only asked one time for the value that all expressions use.

Once you’ve saved the filter condition, the Grouping Formula Properties dialog should look like this:

formula2

Specify the formatting for the formula (I turned on Display $ and set Decimal Places to 2) and save the formula.

Finishing the report

Repeat these steps to create the 31 – 60, 61 – 90, and More Than 90 formulas. The only difference is the expressions used for the filter condition. For example:

GetValueForParameter('As of date', 'D') – 60
(first value for 31 – 60 formula)

GetValueForParameter('As of date', 'D') – 31
(second value for 31 – 60 formula)

(The expressions to use for the other two formulas are left as an exercise for the reader, but should be pretty obvious).

That’s it! Your report should now include Customer Name and the four formulas. No need to group the report on Customer Name or turn on Summary Report. When you run the report, you’re asked for the date to use for the aging calculations. The result should look like this:

aging

Summary

Grouping formulas are a very powerful new feature added in version 5.0 of Stonefield Query. There are tons of uses for them, one of them making it very easy to create a simple aging report.

Thursday, April 9, 2015

Finding the Fields You Need for a Report

If there are a lot of fields in your database, it can be challenging to find the one you're looking for. Here are some tips that may help:

  • If you're not sure what a field contains, click the Values button in step 2 of the report wizards. The different values displayed in the dialog often give a clue as to what the field is used for.
  • Fields are displayed by default in alphabetical order. This makes it easy to find a field in the list. However, sometimes it makes more sense to display fields in the order they appear in the table. Click the SORTASCXPSMALL button to display fields in table order.
  • A long list of fields can make it difficult to find the ones you're interested in. If you know part of the field caption you're looking for (such as a field with "tax" somewhere in the caption), click the findxpsmall button, enter the text, and click OK. If there are any fields that match, the first one is selected. Press F3 to find the next one containing the same text.
  • A long list of tables can make it difficult to find the ones you're interested in. Once you've selected fields from one table, chances are you're next going to select fields from a directly-related table. For example, if you chose a field from Customers, you are more likely to choose a field from Orders than from Products. Click the RELATIONXPSMALL button to reduce the list of tables to only those directly related to ones you've already selected fields from.
  • If you're familiar with the name of your application's tables and fields, you might find it easier to locate the tables and fields you want by their real names. Turn on the Display real table and field names option in the Options dialog to display the real name followed by the descriptive name in parentheses.
  • Sometimes the fields in an application are organized into pairs of "header-detail" tables; invoices and invoice lines is a classic example. These are called "header-detail" pairs because the information displayed in the header or top of an invoice goes in the invoices table and the line items go in the invoice lines table. When looking for a particular field, ask yourself if there's only one of these things in the invoice (invoice number, invoice date, customer number, and so on) or if there can be more than one (product description, quantity sold, etc.). If there's only one, you'll find the field in the header (in this case, invoice) table. If there's more than one, it'll be in the detail (invoice lines in this example) table.
  • Application view shows fields the way your application does in its various dialogs. However, not all fields may appear when Application view is turned on in step 2. If you can't find a particular field, try turning on Database view. (Your version of Stonefield Query may not support application view.)
  • A lot of fields may contain default or even no data; for example, the fields may be there to support certain features in the program, but you may not use those features. To hide those fields, choose Analyze Database from the Tools menu. This function goes through your database, looking at all fields in all tables for fields containing meaningful data (that is, more than a single unique value), and marking those as "favorite." After you've run this function, step 2 in the report wizards displays a FAVORITES button. If you turn this on, the table and field lists show only those tables and fields marked as "favorites." (Your version of Stonefield Query may not support favorites.)

Wednesday, March 4, 2015

Stonefield Query Enterprise Web Version 2.0 Released

Stonefield Software is pleased to announce the release of version 2.0 of Stonefield Query Enterprise Web. There are a dozens of new features in this release, the major ones being:

  • Charts and dashboards are now supported.
  • A new type of filtering is available: group filters. A regular filter condition filters at the record level; for example, show all customers with an invoice amount is greater than $100. A group filter condition filters at a group level; for example, show all customers whose total invoice amounts are greater than $100.
  • Embedded subreports that aren't linked to a field are now supported: click the new Subreport Properties button in step 5. This can be used, for example, to add a chart to a quick report.

See the online help for both Stonefield Query and Stonefield Query Studio for a complete list of new features.

For developers, note that version 2.0 has a breaking change if you’ve created your own plugins, as the interface for some plugins has changed. You’ll need to update and recompile your plugin code to work with version 2.0.

Thursday, February 12, 2015

Improving the Performance of Calculated Fields and Formulas, Part 2

In the previous blog post, I discussed one potential cause of slow performance of calculated fields and formulas: using the RunSQL function. In this post, I’ll discuss another common cause for performance issues: calling a script that retrieves multiple result sets from the database engine.

Note: this is an advanced topic intended for developers or technically-oriented users.

Calling a script from a formula

Sometimes, a formula needs to do more than just use an expression to calculate a value. In that case, it needs to call a script to execute some code. Often, that code retrieves additional data from the database, and that can lead to the same problem discussed in the previous blog post: multiple SQL statements sent to the database engine, slowing down performance.

For example, in Stonefield Query for Sage 300 (Sage 300 is an accounting system formerly known as Accpac), to determine the total quantity on hand for an inventory item, a script has to access the ICILOC table, which contains the quantity on hand at each location (such as each warehouse), and add up the various amounts for a particular item. Here’s a script that would do that:

lparameters tcItem
lcSelect = 'select sum(QTYONHAND) as QOH from ICILOC ' + ;
    'where ITEMNO=?tcItem'
loDB = SQApplication.DataEngine.Databases.GetMainDatabase()
loDB.ExecuteSQLStatement(lcSelect)
lnAmount = QOH
return lnAmount

If the name of this script is GetQOH, it would be used in a formula as follows:

GetQOH(ICITEM.ITEMNO)

The problem with this script is that if the report shows 1,000 inventory items, this code is called 1,000 times, and 1,000 additional SQL statements are sent to the database engine, which would likely be pretty slow.

The solution

The solution is to execute only a single SQL statement that sums the quantity on hand across all location for all items, not just the current one, keep that result set in memory (sometimes called caching), and then do an indexed lookup (a SEEK) to find the one we’re interested in. Here’s the code for the updated GetQOH script:

lparameters tcItem
if not used('_TotalQOH')
    lcSelect = 'select ITEMNO, sum(QTYONHAND) as QOH ' + ;
        '
from ICILOC group by ITEMNO'
    loDB = SQApplication.DataEngine.Databases.GetMainDatabase()
    loDB.ExecuteSQLStatement(lcSelect, , '_TotalQOH')
    index on ITEMNO tag KEY
endif
select _TotalQOH
seek tcItem
lnAmount = QOH
return lnAmount

There isn’t much extra code, but notice the SQL statement is only executed once, the first time the script is called, and that the result set it retrieves calculates the total for all items and indexes the result set on the item number. The second and subsequent times the script is called, it sees that the result set it needs is already in memory so it doesn’t bother retrieving it again, instead doing a SEEK to find the record for the current item. This can result in orders of magnitude improvement in performance, depending on the size of the result set retrieved for the report.

There are a couple of things to watch out for when writing a script that retrieves additional data.

Wrinkle #1: showing current data

After the report is run, the result set retrieved by the script is still cached. So, when you run the report again, it doesn’t bother retrieving a fresh set of data. That’s fine if the data doesn’t change but if users have added or edited records in the meantime and you want the most current data to be used, you have to tell Stonefield Query to close the cached data at the end of each report run. To do that, add the following line of code before the ENDIF statement in the code above:

oApp.oData.oCursorsToCloseAfterRun.Add('_TotalQOH')

Note: in the upcoming Stonefield Query version 5.1, this is a little simpler; you can use:

SQApplication.RegisterCursorToCloseAfterRun('_TotalQOH')

Wrinkle #2: handling multi-database reports

The second issue is a little more complicated, but only occurs if you have a multi-database report; that is, a report in which you’ve selected more than one database using the Database button on the Filter page (not all versions of Stonefield Query support this). Because Stonefield Query does all of the data retrieval it needs for the report before processing calculated fields and formulas, by the time your script is called, only the current database (the one shown in the status bar) is being used so the data your script retrieves comes from that database, not all of the databases for the report. In that case, your script has to handle getting the data from the correct database for the record it’s currently processing. A couple of things are required.

First, in order to know which database the record came from, you have to add the Source Database field to the report (this is a virtual field added to every table when multi-database queries are supported), even if you turn off its Display Field in Report setting so it doesn’t actually appear in the report. That way, your script can identify which database it needs to access to get the additional data for each record.

Second, your script has to retrieve more than one result set; it needs a different one from each database. That means it has to keep track of the current database, connect to the one used by the record if necessary, retrieve the data, and reconnect to the original database. Of course, we’ll cache the different result sets so this only happens once. Here’s the script above updated to support that:

lparameters tcItem
loDB = SQApplication.DataEngine.Databases.GetMainDatabase()
lcCurrent = loDB.CurrentDataSource.DataSource
if type('loReport') = 'O' and ;
    not empty(loReport.aDataSources[1]) and ;
    type('SOURCEDATABASE') = 'C'
    lcDataSource = trim(SOURCEDATABASE)
else
    lcDataSource = lcCurrent
endif
lcCursorSuffix = oUtility.GetValidName(lcDataSource)
lcCursor = '_TotalQOH' + lcCursorSuffix
if not used(lcCursor)
    llReopen = .F.

    if lcDataSource <> lcCurrent
        llReopen = .T.
        loDatabase.OpenDataSource(lcDataSource)
    endif

    lcSelect = 'select ITEMNO, sum(QTYONHAND) as QOH ' + ;
        'from ICILOC group by ITEMNO'
    loDB.ExecuteSQLStatement(lcSelect, , lcCursor)
    index on ITEMNO tag KEY
    if llReopen
        loDatabase.OpenDataSource(lcCurrent)
    endif
endif
select (lcCursor)
seek tcItem
lnAmount = QOH
return lnAmount

Whew! That’s a bit of complicated code, but you can use this example as a template for your own scripts. Just replace the values assigned to the lcCursor, lcSelect, and lnAmount variables, change the name of the variable used for the parameter and the SEEK statement (if desired; no harm if you leave it as “lcItem”), and change the INDEX ON statement to use the appropriate index expression.

Summary

Using the techniques in this blog post and the previous one can make your reports run significantly faster. They’re a bit of work to implement but the results are well worth the effort!

Improving the Performance of Calculated Fields and Formulas, Part 1

Stonefield Query has very fast database and calculation engines. Coupled with a high-performance database server like Microsoft SQL Server or Oracle, reports are often generated in Stonefield Query significantly faster than in other reporting solutions. We’ve heard of cases where a report took hours to run in (unnamed competitor) but only seconds in Stonefield Query.

However, occasionally we come across reports that run slower than expected. Frequently, the cause is a calculated field (defined by the developer in the data dictionary) or formula (defined by the user in the Formula Editor) that doesn’t perform well. This blog post and the next one discuss a couple of common causes and solutions. This post discusses the RunSQL function.

Note: this is an advanced topic intended for developers or technically-oriented users.

Using RunSQL

The RunSQL function has been in Stonefield Query for a long time. It was added to allow a formula to execute a SQL statement that retrieves additional data from the database. For example, suppose you want to show the amount of freight charged for each customer, but freight is a line item in an invoice details table rather than being a discrete field so you can’t just add a field for it to the report. In that case, a formula with an expression like this would calculate the freight charge for each customer:

RunSQL("select sum(AMOUNT) from INVOICE_DETAILS where ITEMNO = 'FREIGHT' and CUSTOMER_NO = ?CUST_ID")

The ?CUST_ID syntax means use the value of the CUST_ID field in the current record of the result set. In this case, the report displays the CUST_ID field from the CUSTOMERS table, then for each record, calculates the freight by executing the SQL statement in the RunSQL function.

The problem with RunSQL

The way Stonefield Query generates the result set for a report is as follows:

  • Determine the SQL statement needed to retrieve the data for “real” fields and for calculated fields and formulas that have the “Send formula to database engine” setting turned on.
  • Add to the SQL statement the fields necessary to perform the calculation of calculated fields and formulas that have the “Send formula to database engine” setting turned off.
  • Retrieve the data using the SQL statement into a temporary result set.
  • Go through every record in the temporary result set and process any calculated fields and formulas that have the “Send formula to database engine” setting turned off to give the final result set.

It’s the “go through every record” in the final point that causes the problem when you use RunSQL. Suppose you have 2,000 customers. As Stonefield Query processes the expression for the Freight formula, it sends a SQL statement to the database engine for every record. In this case, that’s 2,000 additional SQL statements. No wonder the report takes so long to run!

The solution

There are a couple of solutions to this problem. One is to use a new feature added in Stonefield Query version 5.0: grouping formulas. A grouping formula is like a regular formula but allows you to specify a summarization to be done on the expression (like the SUM function used in the SQL statement above), a field to group the summarized data on (like the implied grouping due to the filter on customer number in the SQL statement above), and optionally a filter to limit the records being retrieved (like the WHERE clause on ITEMNO in the SQL statement above). In many cases, a grouping formula can replace a formula using a RunSQL statement, but grouping formulas have one critical advantage: only a single SQL statement is sent to the database and the result set retrieved by it is cached and indexed for high-speed lookups. This results in orders of magnitude better performance, at the cost of using a little more memory.

GroupingFormula

Following the example above, you’d replace the expression of the Freight formula with just AMOUNT, click the Grouping button, select “Sum” for Summary, select CUST_ID for Grouping Field, and specify a filter of ITEMNO equals FREIGHT. The resulting formula is not only easier to create (you don’t have to know the SQL language or real table and field names) but performs a LOT faster.

For those few cases where a grouping formula can’t replace a RunSQL statement (although we haven’t encountered one yet), the other solution is to use the technique discussed in the next blog post: caching and indexing your own result set.

Thursday, November 27, 2014

Stonefield Query for HEAT Version 5.0

Stonefield Software Inc. is pleased to announce the release of version 5.0 of Stonefield Query for HEAT. This release has numerous new features.

  • Stonefield Query has a new report type: gauge report. This allows you to create a gauge showing the current value of something compared to a target or goal value.
  • You can now create a "grouping" formula. A grouping formula is a powerful tool that allows you to create filtered summary values. For example, suppose you want columns in a report that show the sales for last year and the current year-to-date for each customer. Those aren't single values retrieved from the database. Instead, for the first column, use a grouping formula that sums up the sales records for each customer for last year, and use a similar one but for the current year for the second column.
  • The Inventory Control Location Item Details table now also appears in the Purchase Orders module.
  • The Inventory Items table now has a Total Quantity on Hand field that contains the total quantity on hand from all locations.
  • The Inventory Shipments related tables no longer appear in the Order Entry or Purchase Orders modules to avoid confusion with the shipments tables already in those modules.
  • You can now password protect the file created when a report is output to file or email.
  • You can now print to Dymo LabelWriter labels.
  • The Schedule Report function is now available only to advanced users.
  • A new output type is now available: Microsoft Excel - Full Format (*.xlsx), which creates Excel 2007 or later files. The former Microsoft Excel (*.xls) output type was renamed to Microsoft Excel 97-2003 - Full Format (*.xls) so you can tell them apart. The new type does not require Microsoft Excel to be installed to create the file.
  • The "red" error dialog that appears when something goes wrong now has a Help button that brings up the help with information about that dialog.
  • The Locations page in the Options dialog is now available only to administrative users.
  • The Subscription Expired dialog, which appears when you have subscription licenses and your subscription has expired, now has a "Don't remind me again" option; turn that option on to prevent the dialog from appearing again. Also, those licenses are now deactivated rather than being deleted, so if you renew your subscription, they are reactivated.

Stonefield Query for ALERE Version 5.0

Stonefield Software Inc. is pleased to announce the release of version 5.0 of Stonefield Query for ALERE. This release has numerous new features.

  • Stonefield Query has a new report type: gauge report. This allows you to create a gauge showing the current value of something compared to a target or goal value.
  • You can now create a "grouping" formula. A grouping formula is a powerful tool that allows you to create filtered summary values. For example, suppose you want columns in a report that show the sales for last year and the current year-to-date for each customer. Those aren't single values retrieved from the database. Instead, for the first column, use a grouping formula that sums up the sales records for each customer for last year, and use a similar one but for the current year for the second column.
  • The Inventory Control Location Item Details table now also appears in the Purchase Orders module.
  • The Inventory Items table now has a Total Quantity on Hand field that contains the total quantity on hand from all locations.
  • The Inventory Shipments related tables no longer appear in the Order Entry or Purchase Orders modules to avoid confusion with the shipments tables already in those modules.
  • You can now password protect the file created when a report is output to file or email.
  • You can now print to Dymo LabelWriter labels.
  • The Schedule Report function is now available only to advanced users.
  • A new output type is now available: Microsoft Excel - Full Format (*.xlsx), which creates Excel 2007 or later files. The former Microsoft Excel (*.xls) output type was renamed to Microsoft Excel 97-2003 - Full Format (*.xls) so you can tell them apart. The new type does not require Microsoft Excel to be installed to create the file.
  • The "red" error dialog that appears when something goes wrong now has a Help button that brings up the help with information about that dialog.
  • The Locations page in the Options dialog is now available only to administrative users.
  • The Subscription Expired dialog, which appears when you have subscription licenses and your subscription has expired, now has a "Don't remind me again" option; turn that option on to prevent the dialog from appearing again. Also, those licenses are now deactivated rather than being deleted, so if you renew your subscription, they are reactivated.

Stonefield Query for AccountMate Version 5.0

Stonefield Software Inc. is pleased to announce the release of version 5.0 of Stonefield Query for AccountMate. This release has numerous new features.
  • AccountMate Version 9.2 is now supported.
  • Stonefield Query has a new report type: gauge report. This allows you to create a gauge showing the current value of something compared to a target or goal value.
  • You can now create a "grouping" formula. A grouping formula is a powerful tool that allows you to create filtered summary values. For example, suppose you want columns in a report that show the sales for last year and the current year-to-date for each customer. Those aren't single values retrieved from the database. Instead, for the first column, use a grouping formula that sums up the sales records for each customer for last year, and use a similar one but for the current year for the second column.
  • The Inventory Control Location Item Details table now also appears in the Purchase Orders module.
  • The Inventory Items table now has a Total Quantity on Hand field that contains the total quantity on hand from all locations.
  • The Inventory Shipments related tables no longer appear in the Order Entry or Purchase Orders modules to avoid confusion with the shipments tables already in those modules.
  • You can now password protect the file created when a report is output to file or email.
  • You can now print to Dymo LabelWriter labels.
  • The Schedule Report function is now available only to advanced users.
  • A new output type is now available: Microsoft Excel - Full Format (*.xlsx), which creates Excel 2007 or later files. The former Microsoft Excel (*.xls) output type was renamed to Microsoft Excel 97-2003 - Full Format (*.xls) so you can tell them apart. The new type does not require Microsoft Excel to be installed to create the file.
  • The "red" error dialog that appears when something goes wrong now has a Help button that brings up the help with information about that dialog.
  • The Locations page in the Options dialog is now available only to administrative users.
  • The Subscription Expired dialog, which appears when you have subscription licenses and your subscription has expired, now has a "Don't remind me again" option; turn that option on to prevent the dialog from appearing again. Also, those licenses are now deactivated rather than being deleted, so if you renew your subscription, they are reactivated.

Monday, November 17, 2014

Stonefield Query for Sage Pro ERP Version 5.0

Stonefield Software Inc. is pleased to announce the release of version 5.0 of Stonefield Query for Sage Pro ERP. This release has numerous new features.

  • Stonefield Query has a new report type: gauge report. This allows you to create a gauge showing the current value of something compared to a target or goal value.
  • You can now create a "grouping" formula. A grouping formula is a powerful tool that allows you to create filtered summary values. For example, suppose you want columns in a report that show the sales for last year and the current year-to-date for each customer. Those aren't single values retrieved from the database. Instead, for the first column, use a grouping formula that sums up the sales records for each customer for last year, and use a similar one but for the current year for the second column.
  • You can now password protect the file created when a report is output to file or email.
  • You can now print to Dymo LabelWriter labels.
  • The Schedule Report function is now available only to advanced users.
  • A new output type is now available: Microsoft Excel - Full Format (*.xlsx), which creates Excel 2007 or later files. The former Microsoft Excel (*.xls) output type was renamed to Microsoft Excel 97-2003 - Full Format (*.xls) so you can tell them apart. The new type does not require Microsoft Excel to be installed to create the file.
  • The "red" error dialog that appears when something goes wrong now has a Help button that brings up the help with information about that dialog.
  • The Locations page in the Options dialog is now available only to administrative users.
  • The Subscription Expired dialog, which appears when you have subscription licenses and your subscription has expired, now has a "Don't remind me again" option; turn that option on to prevent the dialog from appearing again. Also, those licenses are now deactivated rather than being deleted, so if you renew your subscription, they are reactivated.

Stonefield Query for Sage 300 ERP Version 5.0

Stonefield Software Inc. is pleased to announce the release of version 5.0 of Stonefield Query for Sage 300 ERP. This release has numerous new features.

  • Stonefield Query has a new report type: gauge report. This allows you to create a gauge showing the current value of something compared to a target or goal value.
  • You can now create a "grouping" formula. A grouping formula is a powerful tool that allows you to create filtered summary values. For example, suppose you want columns in a report that show the sales for last year and the current year-to-date for each customer. Those aren't single values retrieved from the database. Instead, for the first column, use a grouping formula that sums up the sales records for each customer for last year, and use a similar one but for the current year for the second column.
  • The Inventory Control Location Item Details table now also appears in the Purchase Orders module.
  • The Inventory Items table now has a Total Quantity on Hand field that contains the total quantity on hand from all locations.
  • The Inventory Shipments related tables no longer appear in the Order Entry or Purchase Orders modules to avoid confusion with the shipments tables already in those modules.
  • You can now password protect the file created when a report is output to file or email.
  • You can now print to Dymo LabelWriter labels.
  • The Schedule Report function is now available only to advanced users.
  • A new output type is now available: Microsoft Excel - Full Format (*.xlsx), which creates Excel 2007 or later files. The former Microsoft Excel (*.xls) output type was renamed to Microsoft Excel 97-2003 - Full Format (*.xls) so you can tell them apart. The new type does not require Microsoft Excel to be installed to create the file.
  • The "red" error dialog that appears when something goes wrong now has a Help button that brings up the help with information about that dialog.
  • The Locations page in the Options dialog is now available only to administrative users.
  • The Subscription Expired dialog, which appears when you have subscription licenses and your subscription has expired, now has a "Don't remind me again" option; turn that option on to prevent the dialog from appearing again. Also, those licenses are now deactivated rather than being deleted, so if you renew your subscription, they are reactivated.