Analysis Services Requirements for (All) Level

English Query generates Multidimensional Expressions (MDX) queries with the assumption that each dimension has an (All) level. The (All) level is the top level of a dimension that aggregates all the members of all the levels with no breakdown. When you create a dimension using Cube Editor, the dimension has an (All) level by default.

If a dimension does not have an (All) level, users may not get the results they expect. For example, for the question, "show the total unit sales", English Query generates this query:

SELECT{Measures.[UnitSales]} ON COLUMNS
FROM Sales

If the [Time] dimension has an (All) level, this query retrieves the sum of the unit sales for all of the years in the [Time] level. For example, if the [Time] dimension contains the years 1997 and 1998, this query retrieves the combined total sales for 1997 and 1998. However, if the [Time] dimension does not have an (All) level, this query retrieves the unit sales for only the first year in the [Time] dimension. For example, if the [Time] dimension contains the years 1997 and 1998, this query retrieves the total unit sales for 1997 only.

For more information about OLAP cubes and MDX syntax, see Introduction to Cubes and Basic MDX.