Home Service Manager Portal Feature Requests
We appreciate you taking the time to vote and add your suggestions to make our products awesome! Your request will be submitted to the community for review and inclusion into the backlog.

We recommend reviewing what is submitted before posting, in case your idea has already been submitted by another community member. If it has been submitted, vote for that existing feature request (by clicking the up arrow) to increase its opportunity of being added to Cireson solutions.

For more information around feature requests in the Cireson Community click here.

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

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.
1 votes

Submitted · Last Updated

Comments

  • john_doylejohn_doyle Cireson Support Ninja IT Monkey ✭✭✭✭
    @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_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
    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_doylejohn_doyle Cireson Support Ninja IT Monkey ✭✭✭✭
    @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_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
    Thanks John, great post !
Sign In or Register to comment.