Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Sebastiaan Draaisma
    ROQL report on Holiday table in reportAnswered
    Topic posted December 11, 2018 by Sebastiaan DraaismaBlack Diamond: 60,000+ Points 
    21 Views, 8 Comments
    Title:
    ROQL report on Holiday table in report
    Summary:
    SELECT H.Name, H.HolidayDate FROM Holiday H
    Content:

    Hi there.

    Since the Holiday table is not available for analytics by default, I'm trying to create a report on this through ROQL.
    I have the query working in the Connect Object Explorer but I'm having dificulties creating a new report that soly exsists out of a ROQL query.

    Initialisation

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

    Process

    $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT H.Name, H.HolidayDate FROM Holiday H" )->next();
    while($holiday =  $roql_result->next())
    {
    $rows[0][0]->val = $holiday['Name'];
    $rows[0][1]->val = $holiday['HolidayDate'];
    }

    Any advice for me on how to get this right? :-)

    Image:

    Best Comment

    Vlad

    try this:

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

    Comment

     

    • Vlad

      Hey Sebastiaan,

      I'll try to look into this.

      In the meantime, check out this: http://documentation.custhelp.com/euf/assets/devdocs/cloud18d/olh/Analytics/topicrefs/t_Create_a_custom_script_ah1137760.html

      What you would have to do is build an array in the $exit_obj variable, similar to $rows. The report itself would not return any data and the $exit_obj array (2 dimensional) would be appended to the report.

      Not 100% sure about this yet, but the object would look something like this:

      $exit_obj[0][0] = "New Year's Day";
      $exit_obj[0][1] = "2018-12-31Z";
      $exit_obj[1][0] = "Spring Equinox";

      .....

    • Sebastiaan Draaisma

      Thanks Vlad :-)

      I read the article and did a test with:

      $exit_obj[0][0]->val = "New Year's Day";
      $exit_obj[0][1]->val = "2018-12-31Z";
      $exit_obj[1][0]->val = "Spring Equinox";
      $exit_obj[1][1]->val = "2019-02-20Z";

      This did gave me a correct output and gives me something to experiment with. I'm not sure as to which data table I have to add to my report to be able to save it. I asume I can use any table and unset the aditional rows?

      if ($rows[0][0]->val == 0)
      {
          unset($rows[0]);
      }

       

    • Vlad

      Good job! You can use empty single quotes as the column expressions when you define the columns on the report

    • Sebastiaan Draaisma

      That I did but I used the accounts table which creates an NULL row for every account entry in the system as I was unable to save the report without a table (even if I don't have any expressions).

      I'm going to experiment a little with an associative array to see if I can get it working :-)
      Thanks for the help so far.

    • Sebastiaan Draaisma

      Hmm I think I need a little assistance. I experimented a little but I only manage to get a 1 row output...

      Initialisation

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

      Process

      if ($rows[0][0]->val == 0)
      {
          unset($rows[0]);
      }

      Finish

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

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

    • Vlad
      You need to increment $exit_obj[i][0], roght now you are overwriting for i=0
      • Vlad

        try this:

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

    • Sebastiaan Draaisma

      Works like a charme! laugh
      Thanks Vlad! Gives me a better understanding and will allow me to help others.