Crosstab with adjacent data 1

[lordhawkins]

I've got a functional crosstab report like this

[tt] Sizes
Style Color Store Box No. S M L Total
ABC-123 Red Bay 00035 57 25 15 92
00036 45 50 18 113
Total 102 75 33 210
North 00045 20 3 5 28
Total 20 3 5 28
Total for Red 122 78 38 238
[/tt]
The data come from a SQLServer stored procedure. I'm using CR 8.5.
I've been asked to include the store's box count at store right of its subototal, like this

[tt] Sizes
Style Color Store Box No. S M L Total Boxes
ABC-123 Red Bay 00035 57 25 15 92
00036 45 50 18 113
Total 102 75 33 210 2
North 00045 20 3 5 28
Total 20 3 5 28
Total for Red 122 78 38 238 1
[/tt]
How could I achieve this? If I'm not wrong the crosstab can't be tampered with.

 

[synapsevampire]

Crystal doesn't allow for that flexibility within a cross-tab.

You can create a manual cross-tab own using formulas (group by Style, Color, Store, Box No, place summaries in the Box No group footer, and suppress the details.

The formulas would look like:

if {table.size} = "S" then
1
else
0

Place it in the details, right click it and select Insert->Sum and have it do so for each group (there's an option in CR 8.5 to do all summaries at once.

Won't be too bad to build out and allows for your formatting.

-k

 

[lordhawkins]

I'm going to give it try...
But...

The data come in a table like this

[tt]style color store size boxnumber qty
ABCD RED BAY S 000035 12
ABCD RED BAY M 000035 8
ABCD RED BAY L 000035 14
ABCD RED BAY S 000036 4
ABCD RED BAY S 000036 3
ABCD RED BAY S 000036 2
ABCD RED BAY S 000036 2
[/tt]
In your experience, It's possible to craeate the report?

 

[lbass]

You could try to insert a second crosstab where you use the same rows, with no columns and only one summary, count of boxes. You could then suppress the labels, and suppress the row totals for the inner cells. Then go to the customize style tab->format grid lines->select the lines around the suppressed labels and uncheck "draw". Then use the zoom feature to overlay the second crosstab over the first so that the boxes column aligns correctly.

-LB

 

[synapsevampire]

Sure, you can do this using manual methods, and LB's idea of overlaying might work for you as well.

-k

 

[lordhawkins]

I'm trying right now lbass's advice. The crosstab is a variable width (the order can have different sizes). How can I do the first crosstab "push" the second one?

 

[lbass]

You can't really. In XI there is the capacity for formatting objects to relative positions, but because you need to suppress part of the grid, this wouldn't help. You could consider using the box count as your first column, so that the variation wouldn't matter.

If you have variation in the number (and type) of size columns, then a manual crosstab would not work as well as an inserted crosstab, as you would have to have formulas or running totals that accounted for each possible column.

-LB

 

[lordhawkins]

I'm trying using the box count first to bypass the "stretching problem". Is simpler and elegant.
But...
The count summary is adding 1 for every number in the grid, not for the box number. Like this
[tt]
Style Color Store Box No. S M L Total Boxes
ABC-123 Red Bay 00035 57 25 0 92
00036 0 0 18 18
Total 57 25 18 110 3
North 00045 18 0 5 23
Total 18 0 5 23
Total for Red 75 25 23 138 2

[/tt]

I'll try modifying the stored procedure to bring "1" as a calculated field, and use that as the counting field.
Any idea if this will function?

 

[lbass]

What are you using for your summary field in the box crosstab? It should be distinctcount of {table.boxno}, I think. Is the box crosstab displaying the correct rows/results before you suppress the labels?

-LB

 

[lordhawkins]

You're right.
I've used distinct count instead and the data that came is right. Now I'm experimenting with field suppresing to show only the fields wanted.

By the way...

How could I insert some space between colors?

 

[lbass]

You could create a formula {@space}:
whilereadingrecords;
" "

Add this as your second row field. This will create a subtotal that you can suppress. You can minimize the width of the label area, though not eliminate it.

-LB