RightNow Analytics — The SQL Way

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