0 Replies Latest reply: Oct 29, 2012 2:26 PM by ricksharpy RSS

    Remedy ARS 7.6x ODBC Connection Needed !

      I searched the site for my answer, but haven't found anything about connecting Qlikview to Remedy ARS 7.6x.

       

      Has anyone been able to connect to Remedy via ODBC, and have readable table structures ? I am trying to alleviate having to create an export of the entire Incident (HPD:Help Desk) or Change (CHG:Infrastructure Change) form data to an xls file. There must be a way to directly connect to Remedy ?

       

      All ideas much appreciated !

       

      Rick

       

      I solved my own problem, and want to share my "script" code to get QV to connect to Remedy 7.6x database.

       

      1.  Create a local ODBC either User or System connection, using the ARS OOTB ODBC setup from BMC; just as you would load the User Tool, et al.

      2.  Create a new OV document.

      3.  Click File --> Edit Script --> Data tab.  Select ODBC and Connect.

      4.  Select the ARS ODBC entry created in Step 1. 

      5.  Enter valid ARS UserID and Password and Test connection.  The resulting script entry will have a "masked" UserID and Password for future uses.

      6.  Resulting test and Script ODBC connect entry will look similar to this:

       

      ODBC Conx.jpg

       

       

       

      Sample Script: Incident:

      SQL SELECT "Assigned_Group",     "Assigned_Support_Company",     "Assigned_Support_Organization",     Assignee,     "Categorization_Tier_1",     "Categorization_Tier_2",     "Categorization_Tier_3",     "Closed_Date",     "Contact_Company",     Department,     Description,     "First_Name",     "Group_Transfers",     Impact,     "Incident_Number",     "Individual_Transfers",     "Last_Resolved_Date",     "Last_Name",     Organization,     Priority,     "Product_Categorization_Tier_1",     "Product_Categorization_Tier_2",     "Product_Categorization_Tier_3",     "Product_Model_Version",     "Product_Name",     Region,     "Reported_Date",     "Reported_Source",     "Responded_Date",     "Service_Type",     Site,     "SLM_Status",     Status,     "Submit_Date",     "Total_Time_Spent",     "Total_Transfers",     UnknownUser,     Urgency

      FROM "HPD_Help_Desk"

      WHERE "Service Type" = 'User Service Request' OR "Service Type" = 'User Service Restoration' OR "Service Type" = 'Infrastructure Restoration' OR "Service Type" = 'Infrastructure Event';

       

      AgeInDays:

      LOAD     

      [Incident_Number],     

      NOW() - [Submit_Date] AS Age,     

      [Service_Type],     

      if([SLM_Status]='Service Targets Breached','Service Targets Breached','Within Service Targets Or No Service Targets') as slm_status

      Resident Incident;