Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Server Analysis Services

Not trivial MDX - help needed

Hi,

I have a cube with:
- a Version dimension {1,2,3,4}
- a FY Time dimension {2001,2002,....2006}
- a measure X (currrency)

I want to show "Current Version USD" value on my report, which will be a value of the measure X associated with the latest version for a given year.

Example:

FY_01__|__02__|__03__|__04__|__05__|__06
Version 1_100__|_50__|__10__||__980_| __
Version 2_200__|__||||__35_
Version 3_30|__70_||__60__|| __
Version 4|__||__80__|__30__| __
--
Current Version USD30|__70_|__10__|__80__|__30|_35

I'm trying to come up with an MDX showing only:
FY_01__|__02__|__03__|__04__|__0 5__|__06
Current Version USD30|__70_|__10|__80__|__30|__35

Any help appreciated.
Thanks,
Kris

[802 byte] By [z248910] at [2007-12-25]
«« financial year
»» Printing Server Report with Report Viewer - VS 2005
# 1

Hi Kris,

Assuming that you're using AS 2005, the MDX query could be like:

With Member [Measures].[Current Version USD] as

([Measures].[X], Tail(NonEmpty([Version].[Number].[Number].Members,

{[Measures].[X]})).Item(0).Item(0))

Select [FY Time].[Year].[Year].Members on 0,

{[Measures].[Current Version USD] } on 1

from [FinanceCube]

DeepakPuri at 2007-9-3 > top of Msdn Tech,SQL Server,SQL Server Analysis Services...
# 2

Thanks a lot.

Works great.

Cheers,

Kris

z248910 at 2007-9-3 > top of Msdn Tech,SQL Server,SQL Server Analysis Services...
SQL Server
SQL Server Analysis Services

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified