Cross Tab Crystal 8.5

[ste4en]

I am trying to make a cross tab report based on data structures as below:

ITEM modified
Widget1 5/15/06
Widget1 6/15/06
Widget2 3/13/05
Widget2 6/14/06
Widget2 7/23/06
Widget2 8/15/06
Widget3 3/15/05

The cross tab, should list the number of mods accross the top and the date of each mod in the cell; as follows:

item;1;2;3;4;5
Widget1;5/15/05;6/15/06
Widget2;3/13/05;6/14/06;7/23/06;8/21/06
Widget3;3/15/06
Widget4.....

How can I achieve this.

Thanks

 

[IdoMillet]

Can't be done with a CrossTab, but you can simulate it.

The top row would be simple text in the report header.

Group on Item, and sort by date within the group.

In GH section (suppressed), use a formula to reset a global string var to ""
IN D section (suppressed), use a formula to concatenate the date to the global string var
In GF section (visible), show the item name and use a formula to show the string var.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

I don't think you will be able to do this in a crosstab in 8.5 unless you have a sequence number for each modification.
Instead, you could insert a grop on {table.item} and then use three formulas:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar datex := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar datex := datex + totext({table.date},"MM/dd/yy") + " "; //Replace spaces with a divider if you wish

//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar datex;
trim(datex)

If you use a divider like a semicolon with no space, then to remove the final semicolon, change {@display} to:

whileprintingrecords;
stringvar datex;
left(datex,len(datex)-1)

Then drag the group name into the group footer and suppress the detail and the group header section. The only problem I can foresee is if you have a large number of dates and then you would run into the 254-character string limit for 8.5.

-LB

 

[lbass]

Ido, I didn't see your post. Fooling around too long with this!

-LB

 

[IdoMillet]

Great Minds... )

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ste4en]

Thanks for the responses - I don't want to end up with hundreds of dates ascolumn headers, just a number from 1 - 6 say, showing the instance / number of instances when the mod was issued.

I think that the it looks like I need to deal with this in Access before I get to Crystal - so I posted the question in the Access query forums.

thanks

 

[lbass]

Nobody was suggesting that you use the date field as a column header. You seem to want to put the dates in columns based on the sequence they were issued. Both Ido's and my solution addressed that.

-LB

 

[ste4en]

Ok I guess I did not see where you were going, I am a beginner, but now I get it...the 256 limit may become an issue one day but it will work just fine for now.

Thanks again.

Steve

 

[ste4en]

Ibass/IdoMillet,

Hope you pick up this message; I tried what you gave me, but I get the error "A string can be at most 254 characters long"

I deleted the data I have in the database to just show a few records with only one instance if a date and still get the error.

Any ideas,

thanks
Steve

 

[IdoMillet]

It would help if you take the time to explain what you did.
Show the formulas and state their sections.

If one of the formulas gets beyond 254 characters, you are in the best position to debug by tracing what's going on. You can modify the formula by modifying it to accumulate a short constant and watch what it does.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ste4en]

I did exactly what Ibas laid out. I changed the date field for my date field and grouped on my item field.

At your suggestion I changed the date for an "M", and it gave me a list of widgets with a M next to them; Just one M as there was only one modified date using:

stringvar datex := datex + totext("m");

THEN I tried the date again and no luck same string error. So I tried to go back to the M and that does not work either now, just a list of widgets with no "M"

?
Thanks for helping.

 

[lbass]

How many dates can there be per widget?

-LB

 

[ste4en]

I would say a max of 10. But could be more. Usually 1 or 2. Incidently widgets are actually drawings and the dates are the dates of drawing revisions, so it depends on how many times then engineer changes things.

 

[lbass]

You should be able to avoid the 254 character limit then, by making sure each element of the string is less than 25 characters. To minimize the use of characters, use a formula like:

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar datex := datex + totext({table.date},"MM/dd/yy") + ";"; //Replace spaces with a divider if you wish

This would only use 9 characters per date, meaning you could have as many as 28 dates in a string. This suggests that you might not have the reset formula placed in the right section. For further help, you will have to provide more information about exactly what you did--the exact formulas you used and where you placed them.

-LB

 

[ste4en]

Ibas, I started again in a new report and it worked just fine.
Many Thanks
Steve

 

[ste4en]

Ido/Lbass

If you pick this up this post I have one modification that would make the report better...

I would like the string of dates created by the statement below to show the dates in order, right now they seem to be random or coming from the way they are entered into the table - (not always in date order). Is this possible.

stringvar datex := datex + totext({table.date},"MM/dd/yy") + ";"; //Replace spaces with a divider if you wish


Thanks

 

[lbass]

All you need to do is sort by the date field. Go to report->sort records and add the date field there.

-LB

 

[ste4en]

Thanks again.