Try using Generate() to iterate through the states:

Generate([State].[State Code].Members,
TOPCOUNT([Vendor].[Vendor Name].Members,10,
[Measures].[Amount])) ON ROWS
FROM APData

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Oops - need to include the current state, via CrossJoin:

Generate([State].[State Code].Members,
CrossJoin({[State].CurrentMember},
TOPCOUNT([Vendor].[Vendor Name].Members,10,
[Measures].[Amount]))) ON ROWS
FROM APData

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks Deepak. That worked pretty well.

How do I eliminate rows with blank cells? I do not want to return anything
for states with no Amount. I want to return only 3 Vendors, if that's all a
state has with Amounts > 0.

Also would like to know if there is a way to submit this query through Excel.



"Deepak Puri" wrote:

Hi Chris,

Using the Non Empty option on rows in the MDX query should suppress
blank rows:

Non Empty Generate([State].[State Code].Members,
TOPCOUNT([Vendor].[Vendor Name].Members,10,
[Measures].[Amount])) ON ROWS
FROM APData
Don't know any way to directly submit an MDX query in Excel; but if you
select Field Settings/Advanced Options for the [Vendor Name] field, the
Show Top .. feature can be enabled. This might give you similar results
if [State Code] and [Vendor Name] levels are just below [All].


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks again for you help.

The Top N functionality doesn't seem to work correctly with > 1 level.
When I choose vendor name and show top 10, I get only the top 10
overall rather than the top 10 within each state. More oddly, I don't
actually get the top 10 overall, I get the top 6. If I choose top 5, I get
the top 3. Always seems to be a few short.

Do you have any other suggestions on how to submit this MDX query?
I need to get the results back in a format that could be imported or
copied into Excel.



"ChrisBusch" wrote:

Depends on the tools you have available - using SQL Server components,
here's a couple of options:

- You can run MDX like a SQL query with "flattened" results in a DTS
package, if the destination already has columns created to receive the
data (discussed in this NG)

- A Reporting Services Table Report can be built from an MDX query, and
rendered as an Excel or CSV file


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

What do you mean by "discussed in this NG"?

"Deepak Puri" wrote:

Here's an earlier thread from this News Group:

http://groups-beta.google.com/group/...rver.olap/brow
se_thread/thread/8c71a535e7f4e41a/dcf4221a22c7c42f?hl=en#dcf4221a22c7c42
f
You can actually query an OLAP cube directly from a TransformData task
in DTS. We are running such a query every hour, to detect a timeout
threshold. Here are some details of our scheduled DTS package:

- Source Connection: Microsoft OLE DB Provider for OLAP Services 8.0
(a drop-down option). Properties point to the OLAP Server & DB.

- On the TransformData Source Tab, SQL Query is selected as source.
We have put an MDX query, results get "flattened" to a rowset.
Special SQL syntax is supported, but haven't figured that out yet.

- The Destination Connection is relational OLE DB Provider for SQL
(results filled in pre-defined SQL table). I guess Excel could work
instead here, but you would have to experiment with column mapping.

For the MDX query, a difference from SQL is that, if an empty set is
returned, the result columns are not defined, so check for that first.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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