Filtering

Filtering a Result Set

The Filtering Usage Guide is built upon the SQL Standards and Practices Guide, demonstrating how to filter a result set based on a selection.

Workspace

The complete Workspace to demonstrate filtering is displayed in the next image. The Filter_C assembly is for C# and Filter_VB is for VB.Net.

Image 13 from Filtering Usage Guide

The next image displays the Dashboard filtered for the selection, TestUser01, which will be explained as this document continues.

Image 14 from Filtering Usage Guide

The Combo Box, which displays TestUser01 in the preceding image, is the result of a bound parameter, paramAuditUserFromAuditMember, and a Method Query.

Image 15 from Filtering Usage Guide

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.CSharp;
using OneStream.Finance.Database;
using OneStream.Finance.Engine;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using OneStream.Stage.Database;
using OneStream.Stage.Engine;
using OneStreamWorkspacesApi;
using OneStreamWorkspacesApi.V800;

namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
{
	public class AuditUserFromAuditMember
	{
		public static DataTable getAuditUserFromAuditMember(SessionInfo si, DashboardDataSetArgs args)
		{
			try
			{
				StringBuilder sql = new StringBuilder();
				DataTable dt = new DataTable();

				sql.AppendLine("SELECT [AuditUser] FROM [AuditMember] GROUP BY [AuditUser] ORDER BY [AuditUser]");

				List<DbParamInfo> parameters = new List<DbParamInfo>()
				{
				};

				using (DbConnInfo database = BRApi.Database.CreateApplicationDbConnInfo(si))
				{
						dt = BRApi.Database.ExecuteSql(database,sql.ToString(),parameters, false);
				}
                return dt;
            }
            catch (Exception ex)
            {
                throw ErrorHandler.LogWrite(si, new XFException(si, ex));
            }
		}
	}
}

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Text
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports OneStreamWorkspacesApi
Imports OneStreamWorkspacesApi.V800

Namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
	Public Class AuditUserFromAuditMember

        Public Function getAuditUserFromAuditMember(si As SessionInfo, args As DashboardDataSetArgs) As DataTable
            Try
				Dim sql As New StringBuilder()
				Dim dt As New DataTable()

				sql.AppendLine("SELECT [AuditUser] FROM [AuditMember] GROUP BY [AuditUser] ORDER BY [AuditUser]")

				Dim parametersInfo As New List(Of DbParamInfo)

				Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
					dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, parametersInfo, False)
				End Using

				Return dt

            Catch ex As Exception
                Throw New XFException(si, ex)
			End Try
        End Function

	End Class
End Namespace

Based on the selected parameter, which serves as the criteria for the where clause in the getAuditMember method/function; this method returns the result set for the Data Adapter, daAuditMember, which is then displayed in the Grid View, grdAuditMember. The following image showcases the Data Adapter, followed by the corresponding C# and VB.Net code.

Image 18 from Filtering Usage Guide

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.CSharp;
using OneStream.Finance.Database;
using OneStream.Finance.Engine;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using OneStream.Stage.Database;
using OneStream.Stage.Engine;
using OneStreamWorkspacesApi;
using OneStreamWorkspacesApi.V800;

namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
{
	public class AuditMember
	{
		public static DataTable getAuditMember(SessionInfo si, DashboardDataSetArgs args)
		{
			try
			{
				StringBuilder sql = new StringBuilder();
				DataTable dt = new DataTable();

				string strAuditUser = args.CustomSubstVars.GetValueOrEmpty("paramAuditUserFromAuditMember");

				sql.AppendLine("SELECT ");
				sql.AppendLine("[AuditUser] ");
				sql.AppendLine("[AuditTime], ");
				sql.AppendLine("[AuditInsUpdateDel], ");
				sql.AppendLine("[Name], ");
				sql.AppendLine("[Description] ");
				sql.AppendLine("FROM ");
				sql.AppendLine("[AuditMember] ");
				sql.AppendLine("WHERE ");
				sql.AppendLine("[AuditUser] = @AuditUser ");

				List<DbParamInfo> parameters = new List<DbParamInfo>()
				{
					new DbParamInfo("@AuditUser", strAuditUser),
				};

				if (!String.IsNullOrEmpty(strAuditUser))
				{
					using (DbConnInfo database = BRApi.Database.CreateApplicationDbConnInfo(si))
					{
						dt = BRApi.Database.ExecuteSql(database,sql.ToString(),parameters, false);
						return dt;
					}
				}

				return dt;

            }
            catch (Exception ex)
            {
                throw ErrorHandler.LogWrite(si, new XFException(si, ex));
            }
		}
	}
}

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Text
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports OneStreamWorkspacesApi
Imports OneStreamWorkspacesApi.V800

Namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
	Public Class AuditMember
        Public Function getAuditMember(si As SessionInfo, args As DashboardDataSetArgs) As DataTable
            Try
				Dim sql As New StringBuilder()
				Dim dt As New DataTable()

				Dim strAuditUser As String
				strAuditUser = args.CustomSubstVars.GetValueOrDefault("paramAuditUserFromAuditMember")

				sql.AppendLine("SELECT ")
				sql.AppendLine("[AuditUser] ")
				sql.AppendLine("[AuditTime], ")
				sql.AppendLine("[AuditInsUpdateDel], ")
				sql.AppendLine("[Name], ")
				sql.AppendLine("[Description] ")
				sql.AppendLine("FROM ")
				sql.AppendLine("[AuditMember] ")
				sql.AppendLine("WHERE ")
				sql.AppendLine("[AuditUser] = @AuditUser ")

				Dim parametersInfo As New List(Of DbParamInfo) From {
					New DbParamInfo("@AuditUser", strAuditUser)
				}

				If Not String.IsNullOrEmpty(strAuditUser) Then
					Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
						dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, parametersInfo, False)
					End Using

					Return dt
				End If

                	Return dt

            Catch ex As Exception
                Throw New XFException(si, ex)
			End Try
        End Function
	End Class
End Namespace

C# Implementation

For the Dashboard and supporting Workspace artifacts to leverage the C# code, the Dashboard Maintenance Unit, Workspace Assembly Service is configured to Filter_C.ServiceFactory.

Image 21 from Filtering Usage Guide

With this configuration, the Service Factory assembly file is referenced, which returns WsasDataSet. A partial code snippet of the Service Factory assembly file is below.

namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
{
	public class ServiceFactory : IWsAssemblyServiceFactory
	{
        public IWsAssemblyServiceBase CreateWsAssemblyServiceInstance(SessionInfo si, BRGlobals brGlobals,
            DashboardWorkspace workspace, WsAssemblyServiceType wsAssemblyServiceType, string itemName)
        {
            try
            {
                switch (wsAssemblyServiceType)
                {
                    //case WsAssemblyServiceType.Component:
                    //    return new WsasComponent();

                    //case WsAssemblyServiceType.Dashboard:
                    //    return new WsasDashboard();

                    //case WsAssemblyServiceType.DataManagementStep:
                    //    return new WsasDataManagementStep();
                    case WsAssemblyServiceType.DataSet:
                        return new WsasDataSet();
                    //case WsAssemblyServiceType.DynamicDashboards:
                    //    return new WsasDynamicDashboards();

                    //case WsAssemblyServiceType.DynamicCubeView:
                    //    return new WsasDynamicCubeView();

The WsasDataSet assembly file is what references the assembly file to return the AuditMember (getAuditMember) ,i.e., the full result set based on the user selected (getAuditUserFromAuditMember).

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using Microsoft.CSharp;
using OneStream.Finance.Database;
using OneStream.Finance.Engine;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using OneStream.Stage.Database;
using OneStream.Stage.Engine;
using OneStreamWorkspacesApi;
using OneStreamWorkspacesApi.V800;

namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
{
	public class WsasDataSet : IWsasDataSetV800
	{
        public object GetDataSet(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardDataSetArgs args)
        {
            try
            {
                 if ((brGlobals != null) && (workspace != null) && (args != null))
				 {
					if (args.DataSetName.XFEqualsIgnoreCase("getAuditUserFromAuditMember"))
					{
						DataTable dt = new DataTable();
						dt = AuditUserFromAuditMember.getAuditUserFromAuditMember(si, args);
						return dt;
					}
	                if (args.DataSetName.XFEqualsIgnoreCase("getAuditMember"))
					{
						DataTable dt = new DataTable();
						dt = AuditMember.getAuditMember(si, args);
						return dt;
					}
				 }

				return null;
            }
            catch (Exception ex)
            {
                throw new XFException(si, ex);
            }
        }
	}
}

VB.Net Implementation

For the Dashboard and supporting Workspace artifacts to leverage the VB.net code, the Dashboard Maintenance Unit, Workspace Assembly Service is configured to Filter_VB.ServiceFactory.

With this configuration, the Service Factory assembly file is referenced, which returns WsasDataSet. A partial code snippet of the Service Factory assembly file is below.

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports OneStreamWorkspacesApi
Imports OneStreamWorkspacesApi.V800

Namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
	Public Class ServiceFactory
		Implements IWsAssemblyServiceFactory

        Public Function CreateWsAssemblyServiceInstance(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal workspace As DashboardWorkspace, ByVal wsasType As WsAssemblyServiceType, ByVal itemName As String) As IWsAssemblyServiceBase Implements IWsAssemblyServiceFactory.CreateWsAssemblyServiceInstance
            Try
                Select Case wsasType
                    Case Is = WsAssemblyServiceType.Component
                        'Return New WsasComponent()

                    Case Is = WsAssemblyServiceType.Dashboard
                        'Return New WsasDashboard()

                    Case Is = WsAssemblyServiceType.DataManagementStep
                        'Return New WsasDataManagementStep()
// highlight-start
                    Case Is = WsAssemblyServiceType.DataSet
						Return New WsasDataSet()
// highlight-end
                    Case Is = WsAssemblyServiceType.DynamicDashboards
                    	'Return New WsasDynamicDashboards()

                    Case Is = WsAssemblyServiceType.DynamicCubeView
                    	'Return New WsasDynamicCubeView()

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports OneStreamWorkspacesApi
Imports OneStreamWorkspacesApi.V800

Namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
	Public Class WsasDataSet
		Implements IWsasDataSetV800

        Public Function GetDataSet(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal workspace As DashboardWorkspace, _
			ByVal args As DashboardDataSetArgs) As Object Implements IWsasDataSetV800.GetDataSet
            Try
                If (globals IsNot Nothing) AndAlso (workspace IsNot Nothing) AndAlso (args IsNot Nothing) Then
                    // highlight-next-line
					If args.DataSetName.XFEqualsIgnoreCase("getAuditUserFromAuditMember") Then
						Dim instanceAuditUserFromAuditMember As New AuditUserFromAuditMember()
						Dim dt As New DataTable()
                        // highlight-next-line
						dt = instanceAuditUserFromAuditMember.getAuditUserFromAuditMember(si, args)
						Return dt
					End If
                    // highlight-next-line
					If args.DataSetName.XFEqualsIgnoreCase("getAuditMember") Then
						Dim instanceAuditMember As New AuditMember()
						Dim dt As New DataTable()
                        // highlight-next-line
						dt = instanceAuditMember.getAuditMember(si, args)
						Return dt
					End If
	           	End If

                Return Nothing
            Catch ex As Exception
                Throw New XFException(si, ex)
			End Try
        End Function

	End Class
End Namespace

Filtering a Result Set​

Workspace​

C# Implementation​

VB.Net Implementation​

Filtering a Result Set

Workspace

C# Implementation

VB.Net Implementation