This discussion is archived
3 Replies Latest reply: Dec 4, 2012 12:41 PM by 970450 RSS

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

970450 Newbie
Currently Being Moderated
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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points