Make 'Group By' parameter in SQL Chart Widget optional and add X and Y axis columns

Stephane_Bouillon

I have a SQL query that returns rows where the first column is a date and the second column is a numeric value. I want to chart this in a line, bar, or area graph where the date is the horizontal axis, and the values are plotted in the vertical axis. This would be a tremendous help for creating management KPI dashboards.

john_doyle

@Stephane_Bouillon, if your query ends with a group by clause, then the GROUP BY field will be replaced with Category and Value fields.

For example, create a query with this code against the ServiceManagement db (shows tickets created per day for the last 10 days):

  select cast(wi.Created as DATE) [Created], count(1) [Total]<br>  from WorkItem wi<br>  where wi.Created > cast(getdate()-10 as date)<br>  group by cast(wi.Created as DATE)

Then pick Created for Category and Total as the Value.

NB When you are writing your own queries, the 'group by' has to be lower case or the javascript code will not find it, and you will be prompted for a GROUP BY field.

Stephane_Bouillon

Thanks for this suggestion. I don't know how to build the right query for my scenario. What I'm trying to achieve is a simple graph line depicting our backlog evolution over time. Let's say I have a SQL Query with a Date column and for every day a value indicating how many items were created before that date and that were not yet in a closed state by then. I think I know how to achieve that, and this would produce the following result set:

Date        Backlog<br>2017-11-01  50<br>2017-11-02  53<br>2017-11-03  44<br>2017-11-04  40<br>2017-11-05  43<br>...

What I want to do next, is to add a SQL Chart Widget using this query as input and that shows a line, histogram or area like this:

The group by should be optional for this to work, and if I added a Type column containing Incident, Service Request, Problem, or Change, grouping by Type could then show a different colored line per type.

john_doyle

@Stephane_Bouillon You can do this in the current portal, but not using the dashboards. The user interface for the dashboards is too limited.

I created a new thread to show how this could be done.

Stephane_Bouillon

Thanks John, great post !