3305 Main Street Ste 019 Vancouver, WA 98663

Why do my TSSum or TSCount functions return the wrong value after optimizing my workbook?

Symptom

After upgrading to Office Connector version 2.01.0041 or higher, some workbook formulas return the wrong amounts.

Cause

There are two approaches that Office Connector can use to filter Timberline data. The first approach queries Timberline data in bulk, and then uses Microsoft technology to filter the results on the local workstation. The second approach delegates the filtering task to the Timberline server, which reduces the amount of data that travels across the network.

In order to dramatically improve performance, the filters for TSCount and TSSum functions are now formatted so they can be processed directly by the Timberline server. The Timberline server uses the Timberline ODBC driver, is case-sensitive. As a result some values that were “close enough” no longer satisfy the condition when processed by the Timberline server.

Resolution

 

Option 1

Correct the conditions to match the required case. For example, the following condition incorrectly identifies the transaction type using an upper-case V:

TRANTYP = 'Scheduled Value'

 

Changing the upper-case V to lower case will resolve the issue and allow you to benefit from the performance enhancement:

TRANTYP = 'Scheduled value'

 

Option 2

Turn the enhancement off by instructing Office Connector to use ADO filters instead of passing the condition directly to the ODBC driver. You will not benefit from this particular performance enhancement, but you may still benefit from the selective columns enhancement. In some cases, the selective column enhancement performs better.

 

Excel 2003 and prior

1. Click the File menu and then click Properties.

2. Click the Custom tab.

3. In the Name box, type UseFilters.

4. In the Value box, type 1.

5. Click the Add button.

6. Click OK.

7. Save the workbook.

 

Excel 2007

1. Click the Office Button in the upper left corner.

2. Click Prepare and then Properties. The document properties will be displayed at the top above the worksheet and below the ribbon.

3. Click the drop-down arrow next to Document Properties and click Advanced Properties...

4. Click the Custom tab.

5. In the Name box, type UseFilters.

6. In the Value box, type 1.

7. Click the Add button.

8. Click OK.

9. Click the X in the upper right corner of the document properties area to hide the document properties.

10. Save the workbook.