calculate working days using dates within subreport to main report

[lthomas6]

I have a crystal 10 report with sql backend.

My main report has 8 subreports. One of the subreports(RR) has a field called requested Response. this field returns a date value.
The second subreport (IR) has a field called initial response which returns a date value.
What I need is the value for RR-IR in the main report. This will be referred to as the working days. So its calculation equals Requested response minus initial response.

All the fields in the main report are located in the Details of main report.

 

[lbass]

In each sub, set up a shared variable formula in the subreport footer:

whileprintingrecords;
shared datevar rr := <your date here>;

Repeat for the other sub, using a different variable name, e.g., "ir". Then insert a detail_b section in the main report and create a formula like this:

whileprintingrecords;
shared datevar rr;
shared datevar ir;
rr-ir

If these variables can be null, you need to add a detail_c (can be suppressed) with a reset formula:

whileprintingrecords;
shared datevar rr := date(0,0,0);
shared datevar ir := date(0,0,0);

-LB

 

[lthomas6]

I also need to mention that the field rr and ir, in the database are a fieldtype of varchar 255 even though it displays as a date field (03/01/2011).

How would this info alter what put in these formulas?
Because it fails if set as a datevar.

 

[lbass]

That makes no sense to me. How do YOU extract the date from the field? If is is a string field, try wrapping it in date() and see if it returns the correct date.

-LB

 

[lthomas6]

The actual field in the database = 03/01/2011. so in other words even though it is a varchar 255 field, it is displayed in date format.

In the report I tried creating a formula:

@makedate = date(rr)

Then I modified my shared variable to say:
whileprintingrecords;

shared datevar rr := {@makedate}

This returns an error stating " A variable cannot be redeclared with a different type" .

Any suggestion on how I can get this to work? Realy appreciate what you have provided so far.

 

[lbass]

Just convert it right in the formulas:

whileprintingrecords;
shared datevar rr := date({table.varchar});

Be sure to delete your makedate formula first.

-LB

 

[lthomas6]

If I do the above as you stated, it gives me error message:
A variable cannot be redeclared with a different type.

 

[lthomas6]

I was able to figure out the error by renaming my variables.

However the formula in the main report is returning 30,687 for every record. what I am trying to accomplish is to calculate the number of working days. So if rr = 1/1/2011 and ir = 1/10/2011 then it should equal 9. How can I accomplish this?

 

[lbass]

I think you didn't delete the old makedate formulas.

Please verify that the shared variable formulas are generating the correct values in the subreport footer. Then check to see that the same values are being picked up in the formulas in the detail_b section.

-LB

 

[lthomas6]

I did delete the old makedate formula and I have now verified that it is generating the correct values.

The issue now is that it is generating 0 for the first row and records and the correct number on the next row and record.

I tried splitting it so that there are two detail section (detail a and b) and that did not solve the problem. Any idea why?

 

[lbass]

The subs have to be in detail_a, the calculation formula in detail_b, and the reset in detail_c.

-LB

 

[lthomas6]

Thank you very much, that did the job!

With the calculation formula being in details b, it doesn't line up with the other fields in details a. Is there anyway to get the formula in details a and this working?

 

[lbass]

No, the calculation MUST be in a section after the one containing the subs, but if you format detail_a to "underlay following sections", you should be able to align the fields.

-LB

 

[lthomas6]

Thank you, it looks lovely. You have been so helpful! Have a great day.