Hello,
Is it possible to sort data in Template Studio?
Thanks in advance!
Hi Shan,
I hope you’re doing well. I asked a colleague of mine who works in Product Adoption and she mentioned that the best way to sort data is to export and do so in excel, as the functionality does not currently live in Prophix. If you ever have a quick question such as above, feel free to issue a ticket on support.prophix.com and our team will get back to you.
Have a wonderful day & happy holidays.
Nicole O’Halloran- your Customer Success Manager. 
3 Likes
That doesn’t seem like a helpful answer if Prophix is supposed to be a better solution to Excel.
Why wouldn’t you use a custom name set that would sort the information you wanted in the template?
Like a top count?
Shan,
You’re not going to be able to sort directly in Template Studio, but there are ways to have the data appear in Template Studio to appear in a particularly order. Take a look at using MDX in calculated dimension members. There are MDX gurus in the Prophix team that should be able to point you in the direction for the MDX that you need. I’ve done a few, but I fall into the category of ‘a little knowledge to be dangerous’ with MDX, so I’ll let you get the right advice from the gurus.
I haven’t done this, but if you want to have sorting/filtering capabilities similar to Excel, I wonder if you could do this with the O365 add-on for Excel? Someone with O365 Prophix add-on experience might chirp-in with suggestions (or out-right not possible).
When it comes down to a feature that you think should be in Prophix, but isn’t, make sure you log it on the feedback forum.
5 Likes
Thanks for sharing. I’m installing Prophix for Excel to accomplish this goal. Will provide a follow up once completed.
1 Like
Update: I created a MDX to sort the data.
ORDER(Descendants([Division].Default.[ALL],LEAVES), ([Account].Default.&[KS312],[Time].Default.&[2020M02]), BASC)
3 Likes
This thread has been very helpful to me. I have a new scenario that I would like to run by you all if you don’t mind. I have a data entry template with my PARTS dimension in the Rows and ACCOUNTS in the Columns. I am trying to list all of the Leaf Parts in Alpha order by their KEY. I tried using an Alternate Hierarchy which yes did display the parts as I wanted them but I am sure you realized already I cannot use it in a data entry template. I am now exploring the option of using a Part name set with a dynamic MDX formula. Currently I have the following but it is not sorting them correctly: ORDER(DESCENDANTS([Part].Default.[All],LEAVES),([PART].[DEFAULT].[KEY]), BASC) I am curious if someone sees anything I can change in my formula or knows another way to accomplish what I am trying to do. Thank you so much in advance!
Very much an un-tested suggestion, but you could try removing the parentheses around ([PART].[DEFAULT].[KEY]). With the parentheses, you’re specifying to sort by a tuple at the intersection of [PART].[DEFAULT].[KEY] and the default member of all other dimensions, which is going to be unpredictable.
If you remove the parentheses, I think it will sort by the value of the key itself.
1 Like
My updated MDX expression but still didn’t sort like I am hoping:
ORDER(DESCENDANTS([Part].Default.[All],LEAVES),[PART].[DEFAULT].[KEY], BASC)
Thanks for the suggestion Murray
Hi @monte.southwell, since this is MDX-related, it’s best to speak with a Prophix support rep for assistance.
Can you please create an account within our support portal using this link:
Support Portal
Once your account is created, then you can submit a ticket.
Or once you create your account, let me know and I can create that ticket for you to speed up the process.
I attempted to create a support portal account a week ago but still have not heard anything about my account.
@monte.southwell I’m looking into this now - it’s very strange that your account hasn’t been created.
Once it’s set up I’ll submit that ticket for you and email you separately with next steps.
Thanks for you patience!
Thank you Sydney your the best!
1 Like
Hi @monte.southwell, pls try this instead
ORDER(Descendants([Part].Default.[All],LEAVES), [Part].[Default].CurrentMember.Member_Key, BASC)
Fingers crossed, this should work. Please do share how it goes.
Cheers,
Navin
1 Like
This worked beautifully…I did have to make one VERY slight modification to the MDX formula but I can’t tell you why:
ORDER(DESCENDANTS([PART].DEFAULT.[ALL], LEAVES),[PART].[DEFAULT].CurrentMember.Member_Key,BASC)
I needed 2 commas before “LEAVES” to get the syntax to pass.
BUT…the key is Navin is that it worked!
This is a big win in my book!
Thanks to Navin, Murray, and Sydney!
2 Likes
@monte.southwell, that’s great to hear, Monte. A win for you is a win for us all.
Cheers,
Navin
1 Like
Hi @monte.southwell, to this extent, I’m going ahead and closing the ticket given that we have resolved your issue. Should have any other questions or concerns, please feel free to log a new ticket.
Thank you,
Navin
I appreciate it Navin.
1 Like