Latest Cost for an Item

[bo5i]

Hi, I would like to get a report like this

Item Cost
AAA 10
BBB 25
CCC 100

which displays only the latest cost information for an item.

The fact table has following values

Item Date Cost
AAA 08/02/05 20
AAA 08/04/05 10
BBB 08/01/05 50
BBB 08/02/05 25
CCC 08/06/05 100

There are lookup tables for item and date.

Any ideas on achieving this using MSTR.

Thanks

 

[iolaper]

Giving this assuming you have OLAP services, create a metric with Max(Date) and through it in your Report Objects window so that it is hidden.
This should give you the results you need.

If you dont have OLAP services you can still hide that one column by setting the width of the Max(Date) metric column to '0' in your column width section.

You can also throw only these two columns on report services IF you are planning to use report services document.

 

[reisw]

Create your metric by adding date dimensionality, ending fact. This will return the latest cost for each of your items from the fact table.

 

[FLB]

reisw is right and you will not need OLAP Services! ;-)