Recently I got a very interesting query from one of my colleagues. He was trying to sort a table in a SSRS report based on an integer member property, but the sort results were coming wrong according to him. He said that the report was treating the property field as a character and claimed that this was a problem reported by many people all over the MSDN forums. That is when I thought of putting forth this post.
To illustrate the problem as well as the solution, I will be using the Adventure Works cube.
1) The requirement is to sort the Sub Categories based on the key values in the report. For this, a new dataset is made with the following MDX query
4) Now preview the report.
We can see that the key has been considered as a string for sorting, because of which 10 comes after 1. For the proper numerical sorting, we will have to follow the steps below.
1) Modify the MDX query to include the TYPED keyword.