aggregation Problem

I created a cube for Inventory valuation.
Every monthend a static Inventory valuation of every item will be put in the
cubes.
1. I don't want to aggregate the value across the months;
2. If the user view by year, I would like to system to put the Latest
valuation in that year. for examples 2003 = 2003/Dec, 2004 = 2004/Dec, 2005 =
2005/Jan.
How can I achieve it in the cubes design?
Hi Kam
Try looking at the ClosingPeriod MDX function in BOL
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:7B95BD16-6351-407E-95DD-244D962F9266@.microsoft.com...
>I created a cube for Inventory valuation.
> Every monthend a static Inventory valuation of every item will be put in
> the
> cubes.
> 1. I don't want to aggregate the value across the months;
> 2. If the user view by year, I would like to system to put the Latest
> valuation in that year. for examples 2003 = 2003/Dec, 2004 = 2004/Dec,
> 2005 =
> 2005/Jan.
> How can I achieve it in the cubes design?

aggregation Problem

I created a cube for Inventory valuation.
Every monthend a static Inventory valuation of every item will be put in the
cubes.
1. I don't want to aggregate the value across the months;
2. If the user view by year, I would like to system to put the Latest
valuation in that year. for examples 2003 = 2003/Dec, 2004 = 2004/Dec, 2005
=
2005/Jan.
How can I achieve it in the cubes design?Hi Kam
Try looking at the ClosingPeriod MDX function in BOL
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:7B95BD16-6351-407E-95DD-244D962F9266@.microsoft.com...
>I created a cube for Inventory valuation.
> Every monthend a static Inventory valuation of every item will be put in
> the
> cubes.
> 1. I don't want to aggregate the value across the months;
> 2. If the user view by year, I would like to system to put the Latest
> valuation in that year. for examples 2003 = 2003/Dec, 2004 = 2004/Dec,
> 2005 =
> 2005/Jan.
> How can I achieve it in the cubes design?

Aggregate sum query, need help

I have two tables tb1 with item and qtyOnHand and a second table tb2 with item and qtyOrdered I am trying without success to make this happen;
select sum (onHand-Ordered) from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as Ordered
I kind of gathered it would work based on thishttp://weblogs.asp.net/jgalloway/archive/2004/05/19/135358.aspx
I have also tried this;
select tb1.item from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as Ordered, sum (onHand-Ordered) as available from tb1 where tb1.item = RD35
Any ides, there are multiple rows of each item in each table tb1 is inventory with several different locations and tb2 is an orders table.

I'm not sure exactly what values you are looking for but it looks like you want the new quantity on hand after the order for a particular item. Try this:
SELECT
tb1.Item,
IsNull(tb1.qtyOnHand - SUM(qtyOrdered), 0) AS qtyNewOnHand
FROM tb1
LEFT JOIN tb2 ON tb1.item = tb2.item
WHERE tb1.item = RD35
GROUP BY tb1.item, tb1.qtyOnHand
I assumed that tb1 has 1 record per item but that tb2 may have multiple records (orders).
HTH.

|||

Thanks for the reply. There a multiple records in table 1 for a single item, it is an inventory by location table where bin numbers are the primary key. There are multiple records in table 2 as well. Which is why unions and joins are biting me with some multplication. In this particular case RD35 has 3 records in table 1 with a sum of 237 and 6 records in table 2 with a sum of 97.
select table1.item from (select sum (qtyOnHand) from table1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from table2 where item = RD35 group by item) as Ordered, sum (onHand-Ordered) as available from table1 where table1.item = RD35
This ends up giving me; onHand 237, Ordered 97 and vailable 402. available should be 140.

|||

Try this:

SELECT
tbOnHand.item,
OnHand,
IsNull(Ordered, 0),
OnHand - IsNull(Ordered, 0) AS Available
FROM
(
SELECT
item,
SUM(qtyOnHand) as OnHand
FROM tb1
WHERE item = RD35
GROUP BY item
) AS tbOnHand
LEFT JOIN
(
SELECT
item,
SUM(qtyOrdered) as Ordered
FROM tb2
WHERE item = RD35
GROUP BY item
) AS tbOrdered ON tbOnHand.item = tbOrdered.item