Tuesday, April 24, 2018

Stonefield Query for Legrand CRM Version 7.0

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

  • 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 for Sage Pro ERP Version 7.0

We are pleased to announce the release of version 7.0 of Stonefield Query for Sage Pro ERP. There are lots of new features in this release.

  • If you are using FoxPro data, you can now tell Stonefield Query to use an UNC path rather than a drive mapping (which often causes problems because drive mappings aren't supported in scheduled reports) by adding a section in Data.ini that maps the drive letter to the UNC path.

  • 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 for Sage 300 Version 7.0

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

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

  • There are now relationships between the Tax Tracking (TXAUDH) and the Tax Authorities (TXAUTH) tables, the Sales Orders (OEORDH) and Ship-To Locations (ARCSP) tables, and the Shipment Day End Details (OESHTD) and Shipments Day End (OESHTH) tables.

Thursday, April 19, 2018

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.

Friday, April 6, 2018

Grouping Values Together

Sometimes, you want values grouped together. For example, rather than displaying individual ages, such as 25, 26, 27, and so on, you want them grouped, such as "Under 20," "20 - 29," "30 - 39," and so on. To do that, create a formula using an ICASE statement that determines what to display if the value falls into a certain group. For example:

ICASE(Student.Age < 20, " Under 20",
Student.Age < 30, "20 - 29",
Student.Age < 40, "30 - 39",
"40 +")

Note the space at the start of " Under 20"; this ensures it sorts before "20 - 29". Because the expression checks for the numbers from lowest to highest, it doesn’t have to check for ranges such as BETWEEN(Student.Age, 20, 29); the earlier comparison eliminated lesser values. Also notice there is no final condition: that means “any other values”, which is displayed as “40+”.

Here's another example: this groups car manufacturer by country:

ICASE(INLIST(Auto.Make, "Ford", "GM"), "U.S.",
INLIST(Auto.Make, "BMW", "Volkswagen"), "Germany",
"Other")

Note that if there are a lot of groups or a lot of values in each group, the expression may be longer than 255 characters, which is the maximum length of an expression. In that case, create a function that does the work and call the function from the formula. For example, here is the expression for a formula handling age groups:

GetAgeGroup(Student.Age)

Here is the code for that function:

lparameters tnAge
do case
    case tnAge < 20
        return " Under 20"
    case tnAge < 30
        return "20 - 29"
    case tnAge < 40
        return "30 - 39"
    case tnAge < 50
        return "40 - 49"
    case tnAge < 60
        return "50 - 59"
     case tnAge < 70
        return "60 - 69"
    case tnAge < 80
        return "70 - 79"
    case tnAge < 90
        return "80 - 89"
    otherwise
        return "90+"
endcase

Thursday, March 15, 2018

Stonefield Query for Act! Becomes Reporting4Act!

Stonefield Software is pleased to announce a deal with Keystroke Quality Computing: effective March 15, 2018, Keystroke is the exclusive worldwide distributor of our Act! reporting solution. “Stonefield Query for Act!” has been rebranded to “Reporting4Act! - Powered by Stonefield Query” and is available in three new tiers: Pro, Premium, and Ultimate, designed to fit all budgets. All tiers of Reporting4Act! include hundreds of built-in Act! reporting templates and support the custom tables in the new Act! Premium Plus 20.1 right out of the box.

This partnership combines the distribution reach of the world’s #1 Act! Reseller with the technology of the best Act! reporting software on the market, and completes Keystroke’s suite of CRM add-on products including reporting, database design, and workflow automation. For more details, see Keystroke's blog post.

Here are answers to some common questions about this deal.

What happens to my Stonefield Query for Act!?

Nothing: you can continue to use it as you did before. When you upgrade to a new version, you’ll be upgrading to Reporting4Act!, as it replaces Stonefield Query for Act!. Speaking of which, you’ll want to upgrade to version 7.0, because it adds a lot of new features, including support for custom tables added in Act Premium Plus version 20.1 and later.

How does support work?

The same as it did before: Stonefield Software continues to provide support for both Stonefield Query for Act! and Reporting4Act!. You continue to request support by creating a support ticket on our web site.

How does software maintenance work?

If you purchased Stonefield Query for Act! from Stonefield Software, we will contact you when your software maintenance is due for renewal as we did before. If you purchased Reporting4Act! from Keystroke, they will contact you. Software maintenance works the same regardless who you purchase it from: you continue to receive updates, technical support, and use of subscription licenses as long as your software maintenance is current.

Why did Stonefield do this?

Keystroke is a world-renowned Act! reseller with a much bigger global reach than Stonefield. They also have a larger marketing and sales team. As a result, they can reach more resellers and end-users than Stonefield could ever hope to. We feel that this partnership makes both companies better and allows us to put our reporting solution into the hands of more people than ever.

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.