Choose the question
Start with the figures the team needs every morning
Ask your database team for an approved SQL Server view that answers one question. Check its figures against the system your colleagues already use, then build the dashboard around that view.
IntelliFront BI can use the view to give supervisors a shared dashboard for their handover.
Imagine an equipment library that lends devices to wards across two hospital sites. At the morning handover, the coordinator wants to know how many loans are open and how many are past their due-back time. The loan system already tracks them, but the coordinator puts the two site figures together by hand.
The first dashboard can answer that question with one row per site. When a count needs investigating, staff can open the loan system and follow up there. That gives the database team a clear brief for what to make available to the dashboard.
If the loan system can already show both sites in one report and the right people can open it, that report may do the job. Build a new view when it solves a problem people still have at handover.
To build an NHS dashboard from SQL Server, ask the database team to prepare a view with one row per open loan. A view gives the team a named place to maintain the rules. Here, it excludes returned and cancelled loans and supplies a site code and the due-back time, where one has been recorded. Agree how transfers are assigned so the same loan appears at only one site.
Check a few records with the coordinator before building the chart. A loan without a due-back time still belongs in the open total. Show missing deadlines separately so the team can see which records need attention.
Have the database team give the connection used for reporting read access to the approved view. Check that account’s effective access before using it. Microsoft’s SQL Server permissions guide.
In IntelliFront BI, set up a Database Query dataset using the approved connection. Start with a query that returns the counts for each site, then use the dataset in a dashboard grid. Give the columns the names colleagues use at handover. IntelliFront BI dataset setup guide.
Keep the counts together. If East Site shows three open loans, one overdue and one without a deadline, the coordinator knows one loan is overdue and another needs its due-back time checked. Individual loan details can stay in the system where staff already work.
This example assumes the database team has created reporting.vw_OpenEquipmentLoans with one row per open loan. The due-back times in this example are stored in UTC. Use your own approved view and field names.
SELECT
SiteCode,
COUNT(*) AS OpenLoans,
SUM(CASE WHEN DueAtUtc < SYSUTCDATETIME()
THEN 1 ELSE 0 END) AS OverdueLoans,
SUM(CASE WHEN DueAtUtc IS NULL
THEN 1 ELSE 0 END) AS LoansWithoutDueDate
FROM reporting.vw_OpenEquipmentLoans
GROUP BY SiteCode;The query checks whether a loan is overdue when it runs. If you use a cached dataset, a loan can pass its due-back time before the next query run updates the count. The query returns only sites that have open loans. If the team needs sites with zero on the list, ask the database team to include them in the query design.
Show when the figures were checked or retrieved, with a label that describes that event. Test that the time still makes sense when the dashboard uses cached data.
Compare the dashboard with the loan system for East Site. Use the same cutoff and rules for what counts as open and overdue. A live count can change while a cached dashboard still shows an earlier result. If the totals disagree, ask the database team and coordinator to compare the eligible loan references in the systems they’re authorised to use. That gives the database team something concrete to investigate.
Then open the dashboard as a supervisor would. IntelliFront BI lets you assign dashboard access by group. Test with a member of each intended group and with an account that should have no access. If different sites need different figures, test that restriction as well as whether the page opens. IntelliFront BI dashboard setup guide.
Ask the coordinator what they’d do if a site shows an open loan without a due-back time. If they can explain the figure and find the right place to check it, the dashboard is doing useful work.
At the next handover, the coordinator has both site counts in one place. The team can see where to follow up and go back to the loan system when it needs individual records.
Pick one approved SQL view and one figure a colleague already checks. If you’re evaluating IntelliFront BI, use the guide to plan the connection and how you’ll reconcile the figures. The NHS pharmacy reporting evaluation shows how to bring IT and readers into the same trial.
CEO, ChristianSteven Software