I've got a crosstab that I am not able to get to sort by Rank.
For the row I've got one data item with multiple results. For columns I have one that is the Rank for data item A. The second column is the Month, with a measure for calculated data item B.
I am trying to get the crosstab to display the data by rank (ascending) and also with the months ascending. Basically like this:
Rank Jan Feb Mar
Result 1 1 8 6 7
Result 2 2 4 9 6
Result 3 3 1 3 2
I can get this to work fine if the column that contains Month only has one result, but as soon as I add a column with multiple results the Ranking becomes nonsensical.
Any help you guys can offer would be appreciated!
Thanks
Is this a relational or OLAP source?
Can you post the expression for ranking, and can you describe how the ranking is nonsensical?
sorting is obtained in a crosstab using the order dimensional method (at least that is the only way I got it to work)
create a new data item in your query like this: order([Result], [Rank]) and name it Result Ordered
where [Result] is the data item you want to sort and [Rank] is the data item that computes the rank (something like rank([Revenue] witin detail [Product Line])
then instead of the [Result] data item, place on rows the new data item [Result Ordered]
the examples I gave you are hypotethical, because so is your example, but hope you can use it and appy it to your case
Unfortunately I'm not on the data management side, so I honestly can't tell you if it is relational or OLAP.
The rank formula is:
rank([Weighted Duration] within set [Contact Request Sub Area Description])
As for how the Rank is nonsensical, when I set it to sort ascending rather than being 1,2,3,4,etc it comes out randomly. Something like 6,20,34,135,2,etc.
The within set clause would indicate that this is an OLAP model.
Can you give me an idea where items are being displayed on the crosstab? I'm assuming that the Rank is the default measure. Is Contact Request Sub Area Description included or referenced anywhere on the crosstab?
Solve order may be causing issues here. Make sure that rank is set after Weighted Duration.
What is the node in the rows? Are you using the order function or using order wizard under the Tools menu?
The left hand column (node) is the Contact Request Sub Area Description data. Rank is dropped into the first column as its own measure, while the month data is in a second column with Avg Duration as the default measure.
For the sort, I'm just trying to use: Data-->Sort-->Ascending
Try using the order function in the [Contact Request Sub Area Description] data item.
So it would be order ([Cube].[Dimension].[Hierarchy].[Level],[Weighted Duration], BDESC)
If you click on show generated SQL/MDX do you get SQL or MDX? Or both?
I suspect the ranking may be caused by a solve order problem. Try setting the solve order to 2.