USE EXCEL TEMPLATES FOR ADVANCED FORMATTING

With AutoSQL you can write your query results directly to Excel and use a template to pre-define formatting.

In this example we will create a simple sales dashboard in Excel on top of the example Northwind database and save it as a template. We will setup AutoSQL to fill it with data and send the results weekly to the sales director by mail.

Step 1: Create a template

Let’s first create our template. We define two graphs based on two datasets.

The data is available in the ‘Data’ sheet:

Now let’s delete the data in de ‘Data’ sheet and save the file as ‘SalesYTDTemplate.xls’.

Step 2: Setting up AutoSQL

Our actionlist in AutoSQL needs to do two things:

• Create a new Excel file based on the template and put the monthly sales amounts in the data sheet at cell A1
• Append the created Excel sheet and put the sales per city in the data sheet at cell E1. After that, send an email with the results

Action 1: New Excel file based on template

Setup the query to report monthly sales. For more information on how to setup a query in AutoSQL have a look here.

Define the output. Set the output type to Excel and make it ‘Overwrite’ any existing file. This makes sure the template is used as a basis and a new file is created. In the Excel options define the template with the graphs and the data sheet. Reference the ‘Data’ sheet as the sheet to use. Use cell A1 as the starting point.

Action 2: Append data and send mail

Now the second action needs to report the sales per city. We define this in the query and go to the output section. In the output tab we define again Excel as the output type and the same file. However, now we choose to append the existing file which has been created in the first action.

In the Excel settings we don’t need to specify the Excel template anymore. It has been used in the first action to create the file that we are now appending. We do specify to use the ‘Data’ sheet again and put the data in starting cell D1.

We also define to send an email to the sales director with the report attached.

That’s it! Now schedule it and make your sales director happy!