Merge Concerns When Converting To ClientSpace NEXT

Summary

With the advent of ClientSpace NEXT, NetWise has implemented Server Side Merges. This merge methodology shifts the burden of merging data into a document from the local computing hardware to the server, removing the need for the Security Policy Editor and dramatically increasing the speed of large merges. Changes in the merge technology have made the behavior of the office products, Excel in particular, a little less forgiving in regards to format of the data and the merge template documents. The following document describes the current known issues that may crop up with existing merges and how to fix them.

Merge Issues

Array Formulas:

Microsoft defines array formulas as "An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result." This makes array formulas convenient in dealing with large expanding rows of numerical data such as comp code rows in a merge and was often used on tabs called "Calculated" or "Normalized". Unforutunately due to the way the data is merged into the workbook server side, these array formulas no longer work. Any existing merge with array formulas will need to be changed to utilize the COUNTA methodology.

An example of an array formula would be:

"={Begin_CurrentCompCode:Row_CurrentCompCode}"

This would be replaced with:

"=COUNTA(Begin_CurrentCompCode:Row_CurrentCompCode)"

Formulas embedded below expanding row data:

Expanding row data is multiple columns and rows of data, most often variable amounts that will auto expand the table in excel - a good example of this would again be Pricing Comp Code data as the number of rows would change based on the number of Comp Codes returned to the workbook. Server side merge methodology is not as forgiving about placing formulas with name range references below this expanding row type data. Currently the expanding rows will cause any formulas with named range references contained in them to break. It is recommended you alter the Excel Worksheet to place such formulas above any expanding row data on the worksheet.

"Row_" named range issues:

It has been a NetWise best practice to preface the name of expanding row cells with "Row_" - previously including a named range without this preface in an expanding row table would not cause errors when merging data, it would just cause the data to not appear. Server side merges, however key off this naming practice and also group the data in expanding row tables based on the presence of "Row_" at the beginning of the data node name. All expanding row data will need to be formatted in this manner going forward, both from a SQL perspective as well as in the format of the named ranges on the template document. For an example of this type of expanding row data, click the image below.

Data does not merge to presentation layer:

Because we are no longer utilizing the Security policy editor to merge data into a workbook, the security functionality of this tool is no longer available. Depending on your Excel settings you may need to add an extra step to your process when opening an Excel or Word merge.

Allow the data to process to the other tabs by clicking the "Enable Editing" button at the top of the Excel Document

Should you encounter an issue with Server Side merges either in ClientSpace Classic or NEXT that you feel unable to resolve, please create a Client Service Case in the NetWise Extranet.