Take the 2-minute tour ×
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. It's 100% free, no registration required.

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:

SELECT  
 NON EMPTY   
 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:

SELECT  
 NON EMPTY   
 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.

EDIT:

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:

SELECT  
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
    
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
add comment

migrated from stackoverflow.com Dec 20 '11 at 14:13

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

1 Answer

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]
              ,[Date].[Month].[All])
             ,BDESC)
    on Columns
       non empty
      ,[Date].[Month].Children
    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
add comment

Your Answer

 
discard

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