Downloading query results to spreadsheet

RT Users
1

To all,

I have just upgraded to 3.6.4 and am having some difficulty getting the

results of a query into a spreadsheet with the fields I selected for the
query. Instead, I get all sorts of tickets fields I did NOT select and
am missing several that I DID select. Why does RT seem to ignore the
fields I am/not selecting for the query when it downloads the results to
excel? Anyone?

Kenn
LBNL

2

You need this RT extension for better control of export to spreadsheet.

http://svn.bestpractical.com/cgi-bin/index.cgi/bps/browse/RT-View-ConciseSpreadsheet

Jesse, it would be nice if this was the default for RT in the new
reporting work that is being performed.

-ToddOn 2/15/08, Kenneth Crocker KFCrocker@lbl.gov wrote:

To all,

    I have just upgraded to 3.6.4 and am having some difficulty getting the

results of a query into a spreadsheet with the fields I selected for the
query. Instead, I get all sorts of tickets fields I did NOT select and
am missing several that I DID select. Why does RT seem to ignore the
fields I am/not selecting for the query when it downloads the results to
excel? Anyone?

Kenn
LBNL

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

3

At Friday 2/15/2008 05:55 PM, Kenneth Crocker wrote:

To all,

    I have just upgraded to 3.6.4 and am having some difficulty

getting the
results of a query into a spreadsheet with the fields I selected for the
query. Instead, I get all sorts of tickets fields I did NOT select and
am missing several that I DID select. Why does RT seem to ignore the
fields I am/not selecting for the query when it downloads the results to
excel? Anyone?

Kenn
LBNL

Kenn,

That’s the way the spreadsheet feature works (and always has, I
think) - it’s intended to give you a full set of fields for each
ticket. There’s a contrib on the wiki that downloads only the fields
you see on the results screen:

http://wiki.bestpractical.com/view/SpreadsheetDisplayedFields

Good luck,
Steve

4

Todd,

thanks. I'll definitely look at this one and the one Stephen Turner

mentioned. Thanks a bunch!

Kenn
LBNLOn 2/16/2008 1:48 PM, Todd Chapman wrote:

You need this RT extension for better control of export to spreadsheet.

http://svn.bestpractical.com/cgi-bin/index.cgi/bps/browse/RT-View-ConciseSpreadsheet

Jesse, it would be nice if this was the default for RT in the new
reporting work that is being performed.

-Todd

On 2/15/08, Kenneth Crocker KFCrocker@lbl.gov wrote:

To all,

    I have just upgraded to 3.6.4 and am having some difficulty getting the

results of a query into a spreadsheet with the fields I selected for the
query. Instead, I get all sorts of tickets fields I did NOT select and
am missing several that I DID select. Why does RT seem to ignore the
fields I am/not selecting for the query when it downloads the results to
excel? Anyone?

Kenn
LBNL

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

5

Stephen,

thanks. I'm definitely going to look at it. I had modified the default

set of columns in my local directory for this very purpose. It seems a
waste of time to have to delete any unwanted columns one at a time and
then to have them show up anyway in a downloaded result. Oh well, that’s
why we have the user group, huh?

Kenn
LBNLOn 2/19/2008 6:46 AM, Stephen Turner wrote:

At Friday 2/15/2008 05:55 PM, Kenneth Crocker wrote:

To all,

    I have just upgraded to 3.6.4 and am having some difficulty

getting the
results of a query into a spreadsheet with the fields I selected for the
query. Instead, I get all sorts of tickets fields I did NOT select and
am missing several that I DID select. Why does RT seem to ignore the
fields I am/not selecting for the query when it downloads the results to
excel? Anyone?

Kenn
LBNL

Kenn,

That’s the way the spreadsheet feature works (and always has, I think) -
it’s intended to give you a full set of fields for each ticket. There’s
a contrib on the wiki that downloads only the fields you see on the
results screen:

SpreadsheetDisplayedFields - Request Tracker Wiki

Good luck,
Steve

6

At Tuesday 2/19/2008 12:53 PM, Kenneth Crocker wrote:

Stephen,

    thanks. I'm definitely going to look at it. I had modified

the default set of columns in my local directory for this very
purpose. It seems a waste of time to have to delete any unwanted
columns one at a time and then to have them show up anyway in a
downloaded result. Oh well, that’s why we have the user group, huh?

Kenn
LBNL

Kenn,

The extension Todd mentioned looks as though it may have been based
on my original code (on the wiki) - so it’s probably better to go
with that one, especially if it’s going to be brought into the core product.

Steve

7

Stephen & Todd,

I copied the Results.html file from /share/html/Search/ to

/local/html/Search/. Then I made the changes to
/local/html/Search/Results.html (one line for the spreadsheet link. I
also removed the code at the end of the line for “RSS” and “Offline”)
and then I added the new file SearchResults.tsv to /local/html/Search/
(with latest changes commented at the bottom). When I selected the
spreadsheet link from my search results page, I got the message

“The requested URL /Search/ScreenResults.tsv was not found on this server.”

What am I missing?

Kenn
LBNLOn 2/19/2008 10:28 AM, Stephen Turner wrote:

At Tuesday 2/19/2008 12:53 PM, Kenneth Crocker wrote:

Stephen,

    thanks. I'm definitely going to look at it. I had modified the

default set of columns in my local directory for this very purpose. It
seems a waste of time to have to delete any unwanted columns one at a
time and then to have them show up anyway in a downloaded result. Oh
well, that’s why we have the user group, huh?

Kenn
LBNL

Kenn,

The extension Todd mentioned looks as though it may have been based on
my original code (on the wiki) - so it’s probably better to go with that
one, especially if it’s going to be brought into the core product.

Steve

8

At Tuesday 2/19/2008 06:11 PM, Kenneth Crocker wrote:

Stephen & Todd,

    I copied the Results.html file from /share/html/Search/ to

/local/html/Search/. Then I made the changes to
/local/html/Search/Results.html (one line for the spreadsheet link.
I also removed the code at the end of the line for “RSS” and
“Offline”) and then I added the new file SearchResults.tsv to
/local/html/Search/ (with latest changes commented at the bottom).
When I selected the spreadsheet link from my search results page, I
got the message

“The requested URL /Search/ScreenResults.tsv was not found on this server.”

    What am I missing?

Kenn
LBNL

Kenn,

It looks like the new file should be called ScreenResults.tsv, not
SearchResults.tsv.

Steve

9

Stephen,

DUH!! Sorry for not being as observant as I should have. It should have

be obvious.

Kenn
LBNLOn 2/20/2008 5:52 AM, Stephen Turner wrote:

At Tuesday 2/19/2008 06:11 PM, Kenneth Crocker wrote:

Stephen & Todd,

    I copied the Results.html file from /share/html/Search/ to

/local/html/Search/. Then I made the changes to
/local/html/Search/Results.html (one line for the spreadsheet link. I
also removed the code at the end of the line for “RSS” and “Offline”)
and then I added the new file SearchResults.tsv to /local/html/Search/
(with latest changes commented at the bottom). When I selected the
spreadsheet link from my search results page, I got the message

“The requested URL /Search/ScreenResults.tsv was not found on this
server.”

    What am I missing?

Kenn
LBNL

Kenn,

It looks like the new file should be called ScreenResults.tsv, not
SearchResults.tsv.

Steve

10

Stephen & Todd,

I got it to work, somewhat. The titles do not line up with the data.

Stephen, you mentioned using Todd’s version and I have had trouble
following it. The link sends me to a list of titles and I’m not shure
which ones to use/follow )i.e. “html”, “inc”, “lib”, etc.) and then on
to what? If I follow the “html” link (my clue was “Results.html”) then I
see "“Callbacks” and “ConciseSheet” and then “Results.tsv”. I assumed I
just neede to copy the code from “Results.tsv” into “ScreenResults.tsv”.
What about “ScrubHTML”? I’m a little confused here as to use all, part,
which part, etc. Todd, can you help me, please?

Kenn
LBNLOn 2/20/2008 5:52 AM, Stephen Turner wrote:

At Tuesday 2/19/2008 06:11 PM, Kenneth Crocker wrote:

Stephen & Todd,

    I copied the Results.html file from /share/html/Search/ to

/local/html/Search/. Then I made the changes to
/local/html/Search/Results.html (one line for the spreadsheet link. I
also removed the code at the end of the line for “RSS” and “Offline”)
and then I added the new file SearchResults.tsv to /local/html/Search/
(with latest changes commented at the bottom). When I selected the
spreadsheet link from my search results page, I got the message

“The requested URL /Search/ScreenResults.tsv was not found on this
server.”

    What am I missing?

Kenn
LBNL

Kenn,

It looks like the new file should be called ScreenResults.tsv, not
SearchResults.tsv.

Steve

11

Stephen,

HA! I got it working. I had failed to copy & modify the "ScrubHTML"

