Applying SQL and Power BI

reporting to financial services

Mid Yorkshire Hospitals NHS Trust, North East and Yorkshire, Acute

The problem

The finance team needs to be able to effectively and efficiently analyse millions of transactions to gain key insights. Being limited to a finance system without a modern analytics tool, most current processes involve lengthy downloads into spreadsheets to manually process data using Pivot Tables and vlookups.

The challenge

  • How might we hold our data so we are not reliant on disparate and disconnected sources of data or time consuming ledger extracts?
  • How might we hold data in a way that we know what we hold and can manage and redact that data according to date retention requirements? Some 200,000 excel spreadsheets on a file server built up over 20 years is not an acceptable way to manage data in 2022.
  • How might we transform from being data rich but information poor, to information rich? Information needs to be at your figure tips when you need it, not a few weeks later once a time-consuming analysis has taken place.

The outputs

  • Information rich reporting allowing the analysis of millions of transactions in real time.
  • Interactive dashboards – being able to take an interactive accounts receivable dashboard to a meeting with our director of finance and being able answer every question in-situ was a revelation.
  • Power BI data models with transaction level detail all linked to cost centre and general ledger hierarchies for:
    • accounts payable dashboard
    • accounts receivable dashboard
    • unprocessed invoices dashboard.
  • All transactions dataset – linked to PowerBI and Power Pivot – knitting together all subledger extracts: general ledger, accounts receivable, accounts payable, payroll, budgets, balances brought forward, goods received not invoiced, Supply Chain. This was used to identify all transactional samples for audit as well as the agreement of balances dataset for the TAC returns.
  • Account payable spend dashboard – showing purchase order /non-purchase order, PEPPOL compliance, as wells as geographical information.

The outcomes

  • Information rich reporting allowing the analysis of millions of transactions in real time.
  • Interactive dashboards – being able to take an interactive accounts receivable dashboard to a meeting with our director of finance and being able answer every question in-situ was a revelation.
  • Power BI data models with transaction level detail all linked to cost centre and general ledger hierarchies for:
    • accounts payable dashboard
    • accounts receivable dashboard
    • unprocessed invoices dashboard.
  • All transactions dataset – linked to PowerBI and Power Pivot – knitting together all subledger extracts: general ledger, accounts receivable, accounts payable, payroll, budgets, balances brought forward, goods received not invoiced, Supply Chain. This was used to identify all transactional samples for audit as well as the agreement of balances dataset for the TAC returns.
  • Account payable spend dashboard – showing purchase order /non-purchase order, PEPPOL compliance, as wells as geographical information.

The Innovation

Read the full innovation on applying SQL and Power BI reporting to financial services

All the documents and resources relating to this innovation can be found in the downloads section at the bottom of the page.

Read now

What the peer-reviewers said

“A transformative innovation which embraces technology and enhances the skills of the department.”

“A exciting game changer in financial reporting, analysis and interpretation.”

“This innovation allows real time analysis and can turn queries around much quicker.”

Get Involved Today

Downloads

Here are all of the documents and resources related to this innovation

Innovation D3

Applying SQL and Power BI reporting to financial services

Download

Supporting evidence D3

Examples and visual guide – PDF

Download