Home› Analytics

SQL Table Widget in Portal 7.02

GordonGordon Member Adept IT Monkey ✭✭
in Analytics

I wrote a bunch of very simple queries, trying to build a maintenance dashboard for my analysts, which for some reason will not work in the SQL Table or Chart widget in the portal..anyone got any ideas? Here is my query

select wi.workitemid as 'ID', CONVERT(varchar,wi.created,101) as 'Created', RC.displaystring as 'Source'
from workitem as wi

left join CI$User as userTable on wi.affecteduserid = userTable.Id
inner join DisplayString as RC on (wi.sourceid = rc.elementid and rc.localeid = 'ENU')

where createdbyuser = 'First Last'
and wi.WorkItemId like 'IR%'
and rc.displaystring = 'Retired - Console'
and created > '11/01/2016'
 
ORDER BY WI.Created ASC

Best Answer

Answers

  • David_AllenDavid_Allen Partner Advanced IT Monkey ✭✭✭
    Hi Gordon,

    First of all, have you confirmed your query returns results in SQL management studio?

    I made a few minor changes to your query for my environment and it worked fine in management studio and in a the SQL table widget within the portal.

    If you confirm your query returns results, then it maybe that you need to upgrade your portal to the latest version.  There was an issue with the early versions where using 'AS' in the select query did not work in the widget.

    David
  • GordonGordon Member Adept IT Monkey ✭✭
    Yeah the query ran perfect in SQL, and the AS statement runs fine too in other queries.. The issue is one or more of these lines. When I take them out it runs fine in the portal, but I don't know why

    where createdbyuser = 'First Last'
    and rc.displaystring = 'Retired - Console'
    and created > '11/01/2016'
  • GordonGordon Member Adept IT Monkey ✭✭
    Thanks Steve, turns out that 7.4 is better but that 8 has some fresh problems :-)
Sign In or Register to comment.