SQL Table Widget Column Headings

Alex_Marsh

Hi All

Been racking my brains this afternoon trying to work out what's going on.

I have the following query which I plan on using with a customer who needs a team work view with some additional fields on

<div>Select w.WorkItemId as [Id],&nbsp;</div><div>w.Title as [Title],</div><div>dstat.DisplayString as [Status],</div><div>dtier.DisplayString as [Support Group],</div><div>w.AffectedUser as [Affected User],</div><div>w.AssignedUser as [Assigned To User],</div><div>affu.Department as [Department],</div><div>w.Created,</div><div>w.LastModified</div><div>from WorkItem w</div><div>INNER JOIN DisplayString dstat on w.StatusId = dstat.ElementID AND dstat.DisplayString NOT IN ('Failed', 'Resolved', 'Closed', 'Completed', 'Cancelled', 'Skipped')</div><div>Left JOIN CI$User affu on w.AffectedUserId = affu.Id</div><div>LEFT JOIN DisplayString dtier on w.TierId = dtier.ElementID</div><div>where AssignedUserId = @UserId OR TierId IN (</div><div>Select sg.EnumerationId from SupportGroupMapping_CI$DomainGroup_Enumeration sg</div><div>INNER JOIN CI$DomainGroup g ON g.Id = sg.DomainGroupId</div><div>INNER JOIN GroupMembership_CI$DomainGroup_CI$User m ON m.DomainGroupId = g.Id AND m.UserId = @UserId AND g.Analyst = 1)</div>

Having 3 issues predominantly
1) the headings aren't coming through correctly (i.e. spaces removed etc)
2) the dates aren't formatting as per the rest of the portal (I've made an assumption here that the portal will sort date translations out, if not then I'll adjust via SQL)
3) the $('[data-role=grid]').data('kendoGrid').dataSource.read(); method to refresh the data doesn't appear to work so I can't setup an auto refresh for the page

Any ideas on how to solve the above? 

Brian_Wiest

Can help with the first two

1. Learned with Cireson that when using aliases just use case to indicate where spaces belong. Now I am not 100 percent user if its case or just the system detecting the value. IE I use [SupportGroup] it when rendered on the portal it shows up as "Support Group"

2. Dates have to match the column headings that Cireson fires the JS on. So values need to be LastModified will fire correctly but w.LastModified will not.

So what I have done is make my Alias w.LastModified as [LastModified], but this too will not fire the script correctly. So to adjust for that you have to wrap the query in a query example

Select * From

(Select w.id as [Id], w.Lastmodified as [LastModified] from workitem w) WI

Now the JS will read LastModified properly to apply the date/time localization.

3. Sorry about three would like to know that one as well so I have have custom dashboards auto refresh similar to the Cireson ones when needed.

Alex_Marsh

Thanks @Brian_Wiest that worked a treat! Dashboard looking exactly as it should.

Just need to work out how to refresh the page now and should be good to deploy in test