How to Use Excel's CUBESETCOUNT Function

By eHow Computers Editor

Rate: (0 Ratings)

Excel's CUBESETCOUNT function returns the number of members in a set. It typically uses a CUBESET function or reference to a CUBESET function and requires a connection to a SQL Server 2005 or later data source. The following steps will show how to use Excel's CUBESETCOUNT function.

Flag Article

Instructions

Difficulty: Moderate

Step1

Learn the syntax for CUBESETCOUNT. It's CUBESETCOUNT(member_set) where member_set is an expression that refers to a set that is defined by the CUBESET function.

Step2

Expect CUBESETCOUNT to display the text “#GETTING_DATA…” in the cell until the data has been retrieved from the data source. CUBESET will return the #NAME? error value if the Online Analytical Processing Server is not available or not running. The #NAME? error value will also be returned if CUBESETCOUNT uses a CUBESET expression that refers to an invalid connection.

Step3

Determine the causes of a #VALUE! error value from CUBESETCOUNT. This can occur when member_set has more than 255 characters or if the CUBESET command omits the sort_by clause when one is required. A member_set expression longer than 255 characters may be used by entering into a cell and referencing that cell instead of entering it directly into the CUBESET command.

Step4

Interpret the most likely cause of a #N/A error value from CUBESET to be a reference to a session-based object such as a PivotTable that is no longer available.

Step5

Look at some examples of CUBESETCOUNT. =CUBESETCOUNT(CUBESET(“Grades”,[Class].[All Classes].Students”,”All Classes”,1,[Courses].[Grades])) will return the number of members in the cube set defined by [Class].[All Classes].Students of the “Grades” data source. Note that the other CUBESET arguments having to do with captions and sorting are not relevant to CUBESETCOUNT.