This has been on our wish list since XCat 2.1.
There is a way to get around this limitation if you really need to. The biggest problem with what I am about to describe is that it requires adding additional fields to the taxonomy and it really only works if you have a small number of fields you want to sort on.
Here was the challenge we faced: We present our data on a website and needed users to be able to sort on both list price and manufacturer name. If you analyze this requirement you'll see they result in 8 possible sorts.
1. List Price Low to High, then MFG A-Z
2. List Price Low to High, then MFG Z-A
3. List Price High to Low, then MFG A-Z
4. List Price High to Low, then MFG Z-A
5. MFG A-Z, then List Price Low to High
6. MFG A-Z, then List Price Low to High
7. MFG Z-A, then List Price High to Low
8. MFG Z-A, then List Price High to Low
The way to run these compound sorts in MDM is to figure out which fields you want to sort on and then calculate a value for each field you want to sort on, append those values together and store them in the products table. If you used the right algorithm to calculate the values for each field you can then use that new field as your sort field.
We found that the easiest way calculate useable sort fields were to convert each field to a fixed width numeric string. Let's use sort #1, List Price Low to High, then MFG A-Z as an example. We converted all prices to strings 8 characters wide. So, $1,099.45 became 00109945. For the manufacture names we sorted each manufacturer name alphabetically and then created a padded index number for each entry. (So if there were 150 manufacturers, the first mfg would be given a value of 001, the second 002, the tenth 010 and so on.)
We then calculated a padded version of the internal ID# of each product.
Then we appended the price string, the mfg string and the internal ID string together and stored them in a field on the products table. This resulted in unique string for each product in the database we could then use as a sort field whenever we wanted to run sort #1. Of course, if you look closely you'll see that we could also use the same field for sort #4 by sorting in descending order instead of ascending order.
We likewise calculated three additional keys to handle the other sorts. Just as sorts #1 and 4 use the same key by changing the a2i sort order from ascending to descending the key for sort #2 works for #3, #5 works with #8 and #6 works with #7.
Some of you are probably going to ask why we calculated the padded version of the product's internal id#. We discovered that if you don't sort results on a field with unique values in it, then records that have the same value for the sort field are not returned in the same order across multiple result sets. I am not sure if that behavior still exists in MDM or not so you may be able to skip that step.
We actually break the rules when we calculate these sort keys. We take the catalog offline on regular basis and then use a T-SQL statement to calculate the values. SAP doesn't recommend that you make changes directly to the db and will not provide support to help you with this. I would therefore recommend using the API to do these calculations. (Maybe there is easier way to do this by syndicating the data to an xml file, using an xslt transform to calculate the values and then using a batch import to populate the catalog with the sort keys.)
Yes, this is a bit complicated for something the software should support natively however it has worked for us for 4 years.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.