Report automation goes to a whole new level when you discover [range names] in the Analytics Edge Core Add-in! This capability is embedded throughout the product, and it allows you to grab values from your data to be used in subsequent functions, like filtering one query based on the result of another query.
In programming, these would be termed ‘variables’, but I have used the term ‘range name’ since they are similar to Excel’s named ranges. In Analytics Edge, a range is a column of values, and the range name is simply the column name, identified by the square brackets similar to how you can refer to a column in an Excel Table (e.g. “[Sessions]”).
Using Data From A Worksheet – Read Worksheet
To populate a range name, you can use the Read Worksheet function to grab values from one of your worksheets. Simply check the option to Assign ranges names to each column.
Now you can use the range name like [Pageviews] in a function wizard, and the macro will substitute the value of 100 when you run the macro. Change the number in the worksheet, and the macro uses the new number when you refresh!
[Range Names] in Use
Range names can be used pretty much in any wizard field that you can type into. As a simple example, the three columns in the example above can be used in a Filter wizard to set up dynamic filtering.
You can even use range names to determine which worksheet or cell to write the results to.
Real Automation: Range names can be used in a Repeat Macro in this way to query different accounts and write the results to different locations. Just set up a worksheet with the list of accounts and location you want the results written to, then use range names in your query and write to worksheet functions. Boom! Add or remove accounts over time simply by changing your list.
Other Ways To Specify Range Names
Using the Read Worksheet function every time you need a range name can be limiting, so the add-in provides some easier alternatives.
Read Worksheet function: this function has an option that can assign ranges names to each column you read. This is particularly useful when you want to read a setup or configuration worksheet to customize your macro.
Repeat Macro function: as already mentioned, the Repeat Macro function automatically set ranges names for every column in the source used for the repeat macro itself. Every loop of the repeat macro can be customized based on settings in the source table.
Table Name function: this function include an option to assign range names to the table of data in memory. This allows you to grab values at any point in your macro for use later in the same macro.
Current Table: if there are no previously assigned range names, the add-in will look at the current table in memory for a matching column. This is really helpful if you want to grab the result of one query and use it in another query, like getting the detailed transactions for the top selling product, or the traffic sources for the most-visited page.
There are endless combinations for using range names in Analytics Edge macros — don’t be afraid to try a few out!