MDX TopCount

I am using a MDX to get the TopCount and it is working. The issue is that there are typos and miss spelled words in the primary attribute of my selection.

Therefore, I am trying to use a calculated member to get the intersection name (see code below). I am getting "#error" for the [Measures].[Intersection] in my results.

if I add the [Crash].[Intersection Streets].[Intersection Streets] in my topcount expression then I get the wrong topcount result (typo's).

Any ideas how to accomplish this.

Thanks,

with

Member [Measures].[Intersection] as

' [Crash].[Intersection Streets].[Intersection Streets].currentmember.uniquename'

select

{[Measures].[Crash Count],

[Measures].[Injury Crashes],

[Measures].[Fatal Crashes],

[Measures].[Fatalities],

[Measures].[Intersection]}

on columns,

Topcount( CrossJoin([Crash].[County].[County],

[Crash].[City].[City],

[Crash].[TS Crash Route].[TS Crash Route],

[Crash].[TS Crash Mile Station].[TS Crash Mile Station] ),

10, [Measures].[Crash Count])

on rows from

(select ([Crash].[County].[County],

[Crash].[City].[City],

[Crash].[TS Crash Route].[TS Crash Route],

[Crash].[Intersection Streets].[Intersection Streets],

[Crash].[TS Crash Mile Station].[TS Crash Mile Station])

on columns from [SDM_Reports]

where ([Crash].[City].&[Springfield]))

where ([Reportability].[Is Reportable].&[Yes],

[Crash].[Is Intersection].&[Yes],

[Crash].[Statistical Year].&[2004])

Orville,

I am not sure I understood your question, but if you just want to ignore errors and treat typos as 0, you can use IsError function. Example:

IIF(IsError(<yourMember>), NULL, <yourMember>)

|||

Thanks you for reply. Let try to clarify my issue.

There are typos is the street interesction attribute that is resulting in the wrong results. Therefore, I can construct the topcount using an attribute called station which is a numeric representation of the interesction data. However, I would like to retrieve/select the the first instance of each intersection name to be returned with the topcount. Our end users need the intersection name with the the topcount for reporting.

|||

Orville,

I understood that you want to do TopCount on one dimension, but then show not that dimension members, but some other values. It is probably possible, as you could probably have Station attribute with Intersection beeing defined as attribute property. But I would strongly suggest that you try to clean your intersection data before you actually load it into data warehouse. Trying to deal with cleanup issues in MDX is quite expensive in terms of time it takes to write logic and performance penalty from SSAS query.

It is quite possible that I did not understand your question, so if anyone else has better idea on how you can do this, I would be happy to read it too.

Vidas.Matelis <at> SSAS-Info.com

|||

I think the #Error in your calculated member could be fixed by removing the level reference from your calc. The quotes around the expression are no longer necessary in SSAS 2005 either.

eg

Member [Measures].[Intersection] as [Crash].[Intersection Streets].currentmember.uniquename