manual cross tab - need to suppress blank areas 1

[ctwilliams]

I am running CR 8.5, and have created a manual cross tab report that groups by category, and lists items under the columns that I have designated (I created a formula for each column).

It works great, only the items are not formatted/spaced the way I need them to be. They are currently spaced like this...

Category        Column 1   Column 2   Column 3
-------------------------------------------------------
Cat #1          Item a
                Item b
                Item c
                           Item d
                           Item e
                                       Item f
                                       Item g
                                       Item h

However, I want them to be spaced like this...

Category    Column 1    Column 2    Column 3
-------------------------------------------------------
Cat #1      Item a      Item d      Item f
            Item b      Item e      Item g
            Item c                  Item h

How can I "bump up" the items to the top of each column? In other words, how can I suppress the blank areas?

The items are placed in the Detail section of my report. The group 1 header is set to "underlay following sections".

Any suggestions would be appreciated! Let me know if you need any other information.

 

[synapsevampire]

Try posting technical information:

Where are these formulas? What's the layout? Numerous formulas down the column?

Why are you using underlay following sections?

You might adjust each formula to have an if conditional something like:

if
not(
isnull({@formula1})
or
{@formula1} = 0 then
)
then
{@formula1}
else
if
not(
isnull({@formula2})
or
{@formula2} = 0 then
)
then
{@formula2}

This is pretty ugly though, but it will work.

-k

 

[ctwilliams]

My report contains the following fields:

{Category}
{Column}
{Item}

The data looks similar to this:

Category    Column    Item
--------    -------   --------
Cat 1       1         a
Cat 1       1         b
Cat 1       1         c
Cat 1       2         d
Cat 1       2         e
Cat 1       3         f
Cat 1       3         g
Cat 1       3         h
Cat 2       1         a
Cat 2       2         b

I am grouping on the {Category} field.

I have formulas setup for each {Column} value:

{@Column1}:
if {Column} = "1" then {Item}

{@Column2}:
if {Column} = "2" then {Item}

{@Column3}:
if {Column} = "3" then {Item}

I have placed these formulas into the Details section of the report, with text labels placed in the Page Header, like this:

Page Header:      "Category"   "Column 1"   "Column 2"   "Column 3"
Group 1 Header:   {Category} 
Detail:                        {@Column1}   {@Column2}   {@Column3}

I set the Group 1 Header section to "underlay following sections" so that the {Category} value will appear beside the {Column} value instead of above it.

 

[cLFlaVA]

I think you may want to suppress the detail line, then Insert > Summary for each of the three formulas. When the Summary is inserted for each, drag them to the Group 1 Header section, underneath their respective column names.

I've been working with the hell that is manual cross-tabs for the past week, and my level of Crystal expertise went from 0 to, well, still very low! But, I'm "ok" with cross-tabs now.

Yay.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[ctwilliams]

If I insert a summary then it will display a count/sum of the items, like this?

Category    Column 1    Column 2    Column 3
-------------------------------------------------------
Cat #1      3           2           3

I need to display each of the item values on the report, like this...

Category    Column 1    Column 2    Column 3
-------------------------------------------------------
Cat #1      Item a      Item d      Item f
            Item b      Item e      Item g
            Item c                  Item h

 

[cLFlaVA]

Ahh...sorry - misread your question...

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[synapsevampire]

Modify your formulas:

Group Header:
whileprintingrecords;
Stringvar Co1:="";
Stringvar Co2:="";
Stringvar Co3:="";

Details:
whileprintingrecords;
Stringvar Co1;
Stringvar Co2;
Stringvar Co3;
If {table.Column} = "1" then
Co1:=Co1+{table.item}+chr(13)
else
If {table.Column} = "2" then
Co2:=Co2+{table.item}+chr(13)
else
If {table.Column} = "3" then
Co3:=Co3+{table.item}+chr(13);

Now you have 3 different formulas displayed alongside each other in the Group Footer (suppress the details and the group header):

Group Header:
whileprintingrecords;
Stringvar Co1

Group Header:
whileprintingrecords;
Stringvar Co2

Group Header:
whileprintingrecords;
Stringvar Co3

In cr 8.5 you have a maximum of 254 chars output in a fromula though, if this is an issue, create multiple formulas for each column and drop them into a text object.

-k

 

[ctwilliams]

Bless you synapsevampire!!! You are my savior! That worked perfectly!