SSAS Rank with respect to multiple measure and dimension

Question

I've a Rank column in my report.

The logic behind the rank column is based on a Measure value (either a count or sum) and based on three dimensions.

For example,

I've to show a rank based on a Measure value (Total customers) of a selected Company(which will be parameter for the report, which in turn a dimension) based on Period (It can be anything year, half-yearly, quarterly, monthly) and a product group

.

Even the measure value can be chosen as a parameter from the report which makes things complex.

How can I accomplish this?

I think I can use RANK function of SSAS for a calculated field along with ORDER.

But can we create one RANK function without hard coding the measure or dimension?

Answer 1

I would handle this by creating a stored procedure to return the data for my report.

The stored procedure would use the report parameters to build an MDX string and execute it on the SSAS server with OPENQUERY(). The MDX would only have to get the measure that the report is to be ranked by on Columns, and then Period and Product Group on rows.

Execute the MDX in the stored procedure, storing the result in a temporary table, and then SELECT from the temporary table using either the ROW_NUMBER() or RANK() function to add the Rank column to the output.