Overview
We saw how we can join data from multiple tables as well as navigate data organized in complex forms like a parent-child relationship. In order to sort this data, regular sorting using a clause like ORDER BY may not be enough. We need more advanced sorting functions that can allow us to sort data based on complex criteria. RANKX is one of the important sorting functions that can be used for this purpose. In this chapter we will cover this topic.
Explanation
Let’s say we want to rank the internet sales amount for a product against the internet sales amount of all products.
Open SSMS, connect to the tabular SSAS instance where the AdventureWorks tabular model is deployed, and open a new MDX query window. All the perspectives that we saw in the last chapter, will be listed in the Cube pane. Select the Internet Operation perspective in the Cube pane and type the below expression.
EVALUATE (
SUMMARIZE ('Internet Sales'
,[ProductKey]
,"Sum Internet Sales", sum('Internet Sales'[Sales Amount])
,"Rank",RANKX(ALL('Internet Sales'[ProductKey]),SUMX(RELATEDTABLE('Internet Sales'),[Sales Amount]))
)
)
order by [Rank]
Execute the above expression and you should be able to find the results as shown below. In this expression, we are first grouping values by product key and internet sales amount of the respective products. In order to find the rank of these products, we have used the RANKX function. This function expects the table name as the first parameter, expression as the second parameter, and value as the third parameter. It compares the total sales amount of each product against sales amount of all other products.
You can read more about the RANKX function from here to understand more about its functionality.
In this way we can use advanced ranking functions to sort data based on complex criteria.
Additional Information
- Consider reading more about RANX.EQ function which is similar to the RANKX function.
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019
