How To Sort Dashboard Parameters AlphaNumerically Without Specifying Sort Order - Example Code

[RobbSalzmann]

In developing applications, I prefer to group objects intuitively using standardized naming, alphanumeric sorting, and hierarchical grouping.  Dashboard parameters use the "Sort Order" property to sort, which for me adds unwanted cognitive load to a development process.

I developed the code below to automatically sort and group parameters the way you would expect them to be sorted to get around the need to specify Sort Order.

Implement as an extensibility rule and run it from the editor.  After the rule is run, copy, paste an existing parameter, then delete the pasted parameter to force a refresh of the list from the server.  Be sure to remove this code from your production applications.

C#

using System;
using System.Data;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Wcf;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Collections.Generic;

namespace OneStream.BusinessRule.Extender.MSA_ParamSorter
{
    //------------------------------------------------------------------------------------------------------------
    // Reference Code: MSA Parameter Sorter [MSA_ParamSorter]
    //
    // Description: This class sorts the Dashboard Parameters for a given Workspace and Maintenance Unit 
    // The sort is standard lexical (Lexigraphical, Dictionary) order
    // After running the rule, refresh the application and the workspace.
    //
    // Note: This will change all sortorder settings on your parameters for the given Maintenance Unit
    // (most people want this)
    //
    // Usage: Update the Workspace and Maintenance Unit strings then run from the BR Editor.
    //
    // Created By: Robb Salzmann, Mindstream Analytics
    //
    // Date Created: 6-7-2023
    //------------------------------------------------------------------------------------------------------------
    public class MainClass
    {
        public object Main(SessionInfo si, BRGlobals globals, object api, ExtenderArgs args)
        {
            try
            {
                var paramSort = new ParamSort();
                paramSort.SortParams(si, args);
            }
            catch (Exception ex)
            {
                throw new XFException($" {ex.Message}", ex);
            }

            return null;
        }
    }

    // Replace the string assignments for strWorkspace and strMaintUnit as appropriate
    public class ParamSort
    {
        private string strWorkspace = "<YourWorkspaceName>"; // Workspace containing the Maintenance Unit below
        private string strMaintUnit = "<YourMaintenanceUnitName>"; // Maintenance Unit where the Parameters will be sorted

        public void SortParams(SessionInfo si, ExtenderArgs args)
        {
            DataTable results;
            string strSql = null;
            int intSortOrder = 10;

            try
            {
                Guid wsGuid = BRApi.Dashboards.Workspaces.GetWorkspaceIDFromName(si, false, strWorkspace);
                Guid muGuid = BRApi.Dashboards.MaintUnits.GetMaintUnit(si, false, wsGuid, strMaintUnit).UniqueID;
                strSql = $@"SELECT dp.name as name
                            FROM DashboardMaintUnit dmu, DashboardParameter dp
                            WHERE dmu.UniqueID = '{muGuid}'
                            And dp.MaintUnitID = dmu.UniqueID";

                using (DbConnInfo dbConn = BRApi.Database.CreateApplicationDbConnInfo(si))
                {
                    results = BRApi.Database.ExecuteSql(dbConn, strSql, true);
                    List<DataRow> parameters = results.AsEnumerable().ToList();

                    // Sorting the parameters based on the custom logic
                    var comparer = new AlphaNumericComparator();
                    var sortedParameters = parameters
                        .OrderBy(row => row.Field<string>("name"), comparer)
                        .ToList();

                    // Updating the sort order in the database
                    foreach (var row in sortedParameters)
                    {
                        strSql = $"UPDATE DashboardParameter SET SortOrder={intSortOrder} WHERE name='{row["name"]}'";
                        BRApi.Database.ExecuteSql(dbConn, strSql, true);
                        intSortOrder += 10;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new XFException($"{Environment.NewLine}{this.GetType().ToString()}.{System.Reflection.MethodBase.GetCurrentMethod().Name}(): {ex.Message}:{Environment.NewLine}{strSql}", ex);
            }
        }
    }

    public class AlphaNumericComparator : IComparer<string>
    {
        public int Compare(string x, string y)
        {
            if (x == y) return 0;

            string[] xParts = Regex.Split(x, @"(\d+)");
            string[] yParts = Regex.Split(y, @"(\d+)");

            for (int i = 0; i < Math.Min(xParts.Length, yParts.Length); i++)
            {
                if (xParts[i] != yParts[i])
                {
                    return PartCompare(xParts[i], yParts[i]);
                }
            }

            return xParts.Length - yParts.Length;
        }

        private static int PartCompare(string x, string y)
        {
            if (int.TryParse(x, out int a) && int.TryParse(y, out int b))
            {
                return a.CompareTo(b);
            }

            return string.Compare(x, y, StringComparison.OrdinalIgnoreCase);
        }
    }
}

VB

Imports System.Data
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Wcf
Imports System.Threading.tasks
Imports System.Text.RegularExpressions

Namespace OneStream.BusinessRule.Extender.MSA_ParamSorter
	'------------------------------------------------------------------------------------------------------------
	'Reference Code: 	MSA Parameter Sorter [MSA_ParamSorter]
	'
	'Description:		This class sorts the Dashboard Parameters for a given Workspace and Maintenance Unit 
	'					The sort is standard lexical (Lexigraphical, Dictionary) order
	'                   After running the rule, refresh the application and the workspace.
	'
	'Note:              This will change all sortorder settings on your parameters for the given Maintenance Unit
	'                   (most people want this)
	'
	'Usage:				Update the Workspace and Maintenance Unit strings then run from the BR Editor.
	'
	'Created By:		Robb Salzmann, Mindstream Analytics
	'
	'Date Created:		6-7-2023
	'------------------------------------------------------------------------------------------------------------				
	Public Class MainClass
	    Public Function Main(si As SessionInfo, globals As BRGlobals, api As Object, args As ExtenderArgs) As Object
			Try
				Dim paramSort As New ParamSort()
				paramSort.SortParams(si, args)
	        Catch ex As Exception
	            Throw New XFException($" {ex.Message}", ex)
	        End Try		
			
			Return Nothing
	 	End Function	
	End Class

	' Replace the string assignments for strWorkspace and strMaintUnit as appropriate
	Public Class ParamSort
	    Private strWorkspace As String = "<YourWorkspaceName>" 'Workspace containing the Maintenance Unit below
	    Private strMaintUnit As String = "<YourMaintenanceUnitName>" 'Maintenance Unit where the Parameters will be sorted
	
	    Public Sub SortParams(si As SessionInfo, args As ExtenderArgs)
	        Dim results As DataTable
	        Dim strSql As String = Nothing
	        Dim intSortOrder As Int32 = 10
	
	        Try
	            Dim wsGuid As Guid = BRApi.Dashboards.Workspaces.GetWorkspaceIDFromName(si, False, strWorkspace)
	            Dim muGuid As Guid = BRApi.Dashboards.MaintUnits.GetMaintUnit(si, False, wsGuid, strMaintUnit).UniqueID
	            strSql = $"SELECT dp.name as name
	                       FROM DashboardMaintUnit dmu, DashboardParameter dp
	                       WHERE dmu.UniqueID = '{muGuid}'
	                       And dp.MaintUnitID = dmu.UniqueID"
	            Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
	                results = BRApi.Database.ExecuteSql(dbConn, strSql, True)
	                Dim parameters As List(Of DataRow) = results.AsEnumerable().ToList()
	
	                ' Sorting the parameters based on the custom logic
	                Dim comparer As New AlphaNumericComparator()
	                Dim sortedParameters = parameters _
	                    .OrderBy(Function(row) row.Field(Of String)("name"), comparer) _
	                    .ToList()
	
	                ' Updating the sort order in the database
					Dim s As New List(Of String)
	                For Each row In sortedParameters
	                    strSql = $"UPDATE DashboardParameter SET SortOrder={intSortOrder} WHERE name='{row("name")}'"
	                    BRApi.Database.ExecuteSql(dbConn, strSql, True)
	                    intSortOrder += 10
	                Next
	            End Using
	        Catch ex As Exception
	            Throw New XFException($"{Environment.NewLine}{Me.GetType().ToString()}.{System.Reflection.MethodBase.GetCurrentMethod().Name}(): {ex.Message}:{Environment.NewLine}{strSql}", ex)
	        End Try
	    End Sub
	End Class

	Public Class AlphaNumericComparator
	    Implements IComparer(Of String)
	
	    Public Function Compare(x As String, y As String) As Integer Implements IComparer(Of String).Compare
	        If x = y Then Return 0
	
	        Dim xParts As String() = Regex.Split(x, "(\d+)")
	        Dim yParts As String() = Regex.Split(y, "(\d+)")
	
	        For i As Integer = 0 To Math.Min(xParts.Length, yParts.Length) - 1
	            If xParts(i) <> yParts(i) Then
	                Return PartCompare(xParts(i), yParts(i))
	            End If
	        Next
	
	        Return xParts.Length - yParts.Length
	    End Function
	
	    Private Shared Function PartCompare(x As String, y As String) As Integer
	        Dim a, b As Integer
	        Dim isNumericX As Boolean = Integer.TryParse(x, a)
	        Dim isNumericY As Boolean = Integer.TryParse(y, b)
	
	        If isNumericX AndAlso isNumericY Then Return a.CompareTo(b)
	        Return String.Compare(x, y, StringComparison.OrdinalIgnoreCase)
	    End Function
	End Class
End Namespace

[CraigVCPorter]

Very neat - thank you for sharing

[Sergey]

[MarcusH]

Works a treat - thanks Robb.

[EricOsmanski]

This has been fixed in v8.1.

[RobbSalzmann]

@EricOsmanski The sorting happens now when using sort order -1, but the sorting itself is incorrect.  10 and 11 do not come before 2, and 20 does not come before 3. 

The code above will sort this correctly:
Default OS sorting before running ParamSorter (v8.1):

After running ParamSorter:

[EricOsmanski]

Thanks Robb, good clarification - it is alphanumeric, not natural.

[RobbSalzmann]

@EricOsmanski Alphanumeric and Natural sorting are essentially the same and given by the code example above.  OneStream is using Lexical sorting.  While not wanting to split hairs here, the distinction is important.  Humans are consuming and interacting with the UI.  IMO its important the UI is a humanistic experience. 

Computers generally use Lexical sorting by default.  This is where strings are compared character by character from left to right, using the ASCII values of the characters. Lexical sorting is efficient and fine when code needs to keep sets of elements sorted the same way for comparison and parsing. 

The problem arises from comparing strings with numbers as a collection of  ascii characters instead of as a number comprised of those parts  The code provided in the AlphaNumericComparator class in this post overcomes this limitation of lexical sorting described here:

List of string to sort:
"item10"
"item1"
"item20"
"item2"
"item3"

Lexical Character-by-Character Comparison:
"i" in all strings is the same.
"t" in all strings is the same.
"e" in all strings is the same.
"m" in all strings is the same.
The comparison starts to differ at the first digit.

Comparing Digits:
"1" in "item10" vs. "1" in "item1" – They are the same, so the next character is compared.
"0" in "item10" vs. "1" in "item1" – "0" comes before "1" in ASCII, so "item10" is sorted before "item1".
Next, "item1" is compared with "item20". "1" (from "item1") comes before "2" (from "item20") in ASCII, so "item1" is sorted before "item20".
Similarly, "item2" comes before "item3".

[wnindl]

Best extension of your OneStream toolkit. Who has not gone nuts trying to scroll thru find a parameter. And yes, we can search. Thank you for sharing. And even in both languages.