CODEWITHSUNDEEP
sql-serverssasmdx
Pragmateek
Pragmateek

Reputation: 13396

Global aggregation on non-default measure

To aggregate along the default measure we use:

SELECT FROM [Sales]

It returns a "scalar", a value without any header:

+------+
| 1000 |
+------+

How to do the same with non default measure?

This raises an error:

SELECT [Measures].[Quantity] FROM [Sales]

This works:

SELECT [Measures].[Quantity] ON 0 FROM [Sales]

But instead of having a "scalar" we get an additional header with the name of the measure:

+----------+
| Quantity |
+----------+
|   1000   |
+----------+

Upvotes: 1

Views: 46

Answers (1)

vldmrrdjcc
vldmrrdjcc

Reputation: 2132

This kind of query

SELECT FROM [Sales]

is so called query with zero axes. Because no members were assigned to any (non-slicer) axis, the result is considered to have zero axes and by convention would be single unlabeled cell, or at least cell with no distinct row or column headers, and that is what you see.

With this query 

SELECT [Measures].[Quantity] ON 0 FROM [Sales]

you have specified that you want to see Quantity measure on axes 0 (to see Quantity measure on Columns).

To achieve "zero axes" query result you should put your measure on slicer (specified with Where):

SELECT  FROM [Sales]
WHERE [Measures].[Quantity]

Upvotes: 1

Related Questions