Sub Report Help - variable passing

[smueller72]

I have a very similar(I believe) need:

Here's my situation:

General:

The report displays details and $ totals for home inspections performed by home inspections franchisees between the 6th of the prior to the 5th of the current month.

The main report layout is as follows:

GH1: Office Id
Details: Office, Report#, Type of Inspection, Inspection Date, Inspection$
GF1: Count of Inspections, Sum of Inspection $'s, Formula to Calculated Commissions Owed.

Pretty basic stuff I know and the main report is fine.

Now the challenge is that I need to add to the commissions any additional fees that the franchisee owes for the given month that the report is being run for. These fee are housed in a separate table and a single office can owe multiple fees for any given month. My problem is how can I select, sum and add the additional fees to the commissions calculated in the main report? Perhaps a sub report is not the answer especially if I can't pass the sum of the extra fees back to the main report. Is there a solution? Any help would be greatly appreciated.

 

[lbass]

Thanks for starting a new thread. Please note that in the thread you originally posted in, the poster was trying to pass information from an on-demand subreport. You can pass info from a regular subreport to a main report. Place the subreport in a group header section. The subreport should be linked on OfficeID and either linked on a daterange parameter or with the same date criteria in the record selection formula of the subreport as in the main report.

In the subreport, create a formula:

whileprintingrecords;
shared numbervar fees := sum({fees});

Place this in the subreport footer and suppress any sections (even the report footer) that you don't want displayed. Then in the main report, create a formula:

whileprintingrecords;
shared numbervar fees;
sum({@commission},{table.localofficeID}) + fees

Place this in the group footer for local office.

You should also add a reset formula in a group header section_a (above the section the subreport is in). The section can be suppressed, if you like.

//{@reset}:
whileprintingrecords;
shared numbervar fees := 0;

This will prevent the previous value from populating your group footer formula if the subreport has no fees.

-LB

 

[smueller72]

Thank you very much!! It seems to have worked perfectly.

Now one more question/problem:

Even if an office does not have any inspections for the given time period - aka no detail records to report they still need to show on the report because there are minimum commissions they may need to pay and/or they may have additional fees due. These minimums and fees due should show in the the report summary for the given office. Hope this makes sense. Is there way to accomodate this with CR? Thanks in advance for any help you can offer.

 

[lbass]

I don't know what tables you are using in the main report, but you would need to use a table that contains all offices as your leftmost table and then do a left join from that to the table with the inspections in it and also use that master table for your link to the fees subreport. Not sure where the other minimum commissions are coming from.

Or you could add a subreport in the report footer that is limited to fees and commissions for those offices with no inspections.

-LB

 

[smueller72]

Thanks for the prompt response. I had previously tried every combination of linking the tables and just received a variety of errors. The tables are as follows:

Office(contains general office info with OfficeID as the key)
Inspections(contains inspection detail - ties to office by OfficeID)
Office Fees(contains additional fees - tied to Office table by OfficeID)

The commissions are calculated within the main report. If the office doesn't have any inspections against which to calculate against the inspections they are charged a monthly minimum which is just hard coded into the commision calc formula within the main report.

Any ideas? Thanks again in advance for any help.

 

[lbass]

Use the Office table as your master table, with a left join from that to Inspections. Use that to connect to the Fees subreport, too. In the formula where you calculate the commission, you could set up the default to the minimum commission, by using something like:

if isnull({inspection.officeID}) then 500 else
<your commission calc>

-LB

 

[smueller72]

Thanks again. I think I'm getting there. The left outer join is working but all offices still not showing. I think the problem is that I have a record selection formula on the main report that selects the inspections that fall between the 6th of the prior and 5th of the current month. Is there another way for these to be selected that would still make all offices available whether they have inspection records meeting those criteria?

 

[lbass]

You can't just use isnull({inspection.ID}) or {inspection.date} ={?daterange} because there could be offices that have inspection dates in other periods and therefore would not qualify for inclusion under either condition.

I think you have to remove the criteria and then use formulas to capture the data of interest, as in:

if isnull(inspection.officeID}) or
not({table.inspectiondate} in {?daterange}) then
0 else
{table.amt}

Or use running totals, where you use an evaluation condition:

{inspection.date} in {?daterange}

-LB

 

[smueller72]

Ok - sorry for so many questions. So you're suggesting to remove the record selection and create a formula to do the selecting but how/where do I put/use the formula so that the inspections appear in the Detail section? Make sense? Just a bit more expounding on your last post would probably be a bit helpful. Thanks for all your help.

 

[lbass]

The formula doesn't do the selecting. It is only conditionally showing amounts for offices that have inspections during the time period you select. You would create this formula in the formula expert and add it to your detail section instead of an amount field. This is just an example. Any calculations would have to be done like this or using running totals, since now all offices are being returned to teh report.

If you can tolerate having those offices with no inspections display together at the end of the report, it would be simpler for you than going this route.

-LB

 

[smueller72]

Sorry to be such a pain but I still can't get this to work. I've added the same formula that I had as a record selection formula as a formula in the 'Suppress' option of the 'Section Expert' thinking that this would cause the records not meeting the criteria to be suppressed. However, this isn't happening.
Will this work? Any ideas why it isn't?


As an fyi, and probably something I should have mentioned earliers, I am using the CR version that is included with SI 7.0.2. I assume that this may make a difference.

Thanks in advance for your help.

 

[lbass]

You are better off using conditional formulas to display the fields you want instead of using suppression, since suppressed values will still contribute to calculations.
If you were going to use suppression for some reason, you don't want to do that in the section expert, because it will eliminate the offices you are trying to retain. YOu would use field suppression. You would select the fields (not the office field) that could be displaying info outside your date range all at once and then right click->format objects->common->suppress->x+2 and enter the opposite of your selection criteria, e.g.:

not({table.date} in {?daterange})

Perhaps you are using some combination of suppression and conditional formulas.

-LB