Search code examples
crystal-reports

Cross tab with a list of values instead of summation

I want a Cross tab that lists field values and counts them instead of just giving a count for the summation. I know I could make this with groups but I cant list the values vertically that way. From my research I believe I have to use a Display String Formula.

SQL Field Data

-------------------------------------------------
| Play # | Formation |Back Set | R/P |  PLAY    |
-------------------------------------------------
| 1      | TREY      | FG     | R   |  TRUCK    |
-------------------------------------------------
| 2      | T         | FG     | R   |  RHINO    |
-------------------------------------------------
| 3      | D         | FG     | P   |  5 STEP   |
-------------------------------------------------
| 4      | D         | FG     | P   |  5 STEP   |
-------------------------------------------------
| 5      | K JET     | NG     | R   |    DOG    |
-------------------------------------------------

Desired report structure:

-----------------------------------------------------------
| Backet & Formation | Run              | Pass            |
-----------------------------------------------------------
| NG K JET           |  BULLA   1       |                 |
|                    |  HELL    3       |                 |
-----------------------------------------------------------
| FG D               |                  |     5 STEP   2  |
-----------------------------------------------------------
| NG K JET           |  DOG             |                 |
-----------------------------------------------------------
| FG T               |  RHINO           |                 |
-----------------------------------------------------------
Solution

  • Don't see why a Crosstab is necessary for this - especially if the entire body of the report is just that table.

    • Group your records by Bracket and Formation - If that's not something natively configured in your table, make a new Formula field and group on that.

    • Drop the 3 relevant fields into whichever section you need to display. (It might be a Footer, based on whether or not you want repeats

    • Write a formula to determine whether or not Run or Pass are displayed, and place it in their suppression field. (Good luck getting a Crosstab to do that for you! It tends to prefer 0s over blanks.)

    If there's more to the report than just this table, you can cheat the system by placing your "table" into a subreport. And of course you can stretch Line objects across the sections and it will stretch to form the table outlines