PivotChart using external datasource and display on SharePoint Online

Today, I’ve received a requirement like this:

“In Excel, display a chart, the data for that chart is getting from Azure database. Finally, display the chart on the SharePoint page. The chart should be updated when data is updated the Azure database.”

Easy, right?

data source=my.database.windows.net,1433;
initial catalog=my;
persist security info=True;
user id=my@my;
password=aaaa;
MultipleActiveResultSets=True;
App=EntityFramework”

use ngodemo

update Sample_Customers set Country = ‘Canada1234’ where Country = ‘Canada123’

select * from Sample_Customers

 

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets(“Sheet1”).PivotTables(“PivotTable1”).PivotCache.Refresh
End Sub

Sub updatepivottables()
Worksheets(“Sheet1”).PivotTables(“PivotTable1”).PivotCache.Refresh
ActiveWorkbook.Save

End Sub