How To Sort Dashboard Parameters AlphaNumerically Without Specifying Sort Order - Example Code
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
