3305 Main Street Ste 019 Vancouver, WA 98663

Some PivotTables change after upgrading from Sage 100 Contractor v19 to v20

Background

The upgrade from Sage 100 Contractor v19 to v20 represents a major change to the underlying database. Beginning with v20, Sage 100 Contractor now uses a Microsoft SQL Server database. Because of this, the database driver (which provides the connectivity between Liberty Reports and your Sage 100 Contractor data) is different and has different capabilities and limitations.

With the legacy database, column captions that contained spaces were returned by the underlying query with underscore characters in place of spaces. Post-refresh, the underscore characters in the column caption cells were then replaced with spaces. When a PivotTable was created using the results of the query, the edited column captions were used to identify the columns. Since the post-refresh operation does not occur when using Liberty Reports with the Microsoft SQL Server database (since column captions in queries may contain spaces), queries built using the legacy database will be displayed with underscores in the column captions. Consequently, PivotTables that were previously built using the results from the legacy database will drop columns that contained spaces. This will alter the design of the PivotTable because column groupings, row groupings, filters, and values may be removed if their captions previously contained spaces.

Solution

Refresh the PivotTable in order to update the list of available fields. Re-add any columns that were removed from the Columns, Rows, Filters, or Values lists of the PivotTable design.


See Also -
Common issues with custom reports after upgrading from Sage 100 Contractor v19 to v20