Use subreport total in formula in main report

[Former Member]

Hi,

I have a subreport which is a total figure. I just want to muliply this by a number in a field on the main report, can this be done?

Any help appreciated!

[Former Member · Accepted solution]

If you search the Business Objects Notes (see the left side of this page at the top) for the following you'll find a good solid answer:

Kbase (note) / Title

c2007600 / "How to Share Subreport Data with Main Report"

(also works for sharing main report data with sub-report)

Enjoy!

[Former Member · Accepted solution]

If you search the Business Objects Notes (see the left side of this page at the top) for the following you'll find a good solid answer:
Kbase (note) / Title
c2007600 / "How to Share Subreport Data with Main Report"

Tried searching for this in BO Notes but opens a new window where you can search in SAP notes. If I search 'All' all I can find is a PDF (Top 10 troubleshooting tips for Crystal) which has a link to c2007600 in it, but this just dumps you back to the home page because the link goes to the old Crystal Decisions website!

[Former Member · Accepted solution]

Symptom

A report contains a subreport. Data from the subreport is required for calculations in the main report.

How can you share subreport data with the main report in version 7 (or higher) of the Crystal Reports Designer?

Resolution

Shared variables, introduced in Crystal Reports version 7, make it easier to pass values from a subreport to the main report. Using shared variables requires two formulas: one to store the value in a shared variable, the other to retrieve the value from the shared variable.

The most important thing to remember when using shared variables is that Crystal Reports must first evaluate the formula where the value is stored before evaluating the formula that retrieves the shared variable.

For example if you want to pass a grand total from the subreport to do a calculation in the main report, follow these steps:

1. In the subreport, create a formula similar to the one below:

//@SubFormula

//Stores the grand total of the

//{Orders.Order Amount} field

//in a currency variable called 'myTotal'

WhilePrintingRecords;

Shared CurrencyVar myTotal := Sum ({Orders.Order Amount})

2. Place this formula in your subreport.

3. In the main report, create a formula that declares the same variable name:

//@MainFormula

//Returns the value that was stored

//in the shared currency variable called

//myTotal in the subreport

WhilePrintingRecords;

Shared CurrencyVar myTotal;

myTotal

4. Place @MainFormula in a main report section that is beneath the section containing the subreport.

NOTE:======

For the shared variable to return the correct value in the main report, you must place @MainFormula in a main report section that is beneath the section containing the subreport. This ensures Crystal Reports evaluates the @SubFormula before @MainFormula.

One way to do this is to insert a section below the section containing the subreport, and place @MainFormula in this new sub-section:

· On the 'Format' menu, click 'Section'.

· On the 'Sections' list, click the section containing the subreport.

· Click 'Insert' (at top of dialog box). This inserts an additional subsection.

· Click 'OK' to return to the report, and insert @MainFormula into this new sub-section.

The next time you preview the report, @MainFormula displays the value from the subreport. In this particular example, that value was the grand total of the {Orders.Order Amount} field.

============

5. Once you have verified that @MainFormula is returning the correct value from the subreport, you can include this formula in other main report formulas, such as:

//@NewFormula

//includes data from subreport

{@MainFormula}+ Sum ({Customer.Last Year's Sales})

· Place this formula in the same section as @MainFormula, or in a section further down on the report.

You have now successfully shared data from a subreport with the main report.

NOTE: =====

This is not possible with On Demand Subreports in Crystal Reports.

[Former Member · Accepted solution]

For the shared variable to return the correct value in the main report, you must place @MainFormula in a main report section that is beneath the section containing the subreport. This ensures Crystal Reports evaluates the @SubFormula before @MainFormula. 
One way to do this is to insert a section below the section containing the subreport, and place @MainFormula in this new sub-section

This has nailed it!

Many thanks!

[Former Member]

Hi..

If your subreport contains summaries then you can't pass this values to shared variables.

Do the following to get the values in Main report.

--Insert running total to get the total in your subreport and pass this running total value to a shared valiable in subreport.

--In the main report again defaine the same shared variable and display the total in main reprot.

Thanks,

Sastry

[Former Member]

--Insert running total to get the total in your subreport and pass this running total value to a shared valiable in subreport.
--In the main report again defaine the same shared variable and display the total in main reprot.

This doesn't make any difference!

[Former Member]

Hi Zeb

Open Crystal Reports help and search for the keyword "shared variable". You would get the complete details of how apply a share variable, what should be kep in mind in order to get correct values while passing a shared variable from subreport to main report.

Hope this helps!!!!

Regards

Sourashree

[Former Member]

To be brutally honest, I've never found Crystal help that helpful!

Anyway, what I got in my subreport is this:

WhilePrintingRecords;
Shared CurrencyVar DayTotal := Sum({COMP.PPRICE})

and what I have in my main report is this...

WhilePrintingRecords;
Shared CurrencyVar DayTotal;
DayTotal * {DAY.BLOCKEDOUT}

I can see that the subreport is working correctly however, the main report is wrong.

For example if the blocked out number is 13 and the day total 58.75, the main formula is calculating it to be 585.00 and I can see this is because it is always one record behind. On the previous line the DayTotal was 45, so 13 * 45 is 585.

What have I done wrong here?

[Former Member]

Hi...

Where you have inserted the subreport in your main report?

Thanks,

Sastry

[Former Member]

Where you have inserted the subreport in your main report?

It is in the 'Details'

[Former Member]

Hi

Would suggest you to confirm the below points:

- Have you inserted the formula(Where you have used Shared variable) in the subreport section.

- If you have inserted the subreport in the detail section of main report and inserted the formula in below sections(Group footer or report footer) then only it will show the output

Hope it helps.

Regards

Asha.

[Former Member]

Hi zeb zeb

Yes this can be done.When you want to use the value of subreports in the main report then you can use Shared variable in the subreport.and the particular value of Shared varible can be use in main report.

Regards

Asha