Hacking the Query Worksheet

The current release of the Analytics Edge Basic Add-in, Standard Add-in and Core Add-in (Quick Queries) allow you to directly edit a worksheet containing all of the query details. This is an advanced topic, so caution is strongly recommended.To expose the worksheet, you can select the Edit Mode > Show Query Worksheet menu from the Analytics Edge ribbon, or simply use Excel’s Unhide function to unhide the worksheet (“ᴁ Analytics Edge Queries “).

The left column contains the parameters used by the queries, and each column after that contains a query. You should use the query wizards to create a sample of queries to see how the various parameters are used. They are mostly undocumented and subject to change, but backward compatibility is usually maintained.

You can manually change any query field, but parameter names that are not recognized will simply be ignored (no error message). Be consistent with the query name (top row), and the worksheet and topleftcell for the WriteToWorksheet function.

You can also enter Excel formulas in any cell, but you will have to change the cell from Text formatting to General formatting for the formula to be recognized as such.

You can copy a query column, but you will need to change the name (top row) and worksheet/topleftcell for the WriteToWorksheet, as well as whatever difference you want between the queries.

Note that many of the parameters use a combination of API terminology and/or Analytics Edge-specific conventions, and that those conventions may differ between connectors (and will change over time).

Keep a backup of your workbook before you start. Good luck.