Using the Repeat Macro Function

repeatmacroThe Analytics Edge Core Add-in v2.20 included an enhancement to the Repeat Macro function, making it easier to reports that combine a series of queries with one refresh.

Operation is simple: when setting up a Repeat Macro, you identify a source worksheet from which to get a table of values. The rest of the macro will be repeated for each row in the table. Have 30 clients? Create 30 rows.  The macro will be repeated for each row in turn.

A number of other Analytics Edge features need to be leveraged to make this truly useful, and there are a few things you need to consider when building a solution.

Macros must be stand-alone

All Analytics Edge macros must be able to operate by themselves. To pass information into a macro, you can read the data from a worksheet, and that is how the Repeat Macro works. When it reads the worksheet, it puts the values from the first row into Analytics Edge named ranges, similar to Excel named ranges. You use them in a similar fashion — when you need to get one of the values, you enter the column name surrounded by [square brackets].

Lets say you wanted to process 30 websites, and for each website, you needed the siteUrl. In your reference worksheet, you would create a column named “siteUrl”, and in the actual query for website data, you would enter “[siteUrl]” in place of the website value. As the Repeat Macro steps through the rows in your worksheet, the [siteUrl] will be replaced by the value from each row in turn. The first time through the macro, it would get the first row’s value; the second time through, the second row’s value, and so on.

Writing Separate Result Sets

Of course, at the end of the macro, you will need to write out your results in some way. You can write each result set to a separate worksheet, PDF file ,or copy of the workbook. You would use the [siteUrl] value again here to make sure the worksheet or file name is different for each website.

Combining Results

You could also merge all of the query result sets together using the AppendToWorksheet function — pick a target worksheet and append the results of each loop to the bottom of the worksheet. The target worksheet can be cleared by the Repeat Macro function before the first lop if desired by selecting the option.

If you need to be able to tell each loop apart, the Arrange – Insert a column function could be used to insert a new column with an initial value of “[siteUrl]”, for example, which would populate that column with the website that the query was for.

Macros are executed in order

The Repeat Macro is just like any other Analytics Edge macro, and it is executed in alphabetical order when Refresh All is clicked, or when a Scheduled Refresh is triggered. This means you can create a macro that runs before the Repeat Macro to set the stage. Maybe it downloads all the campaigns that were run in the past month and saves them to a worksheet; then the Repeat Macro could read that worksheet and generate a report for each of those campaigns.

Making a Repeat Macro

To create a Repeat Macro, create a new macro and then select FileNew Repeat Macro from the Analytics Edge ribbon, and the wizard will open. Select the worksheet and/or range of cells to use and click Finish. A new function step will appear at the top of the Task Pane. Note that if you already have function steps in your macro, the Repeat Macro function will be always be placed at the top.

The worksheet selection options are the same as the Read Worksheet function. You can choose the whole worksheet, a table identified by the top-left cell (recommended if the table is clearly separated from other data and may change in size), or a specific range of cells.

You can choose to process 1 row at a time, or if your other macro functions can handle it, more. If you choose more than 1 row per cycle, functions that can use a list will use all of the values, but the rest will only use the values of the first row in the set.