There is probably an easy answer but I don’t see it. I have a simple table with two heirarchies: OrderNo, LineNo and one measure: ExtPrice
I want to sort by ExtPrice Desc in the design view but it always groups by the OrderNo first. I have set it to a flat table and gone into OrderNo and LineNo and set them to Unspecified.
It continues to group by OrderNo first. How can I get it to sort by ExtPrice in design view? Thank you.
We also encountered similar problem in our project. Unfortunately Dundas does not provide this sorting option independently on a measure column (Atleast this is what I know). The workaround for this is that you can duplicate the ExtPrice column as dimension (varchar or something) and then use that varchar column to sort using the ExtPrice column and make it hidden
But this might hit the performance.
Hope this helps.
After not getting any responses I submitted this question to Dundas support and was also told that it was not possible. Another work around is to add a column with a unique key to the table and order by that column, hiding that column in the visualization if desired. Our unique key is this case is made up of two columns but I do now see how I could achieve this by adding an extra column. I was waiting to hear back from Support if this would be a good candidate to submit as a bug or feature request. It seems to me that a completely flat table with no row headers should be sortable on any column without any group by field being required.
Hi,
It’s true that adding dimensions to Rows in the metric set means you’re grouping by those values in that order, and this multi-dimensional way that metric sets work is the same regardless of what visualization you’re using and continues after re-visualizing. There is currently no option available for when you want to “break” this grouping, so to effectively sort by ExtPrice values without any grouping you would instead need to group by a column/dimension made up of unique values.
If your data source contains any other column besides OrderNo & LineNo that is unique, it could be added first under Rows in the metric set so that you aren’t grouping by non-unique order numbers. If there isn’t and there is nothing identifying each row of this data other than the combination of order and line numbers, creating a hierarchy from the main menu allows you to add both OrderNo and LineNo as Member Key Source (click Add Key when setting up a level to add a 2nd key) so that it’s made up of the OrderNo-LineNo pair of values, and would not group only by order numbers if it’s added first under Rows. If it hasn’t been raised yet with Support we do have a feature request we can add you to for a way to sort by measure without grouping by any dimension, but right now you would instead need to group first by any dimension of unique values.
Jamie, thank you for the response. I appreciate the work around to accomplish what I need. I did just submit a feature request to @christian.pervan in Support to add as a feature request if it fits in with the Dundas model:
Allow ability to sort by any measure in a flat table without having a group by applied first
Thanks again for the suggestion.