Monthly Reporting

There is capability to report user interactions based on visited school pages within the FBIT service by harnessing transactional data from Azure monitor logs (via a log analytics workspace) and relational store in Azure SQL database. An automated ETL process implemented in Excel’s Get & Transform (Power Query) facilitates the production of a monthly summary report for intended stakeholder(s).

Stakeholder(s)

Product Owner - Schools Financial Benchmarking

Get started

Locate the FBIT Report TEMPLATE.xlsx file found in this DfE Sharepoint Location.
Open the report with the Open in app option and not the browser option.
In the Data Ribbon of excel, locate Get Data button, click on the drop down to select Data Source Settings.
As seen below, select Edit Permissions and provide credentials for access to data source.
- Use organization account for log analytics and database for Azure SQL connection
Upon successful credential log-in, still on the Data ribbon, locate and select the Queries & Connections button.
Confirm the seven queries in the Queries & Connections view by the right hand side of excel work book.

The image below signifies successful access to the data sources
On the Data ribbon, locate and select the Refresh All button to refresh all seven queries.
Confirm refresh by hovering on each query to see Last refreshed timestamp, ignore Load status and Data Sources values as it has no correlation with the data refresh status.

Prepare Report

Total Schools

The workbook’s SchoolData sheet is updated and loaded (overwrite) after a successful refresh. The Financial table in the data SQL database is updated after a new financial data load with a corresponding RunId. This ensures that we are reporting only on schools that made financial submission for the reporting year. It is typical to have the same records of school data loaded through out an academic/financial year.

CFP Completion

The workbook’s CfpData sheet is updated and loaded (overwrite) after a successful refresh.

School Engagement

In the SpendingPrioritiesRequestData sheet of the workbook, navigate to the end of the existing SpendingPrioritiesRequestData table and select an empty cell preferably in column F.
Load the SchoolEngagement query to the existing worksheet.
- To achieve this step, right click on the SchoolEngagement query under the Queries & Connections view,
- Select Load to,
- In the Import Data pop-up view, select the options as shown in the below image and click on the OK button to initiate load
Copy the loaded data excluding headers and paste value at the end of existing table to append data.
Delete the loaded data after append.

Pivot Tables

In the PivotTables sheet of the workbook, refresh all four pivot tables.
In the Summary sheet of the workbook, insert a row above Total Unique Schools of the School Engagement summary.
Copy the values from PivotTables sheet into the relevant section in Summary

NOTE:

For School Engagement summary, FBIT Total Schools (all time) and Percentage of FBIT engaged are calculated rows, do not overwrite but update formula if required.

For CFP completion summary, Total plans created and # Schools with plans are calculated rows, do not overwrite but update formula if required.

School Features, Trust Features, LA Features and SfpData

In the Summary sheet of the workbook, select preferably cell H4.
Load the SchoolFeatures query to the existing worksheet.
- To achieve this step, right click on the SchoolFeatures query under the Queries & Connections view,
- Select Load to,
- In the Import Data pop-up view, select the options as shown in the below image and click on the OK button to initiate load
Repeat steps 16 and 17 to load TrustFeatures, LAFeatures and SfpData respectively.
Copy the loaded data excluding headers and paste values to overwrite the respective existing summary report.
Upon overwrite completion, delete the loaded data (columns H to J)
Save the Excel workbook.
Make a copy of the saved file and save to the same DfE Sharepoint Location.
- Rename file to match FBIT Report - yyyy-mm, for instance, if reporting for the month of June in the year 2025, file name should be FBIT Report - 2025-05
Inform / share report with stakeholder(s).

Report Queries

School Engagement

On p01 Log Analytics workspace, run the following KQL query to cover whole of the last month. The maximum number of rows being shown needs to be updated in the query results in order for them to not be truncated.

let time_start = startofmonth(datetime(now), -1);
let time_end = endofmonth(datetime(now), -1);
GetEstablishmentRequests
| where
    Establishment == "school"
| where
    ResultCode == 200
| where
    Feature == "spending-priorities"
| where
    TimeGenerated between (time_start .. time_end)
| order by
    TimeGenerated asc
| project
    Urn = Identifier,
    ExcelDate = format_datetime(TimeGenerated, 'dd/MM/yyyy HH:mm:ss')

NOTE: Although the query above could be modified to capture the whole of the past year, this is not advised due to potentially exceeding the row limit if executing from within Log Analytics in Azure Portal.

Total Schools

On p01 database run the following SQL query, remember to update RunId to match the reporting year.

SELECT
    [URN],
    [OverallPhase],
    [FinanceType]
FROM [dbo].[Financial]
WHERE [RunId] = '2025' -- update RunId value to match the reporting year
ORDER BY [URN]

School features

On p01 Log Analytics workspace, run the following KQL query to cover whole of the year up to the end of the last month:

let time_start = startofmonth(datetime(now), -12);
let time_end = endofmonth(datetime(now), -1);
GetEstablishmentRequests
| where
    Establishment == "school"
| where
    ResultCode == 200
| where
    TimeGenerated between (time_start .. time_end)
| project
    TimeGenerated,
    Feature,
    Identifier
| summarize
    Visits=count(), UniqueVisits=count_distinct(Identifier)
    by Feature
| sort by
    Visits desc

Trust features

On p01 Log Analytics workspace, run the following KQL query to cover whole of the year up to the end of the last month:

let time_start = startofmonth(datetime(now), -12);
let time_end = endofmonth(datetime(now), -1);
GetEstablishmentRequests
| where
    Establishment == "trust"
| where
    ResultCode == 200
| where
    TimeGenerated between (time_start .. time_end)
| project
    TimeGenerated,
    Feature,
    Identifier
| summarize
    Visits=count(), UniqueVisits=count_distinct(Identifier)
    by Feature
| sort by
    Visits desc

Local Authority features

On p01 Log Analytics workspace, run the following KQL query to cover whole of the year up to the end of the last month:

let time_start = startofmonth(datetime(now), -12);
let time_end = endofmonth(datetime(now), -1);
GetEstablishmentRequests
| where
    Establishment == "local-authority"
| where
    ResultCode == 200
| where
    TimeGenerated between (time_start .. time_end)
| project
    TimeGenerated,
    Feature,
    Identifier
| summarize
    Visits=count(), UniqueVisits=count_distinct(Identifier)
    by Feature
| sort by
    Visits desc

CFP Completion

On p01 SQL database, obtain the CFP records for the whole year up to the end of the last month:

SELECT
    [URN],
    [Year],
    [IsComplete]
FROM [dbo].[FinancialPlan]
WHERE   [UpdatedAt] > CAST(DATEADD(MONTH, -12, DATEADD(day, 1 - DAY(GETDATE()), GETDATE())) AS DATE)
    AND [UpdatedAt] < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
ORDER BY [URN], [Year]

SFB Decommissioning

On p01 Log Analytics workspace, run the following KQL query to cover whole of the year up to the end of the last month:

let time_start = startofmonth(datetime(now), -12);
let time_end = endofmonth(datetime(now), -1);
GetSfbReferrerRequests
| where
    TimeGenerated between (time_start .. time_end)
| project
    TimeGenerated,
    IsDeepLink
| summarize
    Visits=count()
    by IsDeepLink, Month = format_datetime(startofmonth(TimeGenerated),'yyyy/MM')

TIPS!!

To integrate, update or change log analytics query in Excel see External Reference

To integrate, update or change SQL query in Excel see External Reference