3 Replies Latest reply: Dec 4, 2012 2:41 PM by 970450 RSS

    Quantity from aggregate table is not summed up. works fine on detail fact

      I have the Revenue Fact table and Revenue Agg Fact table. Aggregate table has data at Product Category level.

      When I select Product Name and Qty, the reports comes up fine and sums up quantity by Product Name. The sql generated is
      select p.product_name, sum(r.quantity)
      from product p, revenue r
      where p.product_id = r.product_id
      group by p.product_name

      If I select Product Category and Qty in my report, the query hits the aggregate table but it does not Sum the Qty. The sql fired is
      +select distinct P.product_category, R.Qty+
      From Product_category p, Revenue_Agg R
      where p.product_category_id = R.product_category_id

      Do I need to set the aggregation level separately for aggregate fact table?