Passing shared variables based on group totals in the sub report to Main report - CRW 2008

[Former Member]

Hi,

I am passing global parameters from the main report (run as of date & item code range, category code range) and then need to pass back
shared variables based on group totals from the sub report to the main report.   I have setup formula in the main report calling
the shared variables from the sub-report, however the totals from the sub report are not calculating & passing through properly.  

The sub-report at item level total is working perfectly in the main report, however the shared variables from the sub report are not
summarising to the category level or grand total.   

On the main report, the shared variables for category group totals and report totals from the sub-report are just printing the values for the last
item on each section (viz not group totals).  On the sub-report, I have placed the formula for each of the shared variables on the canvas in either the same section as the footer to which  the category total or grand total reports but that hasn’t helped the totalling. 

Sub report  is linked on “AsOfDate & item code.   Have also tried linking on Cat code.  Item code link & cat code link are set to
select data based on sub report fields.

For testing, I’ve saved out the sub report as  new report (separate from the main report) and the category & grand totals and variable formula appear to be working perfectly when separated from the main report.

Based on the samples below, XY category totals are just printing 2, $40, and for Category Z, the cat totals are just printing $ 7, $90
for both category and the report grand totals are printing &, $90, all being the last record read in that group or for the report total

I know i need to initialise the group header of each section.

Sample Data:

Item Qty Amt

Cat XY

A   4, $ 30

B  2,  $40

  Cat XY total  is 6, $70

Cat Z

C 5, $10

D 7, $90

  Cat total is   12, $100

Grand total should be 18, $170

Report is like this

Main report

Group footer #3 Item code <Sub report group footer 2 of Item totals>   <other fields & sub totals from different tables >

Group footer #2  (other sort) 

Group footer #1 Category totals   <@vCatSalesQty>  <@vCatSalesAmt>   other totals from the main report

Report Footer  (Grand totals)        <@vGTSalesQty>  <@vGTSalesAmt>  

Sub report: Sales file – sorts category, then item code

Detail   (Suppressed):     <Item Code>  <Month>  <Qty> <Amount>

Group Footer 2 Item totals     <Qty, Item>  <Amount, Item>  

Group Footer 1 Category totals (hidden)  <Qty, Item>  <Amount, Item>  

Report Footer Grand total (hidden)   <Qty, Item>  <Amount, Item>  

For Category totals I created a formula in the sub-report to declare and set all the variables.  I created
similar one for qty.

WhilePrintingRecords;

shared CurrencyVar CatSalesAmtYTD := Sum ({@Sales Amount YTD}, {ICITEM.CATEGORY});

I created this formula @vCatSalesAmt  (for amount)  in the main report, calling the shared variable, and placed it in Group footer 1 Category totals.  I created similar one for qty.

WhilePrintingRecords;

  Shared CurrencyVar CatSalesAmtYTD;

For grand totals I created a formula in the sub-report to declare and set all the variables.  I created similar
one for qty.

WhilePrintingRecords;

  shared CurrencyVar GTSalesAmtYTD := Sum ({@Sales Amount YTD});

I created this formula @vGTSalesAmt  (for grant total amount)  in the main report, calling the shared
variable, and placed it in Report footer. I created similar one for qty.

WhilePrintingRecords;

Shared CurrencyVar GTSalesAmtYTD;

Can someone please let me know why the group totals & report total from my sub report are not calculating correctly?

[abhilash_kumar]

Hi Jeff,

The Subreport has to be placed in a section above the section that holds the shared variable formula in the Main Report.

For the Grand Total you can simply modify the shared variable formula on the Group Footer to:

WhilePrintingRecords;

currencyvar gtotal;

shared CurrencyVar GTSalesAmtYTD;

gtotal := gtotal + GTSalesAmtYTD;

GTSalesAmtYTD;

And then create another formula in the Report Footer with this code:

WhilePrintingRecords;

currencyvar gtotal;

[Former Member]

Hi Abilash,

Thanks.  The sub-report is already placed in the section above the ones where I am using the variables.   I'll try changing it to a global variable and let you know. Hopefully that will overcome the issue where the total is only printing the values from the sub report for the last record read in that group.

[abhilash_kumar]

Wait. A Global Variable will not work here; you need a Shared Variable.

So, the Subreport has multiple groups? And you're trying to add all those Subtotals in the Subreport and show it on the Group Footer in the Main Report?

If yes, then your formula in the Subreport should actually do a Grand Total and that value should be send to the Group Footer in the Main Report using a Shared Variable.

The formula in the Subtotal should be:

shared currencyvar tot := Sum({measure_field})

In the Main report you can then use this shared variable to show the value in the Group Footer. The Grand Total in the Main Report can be produced with the steps I suggested in my previous post.

If this does not help, would you be able to attach the report with saved data? Sometimes it gets difficult to visualize the report with the description mentioned.

-Abhilash

[Former Member]

Thanks Abilash,

Yes group totals and grand total form the sub report need to be inserted to the main report.

For the report footer (grand total), I had similar to yours (shared currencyvar tot := Sum({measure_field})) , except mine is based  on a formula in he sub report:

WhilePrintingRecords;

  shared CurrencyVar GTSalesAmtYTD := Sum ({@Sales Amount YTD});

And for the group totals but category, I had

shared CurrencyVar CatSalesAmtYTD := Sum ({@Sales Amount YTD}, {ICITEM.CATEGORY});

Tomorrow I can post a sample of the report with data.

Thanks again,

jeff 

[Former Member]

Hi Abhilash,

How is the best way to get you the report file? 

Thanks,

jeff

[abhilash_kumar]

You may attach the report to this thread. To attach the report change the report's extension from .rpt to .txt and click the 'use advanced editor' link in the reply window.

-Abhilash

[Former Member]

Hi Abhilash,

Thanks.  I already tried that but I get the message "The content type of this attachment is not allowed.