Miscellaneous Ramblings on Hacking

June 8, 2010

RightNow Analytics — The SQL Way

Filed under: CRM, RightNow — Zack Bethem @ 1:47 am

I always lose sight of this when I need it. That and RightNow’s website hides this from anyone’s view (side rant: really, how does one search in Google for RightNow the product?). Gartner says RightNow is #1 for customer support KB search, but I can never find shit using their support KB search. (side rant: seriously how much was Gartner paid?)

Create a New Report via SQL

To do this, we’re going to be using a custom script.

  1. Define a filter that will always return zero rows.
    filter: 1=0
  2. Select Level > Custom Scripts
  3. Select the ‘Finish tab of custom scripts
  4. Paste in the following while modifying the SQL to your liking:$temp=array();
    $row_idx = 0;
    //Run the desired query
    $query=sql_prepare
    (sprintf("
    SELECT label,ac_id, header_code, init_code, process_code, exit_code
    FROM ac_scripts a, labels l
    where a.ac_id = l.label_id
    and tbl=121
    and (header_code is not NULL
    or init_code is not NULL
    or process_code is not NULL
    or exit_code is not NULL)
    "));
    //Each column in the SELECT clause will need to be returned as the appropriate data-type (INT for integer, NTS for string, DTTM for datetime)
    sql_bind_col($query,1,BIND_NTS,80);
    sql_bind_col($query,2,BIND_INT,0);
    sql_bind_col($query,3,BIND_NTS,1000);
    sql_bind_col($query,4,BIND_NTS,1000);
    sql_bind_col($query,5,BIND_NTS,1000);
    sql_bind_col($query,6,BIND_NTS,1000);
    //For each record returned in the above query, return it as one row of output, each field in its respective column
    while ($temp = sql_fetch($query))
    {
    $exit_obj[$row_idx][0]->val = $temp[0];
    $exit_obj[$row_idx][1]->val = $temp[1];
    $exit_obj[$row_idx][2]->val = $temp[2];
    $exit_obj[$row_idx][3]->val = $temp[3];
    $exit_obj[$row_idx][4]->val = $temp[4];
    $exit_obj[$row_idx][5]->val = $temp[5];
    ++$row_idx;
    }
    //Clear your buffer
    sql_free($query);
  5. $exit_obj is the array returned, whose values are subsequently displayed in the report
Advertisements

5 Comments »

  1. If this does what I think it should . . I’ll be ecstatic! I’ll be trying this out as soon as I have time; thanks for sharing!

    (I also can’t find RN content online other than in their own community. I think that their community is pretty solid so don’t mind having only one place to search.)

    Comment by sophistical — June 8, 2010 @ 4:29 pm

  2. It works! (I couldn’t wait, I had to give it a try.) I did have to add a column with a string (or whatever data type you need, a zero for integer or two single quotes for a string.) I also had to manually add the tables my SQL referenced to the report through the tables button. It does appear (unfortunately) that I cannot open records that are returned so a lot of the functionality I could use this for is lost but for summary data this will work great!

    Comment by sophistical — June 8, 2010 @ 4:52 pm

    • Yeah. It completely voids the relationship back to the original record.

      One quick note. If you have a custom field that you want back, the syntax is C\$. For example: c\$const_individual_key. The ‘\’ provides the escape sequence for the php reserved character ‘$’.

      Comment by Zack — June 8, 2010 @ 5:15 pm

      • Thank you, I wouldn’t have figured that out and will definitely need it eventually.

        Comment by sophistical — June 8, 2010 @ 5:18 pm

  3. I have loved using this functionality, but I have noticed that this consistantly only returns 1250 rows of data, even when the report says there are 80,000+ rows. The extra rows are filled with null values. Any suggestions on this?

    Comment by onegd4u — October 14, 2010 @ 10:26 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: