When using the Chart widget- Sorting

Brian_Wiest

Using the Chart widget, the chart produced creates the group by in the order of work items. 
Management wants the support tiers to always be in alphabetic order. Has anyone been able to accomplish this?

Thomas_Hathaway

I would recommend using an SQL chart Widget instead and query the ServiceManagement DB and use the order by tierid asc/desc at the end. Here's an example of a query I just wrote:

select distinct  

wi.TierId as 'Support Group GUID',

DS.DisplayString as 'Support Group'

from [WorkItem] wi join DisplayString as DS

on wi.TierId = ds.ElementID

order by ds.DisplayString asc

This query gets you the the tierid's then matches it with its displayname then orders it by support group alphabetically. 

Here's the KB on SQl Chart Widgets and there's a note that it doesn't allow aggregate functions like count which is a bummer but the order by works just fine. 

Geoff_Ross

I have had something similar working without issue. Please see https://community.cireson.com/discussion/1525/working-with-dates-in-dashboard-designer-grids

Thomas_Hathaway

I would recommend using an SQL chart Widget instead and query the ServiceManagement DB and use the order by tierid asc/desc at the end. Here's an example of a query I just wrote:

select distinct  

wi.TierId as 'Support Group GUID',

DS.DisplayString as 'Support Group'

from [WorkItem] wi join DisplayString as DS

on wi.TierId = ds.ElementID

order by ds.DisplayString asc

This query gets you the the tierid's then matches it with its displayname then orders it by support group alphabetically. 

Here's the KB on SQl Chart Widgets and there's a note that it doesn't allow aggregate functions like count which is a bummer but the order by works just fine. 

Brian_Wiest

I was attempting to use the SQL chart widgets, but ran into a functional issue the when using the option "display selected item details" I would get the details but when clicking on a specific row it would open the search page. 
I had opened a ticket with support and was provided a fix of wi.WorkItemId as [Id] now I am getting the behavior needed for management. 
Also learned the code I attempted wi.WorkItemId as [ID] will not work. 
Now if the Date columns can get fixed my management would be happy. 

Geoff_Ross

@Brian_Wiest
What do you need fixed with the Date Columns?

Brian_Wiest

See Cireson ticket IR58434
Summary All Date Columns using the saved searchs or SQL widgets display as YYYY-MM-DDT00:00:00:000Z

Now that I have the row click working as needed attempted to use convert but the widget would not accept the query

CONVERT(varchar,DATEADD(hh,-5,[Created]),100) [Created],

CONVERT(varchar,DATEADD(hh,-5,[Last Modified]),100) [Last Modified],

And the above still doesn't account for Daylight savings. 

Geoff_Ross

I have had something similar working without issue. Please see https://community.cireson.com/discussion/1525/working-with-dates-in-dashboard-designer-grids

Brian_Wiest

Well sometimes it takes two tries. Changed to location of the convert statement is the query and it is working now.

Geoff_Ross

Great, nice one.