Getting only last value from subreport to main 1

[antonx]

Hi,

My main report is grouped by a formula (QTR_Desc) and my subreport is grouped by a formula (QTR_Desc). Both formulas are the exact code:

DatePart ("q", {NEW_BUS_DM.TRANSACTION_DT}) & "Q"

I have my subreport in GH1a in the main report so that it runs first. All the other fields are in GH1b. My problem is that I only get the last value 4Q from my subreport displayed in GH1b. Is there a way to pass a series of subtotals from one report to another when both reports use the exact group formula?


Here is the problem:

sub 1Q 1,315
sub 2Q 1,300
sub 3Q 1,525
sub 4Q 1,385

main 1Q 1,398 1385

sub 1Q 1,315
sub 2Q 1,300
sub 3Q 1,525
sub 4Q 1,385

main 2Q 1,141 1385

sub 1Q 1,315
sub 2Q 1,300
sub 3Q 1,525
sub 4Q 1,385

main 3Q 916 1385

sub 1Q 1,315
sub 2Q 1,300
sub 3Q 1,525
sub 4Q 1,385

main 4Q 635 1385


Thank you.

 

[synapsevampire]

To get the best results, please post Crystal and database version, example data and expected output.

You gave no indication of what you intend to do with these subtotals, so it's very difficult to describe a proper solution, but this should help:

If you simply want to display the quarterly results, display them within the subreport, if you need them for calculations against the main report numbers, create 4 shared variable in the subreport to return the values.

-k

 

[Madawc]

In the subreport, do a running total for each quarter. Pass these back using shared variables. Or else just put them in the subreport total line and display them from there, if it suits your needs.

Madawc Williams
East Anglia, Great Britain

 

[antonx]

Happy New Year !

I have Crystal Reports 8.5 and using DB2 tables connected to the mainframe (via ODBC).

Please see my original thread for example data.

Ok, now I use 4 shared variables (each variable is a subtotal and represents a quarter) created in the subreport and then it passes them to the main report. The problem is that only the last value (4Q) is displayed in the main report. The others (i.e 1Q, 2Q, 3Q) displays zero which is incorrect.

Here is an example of what I am getting in my main report:
main 1Q 1398 0
main 2Q 1141 0
main 3Q 916 0
main 4Q 635 1385

This is what I want:
main 1Q 1398 1315
main 2Q 1141 1300
main 3Q 916 1525
main 4Q 635 1385

Then I would need to divide the two values in each quarter (for example 1398/1315).

Here are my 4 shared variables (the way they are defined in my subreport in Group Header @Qtr_Desc):

@Shr_Qtr1_Plan
whileprintingrecords;
shared numbervar Shr_Qtr1_Plan := 0;

if GroupName ({@Qtr_Desc}) = '1Q'
then Shr_Qtr1_Plan := Sum ({@Curr_Year_Plan_Pol_Cnt}, {@Qtr_Desc})
else Shr_Qtr1_Plan := 0;

Shr_Qtr1_Plan;

@Shr_Qtr2_Plan
whileprintingrecords;
shared numbervar Shr_Qtr2_Plan := 0;

if GroupName ({@Qtr_Desc}) = '2Q'
then Shr_Qtr2_Plan := Sum ({@Curr_Year_Plan_Pol_Cnt}, {@Qtr_Desc})
else Shr_Qtr2_Plan := 0;

Shr_Qtr2_Plan;

@Shr_Qtr3_Plan
whileprintingrecords;
shared numbervar Shr_Qtr3_Plan := 0;

if GroupName ({@Qtr_Desc}) = '3Q'
then Shr_Qtr3_Plan := Sum ({@Curr_Year_Plan_Pol_Cnt}, {@Qtr_Desc})
else Shr_Qtr3_Plan := 0;

Shr_Qtr3_Plan;

@Shr_Qtr4_Plan
whileprintingrecords;
shared numbervar Shr_Qtr4_Plan := 0;

if GroupName ({@Qtr_Desc}) = '4Q'
then Shr_Qtr4_Plan := Sum ({@Curr_Year_Plan_Pol_Cnt}, {@Qtr_Desc})
else Shr_Qtr4_Plan := 0;

Shr_Qtr4_Plan;

