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

  1. Open the downloaded template and set it up as described in in the previous post Step A.
  2. 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.

  1. Select the Data tab and pick new query -> From Azure -> From Azure SQL Database
  2. In the dialog box which appears (see image below)...
    Enter server name: publicview.database.windows.net:1433 Note that's a colon!
    Enter database name: BeltonBap
  3. 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
  4. Press ok and the second _SQL Server database" dialog box appears as below.
  5. Select Database in the left hand green column as shown below.
  6. Enter your user name and password for your Public View Database Service account
  7. Press connect

It looks like this:
Modal Sequence

Within a second or two, you'll see the live metric data from the Public View Database System, as shown below.

  1. 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".
    Load To Options

Step 3: Tell Excel where precisely to put the data

  1. Clicking "Load To.." displays the dialog box shown below.
  2. select "Existing Worksheet"
  3. click the little icon to the right

Load To Options

  1. 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.
    Load To Options

  2. 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.
    Load To Options


What the database knows

  • 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.