1. Unit Sales by Promition Media Type

Simple bar graph - make sure things are working.

MDX

WITH
	MEMBER Measures.[$Unit Sales] 
		AS 'Measures.[Unit Sales]', FORMAT_STRING = '#,#'
SELECT 
	{
	[Measures].[$Unit Sales]
	}
	ON COLUMNS, 
	ORDER(
		EXCEPT(
			[Promotion Media].[Media Type].MEMBERS,
			{[Promotion Media].[Media Type].[No Media]}
		),
		[Measures].[$Unit Sales],
		DESC
	)
	ON ROWS
FROM [Sales]

					

Resulting display

2. Unit Sales by Promition Media Type

Same simple query displayed as pie chart.

MDX

Resulting display

3. Units Shipped and Ordered by Store

Displays two measures on column axis.

MDX

SELECT
{
	[Measures].[Units Shipped],
	[Measures].[Units Ordered]
}
ON COLUMNS,
TOPCOUNT(
	[Store].[Store Name].MEMBERS,
	10,
	[Measures].[Units Shipped]
)
ON ROWS
FROM [Warehouse]

					

Resulting display

4. Unit Sales by State and Education

The simplest crossjoin case - displayed in typical grid fashion.

MDX

Select
  Order(
    {
      [Measures].[MeasuresLevel].[Unit Sales]
    },
    [Measures].[MeasuresLevel].[Unit Sales],
    DESC
  )
  On Columns,
  Order(
    {
       CrossJoin(
         {
           [Customers].[Country].[USA].Children
         }, 
         {
           [Education Level].[Education Level].Members
         }
        )
      },
      [Measures].[MeasuresLevel].[Unit Sales], 
      DESC
    )
  On Rows
From [sales]
					

Resulting display

5. Unit Sales by State and Education

Previous query has been restructured to move measure to where clause so that each dimension can have its own axis - which makes rendering template display as clustered bars.

MDX

Select
  Order(
    { [Education Level].[Education Level].Members }, 
    [Measures].[MeasuresLevel].[Unit Sales], 
    DESC
  )
    On Columns,
  Order(
    { [Customers].[Country].[USA].Children }, 
    [Measures].[MeasuresLevel].[Unit Sales], 
    DESC
  )
    On Rows
From
  [sales]
Where
  [Measures].[MeasuresLevel].[Unit Sales]
					

Resulting display

6. Unit Sales by Education and State

Same query but dimension are swapped to make it present properly as stacked pie. The second dimension, now Education, is for the slices of the pie. These 5 members add up to 100%, it makes sense to slice the pie by Education.

MDX

Select
  Order(
    { [Customers].[Country].[USA].Children }, 
    [Measures].[MeasuresLevel].[Unit Sales], 
    DESC
  )
    On Columns,
  Order(
    { [Education Level].[Education Level].Members }, 
    [Measures].[MeasuresLevel].[Unit Sales], 
    DESC
  )
    On Rows
From
  [sales]
Where
  [Measures].[MeasuresLevel].[Unit Sales]
					

Resulting display

7. As Doughnut

In fm7, the same query is displayed with the Doughnut chart, which is the same as a stacked pie except it leaves a hole.

MDX

Resulting display

8. The third dimension

What you’ve been waiting for. A third dimension introduced in the Pages axis. The chart rendering template puts each page tuple in a separate graph.

MDX

With
	Member Measures.[$Unit Sales] 
		AS 'Measures.[Unit Sales]  / 1000', FORMAT_STRING = '#,#'
Select
  [Measures].[MeasuresLevel].[$Unit Sales]
    On Columns,
  Order(
    { [Education Level].[Education Level].Members }, 
    [Measures].[MeasuresLevel].[Unit Sales], 
    DESC
  )
    On Rows,
  Order(
    { [Customers].[Country].[USA].Children }, 
    [Measures].[MeasuresLevel].[Unit Sales], 
    DESC
  )
    On Pages
From
  [sales]
					

Resulting display

9. Cities Level

Query is changed to use Cities level, which results in a higher cardinality trellace. But it also shows that column charts are not well suited for displaying long captions.

MDX

With
	Member Measures.[$Unit Sales] 
		AS 'Measures.[Unit Sales]  / 1000', FORMAT_STRING = '#,#'
	SET [Dicer Filtered Set]
      AS '{Descendants ([Customers].[All Customers], [Customers].[City],SELF)}'
Select
  [Measures].[MeasuresLevel].[$Unit Sales]
  On Columns,
  Order(
    { [Education Level].[Education Level].Members }, 
    [$Unit Sales], 
    DESC
  )
  On Rows,
  Non Empty
  {
	Head (
		Order ([Dicer Filtered Set],
			[$Unit Sales],
			BDESC
		),
		10
	)
  }
  On Pages
From 
  [sales]
					

Resulting display

10. Bars are better

Changing chart type to Bar results in the whole caption being shown.

MDX

Resulting display

11. Try as pie trellis

Same query displayed as a pie trellis. I think this looks pretty cool. But in a real program there would be a "show legend" button.

MDX

Resulting display

12. More pies!

Drop level down to City and get top 50. This trellis chart would best supplement one which showed sales for each city.

MDX