Here is my variable in the main report:
@Qtr_Plan
whileprintingrecords;
shared numbervar Shr_Qtr1_Plan;
shared numbervar Shr_Qtr2_Plan;
shared numbervar Shr_Qtr3_Plan;
shared numbervar Shr_Qtr4_Plan;
numbervar Qtr_Plan := 0;

if GroupName ({@Qtr_Desc}) = '1Q'
then Qtr_Plan := Shr_Qtr1_Plan
else
if GroupName ({@Qtr_Desc}) = '2Q'
then Qtr_Plan := Shr_Qtr2_Plan
else
if GroupName ({@Qtr_Desc}) = '3Q'
then Qtr_Plan := Shr_Qtr3_Plan
else
if GroupName ({@Qtr_Desc}) = '4Q'
then Qtr_Plan := Shr_Qtr4_Plan
else Qtr_Plan := 0;

Qtr_Plan;

In the main report, my subreport is defined in GH1a and all the other fields including Qtr_Plan is defined in GH1b.

How can I move each shared variable defined in the subreport and put them in the GH1b (i.e. group header) of the main?

Thank you for reading this and if you have any other ideas on how to display the correct values in the main I would very much appreciate it.

 

[lbass]

I think the problem is with your subreport link. Try linking the subreport to the main report on the grouping formulas {@QTR_Desc}. Then each header will show only the results of the appropriate group, and when you suppress the details, you should get the layout you are looking for. You could format GH1a to "Underlay following sections", which would align the GH1a and GH1b results.

To designate the subreport sums as a shared variable, all you need to do is one formula:

whileprintingrecords;
shared numbervar plan := Sum ({@Curr_Year_Plan_Pol_Cnt}, {@Qtr_Desc})

Then in the main report (GH1b), create one formula:

whileprintingrecords;
shared numbervar plan;
Sum ({mainreporttable.calculation}, {@Qtr_Desc}) % plan

These will pick up the appropriate values based on the groups they are in.

-LB

 

[antonx]

Thank you LB ! It worked just fine.

One more question (I have Crystal Reports 8.5):
Is it possible to have a main report call subreport1 and then in subreport1 call another subreport1A (subreport within a subreport). Then the results of subreport1 and subreport1A is passed to the main report?

I tried this and it does not work. When I insert subreport1 in the main report and run it, the main report includes the results from subreport1 but excludes subreport1A.

Thank you !

 

[lbass]

You cannot create subreports within subreports. You could try creating this as a another separate subreport within the main report. Or you could explain why you think you need one--there's likely to be a way around it.

-LB

 

[antonx]

Thanks LB!

Now that you cannot have a subreport within a subreport, I did the report differently. Again I have Crystal Reports 8.5.

I have a main report with 2 subreports (both in report footers). One subreport containing the plan counts are in RFb and the second subreport containing the actual counts are in RFc. It looks as follows:

In my main report:
Period Plan_Cnt
RFb sub1 1Q 1315
2Q 1300
3Q 1525
4Q 1385

Period Actual_Cnt
RFc sub2 1Q 1398
2Q 1141
3Q 916
4Q 635

The periods 1Q, 2Q, 3Q and 4Q in both RFb and RFc are exact formulas. If I need to create 4 shared variables in RFb and then pass them to RFc, how do I make sure that the plan counts in each period are lined up correctly with the actual counts in the same period. This is what I mean:

This is what I want
Period Actual_Cnt Plan_Cnt Actual/Plan
RFc sub2 1Q 1398 1315 1.06
2Q 1141 1300 0.88
3Q 916 1525 0.61
4Q 635 1385 0.46

Because then I will need to divide 1398/1315 for 1Q, then divide 1141/1300 for 2Q and so on...

Thank you very much and I am learning alot (at least about subreports)!

 

[lbass]

All of your plan cnt and actual cnt formulas should be created as shared variables in their respective subreports for the displays in RFb and RFc. For the Actual Cnt subreport in RFb, remove the Period column. Suppress all unused subreport sections. Then insert RFd, and in the main report create formulas for Actual/Plan column of the form:

whileprintingrecords;
shared numbervar Shr_Qtr1_Plan;
shared numbervar Shr_Qtr1_Actual;

