Main report and subreport not displaying properly when grouped

[Former Member]

I've developed a report with a main report and a subreport embedded in the Group Footer section.  I'm trying to generate a report that will give me the following:

CORRECT FORMAT:

Main report A

   Subreport A

      Main+Subreport Group total A

Main report B

   Subreport B

      Main+Subreport Group total B

Main report C

   Subreport C

      Main+Subreport Group total C

         Report total A+B+C

I have no problem passing the group totals to the main report total.  My problem is the order in which these groups are displaying.  What I'm getting right now is:

WRONG FORMAT:

Main report A

   Subreport A

      Subreport Group total A

   Subreport B

      Subreport Group total B

   Subreport C

      Subreport Group total C

Main report B

   Subreport A

      Subreport Group total A

   Subreport B

      Subreport Group total B

   Subreport C

      Subreport Group total C

Main report C

   Subreport A

      Subreport Group total A

   Subreport B

      Subreport Group total B

   Subreport C

      Subreport Group total C

         Report Total A+B+C

How do I avoid having subreports A and B from re-appearing in the other groupings?  In other words, how do I make my report to display in the CORRECT FORMAT shown above?  Please advice.  I'd appreciate any help I can get out there.  Thank you much.

[abhilash_kumar]

Hi Fione,

You need to dynamically suppress the section that holds the Subreport for each Group Footer.

Go to the Section Expert > Select the section that holds Subreport A > Click the formula button beside Suppress and use this code:

{Group_Name_field} <> "Group_Value 1"

Replace {Group_Name_field} with the field that the report is grouped on and Group_Value with the value of the first group.

Use the same code for all the other sections too, however with the different Group Value.

-Abhilash

[Former Member]

Thank you, Abhilash!  It worked like a charm.  I'd still be struggling with this without your help.  I really appreciate your help.

Now I'm encountering another problem--my subreport groups do not total after suppressing with the formula you instructed in your previous response.  No matter what I did, I'm still getting only 1 group total instead of a grand total of all groups within the subreport.

Would you please help me with this dilema?  Thank you.

[abhilash_kumar]

Well when you suppress/hide a section that holds the Subreport, it no longer executes and hence any shared variables that you might have bring back zero or blank values.

-Abhilash

[Former Member]

Is there any way to resolve this issue?  Please advice.  Thanks.

[abhilash_kumar]

I guess you display the Grand Total in the Main Report, don't you?

Do you use shared variables to calculate the Grand Total?

-Abhilash

[Former Member]

Yes, I do shared variables to calculate the Grand Total.  However, the subreport total that was passed to the main report for calculation picked up only for the last group total since the other groups were hidden (following your instructions on Sep 25).  This is what I have in both reports:

In subreport:

WhilePrintingRecords;

Shared NumberVar totalNonGL;

   totalNonGL :=sum({ACTRANS.TRAN_AMOUNT})

In main report:

WhilePrintingRecords;

Shared NumberVar totalNonGL;

   totalNonGL + sum({ACTRANS.TRAN_AMOUNT})

As a result, my Grand Total is only composed of:

Last group's total + main report's total

instead of subreport's total for all groups + main report's total

Please advice on what I did wrong and how to correct this.  Thank you.

[abhilash_kumar]

Let me understand the way this is laid again please.

Let's take this part here:

Main report A

   Subreport A

      Subreport Group total A

   Subreport B

      Subreport Group total B

   Subreport C

      Subreport Group total C

All this while I was under the impression that Subreport A, B and C are three separate subreports placed in the Group Footer section (I know should have read your first statement carefully), nevertheless, does this mean that for a Group labelled A (for instance) in the Main Report, the Subreport shows all records instead of just showing the data for the Group labelled A?

And that is probably why you wanted to suppress the other groups in the Subreport?

You could easily have the Subreport display data respective to the Group Label by passing the Group Name to the Subreport and filtering the records in the Subreport on the same Group Name.

Right-click the Subreport > Select Change Subreport Links > Move the Group Name field to the pane on the right > From the drop-down that says 'Subreport field to use', choose the field that maps to the Group Name - just like you would normally join two database fields.

I'm a little confused with the report anyway.

-Abhilash

[Former Member]

Sorry for the confusion.  Let me see if I can explain this more clearly this time.  Basically, I created a report with a main report showing AP transactions and a subreport showing AC transactions.  Both of these reports are grouped by Accounting Unit and have a paramenter for users to select by Accounting Unit(s).

Main report Group A

   Subreport Group A

      Main + Subreport Group A total

Main report Group B

   Subreport Group B

      Main + Subreport Group B total

Main report Group C

   Subreport Group C

      Main + Subreport Group C total

         Report total = Groups A+B+C from both main and subreport

So with your help on 9/25, I was able to make both reports to display data in groups A, B, C by suppressing all unrelated records and just show the data for the respective group.

My dilema here is to be able to create a report total to add up all group totals from both the main and subreport.  What I'm getting right now is all group totals from the main report and group total from just the last group from the subreport (instead of groups A, B, C).

With your expertise, hope you can give me some guidance on this issue.  Thank you.

[abhilash_kumar]

Could you paste the formula you've used to show the Subtotals at the Group Level please. It should look something like this:

WhilePrintingRecords;

Shared NumberVar totalNonGL;

NumberVar gtotal;

gtotal := gtotal + ( totalNonGL + sum({ACTRANS.TRAN_AMOUNT}, {Group_field}) )

totalNonGL + sum({ACTRANS.TRAN_AMOUNT}, {Group_field})

The formula in the Report Footer should then look like this:

WhilePrintingRecords;

NumberVar gtotal;

-Abhilash