Dashboard Development
05/01/2019

Detail/Background

As part of a new project within the Adults area of a local council, It was requested for interactive dashboards to be developed to display a detailed level of Alerts and Warnings for clients within the safeguarding area of the Adults data.

Currently the data was displayed in a multitude of reports and with the data differing in detail and quality across the board.

Constraint

As part of the Alerts and Warnings it was a requirement that these Alerts and Warnings would both be counted and displayed by county, team, worker, area etc. During the process of development other user stories would feed into the development and would need to be integrated into the solution.

The KEY Intelligence Solution

During meetings with the project team and potential users of the dashboards flip chart designs of the dashboards were mocked up.
We then took those flip chart designs away and using BI  Dashboard Designer we created some mock Dashboard designs with static default data.

These were then published to a Development environment for the users to view and decide upon any changes they would like to the layout and design of the Dashboard.
If there were any changes requested then these were made and then new mock designs published for further checking.

Only once the design had been signed off by the users was the interactive part of the Dashboard developed. This meant adding queries direct from the database the Safeguarding data was stored in.

The components of BI Dashboard Designer have a large amount of options pertaining to interactivity and what data to display and what data to send where. These features allowed us to create a fully interactive dashboard that had differing results depending on what was selected.

To display the counts of each type of Alert and Warnings we set the display data of a Graph and Table component to the results of a query that contained the counts and type of Alert or Warning.
The query was set to insert the results of the query into an spreadsheet tab appropriately named so it was easy to refer to.

Once this was displayed we were then able to set the source and destination data. The source data was set to the Alert Type and Status. Eg Alert 1 and Red

These were set to a destination in the spreadsheet on another tab these were colored Yellow to denote a prompt value, we created another query that used these values as its filter values.
This query was set to return the clients within that Alert or Warning, these were then inserted into yet another spreadsheet tab. These would then be picked up as the Display data for the list view component and would show the users the details of those that had that Alert or Warnings.

As a further step as it was possible that clients could have more than one alert or warning when selecting a row it would insert that data into another cell and this would then be used in another query designed to return all Alerts and Warnings for that client.

The results of this query would populate another spreadsheet tab and would be the display data for the final list view on the dashboard.

That only facilitated part of the interactivity what we then had to do was to set a trigger cell for the queries to be re-triggered. These were set to be the cells that were destination cells for the graph clicks and list view clicks.

We had options of when to re-trigger the queries being when the cell changed or when it became a value, we chose when the cell changed so that it would re-trigger on all changes and be fully interactive.

To deal with the other constraint we added some other combo box components which would display a static set of scopes being County, Area, Team and Worker.

Then when an option was selected this would be inserted into a cell which would be come the trigger cell for a number of other queries and also a dynamically visible combo box. Each scope option was given its own combo box in order to display its own set of results.

To use the dynamic visibility property we had to ensure that the status cell for the combo box met the specific key we set, therefore the status cell for each combo box was set to the destination cell of the Scope combo box and each key was set as per one of the scope items.

We then created queries to return a list of teams, workers, areas etc which would have its re-query trigger cell set to be when the Scope combo box destination became the Key text for the query we were creating.

Once we had achieved this we had to set the destination for the selected team, worker, area etc to a spreadsheet tab cell in order for it to be used effectively elsewhere in the dashboard.

This was required in order for the counts of alerts and warnings, and the list of clients returned when selecting an Alert or warning to only show for the selected worker or team.
To achieve the above we edited all queries that would need to be narrowed down based on team, worker area etc to include some option filter values and linked these to the relevant cells on the spreadsheet.

They had to be optional otherwise we would have ended up with some queries returning zero rows and this was not an expected outcome.

This completed the development of the Dashboard so we then undertook a thorough testing of each click functionality and the data which was being entered. If we came across any dead clicks or data that was incorrect we ensured that we checked each element that would have interacted with that click to ensure it was working fully and made any tweaks that were required to fix the issues.

Upon completion of testing the Dashboard was published again to a development environment and the users informed so they could completed their testing of the dashboard.
As with the mock designs if any element or design changes were required these were made and the republished for checking again.

POSITIVE OUTCOME

All data is now contained in a single dashboard and has incorporated other much sought after data for each level of the business. A saving in the time it takes to review data and make decisions, whilst also enabling better data quality.

By using keyintelligence.uk, you agree to our use of cookies to enhance your experience.