Sorting dimension queries

You can sort elements processed by a dimension query based on various element properties.

To sort the selected elements, use order by keywords, followed by one or more element properties and the order mode. These are the possible properties:

  • olapname: Implies ordering alphabetically by element name.
  • parentcount: Implies ordering by the number of element’s parents.
  • childcount: Implies ordering by the number of element’s children.
  • attribute: Specifies an attribute name by string literal or a string variable, elements will be sorted.
Note: The properties are repeatedly accessed by the sorting algorithm. The property used in the order-by clause is requested from OLAP only once per element.

Elements are sorted after they are filtered. If there are multiple sorting options, they are applied from left to right. First, the elements are sorted by the first or leftmost property. This generates the overall sort order. Sorting by the second property then occurs. If multiple elements share the same value for Property 1, they are grouped and sorted within those groups by the next option. Therefore, the overall sort order from the previous sorting is not affected. The sorting mode must be specified for every used element property. The mode can be either Ascending (asc) or Descending (desc).

For example, you have a list of persons. For each, Property 1 is their name and Property 2 is their age. Four and two of the persons share the same name.

Element Property 1 - Name Property 2 - Age
Person 1 John 21
Person 2 John 24
Person 3 John 23
Person 4 Alfons 50
Person 5 Richard 11
Person 6 John 29
Person 7 Xavier 13
Person 8 Richard 15

In this example, Property 1 is sorted in ascending order and Property 2 in descending order.

These tables show an example of a list of persons. For each person, Property 1 is sorted in ascending order:

Property 1
Alfons
John
John
John
John
Richard
Richard
Xavier

Then, Property 2 is sorted in descending order. But, because multiple elements have the same Property 1 value, they are grouped and sorted within that group:

Property 2
Alfons 50
John 29
John 24
John 23
John 21
Richard 15
Richard 11
Xavier 13

Example of sorting in dimension queries


     OLAPConnection connection = OLAPCreateNamedConnection("");

	string errorMessage = "";
	int errorCode = 0;

	string sDimension = "Product";
	string nameAttribute = "NAME";

	try
	{
		OLAPElementList list = on connection select all from sDimension order by olapname asc, nameAttribute desc;

		foreach (OLAPElement e in list)
		{
			WriteLine("\t" + ToString(e));
		}
	}
	catch (errorMessage, errorCode)
	{
		WriteLine("Exception: " + errorMessage);
	}

	OLAPDisconnect(connection);