How to obtain "created date" as a column in a saved search ?

Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
When I create a search for incidents or service requests, the columns available do not contain the "Created Date". How can I add this column or is there another way to obtain that information for an exportable list of items ? I'm running v8.2 of the portal.

Best Answer

Answers

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Build a SQL Table Widget. 
    Add the columns you want and then you will have the export to csv option
  • Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
    Thanks for your time and effort Brian, I tried to create a simple query but I'm struggling with the translation of the enumerations. This is what I have for trying to convert the TierId into the support group name: I get results, but the Tier column is missing

    SELECT wi.WorkItemId AS 'Id', wi.Created, wi.AffectedUser, wi.Title, wi.impact, wi.urgency, wi.StatusId, wi.AssignedUser, wi.TierId, ds.DisplayString Tier, wi.LastModified, wi.ResolvedDate, wi.ClosedDate
    FROM WorkItem AS wi
    LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId
    INNER JOIN [DisplayString] ds ON ds.ElementID=enum.EnumerationID

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    You are close, Updated to match ones I use and that I know work in the portal. 

    SELECT wi.WorkItemId AS [Id], 
    wi.Created, 
    wi.AffectedUser, 
    wi.Title, 
    dsimpact.DisplayString as [Impact], 
    dsurgency.DisplayString as [Urgency], 
    dsstatus.DisplayString as [Status], 
    wi.AssignedUser, 
    dssupport.DisplayString as [Tier], 
    wi.LastModified, 
    wi.ResolvedDate, 
    wi.ClosedDate
    FROM WorkItem AS wi 

    LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
    LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID 
    LEFT JOIN [DisplayString] dsstatus ON dsstatus.ElementID=wi.StatusId
    LEFT JOIN [DisplayString] dsimpact ON dsimpact.ElementID=wi.Impact
    LEFT JOIN [DisplayString] dsurgency ON dsurgency.ElementID=wi.urgency
  • Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
    Thanks I'm almost there, I added a TOP 3000 in the SELECT statement and an ORDER BY wi.Created DESC


    For some reason I'm getting a lot of duplicates in the result. My SQL skills are very rusty, no idea what I might be doing wrong ?
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Share you current code
  • Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
    I noticed that the query substitutes the enumeration values in different languages and creates a row for every possible combination of impact and urgency in three languages (Low-Faible-Laag, Medium-Moyen-Gemiddeld,  High-Elevée,Hoog)
  • Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
    Also the Status is translated. I only need the English data
  • Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
    When I look at the SQL query for one of the default ones it contains a statement (@LanguageCode, 'ENU') but the syntax is complicated. Where should I put that in my code ?

    SELECT TOP 3000 wi.WorkItemId AS [Id], wi.Created, wi.AffectedUser, wi.Title, dsimpact.DisplayString as [Impact], dsurgency.DisplayString as [Urgency], dsstatus.DisplayString as [Status], wi.AssignedUser, dssupport.DisplayString as [Tier],  wi.LastModified, wi.ResolvedDate, wi.ClosedDate
    FROM WorkItem AS wi 
    LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
    LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID 
    LEFT JOIN [DisplayString] dsstatus ON dsstatus.ElementID=wi.StatusId
    LEFT JOIN [DisplayString] dsimpact ON dsimpact.ElementID=wi.Impact
    LEFT JOIN [DisplayString] dsurgency ON dsurgency.ElementID=wi.urgency
    ORDER BY wi.Created DESC

  • Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
    Great !

    Thanks for your time and effort Brian, it's a life saver !
Sign In or Register to comment.