Fixing a calculation at a certain level

building reports desktopintelligence
1

This is an issue I’ve been wrestling with for a couple of days.

I have Items which group into Item Families. And I have 3 calculations. Calc A is a complicated local variable. Calc B is a complicated local variable. Calc C is based on Calc A and Calc B. (Don’t struggle with the numbers in the following - they won’t make sense)

Item Family Item Id Calc A Calc B Calc C

ABC A 10 15 17.5
ABC B 15 20 28
ABC C 18 25 26.3

DEF D 22 42 35
DEF E 25 36 41
DEF F 21 38 35

Calc C is the only one that I need to see in the report. What I want to do is to be able to roll Calc C up to the Item Family level. Calc C is additive but when I delete the Item Id from the report, Calc A and Calc B are re-calculated at the Item Family level and Calc C is calculated from them. This ends up being wrong.

So, I’d like to see

Item Family Calc C

ABC 71.8
DEF 111

What I want is for Calc A, Calc B, and Calc C to still calculate at the Item Id level and then sum Calc C for me. I have a feeling that I should be able to fix Calc A and Calc B to be calculated at only the Item Id level but am not sure how to do it ??

Is there any way to do this ??

Thanks,
Matthew

Listserv Archives (BOB member since 2002-06-25)

2

Matthew Ferguson wrote:

So, I’d like to see

Item Family Calc C

ABC 71.8
DEF 111

What I want is for Calc A, Calc B, and Calc C to still calculate at the Item Id level and then sum Calc C for me.

Matthew,

You can use the “Hide” option to achieve your requirement. You can find this option in the “Pivot” tab of the " Table Format " dialog box. You can invoke this dialog box using the menu option Format/Table.

  1. Select your report block and choose the menu option
    Format/Table.
  2. In the Tbale Format dialog, you can the see the tab “Pivot”.
    In this all the objects in the block will be displayed. In your case it will be Item Family, Item Id, Calc A, Calc B and Calc C.
  3. Hide the objects Item Id, Calc A and Calc B. 4. Fold the resulting block at the Item Family level.

This will lead to your required result.

I hope this will help you.

Chandrashekar.S

Listserv Archives (BOB member since 2002-06-25)