Excel, PowerQuery, and the Cireson ServiceManagement DB
So if you want something for let's say a large monitor in a public area, you'll have to do something a bit more custom. Which is why I turned to Excel, PowerQuery, and leveraging the ServiceManagement database. So I came up with this -
Now, these stats aren't anything terrifically complicated but near real time stats should be really simple to digest which is why I'm a fan of this. However I do have to concede that I decided to use SQL queries inside of PowerQuery to generate some of these results and crack some VB open for this, because what I wanted is a "stoplight" indicator of how Support Groups are performing throughout the day. So the colored boxes that show "Resolved Today" actually change color based on if they are:
- significantly lower (less than 90%)
- lower (90%-100%)
- equal (100%)
- greater (100%-110%)
- significantly greater (110% or greater)
...than the Created Today count. It's nothing more than a really big simple IF statement that is triggered when the workbook automatically refreshes/recalculates (which happens every 15 minutes) and then changes these text box colors. For some added effect, the inner bounds (lower and greater) change to a gradient instead of a solid color.
Now there is still a degree of required understanding of how the ServiceManagement DB is laid out. Since the schema isn't available, a few SELECT TOP 10 * queries are mandatory to guess your way through it. Then again, you could also just use PowerQuery (again, no code approach) and guess your way through that way. I'd say that the big advantage of PowerQuery is that if you understand the least bit about Excel you get all the benefits of the PowerBI platform but in a fashion that doesn't require a significant investment of time in learning how the tool works/learning a wholly new tool. That and you can combine multiple data sources into a single file so you could do something like point the workbook at Cireson's ServiceManagement DB, the stock SCSM DW, a SharePoint List, Project Server, etc. and further build out the picture of IT. But again - you can absolutely do all of what I just said with PowerBI. Otherwise, I'd say use what you know!
I'm still working on getting a stripped down, plug 'n play version of this Excel file to share. So in the interim, if this is something you're interested in I can certainly post the queries at anyone's request so you can build your own Excel dashboard!