With
	Member Measures.[$Unit Sales] 
		AS 'Measures.[Unit Sales]  / 1000', FORMAT_STRING = '#,#'
	Set [Dicer Filtered Set]
      As '{Descendants ([Customers].[All Customers], [Customers].[City],SELF)}'
Select
  Order(
    {
      [Measures].[MeasuresLevel].[$Unit Sales]
    },
    [$Unit Sales],
    DESC
  )
  On Columns,
  Order(
    { [Education Level].[Education Level].Members }, 
    [$Unit Sales], 
    DESC
  )
  On Rows,
  Non Empty
  {
	Head (
		Order ([Dicer Filtered Set],
			[$Unit Sales],
			BDESC
		),
		50
	)
  }
  On Pages
From [sales]
					

Resulting display

13. A pie replacement

I often use 100% bar charts instead of a pie trellis for this type of data. It gives a different visual perspective. Ok, now onto some Adventure Works queries and displays.

MDX

With
	Set [Dicer Filtered Set]
      As '{Descendants ([Customers].[All Customers], [Customers].[City],SELF)}'
Select
  Order(
    { [Education Level].[Education Level].Members }, 
    [Measures].[Unit Sales],
    DESC
  )
  On Columns,
  Non Empty
  {
    TopCount ([Dicer Filtered Set], 50,[Measures].[Unit Sales])
  }
  On Rows
From
   [sales]
Where
  [Measures].[Unit Sales] 
					

Resulting display

14. Reseller Sales Amount, Sales Territory Country by Product Subcategory

The query has Date on the Pages axis to make it a trellis. The top chart is 100% bars - the bottom is value bars. I think both points of view are valuable.

MDX

WITH
  MEMBER [Measures].[$RSA]
    AS '[Measures].[Reseller Sales Amount]  / 1000', FORMAT_STRING = '$#,#'
	SET Top5Products AS
		'TOPCOUNT([Product].[Subcategory].[All Products].Children,5,[Measures].[Reseller Sales Amount])'
SELECT
  Top5Products
  ON COLUMNS, 
  Non Empty
  Order(
    {
      [Sales Territory].[Sales Territory Country].[All Sales Territories].Children
    },
    [Measures].[Reseller Sales Amount],
    DESC
  )
  ON ROWS,
  {
    [Date].[Calendar Year].[All Periods].Children
  }
    ON PAGES
FROM
  [Sales Targets]
WHERE
  {
    [Measures].[$RSA]
  }
					

Resulting display

15. Columns and Rows axes swapped

When these axes are swapped, it changes which dimension is used as the chart series. Still trellised by date.

MDX

WITH
  MEMBER [Measures].[$RSA]
    AS '[Measures].[Reseller Sales Amount]  / 1000', FORMAT_STRING = '$#,#'
SELECT
  NON EMPTY
  Order(
    {
      [Sales Territory].[Sales Territory Country].[All Sales Territories].Children
    },
    [Measures].[Reseller Sales Amount],
    DESC
  )
    ON COLUMNS, 
  TOPCOUNT (
    [Product].[Subcategory].[All Products].Children,
    5,
    [Measures].[Reseller Sales Amount]
  )
     ON ROWS,
  {
    [Date].[Calendar Year].[All Periods].Children
  }
    ON PAGES
FROM
  [Sales Targets]
WHERE
  {
    [Measures].[$RSA]
  }
					

Resulting display

16. Reseller Sales Amount x Month of Year x Sales Territory Country

Pages axis in this query makes it trellis by Country. An interesting observation is the pattern of reseller sales found in the US and in France compared to the pattern observed in the other countries. I’d try to understand that.

MDX

WITH
  MEMBER [Measures].[$RSA]
    AS '[Measures].[Reseller Sales Amount]  / 1000', FORMAT_STRING = '$#,##0'
SELECT
  {
    [Measures].[$RSA]
  } 
    ON COLUMNS, 
  {
    [Date].[Month of Year].[All Periods].Children
  }
    ON ROWS,
  NON EMPTY
  ORDER (
    {
      [Sales Territory].[Sales Territory Country].[All Sales Territories].Children 
    }, 
    [Measures].[Reseller Sales Amount], 
    DESC
  )
    ON PAGES
FROM
  [Sales Targets]
					

Resulting display

17. Reseller Sales Amount x Product x Month x Country

By moving the measure to the where clause, I can have three dimensions represented. Product category replaces Measure as the inner-most charting dimension. So each of the bars in the previous result is now sliced by product category.

MDX

WITH
  MEMBER [Measures].[$RSA]
     AS '[Measures].[Reseller Sales Amount]  / 1000', FORMAT_STRING = '$#,##0'
  SET Top5Products AS
	  'TOPCOUNT([Product].[Subcategory].[All Products].Children,5,[Measures].[Reseller Sales Amount])'
SELECT
  Top5Products
    ON COLUMNS, 
  {
    [Date].[Month of Year].[All Periods].Children
  }
    ON ROWS,
  NON EMPTY
  ORDER (
    {
      [Sales Territory].[Sales Territory Country].[All Sales Territories].Children 
    }, 
    [Measures].[Reseller Sales Amount], 
    DESC
  )
    ON PAGES
FROM
  [Sales Targets]
WHERE
   [Measures].[$RSA]
		

Resulting display

18. Same query displayed as 100% columns.

Again a nice way to show the relative sales breakdown by product category.

MDX

Resulting display