Manual Crosstab with DateFields and a restricted layout

[Jcfx]

CR10 on a SQL db

I am looking for a better approach to a quick and dirty report that I had to generate last week. The report is restricted by the requirements of the layout.

Data involved is as follows.

Ord_hdrnumber ord_refnumber stp_arrivaldate stp_schdlatest City
733333 123 DateTimefield DateTimeField Ypsi -->Pickup
733333 123 DateTimefield DateTimeField Boling-->Delivery


This is one record, the first line is the pickup of the order, the second line is the delivery of the order. The department head wants the report to be set up with the data as follows:

OrderNumber, RefNumber, P/u Actual, P/u Latest, MinLate, Delv Actual, Delv Latest, MinLate.

I’ve used the following formulas to display this in a hurry in a manual crosstab. Now of course they want calculations and to schedule the report, so my quick and dirty is no longer valid

In the Group Header: <Grouped by ord_hdrnumber>
//@AssignColumns
  whileprintingrecords;
Stringvar Co1:=""; // P/U Latest
Stringvar Co2:=""; // P/U Actual
Stringvar Co3:=""; // Del Latest
Stringvar Co4:=""; // Del Actual
StringVar Co5:=""; //P/U Min Late
StringVar Co6:=""; //Del Min Late

Detail:
//@AssignValues
whileprintingrecords;
stringvar Co1;
stringvar Co2;
stringvar Co3;
stringvar Co4;
stringvar Co5;
stringvar Co6;

//code for Ypsi is 43947
//code for Boling is 66097

If {rpt_ypsiboldelay;1.stp_city}=43947
 then
Co1:=Co1+cstr({rpt_ypsiboldelay;1.stp_schdtlatest})+chr(13);// P/U Latest
If {rpt_ypsiboldelay;1.stp_city}=43947
then
Co2:=Co2+cstr({rpt_ypsiboldelay;1.stp_arrivaldate})+chr(13);// P/U Actual
If {rpt_ypsiboldelay;1.stp_city}=43947
 then
Co5:=Co5+ToText({rpt_ypsiboldelay;1.MinLate})+chr(13);//P/U MinLate
If {rpt_ypsiboldelay;1.stp_city}=66097 
 then
Co3:=Co3+cstr({rpt_ypsiboldelay;1.stp_schdtlatest})+chr(13);// Del Latest
If {rpt_ypsiboldelay;1.stp_city}=66097
then
Co4:=Co4+cstr({rpt_ypsiboldelay;1.stp_arrivaldate})+chr(13);// Del Actual
If {rpt_ypsiboldelay;1.stp_city}=66097 
 then
Co6:=Co6+ToText({rpt_ypsiboldelay;1.MinLate})+chr(13);// Del MinLate

GroupFooter for each Column formulas identical except for the variable to display:
//@P/ULatest
whileprintingrecords;
Stringvar Co1

Fields are brought over via a stored procedure. MinLate is a calculated field that is the difference between the scheduled date <stp_schdtlatest> and the arrival date <stp_arrivaldate> in minutes.

I’ve never done a manual crosstab with Date fields before, and my original approach was to have two detail lines, Da would have the pickup information to the right<p/u Actual, p/u Latest> and Db would have the delivery information to the left, then use underlay to line them up. I could not get the layout lined up, so I resorted to the above solution.

I am sure there is a really easy way to do this without changing my fields to Strings, but I didn’t see it on Friday?

Any assistance would be appreciated.


Julie
CR 9,10 CE10 Sql DB

 

[Madawc]

A manual crosstab is normally done with a set of Running Totals, each with a distinct rule for totalling. A dodge for multiplying very similar fields is two have Crystal open twice and use copy-and-paste to a dummy report, changing the name before copying them back.

Are you sure you can't do a regular crosstab, and save yourself a lot of work? In Crystal 10, at least, you can do crosstabs from a formula.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Jcfx]

Thanks for the reply Madawc.

The regular crosstab got me close as well, but the layout is still not what they are looking for. Time to go to the boss and let him make the final decision. I've spent way to much time on this



Julie
CR 9,10 CE10 Sql DB