USE EXCEL TEMPLATES FOR ADVANCED FORMATTING

Creating Dynamic Dashboards

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.

Example Excel dashboard

The data is available in the ‘Data’ sheet:

Example Excel dashboard data

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.

Create query

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.

Define output

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.

Optional Excel settings

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.

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

Append to existing Excel file

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.

Choose different Excel sheet

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