Help with Cross Tab

Have a report that I would like to put the totals into a cross tab sheet
Need some help

Gordon HughesDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gordon HughesDirectorAuthor Commented:
Have decided to remove the cross tab from the report
Gordon HughesDirectorAuthor Commented:
Thanks for your input
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.