Framework Manager - Calculated Regular Aggregate Problem

[cognosguru]

Hello Cognoids,

Need some help here.Ã,  Have a calculated field that I created in FM.Ã,  The field is a Percentage.Ã,  For example Actual Revenue / Planned Revenue.Ã,  Then the Regular Aggregation property is set to Calculated.Ã, 

In the report I bring in Year, Actual Revenue, Planned Revenue, Percentage.Ã,  The percentage is wrong.Ã,  The aggregation is incorrect.Ã,  If I do the calc manually in Report Studio with Calculated setting it works fine.Ã, 

I recreated the same problem using GO.

I am hoping someone else has run into this.

Thanks,

Allen.

[Darek]

First, I think that the name "COGNOID" is unofficially reserved to current (or former) servicemen (and women) of COGNOS Corporation.

Kidding asside, what do you mean by "Aggregation is incorrect"? Could you provide some examples.

[cognosguru]

Hi Darek,

In that case both of us are Cognoids.  :)

As far as examples, The results received are incorrect from the calculation created in Framework manager (Percent)

Order year     Revenue            Planned revenue   Percent            Manual Calc
2004          $34,750,563.50    $36,523,571.34     978,304.8%      95.1%
2005          $62,558,488.56    $66,750,519.72     1,584,317.5%   93.7%
2006          $74,267,335.82    $80,386,221.04     1,580,863.8%   92.4%

[CoginAustin]

Is actual revenue and planned revenue in the same fact table? If it is then I would look at your calc. If they are 2 different table with conformed dimension(Time) tying them together then did you make 2 star groupings each in its own namespace?

If they are in 2 different table then you need to make sure the calc is done in the namespace above the star groupings

[cognosguru]

Thanks Austin,

They are from the same fact table.  The calculation is fine.  It simply doesn't work.  The answer from Cognos support is that it is a possible bug.  So I am looking for work arounds.

Allen.

[CoginAustin]

Can you show us the SQL from your GO test? I am curious to see if we can find the problem this way. Thanks

[cognosguru]

select
       Order_header.Order_year  as  Order_year,
       1  as  C_____CubeDetailsItem,
       XSUM(Order_details.Revenue  for Order_header.Order_year )  as  Revenue,
       XSUM(Order_details.Planned_revenue  for Order_header.Order_year )  as  Planned_revenue,
       XSUM((Order_details.Revenue / Order_details.Planned_revenue)  for Order_header.Order_year )  as  Percent2,
       (XSUM(Order_details.Revenue  for Order_header.Order_year ) / XSUM(Order_details.Planned_revenue  for Order_header.Order_year ))  as  Manual_Calc
from
       (select
              extract(year from Order_header.ORDER_DATE)  as  Order_year,
              Order_header.ORDER_NUMBER  as  ORDER_NUMBER
        from
              gosales...ORDER_HEADER Order_header
       ) Order_header,
       (select
              (Order_details.QUANTITY * Order_details.UNIT_SALE_PRICE)  as  Revenue,
              (Order_details.QUANTITY * Order_details.UNIT_PRICE)  as  Planned_revenue,
              Order_details.ORDER_NUMBER  as  ORDER_NUMBER
        from
              gosales...ORDER_DETAILS Order_details
       ) Order_details
where
       (Order_header.ORDER_NUMBER = Order_details.ORDER_NUMBER)
group by
       Order_header.Order_year,
       1
order by
       Order_year asc

[CoginAustin]

Sorry, the FM SQL and the RS SQL. This way we can see the difference between a working version and a non-working version

[cognosguru]

Here is the Orders query.Ã,  Percent 2 is the calculation for FM.Ã, 

select
   Order_header.ORDER_NUMBER as Order_number,
   Order_header.RETAILER_NAME as Retailer_name,
   Order_header.Order_year as Order_year,
   Order_header.Order_month as Order_month,
   Order_header.ORDER_DATE as Order_date,
   Order_method.Order_method as Order_method,
   Order_method.ORDER_METHOD_CODE as Order_method_code,
   Product.PRODUCT_NAME as Product_name,
   Order_details.QUANTITY as Quantity,
   Order_details.UNIT_COST as Unit_cost,
   Order_details.UNIT_PRICE as Unit_price,
   Order_details.UNIT_SALE_PRICE as Unit_sale_price,
   Order_details.Gross_profit as Gross_profit,
   Order_details.Revenue as Revenue,
   Order_details.Planned_revenue as Planned_revenue,
   Order_details.Production_cost as Production_cost,
   Order_details.Margin as Margin,
   (Order_details.Revenue / Order_details.Planned_revenue) as Percent2,
   Returned_item.Return_quantity as Return_quantity,
   Returned_item.Return_date as Return_date,
   Returned_item.Reason_description as Reason_description
