Questions about ColumnMap, Sorting & Excel

All,

We are looking to try to customize RT to get a little additional information out of it easily. We are most interested in SLAs. I’ve created a custom ColumnMap that adds the following:
Response Time - Time between when the ticket was created and the when the ticket was “Started”. Started gets set via script on first response…if it is not already set.
Resolve Time - Time between when the ticket was created and when the ticket was "Resolved"
Shift - We rotate coverage, this tracks who should initially have been responsible for the ticket based on time of day.

I’ve also add the custom searches to the BuildFormatString. This helps tell us a great deal about the ticket, but I would like to do more with this information.

Can I add the ability to Sort or Add Criteria using custom values?

When I click on the field to sort by the customized column, the tickets all disappear. Is it possible to sort tickets based on a custom ColumnMap (calculated from the ticket values)? Is it possible to create customized searches base on customized fields (calculated from the ticket values)? Is it possible to export these values to Excel?

I know I could make this all work with Custom Fields, but I do not want to give our users the ability to change this information…but that just seems a little messy.

-Jennifer

When I click on the field to sort by the customized column, the tickets all disappear. Is it
possible to sort tickets based on a custom ColumnMap (calculated from the ticket values)? Is
it possible to create customized searches base on customized fields (calculated from the
ticket values)? Is it possible to export these values to Excel?

I suspect RT is trying to do a DB sort, screwing up your sorting. Do
you want to search on “Response Time” > 1h ? Those both sound like
things that really want to be stored in Custom Fields if possible.
RT4 should export custom columnmaps to excel fine, since it parses
your Format for the search, rather than using a list of things to
export like RT3.8 does.

I know I could make this all work with Custom Fields, but I do not want to give our users the
ability to change this information…but that just seems a little messy.

Just don’t grant ModifyCustomField on those custom fields and your
users won’t be able to touch them.

-kevin

Collections sorting done in SQL, so it works whole set instead of one page.
Each entry in column map have attribute entry, by default it matches name of
the column map entry. This value is passed into OrderBy method when you
click on column’s header. In your case table has no column ResponseTime and
generated SQL fails and produces empty set.

In simple case attribute can be used to get sorting close to calculated
value, but in your case custom SQL generator required. Look into RT::Tickets
OrderBy method. It implements conversion from special attribute values to
custom SQL.

Dates math is tricky in SQL. Recently I implemented helper functions for
queries generation in DBIx::SearchBuilder and RT, but this code still in
branches. You can steal it from our repo.

Tired of typing on phone… sorry…

Regards, Ruslan. From phone.

All,

We are looking to try to customize RT to get a little additional
information out of it easily. We are most interested in SLAs. I’ve created a
custom ColumnMap that adds the following:
Response Time - Time between when the ticket was created and the when the
ticket was “Started”. Started gets set via script on first response…if it
is not already set.
Resolve Time - Time between when the ticket was created and when the
ticket was "Resolved"
Shift - We rotate coverage, this tracks who should initially have been
responsible for the ticket based on time of day.

I’ve also add the custom searches to the BuildFormatString. This helps
tell us a great deal about the ticket, but I would like to do more with this
information.

Can I add the ability to Sort or Add Criteria using custom values?

When I click on the field to sort by the customized column, the tickets
all disappear. Is it possible to sort tickets based on a custom ColumnMap
(calculated from the ticket values)? Is it possible to create customized
searches base on customized fields (calculated from the ticket values)? Is
it possible to export these values to Excel?

I know I could make this all work with Custom Fields, but I do not want to
give our users the ability to change this information…but that just seems
a little messy.