Most NEXT Analytics customers combine multiple NEXT queries into a single report or dashboard workbook. This is usually a simple process of copying or moving worksheets from one workbook to another, but there are a few things you need to watch out for. This article details the process to problem-free consolidation. This is the third article in a series about using the free reports from NEXT.

Actions Sheets, Data Sheets and Sample Sheets

With NEXT, it is easy to make a custom query, and it can be just as easy to combine multiple queries into a consolidated report. NEXT works by executing script commands that are stored in worksheets with names ending in “_actions” – we refer to these as _actions worksheets, but they may have names like “NextAnalytics_actions”, or “visitors_actions”, or simply “_actions”. One of the last commands in the script tells NEXT to write the results to another worksheet, usually one with a similar name that ends with “_data”. In many of our examples, we include a third worksheet with simple cell references to the data worksheet that use formatting or charting to present the data in an attractive and useful way.

Making Worksheet Names Unique

To combine multiple reports together into a single workbook, you need to avoid duplications. The first challenge is to avoid duplicating the worksheet names. If you used NEXT to build your queries, you may have used unique names for each of them and this is not an issue. If, however, you start with workbooks that use the same names for the worksheets, you need to change them to make them unique. Here is the process:

  • Save your workbook with a new name to protect the original
  • Rename the _actions worksheet first. Any name can be used, but it must end with “_actions”.
  • Rename the data worksheet(s). Any name can be used. Excel will automatically change any cell references from your sample worksheet, but the script commands must be changed manually.
  • In the script commands on the _actions worksheet, identify the line(s) with the command “SaveInWorksheet”, and change the reference for the old data worksheet name to the new name. The reference should be the third parameter in the command (e.g. SaveInWorksheet,current,query1_data,Overwrite,invisible,1,1,unformatted,-1,-1)
  • Refresh the workbook using NEXT and ensure the data and sample sheets are updated properly

Note that some NEXT reports use a variable “WIDGET_NAME” used by the Add Widget utility (part of the Excel add-in) to modify the data worksheet referenced. You can use this mechanism or you can simply replace “WIDGET_NAME_data” with the new worksheet name.

Making NEXT Page Names Unique

In some advanced NEXT Analytics scripts, we establish interim ‘pages’ during the analysis so they can be referenced by other script lines. These ‘pages’ must be unique when all of the various _actions worksheets are combined into one workbook. Scan though the various scripts and look for any “PageCaption” commands that would identify these ‘pages’. If you have more than one _actions worksheet that uses the same PageCaption, then one of them must be changed. To do this:

  • Identify all of the “PageCaption” commands in the script (e.g. PageCaption,basepage)
  • Change the reference in the ‘PageCaption” command to use a new name (e.g. PageCaption,basepage1)
  • Search for and change any reference to that page name in the rest of the script commands. If in doubt, ask support – we’ll be glad to help (e.g. AppendPage,basepage1)
  • Refresh the workbook using NEXT and ensure the data and sample sheets are updated properly (hint: if you had refreshed just before the change, there should be no difference!)

Moving the Worksheets

If your workbook contains cell references between worksheets, you need to move the worksheets in unison so the cell references move with the sheets into the new workbook. If you do this step wrong, you may end up with cell references to the original workbook (instructions here are for Excel 2010).

  • Open the source and destination workbooks in the same instance of Excel (use Excel’s File – Open menu)
  •  Select all of the worksheet in the source workbook; hold the Shift or Ctrl key down while clicking on the worksheet tabs, or right-click on a tab and pick ‘Select all sheets’ from the popup menu
  • Right click on a selected worksheet tab and pick “Move or Copy…”
  • Pick the destination workbook from the dropdown and click OK. Note that “Create a copy” is NOT checked.
  • If the source workbook does not close automatically, then close it and do NOT save since you just moved some worksheets out of it
  • Check your destination workbook to ensure that cell references on the sample worksheets do not have a file prefix in them (e.g. bad cell reference with a file name “=’[ga-visitors-scorecard-week.xlsx]_data’!A6”)
  • Save your destination workbook
  • Refresh the workbook using NEXT and ensure the data and sample sheets are updated properly

Summary

If you created a new query with NEXT using a unique name, you can simply copy or move the actions and data worksheets to a common workbook. If you are using one of NEXT’s free reports, you should check that it references the web property desired (article link) and uses the proper login credentials (article link), then follow the procedures above to make sure the worksheet names and page captions are unique before moving the worksheets to your consolidation workbook.