MDX Essentials: Basic Numeric Functions: The Count() Function - Page 2

The Count Function

According to the Analysis Services Books Online, the Count function "returns the number of items in a collection, depending on the collection." The term "collection" here is generic, as the "collections" to which the Books Online refers can consist of the dimensions in a cube or a tuple, the cells in a set, or the levels in a dimension or a hierarchy. Count handles these "collections" in a couple of different syntactical ways. The scope of this article will be to focus more on the set-specific use of Count(), and we will refer to the function with the appended parenthesis ( "()" ) in that context.

We will examine the syntax for the Count() function, and then take a look at the options it affords with regard to the exclusion of empty cells from the counts it performs. Next, we will undertake practice examples built around hypothetical business needs that illustrate logical uses for the function. We will extend the examples to include exclusion of empty cells in a set we construct, to afford us a scenario for examining the options to include or exclude these cells in the overall count.

Discussion

The Count() function counts the cells defined by the set that is specified in the function. The syntax with which we employ the function affords us the option of specifying whether or not we wish to count empty cells within the range of the specified set. If we do not wish to include empties, we simply add the appropriate keyword to restrict the returned count to populated cells only.

Let's look at some syntax illustrations to further clarify the operation of Count().

Syntax

Syntactically, the set we use to specify the range of cells to count is placed within the parentheses to the right of Count, and separated by a comma from the optional flag we have described. The optional EXCLUDEEMPTY flag overrides the default behavior of the function (which is INCLUDEEMPTY) with regard to empty cells, and forces the function to ignore any unpopulated cells within the set specified.

The syntax is shown in the following string:

Count(Set, [, INCLUDEEMPTY | EXCLUDEEMPTY])

The following example expression illustrates a use of the Count() function. Let's say that FoodMart information consumers, whose data is housed within the Warehouse cube, wish to see the number of Warehouses supporting each of our Product Departments in a given year. The basic Count() function involved, disregarding for the time being the empty or non-empty consideration, would be as follows:

Count ({[Warehouse].[City].MEMBERS })  

This is simply expressing that we wish to count the "number of Warehouse City members" for a given purpose. Looking at our cube structure within the metadata pane of the MDX Sample Application, as shown in Illustration 1, we can verify that there are twenty-three Warehouse City locations by performing a manual count.

If we were to create an MDX query, within which we construct a calculated member to perform the count (see Calculated Members: Introduction and Calculated Members: Further Considerations and Perspectives, among kindred articles, in the Database Journal MDX in Analysis Services series for a hands-on introduction to calculated members), and to obtain the information requested, we might begin with the following:

WITH
    Member [Warehouse].[No. Locations]
AS
    'COUNT ( {[Warehouse].[City].MEMBERS })'
SELECT
    { [Warehouse]. [No. Locations] } ON COLUMNS,

    { [Product].[Product Department].Members} ON ROWS
FROM
    [Warehouse]
WHERE 
     [Time].[1998]

This simple query, for a count of the Warehouse Cities by Product Department, would return a dataset similar to that depicted in Table 1.

If we were to scrutinize the data, or perhaps to query the cube for the same information in a different manner, we would soon realize that the above results reflect every possible combination of Product Department and Warehouse City. This is because we have left the Count() function at default; not stipulating that we wished to leave empty cells (representing Product Department / Warehouse City combinations that witnessed no action), through the use of the EXCLUDEEMPTY keyword in our Count() function means that INCLUDEEMPTY flag is assumed by default.

To prove this, we merely add the EXCLUDEEMPTY keyword, flagging the function to ignore empty cells in the count, as follows:

   COUNT ( {[Warehouse].[City].MEMBERS}, EXCLUDEEMPTY)

Upon execution with the change above, the query would return a dataset similar to that depicted in Table 2.

We will practice the use of the Count() function in the section that follows. Moreover, we will explore the use of the EXCLUDEEMPTY flag we have discussed, to activate the concept of its use in eliminating empty cells from consideration by the function. Finally, we will go a step further, and confirm our results by looking at the data from another perspective.