Creating more complex charts in the Cireson Portal

john_doyle

The SQL Chart widgets are great for quickly generating simple dashboards. Sometimes however you may want to display more complex data. For example, you may wish to display a chart showing the backlog of active work items over the course of the last 10 days.

Here is an example of how that can be done in the Cireson Portal.

First, you need to define a data source. This query will give the number of open incidents, service requests and change requests which have not been resolved, completed or closed on each day.

;WITH lastTenDays AS <br>(&nbsp; SELECT TOP(10) CAST(DATEADD(dd,CAST(-(ROW_NUMBER() OVER (ORDER BY [object_id])-1) as INT),GETDATE()) as DATE) AS daydate<br>&nbsp;&nbsp;&nbsp;&nbsp; FROM sys.all_objects ),<br>&nbsp;WorkItemRange AS (SELECT wi.ClassId<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;, t.daydate<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM lastTenDays t<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LEFT OUTER JOIN WorkItem wi<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON CAST(wi.Created as Date) <= t.daydate<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND COALESCE(CAST(wi.ResolvedDate as Date),CAST(wi.ClosedDate as Date),CAST(wi.CompletedDate as Date),GETDATE()) > t.daydate <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE wi.ClassId in ('A604B942-4C7B-2FB2-28DC-61DC6F465C68','04B69835-6343-4DE2-4B19-6BE08C612989','E6C9CF6E-D7FE-1B5D-216C-C3F5D2C7670C'))<br>SELECT&nbsp; FORMAT(wir.daydate,'yyyy-MM-dd') [Date]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , SUM(CASE WHEN ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' THEN 1 ELSE 0 END) [Incident] <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , SUM(CASE WHEN ClassId = '04B69835-6343-4DE2-4B19-6BE08C612989' THEN 1 ELSE 0 END) [ServiceRequest] <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , SUM(CASE WHEN ClassId = 'E6C9CF6E-D7FE-1B5D-216C-C3F5D2C7670C' THEN 1 ELSE 0 END) [ChangeRequest] <br>FROM WorkItemRange wir<br>GROUP BY wir.daydate<br>ORDER BY 1<br>

Open the Portal as an administrator, and click on the Admin Settings link on the drop-down menu in the top right-hand corner. Click on the link for Dashboard Query Settings.
Click Add.
Enter 'Work Item Backlog' for the name
Set the Data Source as ServiceManagement
Copy/paste the SQL query from above.
Save the query.

Now open SQL Server and run this query against the ServiceManagement database.

select Id, Title<br>from DataSource<br>where Title = 'Work Item Backlog'

Copy the guid from the Id field returned by this query.

Now open the file explorer on the Portal server.
Browse to C:\inetpub\CiresonPortal\CustomSpace\views
Create a file called backlog.js in this folder.
Add this code to the file:

{<br>&nbsp;&nbsp;&nbsp; "Id": "backlog",<br>&nbsp;&nbsp;&nbsp; "layoutType": "semantic",<br>&nbsp;&nbsp;&nbsp; "pageTitle": "Backlog",<br>&nbsp;&nbsp;&nbsp; "view": {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "cssClass": "container-fluid",<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "content": [<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "cssClass": "row",<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "content": [<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "cssClass": "col-sm-12",<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "content": {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "cssClass": "row",<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "content": [<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "cssClass": "col-md-12",<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "type": "viewPanel",<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "ViewPanelId": "vp-backlog"<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ]<br>&nbsp;&nbsp;&nbsp; }<br>}

Next, open the subfolder called viewpanels in the views folder.
Create a file called vp-backlog.js in this folder.
Add this code to that file:

{<br>&nbsp;&nbsp;&nbsp; "Id": "vp-backlog",<br>&nbsp;&nbsp;&nbsp; "TypeId":"chart",<br>&nbsp;&nbsp;&nbsp; "Definition":<br>&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "content":<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;"title":"Backlog",<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "queryId":"298E8367-8E90-DC38-7DD6-BBB0A164DA49",<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;"series":[<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;{"field": "Incident", "name": "Incident", "type": "line", "stack": false},<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;{"field": "ServiceRequest", "name": "ServiceRequest", "type": "line", "stack": false},<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;{"field": "ChangeRequest", "name": "ChangeRequest", "type": "line", "stack": false}<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;],<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;"categoryAxis":{"field":"Date"},<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;"seriesColors":["orange", "blue", "green"]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp; }<br>}

Finally go back to the Portal and open the Navigation Settings menu from the drop-down menu in the top right-hand corner.
Add a new link to /view/backlog
Set the Title, behaviour, icon and visibility.
When you click on the link you should see:

You can specify a different chart type in the view panel. For example, this is the same data displayed as a stacking area chart.

Stephane_Bouillon

Thanks for your time and effort and for sharing John.

I also found a simple solution displaying the backlog by date, but for one workitem type at the time. While mine only uses the standard capabilities in the portal, your end result is nicer This is what I had come up with:

SQL Table Widget

WITH Dates AS (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventDate = DATEADD(MONTH,-1,CONVERT(DATE,GETDATE()))<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventDate = DATEADD(DAY, 1, EventDate)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dates<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventDate < CONVERT(DATE,GETDATE())<br>)<br>SELECT EventDate [Date], (SELECT COUNT(wi.id) -&nbsp; (SELECT COUNT(wi.id) FROM workitem wi&nbsp; WHERE CONVERT(DATE,wi.closedDate) <= EventDate AND wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68') FROM workitem wi&nbsp;WHERE CONVERT(DATE,wi.created) <= EventDate AND wi.ClassId= 'A604B942-4C7B-2FB2-28DC-61DC6F465C68') [Backlog]<br>FROM Dates<br>group by EventDate

SQL Graph Widget - Query Name: Service Request Backlog Trend, Chart Type: Area, Category Field: Date, Value Field: Backlog


What would be needed to achieve the same result as yours is to be able to have multiple value fields columns for a line, bar or area graph and I think a check-box dropdown for the value field would be ideal

Thanks again,
Stephane

john_doyle

@Stephane_Bouillon it would be good to be able to define multiple series for the chart. I agree. I like the work you've done in any case!

Karen_Bruster1

@john_doyle I tried to run the SQL query to get the GUID and it returned a blank.Any Ideas?

Brian_Wiest

When running the query in SQL Management studio does it return a data set?

Karen_Bruster1

@Brian_Wiest No it basically is blank, only think it shows are the tiles, id and Title

James_Johnson

Hi @John,

Could you possibly repost the code from the original post? I'm working on trying to set something like your stacked line chart up and it would be incredibly helpful.

Thanks!

James