OBIEE sort on measure in pivot table

user4109719

I want to sort on the total of the measure in a pivot table. It is pivoting on a time attribute.

I created a column using the the rank function like this:

rank(sum( "Measures"."GP Amt"))

I put it in the first column of the table and also selected the sort ascending option on the column. It is not sorting correctly.

It seems to be sorting the numbers as characters but it's not even doing that consistently. The data type of the column is numeric. Using the cast function to make sure it's seen as numeric doesn't help either.

If I can get it to work I want to make the column invisible. Will it still sort on it then?

Jerry Casey

Try checking off the "Treat as Attribute" box in the "Edit Formula" window.

user4109719

When I check Treat as Attribute it makes the rank column equal to 1 for all rows.

Jerry Casey

Try adding a "By" clause to your calculation:

user4109719

That worked. Thanks.