Help with Cross Tab

Gordon Hughes
Gordon Hughes used Ask the Experts™
on
Have a report that I would like to put the totals into a cross tab sheet
Need some help

Gordon
WO-Stats_JV1.rpt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
As you've seen, you can't use your count formulas for the cross-tab columns.  The rows and columns in a cross-tab basically create groups, and then the cross-tab produces summaries for those groups.  It doesn't make much sense to create columns (ie. groups) that are based on the formulas that you're trying to summarize.

 It would be easy if each type of count was a separate record.  If you had a "Start Backlog" record, and a "Received WO" record, and so on, with a field that contained the record type, then you could create a column for the record type, and have the cross-tab produce a count of the records with each type.  But all of your counts come from a single record (based on the various dates).

 One possible solution (perhaps the only one in a cross-tab) is to forget the columns and just summarize all of your formulas.  You can leave the Columns part empty.  FWIW, I think that you could create a "dummy" column (so you'd be creating a column in the cross-tab, but there would be only one), but I don't see any reason to do that here.  The summaries all come out in one cell and they default to a vertical arrangement, but you can change that to horizontal in the Cross-tab Expert, so they come out side by side.

 Turning on the "Show Labels" option in the Cross-tab Expert added the name of the field being summarized (eg. "@Start Backlog") above each count, but, once created, they are just editable text fields, so I removed the "@" from each one.  Some of the headings were too long, so I expanded the total fields a bit.

 I also used the "Suppress Column Grand Totals" option.  The column grand totals would be for all of the sites on the report, but since you only run the report for one site at a time, the grand totals seemed redundant.

 Off hand, I think that the main things that you lose by using this "no columns" approach are:

 1) Cross-tabs normally have a variable number of columns (you only get a column if there is data for that column), but the "columns" are "fixed" in this approach.  But I'm guessing that you always want to see all of those counts, even if they're 0, in which case the fixed columns aren't a problem.

 2) The cells in a cross-tab are normally separated by a grid, but your counts won't automatically be separated like that, because they are all in the same cell.  If you want, you can try to get around that by adding a border to each count field.  I played with that a little bit and it looks like it will work, but I didn't try to fully implement the idea.  Using borders will leave some blank spaces.  You can remove those by turning off the cell margins in the Cross-tab Expert, but then you may want to expand the fields (eg. make them taller), to leave more space between the figures.

 I'm attaching a copy of your report with a cross-tab like the one that I described (with all of the figures in one cell).  I left your cross-tab and added mine in a separate report footer, so that you can compare them.  FYI, I also changed the page header section so that it's suppressed at the end of the report (OnLastRecord), so that it doesn't appear on the cross-tab pages, since the regular headings don't fit the cross-tab.

 If you like, you can remove the "Total" at the top of my cross-tab by editing that text field and just leaving it blank.

 James

WO-Stats_cross-tab.rpt
Gordon HughesDirector

Author

Commented:
James
Have decided to remove the cross tab from the report
Gordon
Gordon HughesDirector

Author

Commented:
James
Thanks for your input
Gordon
Honestly, it didn't seem like you really needed a cross-tab, since the regular report lines showed pretty much the same thing.  :-)

 IAC, you're welcome.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial