Column Chart showing opened and closed incidents per day

Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
Hi, I'm trying to create a chart that visualizes wether our backlog is growing or shrinking over time. I start by showing the
number of opened and closed incidents per day for the last 60 days. I know how to do it for Opened and Closed in different charts, but I want them overlaid in the same chart. I also want to create a chart that shows the number of non-closed incidents on that day (our backlog).

This is what I have so far:
SELECT CONVERT(Date,ClosedDate) as Closed
   FROM WorkItem wi
   WHERE DATEDIFF(dd,ClosedDate, Getdate()) <= 60
AND wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'
AND ClosedDate > 0
SELECT CONVERT(Date,Created) as Opened
   FROM WorkItem wi
   WHERE DATEDIFF(dd,Created, Getdate()) <= 60 AND wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'
I group by Closed and Opened respectively.



  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Versions 8.0 (or maybe 7.0+) has Cireson analytics included. If you review those dashboards there is one call Work Item trend.
    The Nice thing about analytics is you can edit the dashboard to pull what Cireson SQL statement to build the widget. That should get you started in building similar widgets for your business cases. 
  • Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
    Thanks for your time and effort Brian,

    The Dashboard you refer to is also showing the Opened and Closed items in different charts, which is why I wanted to create my own...
  • Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
    I have the SQL Table Widget query sorted out, see but I can't figure out the SQL Chart Widget chart type and parameters to visualize it the way I want.
Sign In or Register to comment.