keep in mind javascript is needed for full functionality of the web portal.
Welcome back to our usual blog of Excel functions from A to Z. Today we look at the CUBERANKEDMEMBER function.
The CUBERANKEDMEMBER function
When the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or a later data source, tits function returns the Northth, or ranked member, in a set. Use this to return one or more items in a set, such as the top seller or the 10 top students.
The CUBERANKEDMEMBER The function uses the following syntax to operate:
CUBERANKEDMEMBER (Connection, set_expression, rank, )
The CUBERANKEDMEMBER The function has the following arguments:
- Connection: this is mandatory and represents a text string of the name of the connection to the cube
- set_expression: this is also necessary. This is a text string of an established expression, What “{[Item1].kids}”
- set_expression it can also be the CUBESET function or a reference to a cell that contains the CUBESET function
- rank: again, this is obligatory. This is an integer value that specifies the top value to return. And rank it is a value of 1, returns the top value, and rank it is a value of 2, returns the second highest value, and so on. To return the 5 top values, use CUBERANKEDMEMBER five times, specifying a rank, 1 a 5, every time
- subtitle: this argument is optional. This is a text string that is displayed in the cell instead of the title, if one is defined, of the cube.
It should be noted at the same time that:
- the CUBERANKEDMEMBER The function is supported only when the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or a later data source.
- when the CUBERANKEDMEMBER The function evaluates, temporarily display a message “# GETTING_DATA …” in cell before all data is retrieved.
- si el nombre de la conexión no es una conexión de libro válida almacenada en el libro de trabajo, CUBERANKEDMEMBER returns a #NAME? error value. If the online analytical processing server (OLAP) it's not working, is not enabled or returns an error message, CUBERANKEDMEMBER returns a #NAME? error value
- CUBERANKEDMEMBER returns a #N / A error value when the syntax of set_expression is incorrect or when the set contains at least one member with a dimension different from the other members
- to return the background North values in their place, Use the Sort order Y Sort by arguments of the CUBESET function to reverse the order of the set so that the top values in the ordered set are the lower values. As an example, CUBERANKEDMEMBER (“Sales”, $ D $ 4,1) returns the last member, CUBERANKEDMEMBER (“Sales”, $ D $ 4,2) returns the penultimate member, and so on.
Please, see my examples below:
= CUBERANKEDMEMBER (“Sales”, $ D $ 4,1, “Main month”)
= CUBERANKEDMEMBER (“Sales”, CUBESET (“Sales”, “Summer”, “[2020].[June]”,”[2020].[July]”,”[2020].[August]”), 3,” Main month “)
Soon we will continue with our functions from A to Z of Excel. Keep checking: there is a new blog post every business day.
