There are situations when we want to display just a list of members with no data associated with them. Naturally, we expect to get that list in rows, so that we can scroll through them vertically instead of horizontally. However, the rules of MDX say that we can't skip the axes. If we want something on rows (which is AXIS(1) by the way), we must use all previous axes as well (columns in this case, which is also known as AXIS(0)).
The reason why we want the list to appear on axis 1 and not axis 0 is because a horizontal list is not as easy to read as a vertical one.
Is there a way to display those members on rows and have nothing on columns? Sure! This recipe shows how.
Notation for empty set is this: {}. So for the axis 0, we would simply do this:
{ } ON 0Follow these steps to open the Query Editor in SQL Server Management Studio (SSMS):
Follow these steps to get a one-dimensional query result with members on rows:
Put an empty set on columns (
AXIS(0)). Notation for the empty set is this:{}.Put some hierarchy on rows (
AXIS(1)). In this case we used the largest hierarchy available in this cube – customer hierarchy of the same dimension.Run the following query:
SELECT { } ON 0, { [Customer].[Customer].[Customer].MEMBERS } ON 1 FROM [Adventure Works]
Although we can't skip axes, we are allowed to provide an empty set on them. This trick allows us to get what we need – nothing on columns and a set of members on rows.
Skipping the Axis(0) is a common technique to create a list for report parameters. If we want to create a list of customers whose name contains "John", we can modify the preceding base query to use two functions to get only those customers whose name contains the phrase John. These two functions are Filter() and InStr():
SELECT
{ } ON 0,
{ Filter(
[Customer].[Customer].[Customer].MEMBERS,
InStr(
[Customer].[Customer].CurrentMember.Name,
'John'
) > 0
)
} ON 1
FROM
[Adventure Works]In the final result, you will notice the "John" phrase in various positions in member names:
Instead of skipping the Axis(0), if you put a cube measure or a calculated measure with a non-constant expression on axis 0, you'll slow down the query. The slower query time can be noticeable, if there are a large number of members from the specified hierarchy. For example, if you put the Sales Amount measure on axis 0, the Sales Amount will have to be evaluated for each member in the rows. Do we need the Sales Amount? No, we don't. The only thing we need is a list of members; hence we've used an empty set {} on axis 0. That way, the SSAS engine doesn't have to go into cube space to evaluate the sales amount for every customer. The SSAS engine will only reside in dimension space, which is much smaller, and the query is therefore more efficient.
Some client applications might have issues with the MDX statement skipping axes because they expect something on columns, and will not work with an empty set on axis 0. In this case, we can define a constant measure (a measure returning null, 0, 1 or any other constant) and place it on columns. In MDX's terms, this constant measure is a calculated measure. It will act as a dummy column. It might not be as efficient as an empty set, but it is a much better solution than the one with a regular (non-constant) cube measure like the Sales Amount measure.
This query creates a dummy value NULL on columns:
WITH
MEMBER [Measures].[Dummy] AS NULL
SELECT
{ [Measures].[Dummy] } ON 0,
{ [Customer].[Customer].[Customer].MEMBERS } ON 1
FROM
[Adventure Works]