Formulas with a Sub Report 1

[AngChristman]

Is there a way to use a sub report value in a formula?

What we have is 3 different databases.

1 for estimating
1 for planning
1 for shop floor data collection

I need to get the estimated run time (from estimating) and subtract the actual time (from shop floor data collection). In order to do this, I have to cross two different databases. This causes the report to take way to long to run and it almost causes a system shut down.

If i make a sub report for the actual time (just that field) I can put it into the report and it doesn't take long to run at all. The problem is I don't see how I can write a formula where I take the estimated value from the report and subtract the data in the sub report.

 

[Malekish]

What you are looking for is called a shared variable. Inside the sub report define the variable as shared and then in the parent report you need to define the variable under the same name and shared as well.

If you search this forum for 'shared variable' you will find many examples.

 

[lbass]

Be sure to reference the shared variable in the main report in a section below the one containing the subreport, or the value won't pass to the main report.

-LB

 

[AngChristman]

Would it work if I had the variable 'whilereadingrecords' instead of while printing records? I get a little mixed up on when the formulas are read (1st pass and 2nd pass).

 

[Malekish]

The way I understand it, 'whilereadingrecords' doesn't work that way.

The data needs to be created (either read, calculated, whatever) in the subreport before it can pass the data to the parent.

 

[lbass]

Would WHAT work?

-LB

 

[AngChristman]

Okay, I have entered in the shared variables

In the main report I set the actual seconds it took as Numbervar a:

whileprintingrecords;
shared numbervar a := (Sum ({sfeventcds.elapse-time}, {@StatCode}));

In the sub report I have entered the estimated seconds it should take as variable y:

whileprintingrecords;
shared numbervar y := {PV_JobStep.LabourSecs};


Both variable A and Y are placed in the report footers and show correctly.

Then in the main report I made this my calculation using the two variables

whileprintingrecords;
shared numbervar a;
shared numbervar y;
a-y

I placed is calculation in the Group footer two and it does calculate the difference correctly but one line down. For example I will use Job B and C:

Job B Actual Seconds = 1000 Estimated = 500 Difference = 0
Job C Actual Seconds = 200 Estimated = 300 Difference = (500)
Difference = 100

The difference is for job B shows up in the line across for job C and so on for each following line across.

 

[lbass]

You said the subs were in the REPORT footer, but it appears they are somewhere else. Your calculation formula MUST be in a section below the ones containing the subs, so if subs are in GFa and GFb, then the calculation formula should be in GFc. You might also need a reset formula in the group header to reset the shared variables in case of nulls.

-LB

 

[AngChristman]

Sorry, they are in group footer two. Only the report header, page header and group footer two are showing. Everything else is hidden or surpressed.

I do not have any GFa or GFb's.

How would I reset these?

 

[lbass]

Insert another group footer section by right clicking on the current group footer->insert section below. Then move your calculation formula into GF_b.

-LB

 

[AngChristman]

I need to be able to export this into excel, but if there is GFb, then they won't be lined up and the Export will not work.

Can I place everything into the GFb or does it have to be in the section below?

Can I create another sub and add this into the report separately?

 

[lbass]

You should have mentioned the need to export earlier. You cannot place formulas that calculate based on shared variables in the same section as the subreport that provides their value. Instead, pass the shared variables that you want displayed in the main report to the GF_b by creating formulas that reference each of them and place them next to the value generated in the calculation.

Then force the GF_a to disappear by first removing the borders from each subreport, then selecting each subreport->format subreport->subreport tab->suppress blank subreport. Within each subreport suppress ALL sections. Then in the main report select the GF_a in the section expert->suppress blank section.

What you cannot do is suppress the subreport object itself or suppress the section containing it.

Then export to regular Excel export and choose the GF as the section to determine column width. I tested this and it worked perfectly.

-LB

 

[AngChristman]

Thank you so much. It worked wonderfully, but I had to underlay GBb and when exporting to excel I had use set column widths of 200 otherwise it would merge cells and not allow for sorting.