A previous post looked at how the NHS "Statistical Process Control (XmR) Tool" Excel Template works with Public View's data, with data being manually exported from Public View and pasted into the Excel Template.
This post looks at doing that more efficiently, with a direct database connexion to Public View's Database Service!
Well we thought it was exciting enough to warrant an exclamation mark anyway.
This is the second of two posts looking at inserting Public View metric data into NHS tools for further processing. This is "the easy way" to achieve what the previous post did. Well it should be easy, but then Excel is massively flexible, which can make some specific things quite fiddly to achieve ...
Step 1: Set the Template Up
- Open the downloaded template and set it up as described in in the previous post Step A.
- Select the Review section and press the protect worksheet button to "unprotect" the workbook.
You'll need the spreadsheet password, which NHS staff can generally guess (or mail us and we'll tell you.
If you don't do this, the "get data" options are disabled.
Step 2: Get fresh data directly from Public View.
- Select the Data tab and pick
new query -> From Azure -> From Azure SQL Database
- In the dialog box which appears (see image below)...
Enter server name:
publicview.database.windows.net:1433Note that's a colon!
Enter database name:
- Click advanced options, then paste your query into the SQL statement box. For this example:
select Period, MetricValue from PV_Score
where MetricID = 6 and OrganisationID = 1
order by Period asc
- Press ok and the second _SQL Server database" dialog box appears as below.
- Select Database in the left hand green column as shown below.
- Enter your user name and password for your Public View Database Service account
- Press connect
It looks like this:
Within a second or two, you'll see the live metric data from the Public View Database System, as shown below.
- Click the Load To... option
It's highlighted in yellow in the image below.
This allows you to tell Excel where to paste the data.
See Notes below regarding the warning about "truncation".
Step 3: Tell Excel where precisely to put the data
- Clicking "Load To.." displays the dialog box shown below.
- select "Existing Worksheet"
- click the little icon to the right
Click the cell shown below, which is where the first data point will go, below the column header "Bed Occupancy -..."
Note that this is the "values" column, the dates column is to the left.
Press"OK" and Excel will paste the data into the sheet at the location you just specified. The result should look like this, with the newly loaded data pasted in a fetching orange colour according to Excel's defaults.
You only have to provide the Database user name and password once; Excel remembers it for subsequent data accesses.
You'll need the correct credentials for the Public View Database Service, but you'll also need to ensure you're working from a location which our firewalls allow. So this won't work from the offices of our competition, for example.
The truncation warning appears because Excel's only showing 9 decimal places, where as the original SQL data source (see image on the right) has 15. Well if you wanted to know, now you do.
The NHS Template gets the colour and value of the SPC Variation Icon, shown as an orange circle with an "F" in it here, wrong. See defect report.
- Updated Stroke Metrics 15 Oct 2021
- User Interface Updates 11 Oct 2021
- 287 Metrics 07 Oct 2021
- Automated Testing 20 Sep 2021
- New Release 09 Sep 2021
- New User Defined Reports 04 Sep 2021
- NHS Template Errors 26 Aug 2021
- Templates Direct Database Connexion 26 Aug 2021
- Statistical Process Control (XmR) Tool 25 Aug 2021
- Public View Database Service 23 Aug 2021
- User Defined Pages Beta 22 Aug 2021
- New Top Level SPC Icons 18 Aug 2021
- Public View World Wide 14 Aug 2021
- Public View G Cloud 13 Aug 2021
- Public View Database Service 06 Aug 2021
- More SPC Chart Goodness 05 Aug 2021
- A New Blog Site 04 Aug 2021
- Release 1.4, Database Access 29 Jul 2021
- New SPC Charts 02 Jul 2021
- New Maternity Indicators 02 Jul 2021