Have SQL table, need to build interactive SQL chart
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_White
Cireson Support Super IT Monkey ✭✭✭✭✭
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.
- Set Group By to 'CreatedMonth'
- Under Content Details tick 'Display Selected Item Details.
Let me know if this is what you are after.
Thanks,
Shane.
1
Answers
Example of the table I have (table) and what I'd like to accomplish (bar chart):
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.
Let me know if this is what you are after.
Thanks,
Shane.
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
More than welcome! If you need anything else just let me know :-)
Thanks,
Shane.
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...
No worries haha! How is this one:
Thanks,
Shane.
@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.
OH great! :-)
Let me know if you need anything else!
Thanks,
Shane.
@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.
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.
@Shane_White Unfortunately same thing. Any of the strings with nulls aren't added to the total count in the chart.
@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!
@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.
@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.
@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
@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.
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.
@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.
No worries anytime :)