Grand Total in a Group By line

[UNCTarheels]

I am creating a Inventory Valuation Report, grouped by Product Code. I do not want to display the detail information. I have this part working.

What I need to do now, is to create a percentage field on the group line. The percentage is Extended Cost - Group / Total Extended Cost for Inventory. I need to be able to allow the user to select the Product Code(s) and show the correct percentage. The Grand Total will be for the entire inventory and not just for the Product Code(s) selected.

I tried to use a subreport, but I cannot get access to the grand total of the subreport.

I would appreciate any help!

ps. I know that this is very similiar to at least two new postings, but since they indicate that you need to use a value in a lower band and I do not use that lower band, then I am at a lost. Also, I have not been able to pass a value from a subreport to the main report.

Thanks! A little bit you, a little bit me. [hippie]

 

[Naith]

You can mimic your

Extended Cost - Group / Total Extended Cost

with a formula like:

sum({Extended Cost}, {Product Type})/ sum({Extended Cost})*100

or:

sum({Extended Cost}, {Product Type})%sum({Extended Cost})

Place either of em in your group footer. This will appear for each Product Type.

If you want the user to be able to select certain Product Types, the percentage formulas remain the same, but just create a ProductType parameter, and use it to drive your Record Selection Criteria.

Naith

 

[Naith]

Sorry. The first formula should be

(sum({Extended Cost}, {Product Type})/ sum({Extended Cost}))*100

Apologies,
Naith

 

[UNCTarheels]

Wouldn't this create a grand total only for the products that were selected and not for all inventory items?

If the user selects all inventory items (grouped by product line), then the grand total is correct. If the user selects only one or a range of inventory items, then the grant total only reflects what was selected, not the grand total of the all inventory items. A little bit you, a little bit me. [hippie]

 

[Naith]

The formulas are sum of the cost by group as a percentage of the total cost of the report.

If you want the grand total to reflect all your records regardless of the values entered in your report parameters by the user, you just have to leave the parameter out of the report driving criteria in the selection expert, and conditionally suppress sections where values are not equal to the parameter values.

Naith

 

[IanWaterman]

Instead of using your parameter for selection, group on a formula based upon your parameter.

Formula

If {Product Type} = Parameter then {Product Type} else "OTHER"

I have assumed that your Prod Type is a String, if a number use say 9999999 or any number unlikely to be used.

Use same formula as sugested by Naith, in formula group footer
(sum({Extended Cost}, Formula Group)/ sum({Extended Cost}))*100

In Format group Suppress the group footer when Formula = "OTHER"

Regards

Ian Waterman

 

[UNCTarheels]

Here is where I am at now:

I can access the data from the subreport, but I have not been able to use the data. I have created formulas as follows:

SubReport:
@ExtendedCost - {IM3_ItemCosting.UnitCost} * {IM3_ItemCosting.QtyOnHand}

@TotalExtendedCost - WhilePrintingRecords;
Shared CurrencyVar TEC := sum ({@ExtendedCost});
TEC;

Main Report:
Created Section Group Footer 1a
Placed subreport here

Created section Group Footer 1b
Create formula @ExtendedCost -
{IM3_ItemCosting.UnitCost} * {IM3_ItemCosting.QtyOnHand}
Created formula @TotalExtendedCost -
WhilePrintingRecords;
Shared CurrencyVar TEC;
TEC;
Created formula @PercentageTotalExtendedCost -
{@ExtendedCost}/{@TotalExtendedCost}

I get an error, A number is required here and the cursor is on the {@TotalExtendedCost} part of the formula. What did I miss or do wrong?
A little bit you, a little bit me. [hippie]

 

[UNCTarheels]

Report is working, thread is now closed. A little bit you, a little bit me. [hippie]