Best workflow to batch output formatted Excel reports and email them to recipients

[jt_edin]

My organisation is interested in using Alteryx to help create and distribute some confidential performance reports which will then be emailed to the correct recipient. Note that the data in each report is performance data about the recipient: each employee will be emailed a report on his or her performance.

The reports will be based on a consistent formatted Excel 'front end': one tab will have graphs and summaries, and the data sitting behind this will be on a separate data tab. My experience of Alteryx so far suggests it should be easy to overwrite the data tab whilst preserving the formatted front end. Naturally the graphs and summaries will update based on the new data. Furthermore, the data tab will be 'hidden' in Excel, although it's fine if the user figures out how to unhide this.

My question is really how best to set this up. Is it possible to start with just a single formatted report, and have alteryx overwrite the data in each iteration (x1000s), save as a new file with filename = recipient's name (I will be sure to unique these)? Or does each report need to be created beforehand? If Alteryx can save the report with a new file name, will it preserve both tabs in the XSLX?

The next task will be to email each report to its intended recipient. This should be fairly easy in Alteryx - the email address will be driven by the filename, or from a standalone table. In effect, our system will email one report to each of the 1000s of recipients. I hope my description makes sense!

Is this fairly routine? How best should I set up the Excel report so that it can be duplicated? Thanks!

[pcatterson]

As far as I know, you can't pull in excel formula or graphs into Alteryx.  Your output would only be able to handle the base data.  If you wanted multiple reports, you'd have to create them each and let Alteryx override the data tab of each file (summary page unchanged).  I don't know a way to email them out after that. 

Maybe instead, you could put the reports all in separate folders with different permissions so that people can only access their own folders.

[patrick_digan · Accepted solution]

@jt_edin I've attached a very simple example. You were exactly right about the thought process. Essentially: 1) Have an input tool bring in your data with all of the performance info. 2) Add a record Id. 3) Have a batch macro (not standard macro) save the data to your excel template, line by line. You can see I've hidden the data tab which Alteryx writes out that employee's data to. 4) Inside the batch macro, email that updated template file to that employee. You can see I'm just attaching the file template file I just updated for that employee.

The reason for the batch macro is that it allows you to update your template file and email it out line by line. This is just a simple example, but I think it shows what Alteryx is capable of.

EDIT: I should mention that you would need to give it email addresses in the data excel file as well as provide a from email address in the email tool for this to work when you run it.