Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I wish to add an extra dimension to this query, so that I'm able to see the total amount of an item sold, per month/year:

 Order([Item].Children, Measures.[Total Line Value], DESC) ON COLUMNS,
 { [Total Line Value] } ON ROWS
 FROM        [Sales Analysis]

What I am looking for is something like so:

 Order([Item].Children, Measures.[Total Line Value], DESC), [January 2010].Children ON COLUMNS,
 { [Total Line Value] } ON ROWS
 FROM        [Sales Analysis]

This will eventually allow me to see the total sales of items for a particular month and year, but this throws an error as I'm not sure how to achieve it.


To clarify. The first query above works. It returns the grand total of items sold in order from highest to lowest BUT its for all time. I wish to restrict this to just show the order from highest to lower for just 1 month only, that being January 2010. I have a time field in my cube where it's possible to select the month and year like so: [January 2010].Children - I'm able to view this in excel, I just need to be able to view this in my application.

I have a similar query, this one returns all the sales, for each day of January 2010:

NON EMPTY   { [January 2010].Children } ON COLUMNS,
            { [Total Line Value] } ON ROWS
FROM        [Sales Analysis]

I just want to split this down further to show each individual item's sales for that month, in order from highest to lowest.

share|improve this question

migrated from Dec 20 '11 at 14:13

This question came from our site for professional and enthusiast programmers.

Do you want to look at the same measure (Total Line Value), but restricted by some period of time? – Max Dec 19 '11 at 16:14
Are you just trying to see your items in order of their total line value? – ConcernedOfTunbridgeWells Dec 19 '11 at 16:44
@Max - Yeah thats correct, at the moment I can see the total line value of each item, but its for all time. I wish to restrict this to show it for just 1 month. – BiffBaffBoff Dec 20 '11 at 7:59
up vote 1 down vote accepted

I'm not quite sure what you're asking, but I think you want to do the following (correct me if I'm wrong):

  • You want to see a breakdown of sales by month, one row for each month, with a total for all months at the bottom.

  • You want to see the items across the columns, ordered by the item with the largest sales volume to the smallest.

select non empty
       order ([Item].[Item].Children
             ,([Measures].[Total Line Value]
    on Columns
       non empty
    on Rows
  from [Sales Analysis]
 where [Measures].[Total Line Value]

The columns are each individual item, sorted by the total Line Value over all months, i.e. from all-time best seller to all-time lowest seller, with items having no sales at all excluded.

The rows are the months for all time where data exists. You can use a set definition to specify any date range you want. Note that this assumes the existence of a dimension called [Date].

This query will show the items in order of total sales across the columns and the sales for each month by item down the rows. I'm not 100% sure if this is what you want; feel free to clairfy your question if I'm not on the right track.

share|improve this answer
I just wish to see the total sold for each item per month, with highest at the top and lowest at the bottom. – BiffBaffBoff Dec 20 '11 at 8:05
Fixed it, your query was close, I just had to make some minor adjustments. Thanks alot for your help – BiffBaffBoff Dec 20 '11 at 8:25

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.