dcsimg
Results 1 to 4 of 4

Thread: how to use count in mdx query

  1. #1
    Join Date
    Nov 2006
    Posts
    4

    Exclamation how to use count in mdx query

    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...

  2. #2
    Join Date
    Oct 2006
    Posts
    3

    Dynamic SQL instead?

    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.

  3. #3
    Join Date
    Nov 2006
    Posts
    4
    yeah it need to be in mdx query because im using cubes for my data source....

  4. #4
    Join Date
    Nov 2006
    Posts
    1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •