none
Master Data Services : Display entity members with good sort order when code is numeric

    General discussion

  • a MDS user recently asked me this question: " When looking at entity contents via web UI in the column code, 10 and each code that starts with 1 (10,11..19) always goes before 2 and so on. Is there any way to fix it? I don’t believe that’s the right order."

    ( ping back from my blog's article : http://averbouch.biz/?p=330 )

     

    example:

      so I've started to investigate..

    first (easy solution)

    the easiest way is to add at least one zero before code "1" and code  "2" and so on but if your code size is huge, you will have to add a lot of "0" behind your code so I've tried to find a custom hack in MDS stored procedure: and finally I've found it :)

    second (hard way) solution:

    after a few search on google, I've found this post : http://stackoverflow.com/questions/119730/how-do-i-sort-a-varchar-column-in-sql-server-that-contains-numbers but I had to find where to place this code hack after a few search on mds (by sorting entity members in Web UI and looking at SQL profiler) i've found that a call was made to this stored procedure:  

     

    declare @p16 int
    set @p16=NULL
    declare @p17 mdm.MemberGetCriteria
    <pre class="brush:sql">exec mdm.udpEntityMembersGet @User_ID=1,@Version_ID=5,@Hierarchy_ID=NULL,@HierarchyType_ID=NULL,@ParentEntity_ID=NULL,@Entity_ID=336,@MemberType_ID=1,@ParentCode=default,@ColumnString=N'',@AttributeGroup_ID=NULL,@PageNumber=1,@PageSize=50,@SortColumn=N'Code',@SortDirection=N'ASC',@CountOnly=0,@MemberCount=@p16 output,@SearchTable=@p17
    select  @p16declare @p16 int
    set @p16=NULL
    declare @p17 mdm.MemberGetCriteria
    
    exec mdm.udpEntityMembersGet @User_ID=1,@Version_ID=5,@Hierarchy_ID=NULL,@HierarchyType_ID=NULL,@ParentEntity_ID=NULL,@Entity_ID=336,@MemberType_ID=1,@ParentCode=default,@ColumnString=N'',@AttributeGroup_ID=NULL,@PageNumber=1,@PageSize=50,@SortColumn=N'Code',@SortDirection=N'ASC',@CountOnly=0,@MemberCount=@p16 output,@SearchTable=@p17
    select  @p16


     

    after looking into udpEntityMembersGet, I've found that another stored procedure was called : mdm.udpMembersGet, a huge one! so I've tried to update this SP code and finally it works! be carefull, this hack could slow down the entitymembers get request, and it is only for testing purpose, and if you update MDS with cumulative update, it could be erased! you just have to replace every instance of (there should be 3 of them) :  

     

    IF (@SortOrderColumnQuoted IS NOT NULL) 
    BEGIN
    SET @SQL += @SortOrderColumnQuoted + N' ' + @SortDirection + N', ';
      				
    END

    by:

     

     

     


     

     

     IF (@SortOrderColumnQuoted IS NOT NULL) BEGIN  
                    SET @SQL += N' CASE 
        WHEN ISNUMERIC('+@SortOrderColumnQuoted+') = 1 THEN CONVERT(INT, '+@SortOrderColumnQuoted+') 
        ELSE 9999999 -- or something huge
      END ' + N' ' + @SortDirection + N', ';  
                END 


     

     result: 
      


     

    Xavier Averbouch
    Microsoft Community Contributor
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

    Sunday, October 30, 2011 2:22 PM