how to use count in mdx query

[lancastro1416]

hello people... sorry im new with this stuff, cant get it right... i need help...

suppose i have a table that has the stores in columns and and order count as a row:

Store1 Store2 Store3 Store4
ordercount 100 null null 30

assume that there are many stores listed in the table... how can i have the number of stores that has an ordercount?... ive tried some mdx queries but i still cant get it right...

[johnsonp]

Does it have to be an MDX query? If not, try building a dynamic sql statement by accessing the table column names in the syscolumns table. If your column names are consistent for the different stores, you can really set your output however you want to.

First, get the names of columns you want to get data for:
select c.[name] from syscolumns c, sysobjects o where c.id = o.id
and o.type = 'U' and o.[name] = '<tablename>'

Then you can re-dimension your data with a loop into a temp table. Sorry, I didn't have time to write this part out, but maybe this makes sense.

[lancastro1416]

yeah it need to be in mdx query because im using cubes for my data source....

[c hinkle]

And I have a followup question.
Given three dimensions (Time, Product, and Customer) and a single metric (Revenue), I've been asked to group by Time and Customer and, within each grouping, display the Total Revenue / Unique number of Products purchased by that customer in that time period.

I'm looking for some MDX for that.
chuck