Reporting and Analytics for Service

Get Involved. Join the Conversation.

Topic

    Mohana Gopal Selvam
    Lookup Object ReportAnswered
    Topic posted June 10, 2015 by Mohana Gopal SelvamSilver Crown: 22,500+ Points 
    306 Views, 12 Comments
    Title:
    Lookup Object Report
    Content:

    Hi Gurus,

    We have maintained one stand-alone custom object which maintain the details of the promo-code. The promo-code value will receive from third party system and we load it under Contact's child object. 

    Our objective is, we need to create one report for knowing the details of the promotion code in the Contact's child table report. But when I tried to create a report based on Contact's child object, I could not find the relationship with my custom object. Is it possible to create a report without any relationship with other object? 

    Thanks,

    Mohan

     

    Best Comment

    Anurag Mittal

    Mohan, If you are on or above august 2013 version then use 

    require_once(get_cfg_var('doc_root')."/include/ConnectPHP/Connect_init.phph");

    to include Connect_init file as the one you are using was deprecated on August 2013 release. Look at this release note for more info. 

    Can you also make sure whether you have hard coded incident Id in the first column or second column, if its first column then use $rows[0][0]->val; instead of $rows[0][1]->val;

    Rest of the code looks fine.

    ~Anurag

     

     

    Comment

     

    • Bhagwan Singh Mer

      Mohan,

      In that case, you can look at report linking feature.have you tried that?

      Answer Title: Report Linking in RightNow Analytics

      Thanks,

      MB

    • Mohana Gopal Selvam

      Hi MB,

      Thanks for your response. In our requirement we have to show the details in single report.

      We are maintaining one more standalone object with currency details. We need to convert the amount based on currency code and display it in single report.

      Thanks,

      Mohan

    • Bhagwan Singh Mer

       

      Hi Mohan,

      In our requirement we have to show the details in single report.

      In that case you will have to use Custom script ROQL in analytics to fetch the details for the code and display the same.

      Here is an example where my report dataset is only on interfaces table.It has an id value for which data exists in siteinterface table (exposed in ROQL).Assuming there is no direct relationship between them, I simply take my first column value i.e. interfaces.interface_id and pass it to ROQL>Fetch it and display in 3rd column which is simply a placehodler

      Initialization Tab

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

      Process Tab

      $i_id = $rows[0][0]->val;
       $query = "SELECT s.Language.Name as LangName FROM siteinterface s WHERE s.id=".$i_id;
       $query_results = RightNow\Connect\v1_2\ROQL::query($query)->next();
       $row = $query_results->next();
       $rows[0][2]->val = $row['LangName'];

      You can also do something similar by querying on stand-alone CO and displaying its value in single report

      Thanks,

      MB

    • Mohana Gopal Selvam

      Thanks for your help MB. 

      I tried with my custom objects but I didnt get any output.

      So I tried with standard object query like below. I hard code the incident Id in the first row of the result. Even I did not get the output. Am I miss anything?

      Initialisation Tab

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

      Process Tab

      $incident_id = $rows[0][1]->val;

      //build the ROQL query using the i_id
      $query = "SELECT I.ID FROM Incident I WHERE I.ID=".$incident_id;

      //run the query
      $query_results = RightNow\Connect\v1_2\ROQL::query($query)->next();

      $row = $query_results->next();

      //put the results in the report
      $rows[0][1]->val = "ID ".$row['ID'];

    • Anurag Mittal

      Mohan, If you are on or above august 2013 version then use 

      require_once(get_cfg_var('doc_root')."/include/ConnectPHP/Connect_init.phph");

      to include Connect_init file as the one you are using was deprecated on August 2013 release. Look at this release note for more info. 

      Can you also make sure whether you have hard coded incident Id in the first column or second column, if its first column then use $rows[0][0]->val; instead of $rows[0][1]->val;

      Rest of the code looks fine.

      ~Anurag

       

       

    • Mohana Gopal Selvam

      Thanks Anurag & MB.

      The above code is working fine now. But still its not working for my custom object code. Am I miss anything?

      Initialisation Tab:

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

      Process Tab:

      $i_id = $rows[0][0]->val;

      //build the ROQL query using the i_id
      $query = "SELECT promo.campaignname from ROI.Promotion promo where promo.promotioncode=".$i_id;

      //run the query
      $query_results = RightNow\Connect\v1_2\ROQL::query($query)->next();

      $row = $query_results->next();

      //put the results in the report
      $rows[0][1]->val = "ID ".$row['campaignname'];

      Thanks,

      Mohan

    • Anurag Mittal

      Your code looks good. Can you test your ROQL query in customer portal and see if it works there. 

    • Mohana Gopal Selvam

      Hi Anurag,

      Its working fine in CP also. Do we need to add anything for custom objects?

      Thanks,

      Mohan

    • Anurag Mittal

      No, nothing special for custom object. Write your code in Try Catch block and see if you get some error, i have added my code below.

      Also make sure you have some value for given $i_id 

      try {
          $roqlForContact = "SELECT cp.MemberID FROM Enrollment.ContactPlan cp WHERE cp.c_id = ".$rows[0][0]->val;
          $cons = RightNow\Connect\v1_2\ROQL::query($roqlForContact)->next();
          $con = $cons->next();
          $rows[0][1]->val = $con['MemberID'];
      } catch (Exception $ex) {
          $rows[0][1]->val = $ex->getMessage();
      }

      ~Anurag

    • Mohana Gopal Selvam

      Thanks Anurag,

      My $i_id contains * (Asterisk) character. If I run the same query in CP - Connecct Object explorer it returns value. But the same query error out in analytics. I got the exception like "near "*": syntax error"

      Thanks,

      Mohan

       

       

    • Anurag Mittal

      Mohan,

      Looks like your promo.promotioncode is a string field. If so then pass value within single quote.

      $query = "SELECT promo.campaignname from ROI.Promotion promo where promo.promotioncode= '".$i_id."'";

       

       

    • Mohana Gopal Selvam

      Thanks Anurag,

      Its working fine.