2 Questions on a Crosstab in Crystal reports

[puzzles7]

Hello, I am very familiar with Access but it's been a long time since I've used Crystal and so I'm a little lost. I am working in Crystal 10 and I need to produce a crosstab that shows a list of dates when certain training has been completed for all people in a particular fleet.

My Row headings include Position, Name and EmpID.
The Column heading is the Training Title.
My summary field is the Date the training was completed.

I selected the fleet and the associated training items to pull but I am left with two problems.

1. If no one has taken a specified training item then that item doesn't show. I need to show all items, especially if no one has taken them.

2. I really don't need any summary data here, it doesn't make any sense. I have supressed it in every area I can think of so the summary data no longer shows but now I can't get rid of the white space left behind by the "Totals" rows and columns.

Any help would be most appreciated.

 

[abhi900]

Hi,

i have seen with cross tab that if there are no records for that data then it wnt show up.. irrespective.

i had something similar and the count was 0 for one particular column value but Cross tab refused to show that column.

i think its a limitation for cross tab.

 

[lbass]

Please see thread149-1567016 for one solution to your first problem.

Regarding your second issue, if you go to the customize style tab, you can specify "suppress column totals" and "suppress row totals". You can also suppress subtotals (upper left corner of this screen).

-LB

 

[puzzles7]

Thanks so much. I was able to make your suggestions work. I also tricked the system into showing all my training items by creating a dummy person who has all the training. Now I just have one last question.

How would I apply conditional formatting to the dates of issue when the expiration dates vary by training item?

In other words...My summary fields briong in all the training dates for each item and each employee. Some training classes must be repeated annually while others could be every two or five years. I need to highlight in yellow those that are due to expire within 90 days of the report and in red those that are already expired.

Thanks again for any assistance.

 

[lbass]

I'm not clear on the relationship between training date and expiration date (is there a field for this?)

-LB

 

[puzzles7]

Sorry, in strategy meetings all week.

Let me explain a little better. Below I have shown an example of what I am looking for. The name of the mariners and the training titles will be dynamic. The dates are the dates that the mariners took the classes. Each class has a different timeline for expiration based on a variety of things. The same class though could have a different expiration date for different fleets so everything is driven off of the fleet selected through the parameter.

I need the dates then to be highlighted in yellow or red based on their expected expiration date. My original parameter pulls in the appropriate training with the selected fleet. Now I need to apply conditional formatting to the dates of the training classes taken by each mariner. Is this even possible?

TRN 1 TRN 2 TRN 3
EMP 1 2/15/10(r) 3/15/11 11/10/09
EMP 2 4/25/10 11/10/08(r) 1/2/11
EMP 3 6/30/10 5/27/10
EMP 4 6/12/10

NOTE:SOME CLASSES HAVE NOT BEEN TAKEN BY MARINER

I hope this helps

 

[lbass]

You didn't really answer my question. In what table is the expiration date located? Is it in the mariners table? Is there only one date per mariner/training cell? You would have to add this field to the crosstab and then use a technique that shares the values between summaries. Please answer the above first.

-LB

 

[puzzles7]

Ok, let me try to explain this beter...

I am pulling in the MAX{crew_training.issue_date} for each training item. Because SOMEONE is taking the class then it will simply show as a blank for theose mariners who do not have it.

We only track issue dates for each mariner training because the expiration of each class can vary based on the contract the mariner is working under. Because of this there is no table and I cannot create one within the main database. I brought the issue dates in as my summary field and removed all the totaling functions but I still need to highlight an expiration based on the current fleet, which is chosen in the parameter.

I hope that helps to better explain it.

 

[lbass]

No, not really. What does the "issue date" refer to? Issuing what? Where is the expiration date coming from? Are you saying that the expiration date is user-defined based on the fleet chosen in a parameter? Do you have a formula or something that does a crosswalk between the fleet chosen and the expiration date?

Let's say this is the case and you have a formula like this:

//{@expirationdate}:
Select {?Fleet}
case "Fleet1" : date(2011,3,31)
case "Fleet2" : date(2011,6,30)
case "Fleet3" : date(2010,12,31)

You need to add {@expirationdate} as your FIRST summary field in the crosstab, using a maximum as the summary. In preview mode, right click on the field->format field->suppress->x+2 and enter this:

whileprintingrecords;
datevar exp := currentfieldvalue;
true

Then select your training date summary field->right click->format field->color->background->x+2 and enter:

whileprintingrecords;
datevar exp;
if currentfieldvalue+365 < exp then
crred else
if currentfieldvalue+275 < exp then
cryellow else
crnocolor

This assumes an annual deadline for retraining. I'm still not understanding whether the "expiration date" is a static date per course or whether it is based on intervals, but anyway, maybe you can adapt this technique. I think you would have to build in the course differences in the expiration date formula.

-LB

 

[puzzles7]

I'm still not sure we're on the same page. Currently my Summary field is the Issue Date for the Training class the mariner took. The classes can be taken at various times as many are taken online so there is no consistency. The complete class list varies by fleet as does the need to repeat each class. To give you a better idea Fleet 1 may require Training A & B, both with an annual renewal date. Fleet 2 may require Training A & C, A is required every 2 years and C is annual.

I'm not really following your formula with supression of a 2nd summary field so I'm hoping this makes my case more clear.

Thanks

 

[lbass]

My point is that you have to define what is the intended expiration date within the crosstab. So do you have some field that defines the frequency of the renewal date? If so, what is it, and what are the values it can contain, e.g., annually, biannually (every two years), semiannually, etc.?

-LB

 

[puzzles7]

Unfortunately there is no filed containing this data. I cannot create a table in my source so I will have to code it. I am trying to avoid having a separate report for each fleet by allowing them to select the appropriate fleet and bringing the training items and expiration dates along with that selection.

 

[lbass]

Then you have to create a formula to handle this, e.g.,

//{@Terms}:
select {table.training} //this should be same as your column field
case "TrainingA" : 365 //Annually in days
case "TrainingB" : 730 //Biannually"
case "TrainingC" : 182 //Semiannually" //etc.

If the terms vary by fleet, you will have to build that into this formula like this:

if {?Fleet} = "Fleet1" then (
select {table.training} //this should be same as your column field
case "TrainingA" : 365 //Annually in days
case "TrainingB" : 730 //Biannually"
case "TrainingC" : 182 //Semiannually" //etc.
) else
if {?Fleet} = "Fleet2" then (
select {table.training} //this should be same as your column field
case "TrainingA" : 30 //Monthly in days
case "TrainingB" : 365 //Annually"
case "TrainingC" : 7 //Weekly" //etc.
)

Then add {@terms} as your FIRST summary field in the crosstab, using a maximum as the summary. In preview mode, right click on the field->format field->suppress->x+2 and enter this:

whileprintingrecords;
datevar term := currentfieldvalue;
true //this will suppress the summary

Then minimize the height of this summary by grabbing the borders and dragging.

Then select your training date summary field->right click->format field->color->background->x+2 and enter:

whileprintingrecords;
datevar term;
if currentfieldvalue + term < currentdate then
crred else
if currentfieldvalue + term in currentdate-90 to currentdate then
cryellow else
crnocolor

-LB

 

[puzzles7]

Thank you I will give it a try and let you know how it works out.