Shr_Qtr1_Actual/Shr_Qtr1_Plan

Then in the main report, format both sections RFb and RFc for "underlay following sections." You will have to play with the fields to get the correct alignment, and this will be easier if you use the guidelines and ruler.

-LB

 

[antonx]

Thank you for the response LB. Good idea on the underlay feature.

This means that based on the last example above, I should have a total of 8 shared variables that will be passed to the main report and displayed in the main report. Is this correct?

In your example above;
whileprintingrecords;
shared numbervar Shr_Qtr1_Plan;
shared numbervar Shr_Qtr1_Actual;

Shr_Qtr1_Actual/Shr_Qtr1_Plan

I inserted the result in RFd but it is dividing the wrong numbers. It is dividing the 4Q values (635/1385). I want to divide the 1Q values (1398/1315).

My subreports (sub1 and sub2) each contain only group totals (sub1 contain plan counts and sub2 contain actual counts) by quarter in GH1.

I tried inserting 4 sections GH1a, GH1b, GH1c, GH1d,(with a group selection quarter condition value) in each subreport and creating the shared variables but it still gave me the 4Q values.

This is what I have in my reports:

In sub1:
whileprintingrecords;
shared numbervar Shr_1Q_Plan := Sum ({@Curr_Year_Plan_Pol_Cnt}, {@Qtr_Desc});

Shr3_1Q_Plan;

In sub2:
whileprintingrecords;
shared numbervar Shr_1Q_Actual := Sum ({@Curr_Year_Actual_Pol_Cnt}, {@Qtr_Desc});

Shr_1Q_Actual;

In the main report:
whileprintingrecords;
shared numbervar Shr_1Q_Plan;
shared numbervar Shr_1Q_Actual;
numbervar Q1_Actual_Pct_of_Plan;

Q1_Actual_Pct_of_Plan := Shr_1Q_Actual/Shr_1Q_Plan;

Q1_Actual_Pct_of_Plan;


I'm stuck and can't think of what I am doing wrong.

Thank you.

 

[lbass]

Actually, I misled you in my last post--I was confused by your statement "The periods 1Q, 2Q, 3Q and 4Q in both RFb and RFc are exact formulas." Your formulas should not be specific to a specific quarter, since you have already grouped on quarter and are displaying the group footer sections of each subreport (See my first post above).

In order to calculate the ratio, I think you have to hold the group values in arrays, before you can use them in the calculation, so you could try the following, which tests out here. My apologies to Jim Broadbent and those who are more familiar with arrays than I--there might be a simpler approach.

In sub1, create a formula {@init} and place in the subreport header:

whileprintingrecords;
shared numbervar array qtr := [0, 0, 0, 0, 0, 0, 0, 0];
shared numbervar pointer := 0;

In the sub1 group footer place the following formula. Please note that this, along with the GroupName (Qtr Description) is the only field you need to display in Sub1:

whileprintingrecords;
shared numbervar array qtr;
shared numbervar pointer := pointer + 1;

qtr[pointer] := Sum ({@Curr_Year_Plan_Pol_Cnt}, {@Qtr_Desc});

In the sub2 report header, add the following initialization formula:
whileprintingrecords;
shared numbervar array qtrx := [0, 0, 0, 0, 0, 0, 0, 0];
shared numbervar pointerx := 0;

In the sub2 group footer, place the following formula:
whileprintingrecords;
shared numbervar array qtrx;
shared numbervar pointerx := pointerx + 1;

qtrx[pointerx] := Sum ({@Curr_Year_Actual_Pol_Cnt}, {@Qtr_Desc});

In the main report, ReportFooter_d, place the following formula:

whileprintingrecords;
shared numbervar array qtr;
shared numbervar array qtrx;
shared numbervar pointer;
shared stringvar output;

for pointer := 1 to 4 do(
output := output + totext(qtrx[pointer] / qtr[pointer],2) + chr(13));
output;

Right click this formula->format->common-> and set it to "Can Grow" so that all results will display.

Now you can format the report footer_b and _c to "Underlay following sections."

-LB

 

[antonx]

Yes it worked !!! I got all the the 4 values from each quarter of each subreport and the divisions are correct in my main report.

Thank you very much LB !