IT Monkey:   Click Here to Help Me Build the Agenda for Upcoming Cireson Webinars!

Have SQL table, need to build interactive SQL chart

Liz_RossLiz_Ross Member IT Monkey ✭
edited July 8 in Analytics

HI all I'm trying to take a very basic SQL table which shows the count of submitted IR's by month for a certain year (and also a separate table which does the same but for SR's) and turn it into an interactive bar chart like you can with Cireson's usual bar charts, where it will show the total number at the top of each column in the chart, and when you click each bar it'll show you a list of all the service requests (or IR's) contained in that count.

Data Source: ServiceManagement

Here's what I'm using just for the table:

SELECT MONTH(Created) MONTH, COUNT(*) COUNT

FROM dbo.WorkItem

WHERE YEAR(Created)=2018

AND WorkItemId like 'SR%'

GROUP BY MONTH(Created);

Best Answer

  • Shane_WhiteShane_White Cireson Support Advanced IT Monkey ✭✭✭
    Accepted Answer

    Hi @Liz_Ross 

    Try this query for me that I have attached.


    When you put it into a SQL chart widget you will need to do a few things to get what you want.

    1. Set Group By to 'CreatedMonth'
    2. Under Content Details tick 'Display Selected Item Details.

    Let me know if this is what you are after.

    Thanks,

    Shane.

