This function returns the number of members in the
specified subset of the currently open database.
The OLAPSubsetCount function is designed specifically
to allow you to count the number of members in a particular subset
existing in a given dimension of a specified database. This function
returns a number that equals the total count of subset's members for
a subset in that dimension.
The function has the following syntax:
Syntax: OLAPSubsetMemberCount
(database, dimension, subset)
Explained below are the 3 parameters that have to
be provided when building this function.
- Database : Enter the name of the database which
has the dimension that you want to access / enter the cell reference
which has the name of this Database.
- Dimension : Enter the name of the dimension
in the above mentioned database whose subsets you want the function
to access / enter the cell reference which has the name of such
a dimension.
- Subset : Enter the name of the subset created
in the above mentioned dimension, and whose member names you want
to count / enter the cell reference which has the name of such
a subset.
Remarks
- If a connection is not established with a database
the result will be a #value.
- The dimension and the subset name specified
must exist in the database to which a connection is established
and both these names should be referenced exactly as they stand
in the database.
- All the arguments are compulsory and neither
argument is optional.
Example
This function may be easier to understand with an
example.
- In order to build this function with more ease,
open the specific database and bring a slice from that database
into Excel. In this way you will have connected to PowerOLAP because
you now have the OLAPOpen formula automatically on your sheet
when you bring a slice of the cube into Excel.
- In Excel, use the menu: Insert -> Function
and select PowerOLAP from the drop down list.
- Choose the OLAPSubsetMemberCount function from
the list of functions provided and click "Ok".
- When building the formula enter the cell range
for the database name and the dimension name, as well as enter
the subset name in that dimension for which the function counts
the members. If you don't want to manually type in the dimension
name or the subset name, use the OLAPDimension and the OLAPSubset
function respectively and access the dimension name through these
functions and reference the cell with this value when building
this function.
- In the following example B1 is the cell that
holds the database name and B5 is the cell that holds the dimension
name ,while "ALL" is the name of the subset for which
this function counts the total number of members and this counted
number is the value that will be returned by the function.
=OLAPSubsetMemberCount($B$1,
$B$5, "All") |
The above function returns a value = "
19 " which is the total count of the members in the subset called
"All" belonging to the dimension Time (Periods) (B5) in
the database "Acuity" (B1).