from
   (select
      Order_header.ORDER_DATE as ORDER_DATE,
      extract(year from Order_header.ORDER_DATE) as Order_year,
      extract(month from Order_header.ORDER_DATE) as Order_month,
      Order_header.RETAILER_NAME as RETAILER_NAME,
      Order_header.ORDER_NUMBER as ORDER_NUMBER,
      Order_header.ORDER_METHOD_CODE as ORDER_METHOD_CODE
   from
      gosales...ORDER_HEADER Order_header
   ) Order_header,
   (select
      ORDER_METHOD.ORDER_METHOD_CODE,
      ORDER_METHOD.ORDER_METHOD_EN as Order_method
   from
      gosales...ORDER_METHOD ORDER_METHOD
   ) Order_method,
   (select
      PRODUCT_MULTILINGUAL.PRODUCT_NUMBER as PRODUCT_NUMBER1,
      PRODUCT_MULTILINGUAL.PRODUCT_NAME
   from
      gosales...PRODUCT P,
      gosales...PRODUCT_MULTILINGUAL PRODUCT_MULTILINGUAL
   where
      P.PRODUCT_NUMBER = PRODUCT_MULTILINGUAL.PRODUCT_NUMBER
      and PRODUCT_MULTILINGUAL."LANGUAGE" = 'EN'
   ) Product,
   (select
      Order_details.QUANTITY as QUANTITY,
      Order_details.UNIT_COST as UNIT_COST,
      Order_details.UNIT_PRICE as UNIT_PRICE,
      Order_details.UNIT_SALE_PRICE as UNIT_SALE_PRICE,
      (Order_details.QUANTITY * (Order_details.UNIT_SALE_PRICE - Order_details.UNIT_COST)) as Gross_profit,
      (Order_details.QUANTITY * Order_details.UNIT_SALE_PRICE) as Revenue,
      (Order_details.QUANTITY * Order_details.UNIT_PRICE) as Planned_revenue,
      (Order_details.QUANTITY * Order_details.UNIT_COST) as Production_cost,
      (((Order_details.QUANTITY * Order_details.UNIT_SALE_PRICE) - (Order_details.QUANTITY * Order_details.UNIT_COST)) / (Order_details.UNIT_SALE_PRICE * Order_details.QUANTITY)) as Margin,
      Order_details.ORDER_DETAIL_CODE as ORDER_DETAIL_CODE,
      Order_details.ORDER_NUMBER as ORDER_NUMBER,
      Order_details.PRODUCT_NUMBER as PRODUCT_NUMBER
   from
      gosales...ORDER_DETAILS Order_details
   ) Order_details,
   (select
      Order_details.ORDER_DETAIL_CODE as Order_detail_code,
      Returned_item.RETURN_DATE as Return_date,
      Returned_item.RETURN_QUANTITY as Return_quantity,
      Returned_item.Reason_description as Reason_description
   from
      ( ( select
         Order_details.ORDER_DETAIL_CODE as ORDER_DETAIL_CODE
      from
         gosales...ORDER_DETAILS Order_details
      ) Order_details left outer join ( select
         Returned_item.RETURN_CODE,
         Returned_item.RETURN_DATE,
         Returned_item.ORDER_DETAIL_CODE,
         Returned_item.RETURN_REASON_CODE,
         Returned_item.RETURN_QUANTITY,
         Return_reason.REASON_DESCRIPTION_EN as Reason_description
      from
         gosales...RETURN_REASON Return_reason,
         gosales...RETURNED_ITEM Returned_item
      where
         Return_reason.RETURN_REASON_CODE = Returned_item.RETURN_REASON_CODE
      ) Returned_item on Order_details.ORDER_DETAIL_CODE = Returned_item.ORDER_DETAIL_CODE )
   ) Returned_item
where
   (Product.PRODUCT_NUMBER1 = Order_details.PRODUCT_NUMBER)
   and (Order_header.ORDER_NUMBER = Order_details.ORDER_NUMBER)
   and (Order_method.ORDER_METHOD_CODE = Order_header.ORDER_METHOD_CODE)
   and (Order_details.ORDER_DETAIL_CODE = Returned_item.Order_detail_code)

[CoginAustin]

I assume 1 is SQL and the other is "cognos sql"? Can we get both of the same.

[cognosguru]

The latest post is from FM, the earlier is the report in RS.

[Darek]

From the looks of it you are just getting all row level percent calculations summarized instead of getting a percent ration of the summaries.

Was dimensional info specified?

[cognosguru]

Ok.  Here is the answer.  It is a BUG.  Calculated property is ignored and the calculation is performed incorrectly.  The way to do it is to create a stand alone calculation (same) and use Calculated as the regular aggregate property.  Then it works fine.  It DOES NOT as a part of the Query Subject.

Thanks to all for helping out.  I appreciate your help.

Allen.