Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Mike Kramer
    Displaying Data from an Outside Data SourceAnswered
    Topic posted August 9, 2019 by Mike KramerSilver Medal: 2,000+ Points 
    27 Views, 5 Comments
    Title:
    Displaying Data from an Outside Data Source
    Summary:
    Would like to query a data table from CPQ and have it displayed in and OSC report/workspace
    Content:

    We have a need to query a database outside of OSC and display the results of that query in a read only table/report.  I believe I can create the query myself (we use a middleware software application called Snaplogic) and I can trigger this query to occur (with a button in the workspace). 

    But I'm not sure how my query results need to be stored such that they can be used in a report for displaying to my end users.  These results would be standalone data.  They are not related to any existing object type inside of OSC.  Anyone have any ideas on how to do this? 

    Thanks,

    Mike

    Best Comment

    Robert Surujbhan

    Hi Mike, what you are describing is possible using a standard feature in the Service Cloud extensibility frameworks that can (1) fetch data from external systems and (2) display the results using Reports which you can then add to Workspaces just like any other Report.  The data is fetched in real-time and never stored in the OSvC database.

    When you build these integrations, "virtual tables" are exposed to the OSvC Report Designer GUI which allows you to manage them easily (arrange columns, add charts, etc.).  In your code, you define where the data comes from - in other words, you'd be responsible for populating the "virtual tables" using your middleware system via web service API calls, DB queries, etc. which get executed when the report is run/displayed/refreshed.  It's the perfect solution for what you described as read-only "standalone data".  These virtual tables wouldn't connect to any other object in the system.

    This feature works in both the Agent Browser UI web console or the .NET Agent Desktop console.  Which one are you using?  You'd have to build specialized virtual report add-ins/extensions respectively.  See the below documentation links for details!  If you need additional sample code, please let us know.

    https://documentation.custhelp.com/euf/assets/devdocs/unversioned/BUI_Extensibility/topicrefs/Analytics_for_External_Data.html

    https://documentation.custhelp.com/euf/assets/devdocs/unversioned/Connect_AddIn_Framework/Content/Desktop%20Add-In%20Framework%20API/Add-In%20Types/Read%20Write%20Report%20Add-Ins.htm

    Comment

     

    • Sebastiaan Draaisma

      Hi Mike.

      Just a quick check...
      With OSC, do you mean Oracle Sales Cloud?
      Or did you mean Oracle Service Cloud (OSVC)?

      If you meant Oracle Sales Cloud than this would be the wrong forum as this is the OSVC forum.

    • Mike Kramer

      I meant Oracle Service Cloud (the RightNow product).  The "v" makes no sense to me/us so we call it by its initials, OSC.  

    • Sebastiaan Draaisma

      Hi Mike

      The v comes from Service. It's just to avoid confusion as OSC is already the official abbreviation for Oracle Sales Cloud :-)
      That said...

      One way of getting external data into OSVC may be through a custom script. I have not done this myself with external data but this would be the most logical approach. The report would need to have a table, this can be any table (a small table is interfaces or incident queues), it's just so you are able to save the report. Then the CS (PHP) would need to retrieve the external data and insert this into your report.

      I attach a report example (ROQL) that uses the inc_statuses table but repopulates the entire report with data through a ROQL query. You would need to do something similar except with an external database.

      Initialisation

      require_once(get_cfg_var('doc_root')."/ConnectPHP/Connect_init.php");
      initConnectAPI();

      Finish

      $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT H.Name, H.HolidayDate FROM Holiday H" )->next();

      $i=0;
      while($holiday =  $roql_result->next())
      {
      $exit_obj[$i][0]->val = $holiday['Name'];
      $exit_obj[$i][1]->val = $holiday['HolidayDate'];
      $i++;
      }

    • Robert Surujbhan

      Hi Mike, what you are describing is possible using a standard feature in the Service Cloud extensibility frameworks that can (1) fetch data from external systems and (2) display the results using Reports which you can then add to Workspaces just like any other Report.  The data is fetched in real-time and never stored in the OSvC database.

      When you build these integrations, "virtual tables" are exposed to the OSvC Report Designer GUI which allows you to manage them easily (arrange columns, add charts, etc.).  In your code, you define where the data comes from - in other words, you'd be responsible for populating the "virtual tables" using your middleware system via web service API calls, DB queries, etc. which get executed when the report is run/displayed/refreshed.  It's the perfect solution for what you described as read-only "standalone data".  These virtual tables wouldn't connect to any other object in the system.

      This feature works in both the Agent Browser UI web console or the .NET Agent Desktop console.  Which one are you using?  You'd have to build specialized virtual report add-ins/extensions respectively.  See the below documentation links for details!  If you need additional sample code, please let us know.

      https://documentation.custhelp.com/euf/assets/devdocs/unversioned/BUI_Extensibility/topicrefs/Analytics_for_External_Data.html

      https://documentation.custhelp.com/euf/assets/devdocs/unversioned/Connect_AddIn_Framework/Content/Desktop%20Add-In%20Framework%20API/Add-In%20Types/Read%20Write%20Report%20Add-Ins.htm