Repeating crosstab rows

[lefty38]

in crystal reports version 9.2
I want the rows to repeat themselves
How do I make the row data repeat (for exporst to excel)
Currently I get this result:
(count of people by shift, by state, city, zipcode)
Employee Shift
State City Zip 1 2 3

92801 34 2 3
92802 37 3 2
ANAHEIM 92803 56 27 1
CALIFORNIA CITY 93504 1 0 0
CANOGA PARK 91306 53 0 0
CARSON 90747 5 0 0
CAL EDWARDS AFB 93523 153 36 3

for exporting into excel for sorting etc.
how do I repeat the row data?
like this:
Employee Shift
State City Zip 1 2 3

CAL ANAHEIM 92801 34 2 3
CAL ANAHEIM 92802 37 3 2
CAL ANAHEIM 92803 56 27 1
CAL CALIFORNIA CITY 93504 1 0 0
CAL CANOGA PARK 91306 53 0 0
CAL CARSON 90747 5 0 0
CAL EDWARDS AFB 93523 153 36 3

 

[lbass]

If you make the zip code your row #1, it will force the other rows to repeat. Or you could concatenate the state city and zip in a formula, as in:

{table.state}+" "+{table.city}+" "+{table.zip}

Then use that as your only row field.

-LB

 

[lefty38]

Problem is, I do need all of the row fields to be listed and not suppressed.
Seems like what ever field I place into
row 1, 2, 3, in the row field the text get suppressed and row 4 is not suppressed
column 1 2 3
row1 row2 row3 row4
xxx 1 1 1
xxx xxx 2 1 2
xxx xxx xxx 1 1 1
xxx xxx xxx xxx 1 2 1

How do you shut the suppression off?

 

[lbass]

Nothing I suggested would result in suppression. It looks like you might have "indent row labels" checked in the customize style tab. Try unchecking that and then try either one of my suggestions. If you put the zip code as your first row, each row label will repeat and when exported, will appear in separate columns. If you use the concatenation method, you will have one column containing the zip, state, and city.

-LB

 

[lefty38]

Lb,
Sorry it took a long time to get back to you
the data I was using is an example
the real data is
how many people are requesting training classes by shift
by city, building
[tt]
SHIFT
1 2 3
City Building Crs No Crs Title
AZ 123 BBQ-101 5 1 1
AY 234 Fire Safety 3 32 6
454 Sauces 31 6 1
Seattle AB 2321 Wood Smoke 17 22 1
[/tt]
then I am exporting to excel
where Seattle is listed 1 time for 4 rows of data
building "AB" is listed one time for 2 rows of data
Course number is unique along with class title so
that information is a 1 to 1

In cross tab expert
"indent row labels" is off on all of the rows / grid options
"Group options" greys out on anything I place next to "grand total" in Rows: box
Suppress Row grand totals "checked"
Suppress column grand totals "checked"

Any Ideas?

 

[lbass]

My suggestions are the same. You can either make course number your first row instead of your third, or you can create a formula than concatenates the three fields and then use only that one row.

-LB

 

[lefty38]

I am concatenating the fields together.
{table.City}+ ', '+{table.room}+', '+{table.crs.no}}+', '+{course title)

What formula or character do I use, so when I export to excel
the + ', '+ will force the export in excel to separate into separate columns instead as one column of text?
when exported to excel:
it looks like sample below
[tt] SHIFT
1 2 3
[Column A ]
Everett, AZ, 123, BBQ-101 5 1 1
Tacoma, AY, 234, Fire Safety 3 32 6
Tacoma, AY, 454, Sauces 31 6 1
Seattle, AB,2321, Wood Smoke 17 22 1
[/tt]

thanks

 

[lbass]

If you need them in separate columns, then instead of concatenating, use separate rows, but in this order: course no, title, city, room. Then if you need to, you can shift the columns around in Excel.

-LB

 

[lefty38]

LB, do you have an e-mail addy i can send a file too?
I have 2 samples of the data i am using and the problem associated with this topic

 

[lbass]

No, but you could use the new file upload feature (see Step 3) in the reply area. But I'm not sure why you can't just describe the issue. What happens when you try my last suggestion? Should work.

-LB

 

[lefty38]

LB
sometimes pictures are easier

here is the layout in CR
Crosstab expert

http://mysite.verizon.net/res19czn/index/images/CRexpert.jpg

(next 2 pictures I had to mask out some data)
here is a pic of the preview page

http://mysite.verizon.net/res19czn/index/images/CRpreview.jpg

Here is a pick of the excel export

http://mysite.verizon.net/res19czn/index/images/CRpreview.jpg

I think the problem lies in the group header
you can not put the crosstab expert into the details section

 

[lbass]

Okay, try this. Concatenate the row fields into a formula {@concat}. Add this as your first row. Then add the individual row fields in the order you want. When I tested this, it forced all row fields to repeat. In Excel, you then just need to delete the concatenated column.

-LB

 

[lefty38]

thanks lb

there is a section in the data that didn't work
i think it has to do with null values in the room field

I will try a formula
(if isnull({room}) then
"" else {Room}

to see if that will help

 

[lefty38]

oops
I meant
(if isnull({room}) then
"Other" else {Room}

 

[lbass]

I don't know what you mean.

-LB

 

[lefty38]

Lb your help has been great -

It has help me learn "Crystal crosstabs" is very limited in some ways

in row one - instead of concatinating together the rows
{table.crs_no}+" "+{table.crsTitle}+" "+{table.City}+" "+{table.Bldg}
to create the unique "string" how about
where row #1 is just an sequentionally counting value?
[tt]
SHIFT
# crs no crs title, city bldg 1 2 3
01 213-a MSDOS-1 Everett 123 5 1 1
02 213-a MSDOS-1 Tacoma NW12 3 32 6
03 123-d MSWORD-6.0 Tacoma 454 31 6 1
04 44423 Basket-weave SanFran TwinTwr 17 22 1
[/tt]

how can I sequence the first row so that it forces all of the other rows to not be suppressed?

(my feeble attempt at a formula)
whileprintingrecords;
stringvar type;
if isnull then
type '0' Else + '1'
Loop

In other words row 1 will just be a sequence of numbers
based on how many rows are returned in the data

 

[lefty38]

Laughing - well that did not work
I found recordcount and it did exactly what i wanted

counted each record - so that nothing totaled in the
crosstab column

Now i am looking at something like this
[tt]
CrsNo Crs title City Bldg Shift
F1223-2 FinancePlan ORPORTLAND Other 1 0 0
repeat repeat CAEL SEGUN Other 1 0 0
repeat repeat CASAN DIEG Other 1 0 0
repeat repeat FLFORT WAL Other 1 0 0
repeat repeat NJTETERBOR Other 1 0 0
TNOAK RIDG Other 1 0 0
[/tt]

Now I need a formula that says
if CrsNo row is blank
then copy from preious record

tried this one (didn't work )
stringvar type;
if not isnull({V_WAITLIST_PREREQS.CD_CRS}) then
type := {V_WAITLIST_PREREQS.CD_CRS} else
type := type;

 

[lbass]

I think you should just create a manual crosstab, as this will give you the control you need.

-LB