Wednesday, June 6, 2018

Stonefield Query for ALERE 7.0

We are pleased to announce the release of version 7.0 of Stonefield Query for ALERE. 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 HEAT Version 7.0

We are pleased to announce the release of version 7.0 of Stonefield Query for HEAT. 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.

Wednesday, May 30, 2018

Stonefield Query for AccountMate 7.0

We are pleased to announce the release of version 7.0 of Stonefield Query for AccountMate. 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.

Wednesday, May 23, 2018

Stonefield Query for GoldMine Version 7.0

We are pleased to announce the release of version 7.0 of Stonefield Query for GoldMine. 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.

Thursday, May 3, 2018

Reporting4Act! Version 7.0.6696

Note that as of version 7.0, Stonefield Query for Act! is now known as Reporting4Act! - Powered by Stonefield Query.

We are pleased to announce the release of version 7.0.6696 of Reporting4Act!. This release includes all the previous version 7.0 features, including compatibility with Act! version 20.1. There are lots of new features on the list.

Version 7.0.6696

  • A new Displaying Rich Text sample report was added, demonstrating how formatted text can be included in report output.

  • Several "Details" and "Note" field captions have been updated to "Details (Text Only)" and "Note (Text Only)", to clarify their behavior as compared to the existing "Details (With Formatting)" and "Note (With Formatting)" fields.

  • Several documentation topics have been updated.

Version 7.0.6687

  • Several new sample reports have been added, and all sample reports using the Act! 2010+ data provider have been refreshed.

  • A new "My Record Header" template has been added, demonstrating how to include your My Record information in a report layout.

  • A utility for rebuilding the Act! database views (KQCRebuildOLEDB.exe) is now distributed in the Utilities folder.

  • "Multi" fields for reporting on Act! multi-select picklist fields are now supported for Act! custom tables.

  • Several items involving direct communication with Act! through a plugin now run more quickly than they did previously.

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

Version 7.0

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

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

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