Cross-Tab In Crystal 8.5 - Columns & Values

[Scooby62]

Hi There.

I'm trying to use the cross-tab feature in Crystal Reports 8.5 that will allow me to put a date or a date range with text in the summary cell. Also I'm trying to put the months of the year across the top (columns). Here's what it should look like:

Name | January|February |March|Etc.
--------------------------------------
Kathy |5 to 9 | <blank> | 21 to 24
----------------------------------------
Trish | | 15 to 23 | <blank>

Is it possible to use the summary cell(s) for non-summary data, day ranges (as above) and/or text?

Thanks Everyone.

 

[lbass]

Are the summary results, e.g., "5 to 9", strings with contents like "5 to 9", or are they formulas drawing on database fields? If so, what is the formula you are using?

-LB

 

[Scooby62]

Hi Ibass.

It's just the day part of a date like 12/31/2003 would be 31.

I'm trying to show what days people have booked off for vacation as in the 12th to 20th, single days or odd days.

I'm getting this data from an SQL database.

Does this help?

 

[lbass]

You need to explain how these dates appear in the database. Are there two fields, e.g., {table.startvacat} and {table.endvacat} or is there one date field and some other field that indicates what the date is for?

-LB

 

[Scooby62]

Actually it's one date/time field and the time off category is 'Vacation'.

 

[lbass]

Please try to provide a little more information or show us some sample detail records. Is there a number field that says how many vacation days are being taken, or does this have to be derived from two different records by subtracting the dates? What happens if a vacation crosses months? Does your detail data look like:
Name Type Date NumberofDays
John Doe Vacation 12/20/03 21

Or, does it look more like:
Name Type Date
John Doe Vacation 12/20/03
John Doe Return 01/09/04

-LB

 

[Scooby62]

You are amazing. Thanks for keeping up with me on this. It's so frustrating.

What I really want to do is just show a vacation schedule for the year. The rows are the person's first name, the columns should be January to December. The data in the middle should reallly show each day they have off. I do have a field called EmpTimEffDt (roughly: employee timsheet effective date or the day they are taking off). Crystal forces me to use a summary function on this field so I've placed it on their twice, one using Nth smallest and the second time using Nth largest. This gives me two dates in each cell but it's still not allowing me to show a date range versus maybe just two days the employee has taken off.

It also won't allow me to add the work 'To' as in dateA to dateB (for ranges).

Does this help?

 

[lbass]

Please provide some sample data laid out like my example with the relevant fields you have to work with. Please note that you didn't really answer my questions, so I still can't really help. Or are you saying that there is a separate record for each day during a vacation period? Again, what should happen in your crosstab is the vacation crosses months?

-LB

 

[Scooby62]

Hey.

In terms of the raw data here's an example:

Mary | 04/12/2004 |
Mary | 05/01/2004 |
Mary | 05/02/2004 |
Mary | 05/03/2004 |

As you can see one person can have several records. I want to summarize the date in terms of days under months of the year columns. So the example above would look like this:

Name Jan Feb March April May June July etc.
-------------------------------------------------
Mary | | | | 12 | 1-3 | |

Does this help? I personally don't think it can be done.

 

[lbass]

It can be done. You would have to create a manual cross tab. First group on the employee name. Then for each month create three formulas, changing the variable names for each month and the month value. This assumes that you are working within a one year period.

//{@resetFeb} to be placed in the Grp header:
whileprintingrecords;
stringvar febsingle := "";
stringvar febconsec := "";

//{@Feb} to be placed in the detail section:
whileprintingrecords;
stringvar febsingle;
stringvar febconsec;

if month({table.date}) = 12 then

if {table.date} = next({table.date}) - 1 or
{table.date} = previous({table.date}) + 1 then
febconsec := febconsec + totext(day({table.date}),"00") + ", " else

if {table.date} <> next({table.date})-1 then
febsingle := febsingle + totext(day({table.date}),"00") + ", ";

//{@displfeb} to be placed in the group footer:
whileprintingrecords;
stringvar febsingle;
stringvar febconsec;

if len(febsingle) > 0 then
(left(febsingle,len(febsingle)-2)
+ if len(febconsec) > 0 then ", " +
(if val(mid(febconsec, len(febconsec)-3, 2)) - val(left(febconsec,2)) + 1 > len(febconsec)/4 then
left(febconsec,len(febconsec)-2) else
left(febconsec,2) + " - " + mid(febconsec, len(febconsec)-3, 2))) else

if len(febsingle) = 0 then
(if len(febconsec) > 0 then
(if val(mid(febconsec, len(febconsec)-3, 2)) - val(left(febconsec,2)) + 1 > len(febconsec)/4 then
left(febconsec,len(febconsec)-2) else
left(febconsec,2) + " - " + mid(febconsec, len(febconsec)-3, 2)));

Drag the employee name to the group footer and then suppress the detail section. After creating formulas for other months, you would get a display like:

Jan Feb Mar
Mary 03 14, 22 - 27 22,23, 01 - 17
John 15,16 03 - 15 02 - 03

In other words, it will show the individual days first, followed by any series of days per month. If there are more than one series per month (probably rare?), the days in the series would be displayed individually by default.

-LB

 

[Scooby62]

Wow, thank you so much. I can't believe your persistance and great support.

This is fantastic!

Thanks again for your dedication to your fellow techies.

Take care.

 

[Scooby62]

Hey Ibass.

A couple of things have come up while working on this assignment.

Due to the fact that I have two fields in the detail area (sing and consec) I can't seem to sort the display to sort by day - like 1,2,3. I'm getting things like: 09,04,05,06. Anyway to sort these? Also if days are coming out like 04,05,06,07 why can't it just say: 04-07?

Any ideas?

FYI, I can email you the output if you'd like to actually see it.

 

[lbass]

When I tested these formulas, consecutive numbers were displayed like 04-07, although I've set it up to default to single values if there is more than one set of consecutive figures per month. Are you looking at the display values in the group footer? The accumulation formulas in the detail section, e.g.,{@Feb}, should be suppressed. As I noted in my earlier post, the single values will be displayed before the consecutive values. I couldn't think of a way using this method to organize them in order. However, you should be sorting on your date field, and if you are, the single values will be in order, followed by the consecutive values, e.g., 18, 20, 03 - 09.

-LB