[rt-users] Calculations on custom field values

[Michael Coakley]

I do things like this similar to how Kenneth described but I use a ODBC connection directly to the database and use Excel Pivot tables to build reports. Very flexible and gives you data mining tools right out of the box.

While it isn't built into RT it is in a place where data analysis is easily done, Excel. I definitely don't see that as a bad thing. RT is an amazingly flexible tool for tracking all sorts of transactionable workflows. The mere fact that this list is full of questions about how to do this and that shows how flexible RT is.

Also consider you can publish an Excel PivotTable to a SharePoint site and it will update in real-time (or close to it) and you have a very powerful executive dashboard. 

Mike

Sent from my iPhone

On Mar 2, 2011, at 5:35 PM, Daniel Farst <daniel.farst at case.edu> wrote:

> Kenneth,
> 
> Thanks for the suggestion and I'll look to see if this might be workable on our end, but I was rather hoping that there might be someway to do this all within RT, automatically, to be able to say display on a dashboard or saved search the total cost of all the orders or the average cost per order, etc.
> 
> I was actually a little surprised that this capability wasn't already there since we can do graphs and pie charts of this. I wonder if that's something that can be extended. Or even if this should be a feature request for the next version of RT.
> 
> I don't know much about Perl or if the charting functionality in RT is even extensible but maybe a fake "chart" can be built that would be displayed in the dropdown with Bar & Pie that would just do those calculations..
> 
> - Dan
> 
> -- 
> Daniel Farst
> IT Support Coordinator
> College of Arts and Sciences
> Case Western Reserve University
> 
> daniel.farst at case.edu
> 
> Public key on keyserver.pgp.com
> ----------------------------------
> Wednesday, March 2, 2011, 3:58:32 PM, Kenneth Crocker wrote:
> ----------------------------------
>> Daniel,
> 
>> We DO this! We have over a hundred Queues that are used for
>> Application support in many, many scientific applications as well as
>> the regular AP, AR, GL, etc. Each Queue has a manager and they ALL
>> have a variety of Searches that provide them with such things as
>> Time Estimated, Time Worked (for tickets as well as projects
>> (parent/Child), as well as charts based on CF values. They wanted
>> totals on some of these field from those Searches.
> 
>> What we did was download the search results (we ONLY use the
>> Concise Spreadsheet extension as our download option - required a
>> change to the html page) to Excel, then copy the data (only - no
>> headings) from that excel spreadsheet and paste tha data into an
>> excel template set up just for that report. Excel templates can be
>> set up to not only automatically format the data into properly
>> formatted columns, but automatically sort up to 4 fields, color code
>> different values in specific columns AND automatically execute
>> Macros (like total Time Worked) on the values in specific columns.
>> VIOLA! A report with all the data (headings, footers, repeated
>> headings, sorted rows, etc.) as well as totals on values, etc. the
>> SAME LOOK, the SAME WAY each time we run the search.
> 
>> This is exactly what Excel was designed for. After that, it's just
>> a matter of learning how to use Excel for our specific needs.
> 
>> Anyway, that's just how we do it.
> 
>> Kenn
>> LBNL
> 
>> On Wed, Mar 2, 2011 at 12:20 PM, Todd Chapman <todd at chaka.net> wrote:
>> Daniel,
> 
>> It is certainly possible to write a scrip that updates custom field
>> values when other custom fields,or core RT fields change.
>> It is also possible to have columns that are calculated on the fly as
>> search results are returned, though it takes a bit more work because
>> RT does not have callbacks in all the right places to make this
>> simple, but it is doable.
> 
>> If however you are looking to do calculations across multiple tickets,
>> RT's search interface and search results display are not really
>> conducive to that type of reporting.
> 
>> -Todd
> 
>> On Tue, Mar 1, 2011 at 6:44 PM, Daniel Farst <daniel.farst at case.edu> wrote:
> 
>>> I have a couple of things I'd like to do with RT and haven't been able to find any clear answers how to do it, or if it's even possible. Can anyone point me in the right direction? We'd like to do some simple calculations on values stored in a custom fields and on values from those custom fields in tickets returned from a saved search.
>>> 
>>> Some background info: We use a queue in RT 3.8.8 to keep track of upcoming orders we need to place with a ticket for each order. Those tickets have custom fields tracking several values including order cost, what we're charging for the order, order status, tracking numbers, etc..
>>> 
>>> I'm pretty sure I can accomplish this first part using scrips on a ticket update, but would like confirmation. I'd like to have certain values in a custom field calculated and updated when a different custom field value is changed. i.e. I update the order total and the field holding what we're charging for the order is automatically recalculated and updated as well.
>>> 
>>> The second part I'm not sure about. I'd like to be able to have RT give me the result of a calculation of the values in a custom field for the tickets returned by a saved search (e.g. average value of the cost of orders from user 'abc', sum of the cost of orders where custom field Order_Status = Shipped, etc.). Is this doable by RT? Is there an extension that does this that I would need to install?
>>> 
>>> Thanks!
>>> 
>>> --
>>> Daniel Farst
>>> IT Support Coordinator
>>> College of Arts and Sciences
>>> Case Western Reserve University
>>> 
>>> daniel.farst at case.edu
>>> 
>>> Public key on keyserver.pgp.com