I am having a hard time trying to write the MDX for what should be a
rather simple query.

On my rows I have a series of dates in the format 'dd mmm yyy' ranging
from about 2 years ago til today, and to be updated each day going
forward. On my columns I have a calculated Topcount() member which I
want to return the top 5 traded instruments by exposure on each date.

Currently I can only seem to get the top 5 instruments over the entire
history or over a particular specified time span, rather than a
specific daily top 5 on each individual date.

My query is currently as follows, set to pick up the top 5 on a
particular day but these same top 5 are picked up throughout the
history, as mentioned above:

with
set [Top 5 Instruments] as 'TopCount([Instruments].[Instrument Short
Name].members, 5, ([Measures].[Exposure (USD)],[Calendar Time].[27 Sep
2002]))'
select
non empty {[Top 5 Instruments]} on columns,
non empty {[Calendar Time].[27 Sep 2002]:[16 May 2005]} on rows
from [Positions by Instrument]
where ([Measures].[Exposure (USD)], [Trader].[X])

Thanks in advance.

The immediate problem seems to be that the Named Set is not dynamically
recomputed for each date, so the Axis(0) set stays the same. But what
should the desired report look like, since some of the top 5 Instruments
may be repeated across days and some may not - how would users know?

This recent thread addresses a similar issue, though the TopCount
dimension members are cross-joined with each value of the variable
dimension, making the report clear:

http://groups-beta.google.com/group/...rver.olap/msg/
5b9dc7b08755ec4c?hl=en
dynamically re-evaluated, so instead all the cross-joined combinations
can be computed and saved:

set [top 10 final g codes] as
'Generate([ProductSubset],
topcount(CrossJoin({[Product Family].CurrentMember},
[Attributed Class].[All Attributed Class].[US].children),
10, [Measures].[Cust Ret Last Week]))'
member [Measures].[xrank] as
'Rank(([Product Family].CurrentMember,
[Attributed Class].CurrentMember),
[top 10 final g codes]) - (10 *
Int((Rank(([Product Family].CurrentMember,
[Attributed Class].CurrentMember),
[top 10 final g codes])-1) / 10))'

select
{[Measures].[Cust Ret Last Week], [Measures].[xrank]} on columns,
[top 10 final g codes] on rows
from
[MyCube]
where
([Drive Diagnosis Code].[All Drive Diagnosis Code].[valid],
[VMI].[All VMI].[valid],
[Source Code].[All Source Code].[valid])
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

I understand precisely what you mean, and to be clearer: what I am
interested in are the top 5 exposure amounts (at the instrument level)
at each date, regardless of what instruments they are actually attached
to. Ideally the table would look like this:

Date Exposure1 Exposure2 Exposure3 Exposure4 Exposure5
......

where each Exposure is for a different instrument and Exposure1 >
Exposure2 > Exposure3 etc.

Thank you for pointing me to the earlier query. I am quite new to
databases and very new to MDX so it was a little complex to digest, but
after reading that I thought of trying a CrossJoin within the TopCount,
but this hasn't worked. The code is:

with
set [Top 5 Instruments] as '
TopCount(
CrossJoin({[Instruments].[Instrument Short Name].members},{[Calendar
Time].[27 Sep 2002]:[16 May 2005]})
,5, ([Measures].[Exposure (USD)],[Calendar Time].[27 Sep 2002])
)'

select
non empty {[Top 5 Instruments]} on columns
from [Positions by Instrument]
where ([Measures].[Exposure (USD)], [Trader].[X])

This has just brought up a set of 5 dates and some exposure numbers on
those dates. Is this something like the 5 dates with the highest
exposures? I'm not sure how its used the instruments but this is not
what I am looking for.

Thanks.

