Passing Subreport Value

[BradW]

Hi all,

First, thanks much to all for your help!

I'm using Crystal Reports 2013.

I have a fairly complex report coming off of a SQLServer database. The report involves many tables.

The records in this main report have a one-to-many relationship with one of the tables that shows actions taken on the main record. Each action has a number of DateTime fields that record specific steps on that action. I need to determine the earliest DateTime in one field among all the records present and the latest DateTime in another field (again for all the records).

I then want to transfer them to the main report and finally calculate the amount of time that elapsed between these two DateTime records that have been determined to fit what I need.

I have a Subreport that takes the action records, uses the appropriate DateTime field, calculates the Minimum value and then has a formula that creates a shared variable for the record needed. This all works fine.

I then have a formula on the main report that picks up this shared variable and uses it in the main report.

I plan to use the same process to get the other DateTime field, once I get the first one working correctly.

Everything works, except for the DateTime that is brought back to the main report appears in the detail line AFTER the correct detail line for the relevant record. The value is calculated correctly and is transferred to the main report correctly but then appears in the wrong record, always one record after where it should be.

Can anyone tell me why this is happening and how to correct the problem?

Thanks so much for your help!

Brad

 

[lbass]

The subreport (which I assume is placed in a detail section) runs AFTER the detail line is populated and is shared only then, so is only available after that row. Try inserting another detail section and place the subreport in detail_a and then reference the shared variable in detail_b.

-LB

 

[BradW]

Thanks so much for the quick reply!

 

[BradW]

LB,

I implemented all the changes and it works great, thanks so much! I did implement it in a slightly easier way (tons of fields in details) by inserting a group (there are no other groups on this report) and placing the subreport in the group header. I plan to hide the header/footer when running so that they are not visible.

The value that is coming over from the subreport is used in a couple of formulas AND then the formulas are used in some summaries on this report, the report is blowing up saying that the formulas can not be summarized now. Is there a way to work around this?

Thanks for all your help, I really appreciate it.

Brad

 

[lbass]

You If you are using shared variables in your formula(s), you will need to use variables in the main report in order to create the desired summaries.

-LB

 

[BradW]

LB,

I've tried this, and am still getting errors about fields that can not be summarized, here is what I have:

DateTime field coming in from subreport

I then have a formula (this is in the details line and is calculating for every record):

datediff("s",{DateTime from SubReport},{Second DateTime Field});

I changed this to:

Local NumberVar DE:=
datediff("s",{DateTime from SubReport},{Second DateTime Field});

DE

I then tried this formula to average the records, this is going in the report footer as an overall average:

Local NumberVar AV:=Average{DE};

AV

And this average formula tells me that the field can not be averaged.

Thanks again for all your help!

Brad

 

[lbass]

Change your datediff formula to include some other variables, like this:


Whileprintingrecords;
Shared datetimevar subdt;
Local numbervar DE := datediff(‘s’, subdt, {seconddt});
Numbervar totsec := totsec + DE;
Cnt := cnt + 1;
DE //to display the date diff

In the report footer, add this formula {@avg}:

Whileprintingrecords;
Numbervar totsec;
Numbervar cnt;
If cnt <> 0 then
Totsec/cnt

If the summary is at a group level, you would need to reset the variable by adding a formula like this in the group header:

Whileprintingrecords;
Numbervar totsec := 0;
Numbervar cnt := 0;

PS. I’m writing this on my iPad and you might need to replace the quote marks around the ‘s’ because of the different character set—-but only if you get an error.

-LB

 

[BradW]

That did the trick, thanks so much for all your help, it is much appreciated!