Custom interactive dashboard in excel

The previous post on excel dashboards walked through an example.

Here I make my own custom Operations and Marketing dashboards using the concepts learned from there.

I’m fortunate in that I’ve got control over webservers, DB and storage, and can design my own log strategy.

TODO

share dashboard using Office365?
IP country and town lookups

Summary

We’re going to be using

Live connection to Azure SQL with a readonly user
Excel Table to store data
PivotTable for each chart
PivotChart
Map Chart
Slicer
Dynamic Labelling

Import Live Data

https://docs.microsoft.com/en-us/azure/azure-sql/database/connect-excel

Create a readonly user on Azure for safety. SO

CREATE LOGIN davereadonly WITH PASSWORD = 'secretpassword' 

-- master so can login to SSMS
CREATE USER davereadonlyuser FROM LOGIN davereadonly;

-- osr4rights
CREATE USER davereadonlyuser FROM LOGIN davereadonly;
EXEC sp_addrolemember 'db_datareader', 'davereadonlyuser';

Then login

To modify the cached connection string (which is global on the computer)

Data, Get Data, SQL Server Database There is a Power Query Editor where I can transform data if needed.

After loading I get 19,955 rows of data in an Excel Table.

Rename the Table and worksheet to Data.

Total Requests

Alt H O I auto format column widths

In Excel Table, Table Design, Summerize with PivotTable, New Worksheet

Pivot table: RequestsPivot Worksheet: Requests Pivot

Months field has been created for us.

Refreshing Data

Data, Refresh All

I have to press it twice - once to update data source, then to update PivotTables

Untick enable background refresh - this means we have to only press refresh once now.

If I auto refresh the Data source every minute, it works. However the PivotTable and PivotChart don’t automatically update.

It works when I open the file, but not in real time from the data source which does update every minute.

https://www.excelcampus.com/vba/refresh-pivot-tables-automatically/ looks like a VBA macro is the way to do it.

My preference is now:

PivotTable - refresh on file open
Data source - refresh on file open, background update is off (so once only for press refresh)

Then I’m happy to press the Refresh All button.

Multiple Machines

As expected the connection url and db name are stored in the worksheet, but the username and password is not.

To run the dashboard on anohter machine, enter the username and password, and make sure the Azure SQL allows connections from that IP address (set Firewall Rule in Azure Portal).