CODEWITHSUNDEEP
ssasmdx
SidC
SidC

Reputation: 3213

MDX Query to Produce Revenue by Top N Advertiser

I need to create a report using SSRS 2008 that will show the top 10 advertisers by revenue. I wrote the MDX query in management studio as follows:

Select [Measures].[Value] on 0,
non empty
generate([Dim Industry].[SK Industry ID],
topcount([Dim Advertiser Branch].[Advertiser],
[Measures].[Value],bdesc),10)
on 1
from FuseCube

I get a parse error stating:

Query (5, 20) An expression was expected for the function argument and no expression   
was detected.

My goal is to report the top 10 Advertisers, their associated branch and industry by Value (aka revenue). The remaining ranked advertisers should go into an "others" category so that I can sum the value and get a grand total.

How should I restructure the above MDX query to produce desired results? Is it advisable to create the above query as a named set as it will be used repeatedly?

Upvotes: 1

Views: 883

Answers (1)

Noe Zavala
Noe Zavala

Reputation: 464

TOPCOUNT syntax incorrect:

topcount([Dim Advertiser Branch].[Advertiser], [Measures].[Value],bdesc),10)

Vs

TopCount([Dim Advertiser Branch].[Advertiser], 10, [Measures].[Value]))

Upvotes: 1

Related Questions