Nav, I understand what you're trying to accomplish, but I can't seem to get
the TOPCOUNT to resolve for every time.currentmember on the rows (which is
what Deepak said too). When I echo back time.currentmember via a calculated
member, it has just one value there, so it doesn't to a TOPCOUNT of 5 for
EVERY date, which is what you're looking for. I've tried all kinds of
generates, MDX unions and crossjoins, but have had no success. I'll keep
trying for a bit more. Interesting challenge, indeed.

- Phil

"Nav" wrote:

Phil,

I tried a few other combinations of code too, without success.

Have you had any luck yet?

Maybe Deepak knows of a way of accomplishing this?

Thanks,

Nav

Hi Nav and Phil,

I tried to adapt my previous post to the desired report format, and
here's what I came up with:

Set [TimeRange] as
'{[Calendar Time].[27 Sep 2002]
:[Calendar Time].[16 May 2005]}'
Set [Top 5 Instruments] as
'Generate([TimeRange],
Topcount(CrossJoin({[Calendar Time].CurrentMember},
[Instruments].[Instrument Short Name].members), 5,
([Measures].[Exposure (USD)], [Trader].[X])))'
Member [Measures].[Exposure1] as
'Sum({[Top 5 Instruments].Item(
((Rank([Calendar Time].CurrentMember, [TimeRange])
- 1) * 5) + 1)}, [Measures].[Exposure (USD)])'
Member [Measures].[Exposure2] as
'Sum({[Top 5 Instruments].Item(
((Rank([Calendar Time].CurrentMember, [TimeRange])
- 1) * 5) + 2)}, [Measures].[Exposure (USD)])'Member
[Measures].[Exposure3] as
'Sum({[Top 5 Instruments].Item(
((Rank([Calendar Time].CurrentMember, [TimeRange])
- 1) * 5) + 3)}, [Measures].[Exposure (USD)])'
Member [Measures].[Exposure4] as
'Sum({[Top 5 Instruments].Item(
((Rank([Calendar Time].CurrentMember, [TimeRange])
- 1) * 5) + 4)}, [Measures].[Exposure (USD)])'
Member [Measures].[Exposure5] as
'Sum({[Top 5 Instruments].Item(
((Rank([Calendar Time].CurrentMember, [TimeRange])
- 1) * 5) + 5)}, [Measures].[Exposure (USD)])'

Select
{[Measures].[Exposure1],
Measures].[Exposure2],
Measures].[Exposure3],
Measures].[Exposure4],
Measures].[Exposure5]} on columns,
[TimeRange] on rows
from
[Positions by Instrument]
where ([Trader].[X])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

Thank you for your reply.

I understand the query you have written, up until the definitions of
the calculated members for each of the top 5 exposures. Here I become
confused as to why you're using the Sum() function across [TimeRange],
as I wanted the top exposures at each date within [TimeRange], but I
may be misunderstanding the method you're using here.

Unfortunately, on running this query I got the following error:
Formula error - duplicate dimensions across (independent) axes - when
calculating a query axis

I simplifed (and updated) the query down to try and spot the source of
the error and currently have this (restricted just to the number 1
exposure for debugging purposes):

With
Set [TimeRange] as
'{[Calendar Time].[04 Jan 2005]:[Calendar Time].[02 Jun 2005]}'
Member [Measures].[Absolute Exposure (USD)] as
'Abs([Measures].[Exposure (USD)])'
Set [Top 5 Instruments] as
'Generate([TimeRange], Topcount(CrossJoin({[Calendar
Time].CurrentMember}, [Instruments].[Instrument Short Name].members),
5, [Measures].[Absolute Exposure (USD)]))'
Member [Measures].[Exposure1] as
'[Top 5 Instruments].Item(((Rank([Calendar Time].CurrentMember,
[TimeRange])+ 1) * 5) + 1)'

Select
{[Measures].[Exposure1]} on columns
from
[Positions by Instrument]
where ([Measures].[Absolute Exposure (USD)],[Trader].[X])

However this returns the same error as above. Any ideas?

Many thanks,

Nav