Subreport links

[mcquam]

I have a main report with a linked subreport but I can only see zero values on the main report. I suspect it is a problem with my links as the subreport returns zero records after I linked it.

The subreport (fees1) groups outlays (fees paid) by matter code. eg:

RHb MT-CODE            NET-VALUE
GH1 BBBB0003/000006    -30
GH1 BBBB0003/000007    -30
GH1 BBBB0003/000016    -30
GH1 BBBB0003/000019    -30

There is a formula (outlays) to share the group1 outlays figure with the main report:

WhilePrintingRecords;
Shared numberVar  outlays:= Sum ({NOMTRANS.NET-VALUE}, {NOMTRANS.MT-CODE});

The main report has a formula to show the outlays variable:

WhilePrintingRecords;
Shared numberVar outlays;
numbervar showoutlays := outlays;

This formula is placed in the detail section with the subreport supressed in RHi.

I am trying to link on mattercode but it seems to default to a parameter which I don't want as using the paramenter changes the subreport to return zero records.


I am using MSAccess and CR11.

 

[Madawc]

Check Report > Selection Formulas > Record in the subreport. When you make a link, it always puts a selection statement there. If you don't want it, remove it.

If that's not it, try saving the subreport as a stand-alone report and see if that works.

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

 

[lbass]

You should be linking on matter code, and placing the subreport in the matter code group header GH1a, with the shared variable shown in GH1b or in GF1. If you place the shared variable in the detail section, it will repeat for each detail--but then maybe you want to do that...

-LB

 

[mcquam]

lbass, I do want to show the outlay from the subreport against every matter code record listed in the main report and do a group average.
I moved the subreport into GH2 (I also tried GF1) which is the matter code group and I put the outlays formula in the detail. I linked the subreport with matter code which puts in the parameter ?pm-ftrk_filecoverbackup.mattercode and links with nomtrans.mt-code in the sub. This effectively eliminates all records in the sub. I checked in the select query as madawc suggested and the parameter is not listed.

 

[mcquam]

Here is the SQL query. I need to show all records in the main report and only those in the subreport that match. I wonder if this is where the mistake is.

 SELECT `FTRK_FileCoverBackup`.`MatterCode`, `FTRK_FileCoverBackup`.`Date_Settled`, `FTRK_ReportsData`.`Mortgage_Offer_Rcvd`, `FTRK_OutlaysAndExpenses`.`TMG_Fee`, `FTRK_FileCoverBackup`.`Date_Rcvd`, `FTRK_FeeEarnerDetails`.`Fee_Earner`
 FROM   ((`FTRK-FileCoverBackup` `FTRK_FileCoverBackup` INNER JOIN `FTRK-ReportsData` `FTRK_ReportsData` ON `FTRK_FileCoverBackup`.`MatterCode`=`FTRK_ReportsData`.`MatterCode`) INNER JOIN `FTRK-OutlaysAndExpenses` `FTRK_OutlaysAndExpenses` ON `FTRK_FileCoverBackup`.`MatterCode`=`FTRK_OutlaysAndExpenses`.`MatterCode`) INNER JOIN `FTRK-FeeEarnerDetails` `FTRK_FeeEarnerDetails` ON `FTRK_FileCoverBackup`.`MatterCode`=`FTRK_FeeEarnerDetails`.`MatterCode`
 WHERE  (`FTRK_FileCoverBackup`.`Date_Settled`>=#2006-07-03 00:00:00# AND `FTRK_FileCoverBackup`.`Date_Settled`<#2007-06-12 00:00:01#) AND (`FTRK_FileCoverBackup`.`MatterCode` LIKE 'ABBE0003*' OR `FTRK_FileCoverBackup`.`MatterCode` LIKE 'NATI0002*' OR `FTRK_FileCoverBackup`.`MatterCode` LIKE 'NORT0003*')

 

[lbass]

What are your main report group fields? What tables are you using in the subreport and how are they joined? Do you have other selection criteria in the subreport? The {?pm-} indicates the linking field, so if those are the correct fields that should match, then in the subreport record selection formula you should see:

{nomtrans.mt-code} = {?pm-ftrk_filecoverbackup.mattercode}

I just noticed that you say you are suppressing the subreport--and you can't do that if you want the shared variable to work. You have to instead suppress all sections within the subreport, and also format the subreport (format subreport->subreport tab->suppress blank subreport), and also go to the section expert in the main report->section sub is in->check "suppress blank section" (if you want that section suppressed).

-LB

 

[mcquam]

Many thanks for your help. Although its taken me a while, I'm making some progress. I can see a value in the main report detail section but now I need to calculate an average of the @outlays formula (see above) at GF2 but I don't know how to summarise it as I am trying to average a formula which doesn't work.

GH1 groups on {FTRK_FileCoverBackup.Date_Settled} by month and GF2 groups on {FTRK_FileCoverBackup.MatterCode} in specified order.

 

[lbass]

You are not providing enough information. You just said that the shared variable was appearing correctly in the detail section, so in what sense does {@outlays} not work? Also you need to clarify whether you are talking about the main report or the subreport when you are identifying groups.

Maybe start over and first identify the groups in the main report and where you are placing the subreport, and shared variables, and how the subreport is linked to the main report. Then also identify the groups in the subreport and where the shared variable formula is located.

Finally, please explain your overall goal. Is the average at GF2 level the only thing you are trying to accomplish?

-LB

 

[mcquam]

@outlays works fine now thanks.

I have completed the report except for providing a summary of @outlays. The purpose of this section of the main report is to provide a summary of fees charged less outlays and show the net result by client [(GH1){FTRK_FileCoverBackup.MatterCode}] and month [(GH2)){FTRK_FileCoverBackup.Date_Settled}]. I also need to be able to drill down which I have now accomplished as I can see individual fees, outlays and net fees by matter code.

So the @outlays formula works fine but not at the GF2 level which I need (in other words I need to see the average net fee by client by month). Since your help, the subreport works fine (it is needed because there is often more than 1 outlay fee per mattercode) and I only have the one problem of calculating an average net fee. For example I tried to do:

Average (@outlays)

but it says I can't summarise that field and I'm sure that's an obvious mistake but that is my only remaining problem.

I can list the tables etc if you like but I think I've confused you earlier as it maybe sounded more complicated than it is.

 

[lbass]

I think I do need for you to answer all of my questions, since it appears to me that you keep changing your identification of what is group 1/group 2 and where the subreport is located.

-LB

 

[mcquam]

Main report:

GH1 {FTRK_FileCoverBackup.Date_Settled}
GH1 count{FTRK_FileCoverBackup.MatterCode}
GH1 @reset
GH2 (supressed)@reset
Da outlayssub1.rpt
Db (hidden) MATTR.PROFIT1, @outlays, @netfees
GF2 Avg of MATTR.PROFIT1, (i need ave of @outlays here),(i need ave of @netfees here)
GF1 (supressed)

Subreport formula:

@outlays:

WhilePrintingRecords;
Shared numberVar  outlays:= Sum ({NOMTRANS.NET-VALUE}, {NOMTRANS.MT-CODE});

main report:
[/code]
@showoutlays:

WhilePrintingRecords;
Shared numberVar outlays;
numbervar showoutlays:= outlays;

@netfees:

WhilePrintingRecords;
numberVar showoutlays:={MATTR.PROFIT1}+showoutlays;

[/code]

The subreport layout:

[/code]
GH1 {NOMTRANS.MT-CODE}
D {NOMTRANS.MT-CODE}
GF1 sum of {NOMTRANS.NET-VALUE}
GF1 @outlays

[/code]

 

[lbass]

You are showing me what's in each section, which is helpful, but you have not identified what your group fields are for either the subreport or the main report--which is what is confusing me.

And what fields are you linking the subreport to the main report with?

You also show {@reset} in two different group headers--why? Or are these two different formulas? What's in it?

You say you want an average at the Group #2 level, which means you want an average of the values returned in the detail section. And I don't understand why you would have the subreport in the detail section in the first place. If it is returning one value per group section, it should be in the group header section as I earlier suggested.

-LB

 

[lbass]

Let's assume that your main report groups are on Group#1: date(month) and Group#2: mattercode (this is the reverse of what you said in your post of 6/24, 10:04, but the same as what you said in your initial post). The subreport, linked on mattercode to the main report, should be in GH2b, with a reset formula for the shared variable in GH2a:

WhilePrintingRecords;
Shared numberVar outlays := 0;

In the subreport, the shared variable formula {@outlays} should be in the report footer.

If you want an average of netvalue per mattercode, you can set that up in the shared variable formula also, like:

WhilePrintingRecords;
Shared numberVar outlays:= Sum ({NOMTRANS.NET-VALUE}, {NOMTRANS.MT-CODE});
shared numbervar aveoutlays := average(NOMTRANS.NET-VALUE}, {NOMTRANS.MT-CODE});

In the main report, you do not need to create new variables to show the shared variables, but you do need one to add the sub results, if you want an average at the Group #1 level, so in GH2c, add this formula:

WhilePrintingRecords;
Shared numberVar outlays;
numbervar addoutlays := addoutlays + outlays;
numbervar cnt := cnt + 1;
outlays //this will display the outlays result while doing the calculations

Then in the group #1 footer place a display formula:

whileprintingrecords;
numbervar addoutlays;
numbervar cnt;
if cnt <> 0 then
addoutlays/cnt //average outlays at the group #1 level

If you need a calculation at the group #2 level for average net fees, then I think it would just be:

whileprintingrecords;
numbervar outlays;
(sum({MATTR.PROFIT1},{FTRK_FileCoverBackup.MatterCode})+outlays)/count({FTRK_FileCoverBackup.MatterCode},{FTRK_FileCoverBackup.MatterCode})

...if I'm guessing correctly.

-LB

 

[mcquam]

Many thanks for your efforts once again lbass. Your assumptions are right, Group 1 is month and Group 2 is mattercode. The subreport is linked by mattercode.

I have now placed the subreport at GH2b with a reset at GH2a as you suggest, however I don't think I've been able to explain the problem well enough.

I don't need any further summaries at Group 1 level. I'm interested in showing summaries at Group 2 for:

1A. average fees (average{MATTR.PROFIT1}) (works fine)
1B. average outlays (@outlays I presume)
1C. average net fees (A-B) (this will be ok once I have B)

and drilldown data for Group 2 against individual matter codes:

2A. fee (works fine)
2B. outlays (@outlays I presume)
2C. net fee (works fine)

As it stands I get only the last record at 1B and 2B.

My apologies for dragging this out but it is hard to know what is important and what isn't.

 

[lbass]

Well, wouldn't average outlays simply be:

average({NOMTRANS.NET-VALUE}, {NOMTRANS.MT-CODE})

??? If it is, then use the following formula in the subreport report footer:

WhilePrintingRecords;
Shared numberVar outlays:= Sum ({NOMTRANS.NET-VALUE}, {NOMTRANS.MT-CODE});
shared numbervar aveoutlays := average(NOMTRANS.NET-VALUE}, {NOMTRANS.MT-CODE});

Then in the main report GH2c use the following for net fees:
whileprintingrecords;
shared numbervar aveoutlays;
average({MATTR.PROFIT1},},{FTRK_FileCoverBackup.MatterCode}) - aveoutlays

//{@aveoutlay}- GH2c:
whileprintingrecords;
shared numbervar aveoutlays;

I'm not following the drilldown issue, as I'm not sure what you are showing in the subreport. You would have to drill into the subreport to see the outlays at the detail level (assuming you have the detail level hidden). You wouldn't be able to simultaneously show detail in both main and subreport as the report is currently set up.

Maybe you should show some sample (mock) data as you want it to display, identifying which data is coming from the main and the subreport. Do one view without drilldown and one with.

-LB

 

[mcquam]

Here is some mock data:

Group level report:

Jun 2007   Cases   Fees      Outlays    Net fees
Client 1     209   £144.47   £55.40      £89.07
Client 2     188   £191.28   £33.81     £157.47
Client 3      45   £172.55   £49.11     £126.44
Group 2 summary    £169.43   £46.11     £124.33

Users can drill down into any of the fields above to show for example:

Jun 2007   Fees      Outlays    Net fees
Case 1     £165.00   £30.00     £135.00
Case 2     £210.00   £30.00     £180.00
Case 3      £60.00   £60.00       £0.00

As you will see I don't need to see summary and detail at the same time and my only problem really is showing detail and summary data for outlays which is all coming from the subreport. The purpose of the subreport is to collate transaction data for each case and there is no common date field. There is only 1 fee but there can be several outlays.

 

[mcquam]

I've just realised I made a mistake in the example above. There is no Group 2 summary in the Group level report. Sorry.

 

[lbass]

You didn't answer my question about the average outlay which I guess is what you are showing in the client level report? What trouble are you having in displaying the average at the matter code level? It should work based on my previous suggestions.

I think you should add a second subreport that is linked on mattercode and case for the detail view. You would then just insert an average on outlays and display only the report footer of the subreport in line with the other details. You can't really use the same subreport if you want the matter code level summary in the group header. You can then hide the section containing this new subreport, because this sub won't be used to pass any shared variables.

-LB

 

[mcquam]

I have this in my subreport report footer:

WhilePrintingRecords;
Shared numberVar  outlays:= -Sum ({NOMTRANS.NET-VALUE}, {NOMTRANS.MT-CODE});
shared numbervar aveoutlays:= average({NOMTRANS.NET-VALUE},{NOMTRANS.MT-CODE});

(the outlays figure is a negative value transaction)

In my main report at GH2c I have @sumoutlays:

whileprintingrecords;
shared numbervar aveoutlays

For both client and case level reports for outlays I am currently getting what appears to be the last value in the group which is what makes me think I've got the location of the subreport formulas wrong and that it would work if I could fix this.

 

[lbass]

Where are you getting the "last value in the group"? Do you mean the last value of {NOMTRANS.NET-VALUE}? Please manually test what the value should be with what the shared variable is returning. There should also be a reset for the aveoverlays--you can add that into your reset formula in the GH2a section.

I'm also wondering how you are matching the dates beteen subreport and main report. Are you using the same date range in your record selection criteria in each?

-LB