fiel. The titles line up with the data fine now. The only problem I have
now is that the ticket link data doesn’t display correctly. I get
subject, dates, status, etc. OK. For Parent/Child or
DependsOn/DependedOnBy I get this:
“,
55009,
”.

Any clue about that result? Anyone?

Kenn
LBNLOn 2/19/2008 10:28 AM, Stephen Turner wrote:

At Tuesday 2/19/2008 12:53 PM, Kenneth Crocker wrote:

Stephen,

    thanks. I'm definitely going to look at it. I had modified the

default set of columns in my local directory for this very purpose. It
seems a waste of time to have to delete any unwanted columns one at a
time and then to have them show up anyway in a downloaded result. Oh
well, that’s why we have the user group, huh?

Kenn
LBNL

Kenn,

The extension Todd mentioned looks as though it may have been based on
my original code (on the wiki) - so it’s probably better to go with that
one, especially if it’s going to be brought into the core product.

Steve

12

At Wednesday 2/20/2008 04:25 PM, Kenneth Crocker wrote:

Stephen,

    HA! I got it working. I had failed to copy & modify the

“ScrubHTML” fiel. The titles line up with the data fine now. The
only problem I have now is that the ticket link data doesn’t
display correctly. I get subject, dates, status, etc. OK. For
Parent/Child or DependsOn/DependedOnBy I get this:
“, 55009,
”.

    Any clue about that result? Anyone?

Kenn
LBNL

I’d say you’ve found a bug. It’s
because /Elements/RT__Ticket/ColumnMap wraps the tag around
link field values.

A kludge that might work is to strip out the HTML tag from the
result, a bit like the way the
tag is stripped out:

$row->{ $column } =~ s/,
//g;

You’d need a regex that would strip off everything around the ‘55009’
from ‘, 55009,
’

Steve

13

Stephen,

I have no idea what you just said. What's a regex? How did RT handle

parent/child displays when it downloaded to a spreadsheet in the first
place? Why would that change?

Kenn
LBNLOn 2/20/2008 1:42 PM, Stephen Turner wrote:

At Wednesday 2/20/2008 04:25 PM, Kenneth Crocker wrote:

Stephen,

    HA! I got it working. I had failed to copy & modify the

“ScrubHTML” fiel. The titles line up with the data fine now. The only
problem I have now is that the ticket link data doesn’t display
correctly. I get subject, dates, status, etc. OK. For Parent/Child or
DependsOn/DependedOnBy I get this:
“, 55009,

”.

    Any clue about that result? Anyone?

Kenn
LBNL

I’d say you’ve found a bug. It’s because
/Elements/RT__Ticket/ColumnMap wraps the tag around link field
values.

A kludge that might work is to strip out the HTML tag from the result, a
bit like the way the
tag is stripped out:

$row->{ $column } =~ s/,
//g;

You’d need a regex that would strip off everything around the ‘55009’
from ‘, 55009,
’

Steve

14

Stephen,

On the wiki info about your code there was a reference to the fact that

the changes worked up to 3.4.5, but in 3.6.x it had problems and that
they were fixed with this code:

     } else {
         $row->{ $column } =  $value ;
     }
     $row->{ $column } =~ s/, <br \/>//g;    # ColumnMap adds <br />

tags, which we don’t want
$row->{ $column } =~ s/, (pending other Collection), </em>//g;
}
push @rows, $row;
}"

So I put in the two "$row" lines of code and removed the old one. So,

what am I missing? Is there new info to get this working? Todd?

Kenn
LBNLOn 2/20/2008 1:42 PM, Stephen Turner wrote:

At Wednesday 2/20/2008 04:25 PM, Kenneth Crocker wrote:

Stephen,

    HA! I got it working. I had failed to copy & modify the

“ScrubHTML” fiel. The titles line up with the data fine now. The only
problem I have now is that the ticket link data doesn’t display
correctly. I get subject, dates, status, etc. OK. For Parent/Child or
DependsOn/DependedOnBy I get this:
“, 55009,

”.

    Any clue about that result? Anyone?

Kenn
LBNL

I’d say you’ve found a bug. It’s because
/Elements/RT__Ticket/ColumnMap wraps the tag around link field
values.

A kludge that might work is to strip out the HTML tag from the result, a
bit like the way the
tag is stripped out:

$row->{ $column } =~ s/,
//g;

You’d need a regex that would strip off everything around the ‘55009’
from ‘, 55009,
’

Steve