Answers

  • Liz_RossLiz_Ross Member IT Monkey ✭

    Example of the table I have (table) and what I'd like to accomplish (bar chart):


  • Shane_WhiteShane_White Cireson Support Advanced IT Monkey ✭✭✭
    Accepted Answer

    Hi @Liz_Ross 

    Try this query for me that I have attached.


    When you put it into a SQL chart widget you will need to do a few things to get what you want.

    1. Set Group By to 'CreatedMonth'
    2. Under Content Details tick 'Display Selected Item Details.

    Let me know if this is what you are after.

    Thanks,

    Shane.

  • Liz_RossLiz_Ross Member IT Monkey ✭

    squee Thank you so so so much @Shane_White ! This is perfect!!! I super appreciate it!!! I added in the "AND YEAR(Created)=####" to the Where statement so I could make one for each year, but that was all I had to add! Thanks again!!


    Where WorkItemId like 'IR%'


    AND YEAR(Created)=2018




  • Shane_WhiteShane_White Cireson Support Advanced IT Monkey ✭✭✭

    More than welcome! If you need anything else just let me know :-)

    Thanks,

    Shane.

  • Liz_RossLiz_Ross Member IT Monkey ✭
    edited July 9

    Ack! @Shane_White Can you help with creating the same but for Service Requests? Sorry, being a SQL noob I wasn't sure what to change besides just where workitem id like % which didn't work of course.


    UPDATE: Shoot :( I thought it was perfect but...for some reason the IR chart isn't reflecting accurate numbers. Like we had about 50 in one month (May 2019) but the chart says there's only 10...

  • Shane_WhiteShane_White Cireson Support Advanced IT Monkey ✭✭✭

    No worries haha! How is this one:

    Thanks,

    Shane.

  • Liz_RossLiz_Ross Member IT Monkey ✭

    @Shane_White I got it!!! I think. We don't have all our categories/classifications filled in so it looks like once I took that part out I'm getting accurate numbers again.

  • Shane_WhiteShane_White Cireson Support Advanced IT Monkey ✭✭✭

    OH great! :-)

    Let me know if you need anything else!

    Thanks,

    Shane.

  • Liz_RossLiz_Ross Member IT Monkey ✭

    @Shane_White I'm so so close to being done, thank you again so much -- last question! Is there a way to have it still count those items even if one of the areas is blank? Like it's fine if it pulls up records where the support group column is blank, but I still want it to pull up those records along with the others. Right now it's not counting records that don't have all the criteria filled in. Example - in one month I had 7 records with no Support Group listed, so the chart is only showing me 164 records when my query tells me there's actually 171.

  • Shane_WhiteShane_White Cireson Support Advanced IT Monkey ✭✭✭
    edited July 9

    Hmm.. I think I made a mistake in the last one! Plus I added some ISNULL() functions in to help with missing data.

    Let me know what you think of this query:


    Thanks,

    Shane.

  • Liz_RossLiz_Ross Member IT Monkey ✭

    @Shane_White Unfortunately same thing. Any of the strings with nulls aren't added to the total count in the chart.

  • Liz_RossLiz_Ross Member IT Monkey ✭

    @Shane_White Honestly I'm okay as is :) The bar chart is mostly for showing the count differences by month visually since that's how our management likes it, so I can just take out the "filters" that might have blanks. I'll end up adding in an exportable table version of the actual details below for the whole year which can be filtered more once transfered to Excel (using a separate custom add-on we already have in place).

    This is totally great and a huge step ahead of what I was able to figure out just as is, so thank you again so so much!

  • Shane_WhiteShane_White Cireson Support Advanced IT Monkey ✭✭✭

    @Liz_Ross

    Are you just using the SQL I gave you or have you added anything? If it is coming up in the SQL it should display in the chart, can you send any screenshots of what you are seering?

    Thanks,

    Shane.

  • Liz_RossLiz_Ross Member IT Monkey ✭
    edited July 9

    @Shane_White Hopefully this helps:

    So for the SR coding with "isnull", i've only added "AND Year(Created)=2019" after the WHERE statement. I've changed nothing else.

    NullTestChart.jpg: The only ones showing in the chart for january 2019 are the 2 SRs with an actual Category of "Information". (to be clear, we have anywhere from 20 - 50 SRs any given month in our Development portal. The 5 listed in the chart are the only 5 with a category assigned).

    NullTestChartDetails.jpg: shows the two items pulled for January by the chart.

    WorkItemCount.jpg: However, if I do the standard Cireson Work Item search, for SRs only - created date between 1/1/2019 12 am and 2/1/2019 12 am, there are 16 total, 14 of which have no Category.

    If this helps: On our SR templates, the category field is called Area. However your sql query still seems to pull up the right information for "category" despite the name difference. In our screenshots though you'll see it called "Pending Action" - we changed that ourselves because we intended to use it for another purpose which we just haven't gotten around to yet.

    Ideally though if the null statements work properly, then the same null statements should work on the IR SQL query for which we have some IRs with no category AND no support group assigned.


  • Shane_WhiteShane_White Cireson Support Advanced IT Monkey ✭✭✭

    @Liz_Ross

    This one should work for you! I changed the type of join it was using:

    You can alter this on the IR one too if you want.

    Let me know how you get on,

    Thanks,

    Shane

  • Liz_RossLiz_Ross Member IT Monkey ✭

    @Shane_White It's working!!! I'm getting the accurate counts again thank you!

    For the IR version I'm not sure exactly what to edit since the original IR one didn't have any of the "is null" or the "left join" in it. We'd been tweaking the SR version after that point. The one big difference I noticed was that in the original IR SQL you used wi.PriorityId AS [priority], rather than using a displaystring for it like you have in the latest SR query.

  • Shane_WhiteShane_White Cireson Support Advanced IT Monkey ✭✭✭

    Great glad to hear it @Liz_Ross !

    Well if the IR one is working I wouldn't change anything but if it breaks you just need to add the ISNULL() bits like I did on the SR one and change the display string joins to left joins.

    That priority difference is there because SR priority is a GUID where as IR guid is already a number so does not need to be converted.

    Let me know if you need anything else!

    Thanks,

    Shane.

  • Liz_RossLiz_Ross Member IT Monkey ✭

    @Shane_White Yay! I was able to edit and got it working for IR's! I'm having a weird issue transferring the coding for the SR table between my dev and prod environment but I might wait till tomorrow to let the system work out some kinks before I keep playing with it. Thanks again so much! Attaching the tweaked IR code in case someone wants it too.


  • Shane_WhiteShane_White Cireson Support Advanced IT Monkey ✭✭✭

    No worries anytime :)

Sign In or Register to comment.