Solved

Combining/Joining DataSets

Forum|Forum|1 year ago
April 14, 2025
7 replies
11 views

Hi Everyone.

I am unable to join datasets and suing below code.

Purpose: is to combine the outcome of 2 method queries WF Status and CertificationStatus to get a single table.

Kindly help with your comments or guidance:

Imports System

Imports System.Collections.Generic

Imports System.Data

Imports System.Data.Common

Imports System.Globalization

Imports System.IO

Imports System.Linq

Imports System.Windows.Forms

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

Namespace OneStream.BusinessRule.DashboardDataSet.GetWFStatusandCertification

Public Class MainClass

Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object

Try

Select Case args.FunctionType

Case Is = DashboardDataSetFunctionType.GetDataSetNames

Dim DSnames As New List(Of String)()

DSnames.Add("GetWFStatusandCertificationF")

Return DSnames

Case Is = DashboardDataSetFunctionType.GetDataSet

If args.DataSetName.XFEqualsIgnoreCase("GetWFStatusandCertificationF") Then

Dim WFTable As DataTable = New DataTable("WFSTATUS")

Dim CertTable As DataTable = New DataTable("WFCertSTATUS")

Dim CertTable1 As DataTable = New DataTable("WFCertSTATUS1")

Dim CertTable2 As DataTable = New DataTable("WFCertSTATUS2")

Dim WFTime As String = "2023M12" 'args.NameValuePairs("2023M12")

Dim WFScenario As String = "Actuals" 'args.NameValuePairs("Actuals")

Dim WFName As String = "P1000 Group" 'args.NameValuePairs("P1000 Prudential Group")

Dim methodTypeId As String

Dim methodQuery As String

Dim resultDataTableName As String

Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)

'Create Tables in Memory

WFTable.Columns.Add("ProfileKey")

WFTable.Columns.Add("ProfileName")

WFTable.Columns.Add("ScenarioName")

WFTable.Columns.Add("TimeName")

WFTable.Columns.Add("StatusText")

WFTable.Columns.Add("LastExecutedStepStatus")

WFTable.Columns.Add("LastExecutedStepTimeUTC")

WFTable.Columns.Add("LastExecutedStepTimeEST")

methodTypeId = XFCommandMethodTypeId.WorkflowStatus

methodQuery = "{" & WFName &"}{" & WFScenario & "}{" & WFTime & "}{AllProfiles}{Descendants}{}"

resultDataTableName = "WFSTATUS"

Dim customSubVars As New Dictionary(Of String, String)

Dim objDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(dbConnApp,methodTypeId,methodQuery, resultDataTableName,customSubVars)

For Each Row As DataRow In objDataSet.Tables("WFSTATUS").Rows

Dim easternZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time")

Dim EST = TimeZoneInfo.ConvertTimeFromUtc(Row.Item("LastExecutedStepTime"), easternZone)

WFTable.Rows.Add(Row.Item("ProfileKey"),Row.Item("ProfileName"),Row.Item("ScenarioName"),Row.Item("TimeName"),Row.Item("StatusText"),Row.Item("LastExecutedStepStatus"),Row.Item("LastExecutedStepTime"),EST)

'Return WFTable

End Using

Using DBConAppForCert1 As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)

CertTable1.Columns.Add("ProfileName")

CertTable1.Columns.Add("ProfileKey")

CertTable1.Columns.Add("ScenarioKey")

CertTable1.Columns.Add("ScenarioName")

CertTable1.Columns.Add("TimeKey")

CertTable1.Columns.Add("TimeName")

methodTypeId = XFCommandMethodTypeId.CertificationForWorkflowUnit

MethodQuery = "{" & WFName & "}" & "{" & WFScenario & "}" & "{" & WFTime & "}" &"{True}" &"{}"

resultDataTableName = "WFCertSTATUS1"

Dim CustomSubVars As New Dictionary(Of String, String)

Dim ObjDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(DBConAppForCert1,methodtypeid,methodQuery,resultDataTableName,CustomSubVars)

For Each Row As DataRow In ObjDataSet.Tables("WFCertSTATUS1").Rows

CertTable1.Rows.Add(Row.Item("ProfileName"),Row.Item("ProfileKey"),Row.Item("ScenarioKey"),Row.Item("ScenarioName"),Row.Item("TimeKey"),Row.Item("TimeName"))

' Return CertTable1

End Using

Using DBConAppForCert2 As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)

CertTable2.Columns.Add("ProfileKey")

CertTable2.Columns.Add("ScenarioKey")

CertTable2.Columns.Add("TimeKey")

CertTable2.Columns.Add("SignOffState")

CertTable2.Columns.Add("UserName")

CertTable2.Columns.Add("TimeStamp")

methodTypeId = XFCommandMethodTypeId.CertificationForWorkflowUnit

MethodQuery = "{" & WFName & "}" & "{" & WFScenario & "}" & "{" & WFTime & "}" &"{True}" &"{}"

resultDataTableName = "WFCertSTATUS2"

Dim CustomSubVars As New Dictionary(Of String, String)

Dim ObjDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(DBConAppForCert2,methodTypeId,MethodQuery,resultDataTableName,CustomSubVars)

For Each Row As DataRow In ObjDataSet.Tables("WFCertSTATUS2_SignOffGroups").Rows

CertTable2.Rows.Add(Row.Item("ProfileKey"),Row.Item("ScenarioKey"),Row.Item("TimeKey"),Row.Item("SignOffState"),Row.Item("UserName"),Row.Item("TimeStamp"))

' Return CertTable2

End Using

' Return WFTable

Dim DS_CertStatus As New DataSet("DS_CertificationStatus")

DS_CertStatus.Tables.Add(WFTable)

' DS_CertStatus.Tables.Add(CertTable1)

DS_CertStatus.Tables.Add(CertTable2)

DS_CertStatus.Relations.Add("ProfileKeyRelation",DS_CertStatus.Tables(0).Columns("ProfileKey"),DS_CertStatus.Tables(1).Columns("ProfileKey"),False)

Return DS_CertStatus

End If

End Select

Return Nothing

Catch ex As Exception

Throw ErrorHandler.LogWrite(si, New XFException(si, ex))

End Try

End Function

End Class

End Namespace

dashboards

Best answer by RobbSalzmann

sry about that, here's an update, Monday morning, I misread your initial code...
Here's how you can combine the results, using what you had:

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
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

Namespace OneStream.BusinessRule.DashboardDataSet.GetWFStatusandCertification
    Public Class MainClass
        Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object
            Try
                Select Case args.FunctionType

                    Case Is = DashboardDataSetFunctionType.GetDataSetNames
                        Dim DSnames As New List(Of String)()
                        DSnames.Add("GetWFStatusandCertificationF")
                        Return DSnames

                    Case Is = DashboardDataSetFunctionType.GetDataSet
                        If args.DataSetName.XFEqualsIgnoreCase("GetWFStatusandCertificationF") Then
                            Return GetWFStatusandCertificationF(si, args)
                        End If

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

        Private Function GetWFStatusandCertificationF(ByVal si As SessionInfo, ByVal args As DashboardDataSetArgs) As DataTable
            Dim WFTable As New DataTable("WFSTATUS")
            Dim CertTable2 As New DataTable("WFCertSTATUS2")
            Dim CombinedTable As New DataTable("CombinedWFandCertStatus")

            CombinedTable.Columns.Add("ProfileKey")
            CombinedTable.Columns.Add("ProfileName")
            CombinedTable.Columns.Add("ScenarioName")
            CombinedTable.Columns.Add("TimeName")
            CombinedTable.Columns.Add("StatusText")
            CombinedTable.Columns.Add("LastExecutedStepStatus")
            CombinedTable.Columns.Add("LastExecutedStepTimeUTC")
            CombinedTable.Columns.Add("LastExecutedStepTimeEST")
            CombinedTable.Columns.Add("SignOffState")
            CombinedTable.Columns.Add("UserName")
            CombinedTable.Columns.Add("TimeStamp")

            Dim WFName As String = "P1000 Prudential Group"
            Dim WFScenario As String = "Actuals"
            Dim WFTime As String = "2023M12"
            Dim methodTypeId As String = XFCommandMethodTypeId.WorkflowStatus
            Dim resultDataTableName As String = "WFSTATUS"
            Dim methodQuery As String
            Dim customSubVars As New Dictionary(Of String, String)

            methodQuery = "{" & WFName & "}{" & WFScenario & "}{" & WFTime & "}{AllProfiles}{Descendants}{}"
            Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
                Dim objDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(dbConnApp, methodTypeId, methodQuery, resultDataTableName, customSubVars)
                For Each row As DataRow In objDataSet.Tables("WFSTATUS").Rows
                    WFTable.Rows.Add(row.Item("ProfileKey"), row.Item("ProfileName"), row.Item("ScenarioName"), row.Item("TimeName"), row.Item("StatusText"), row.Item("LastExecutedStepStatus"), row.Item("LastExecutedStepTime"), row.Item("LastExecutedStepTime"))
                Next
            End Using

            methodTypeId = XFCommandMethodTypeId.CertificationForWorkflowUnit
            methodQuery = "{" & WFName & "}" & "{" & WFScenario & "}" & "{" & WFTime & "}" & "{True}" & "{}"
            Using DBConnAppForCert2 As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
                CertTable2.Columns.Add("ProfileKey")
                CertTable2.Columns.Add("ScenarioKey")
                CertTable2.Columns.Add("TimeKey")
                CertTable2.Columns.Add("SignOffState")
                CertTable2.Columns.Add("UserName")
                CertTable2.Columns.Add("TimeStamp")

                Dim objDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(DBConnAppForCert2, methodTypeId, methodQuery, "WFCertSTATUS2", customSubVars)
                For Each row As DataRow In objDataSet.Tables("WFCertSTATUS2_SignOffGroups").Rows
                    CertTable2.Rows.Add(row.Item("ProfileKey"), row.Item("ScenarioKey"), row.Item("TimeKey"), row.Item("SignOffState"), row.Item("UserName"), row.Item("TimeStamp"))
                Next
            End Using

            For Each wfRow As DataRow In WFTable.Rows
                Dim profileKey As Object = wfRow("ProfileKey")

                Dim certRows As DataRow() = CertTable2.Select("ProfileKey = '" & profileKey.ToString() & "'")

                For Each certRow As DataRow In certRows
                    Dim newRow As DataRow = CombinedTable.NewRow()

                    newRow("ProfileKey") = wfRow("ProfileKey")
                    newRow("ProfileName") = wfRow("ProfileName")
                    newRow("ScenarioName") = wfRow("ScenarioName")
                    newRow("TimeName") = wfRow("TimeName")
                    newRow("StatusText") = wfRow("StatusText")
                    newRow("LastExecutedStepStatus") = wfRow("LastExecutedStepStatus")
                    newRow("LastExecutedStepTimeUTC") = wfRow("LastExecutedStepTimeUTC")
                    newRow("LastExecutedStepTimeEST") = wfRow("LastExecutedStepTimeEST")

                    newRow("SignOffState") = certRow("SignOffState")
                    newRow("UserName") = certRow("UserName")
                    newRow("TimeStamp") = certRow("TimeStamp")

                    CombinedTable.Rows.Add(newRow)
                Next
            Next

            Return CombinedTable
        End Function
    End Class
End Namespace

SKMundraAuthor

JackLacava pls help with your comments.

RobbSalzmann

why do you want a single table for two different sets of results?

SKMundraAuthor

Hi Robb, finance team wants to have one report showing Workflows status and is it is certified then name of the user along with certification time stamp. As I understood these two information are coming via 1 different method queries in Standard reports.
I was thinking If I can combine those I can get the output.

does this make sense?

RobbSalzmann

I would lean toward using a join and just one query, then you're only dealing with one result set. something like this....

SELECT 
    WF.ProfileKey, 
    WF.ProfileName, 
    WF.ScenarioName, 
    WF.TimeName, 
    WF.StatusText, 
    WF.LastExecutedStepStatus, 
    WF.LastExecutedStepTimeUTC, 
    WF.LastExecutedStepTimeEST,
    Cert.SignOffState,
    Cert.UserName,
    Cert.TimeStamp
FROM 
    WorkflowStatus AS WF
JOIN 
    CertificationForWorkflowUnit AS Cert
    ON WF.ProfileKey = Cert.ProfileKey
WHERE 
    WF.WFName = 'P1000 Prudential Group'
    AND WF.WFScenario = 'Actuals'
    AND WF.WFTime = '2023M12'

SKMundraAuthor

Hey Robb, kindly suggest if you see a workaround or this, or if we can do this join, I am not a coder - just learning and delivering.

RobbSalzmann

Sure. Here are some ideas how I might refactor what you're doing into a single query:

see below...

SKMundraAuthor

Hi Robb, thank you so much !
I am unable to find a table named 'WorkFlowStatus' - am I missing something basic here? kindly suggest.

RobbSalzmannAnswer

sry about that, here's an update, Monday morning, I misread your initial code...
Here's how you can combine the results, using what you had:

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
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

Namespace OneStream.BusinessRule.DashboardDataSet.GetWFStatusandCertification
    Public Class MainClass
        Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object
            Try
                Select Case args.FunctionType

                    Case Is = DashboardDataSetFunctionType.GetDataSetNames
                        Dim DSnames As New List(Of String)()
                        DSnames.Add("GetWFStatusandCertificationF")
                        Return DSnames

                    Case Is = DashboardDataSetFunctionType.GetDataSet
                        If args.DataSetName.XFEqualsIgnoreCase("GetWFStatusandCertificationF") Then
                            Return GetWFStatusandCertificationF(si, args)
                        End If

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

        Private Function GetWFStatusandCertificationF(ByVal si As SessionInfo, ByVal args As DashboardDataSetArgs) As DataTable
            Dim WFTable As New DataTable("WFSTATUS")
            Dim CertTable2 As New DataTable("WFCertSTATUS2")
            Dim CombinedTable As New DataTable("CombinedWFandCertStatus")

            CombinedTable.Columns.Add("ProfileKey")
            CombinedTable.Columns.Add("ProfileName")
            CombinedTable.Columns.Add("ScenarioName")
            CombinedTable.Columns.Add("TimeName")
            CombinedTable.Columns.Add("StatusText")
            CombinedTable.Columns.Add("LastExecutedStepStatus")
            CombinedTable.Columns.Add("LastExecutedStepTimeUTC")
            CombinedTable.Columns.Add("LastExecutedStepTimeEST")
            CombinedTable.Columns.Add("SignOffState")
            CombinedTable.Columns.Add("UserName")
            CombinedTable.Columns.Add("TimeStamp")

            Dim WFName As String = "P1000 Prudential Group"
            Dim WFScenario As String = "Actuals"
            Dim WFTime As String = "2023M12"
            Dim methodTypeId As String = XFCommandMethodTypeId.WorkflowStatus
            Dim resultDataTableName As String = "WFSTATUS"
            Dim methodQuery As String
            Dim customSubVars As New Dictionary(Of String, String)

            methodQuery = "{" & WFName & "}{" & WFScenario & "}{" & WFTime & "}{AllProfiles}{Descendants}{}"
            Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
                Dim objDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(dbConnApp, methodTypeId, methodQuery, resultDataTableName, customSubVars)
                For Each row As DataRow In objDataSet.Tables("WFSTATUS").Rows
                    WFTable.Rows.Add(row.Item("ProfileKey"), row.Item("ProfileName"), row.Item("ScenarioName"), row.Item("TimeName"), row.Item("StatusText"), row.Item("LastExecutedStepStatus"), row.Item("LastExecutedStepTime"), row.Item("LastExecutedStepTime"))
                Next
            End Using

            methodTypeId = XFCommandMethodTypeId.CertificationForWorkflowUnit
            methodQuery = "{" & WFName & "}" & "{" & WFScenario & "}" & "{" & WFTime & "}" & "{True}" & "{}"
            Using DBConnAppForCert2 As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
                CertTable2.Columns.Add("ProfileKey")
                CertTable2.Columns.Add("ScenarioKey")
                CertTable2.Columns.Add("TimeKey")
                CertTable2.Columns.Add("SignOffState")
                CertTable2.Columns.Add("UserName")
                CertTable2.Columns.Add("TimeStamp")

                Dim objDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(DBConnAppForCert2, methodTypeId, methodQuery, "WFCertSTATUS2", customSubVars)
                For Each row As DataRow In objDataSet.Tables("WFCertSTATUS2_SignOffGroups").Rows
                    CertTable2.Rows.Add(row.Item("ProfileKey"), row.Item("ScenarioKey"), row.Item("TimeKey"), row.Item("SignOffState"), row.Item("UserName"), row.Item("TimeStamp"))
                Next
            End Using

            For Each wfRow As DataRow In WFTable.Rows
                Dim profileKey As Object = wfRow("ProfileKey")

                Dim certRows As DataRow() = CertTable2.Select("ProfileKey = '" & profileKey.ToString() & "'")

                For Each certRow As DataRow In certRows
                    Dim newRow As DataRow = CombinedTable.NewRow()

                    newRow("ProfileKey") = wfRow("ProfileKey")
                    newRow("ProfileName") = wfRow("ProfileName")
                    newRow("ScenarioName") = wfRow("ScenarioName")
                    newRow("TimeName") = wfRow("TimeName")
                    newRow("StatusText") = wfRow("StatusText")
                    newRow("LastExecutedStepStatus") = wfRow("LastExecutedStepStatus")
                    newRow("LastExecutedStepTimeUTC") = wfRow("LastExecutedStepTimeUTC")
                    newRow("LastExecutedStepTimeEST") = wfRow("LastExecutedStepTimeEST")

                    newRow("SignOffState") = certRow("SignOffState")
                    newRow("UserName") = certRow("UserName")
                    newRow("TimeStamp") = certRow("TimeStamp")

                    CombinedTable.Rows.Add(newRow)
                Next
            Next

            Return CombinedTable
        End Function
    End Class
End Namespace

SKMundraAuthor

Awesome Robb, thanks for such a swift response and solution.

ShivaPrasad

Hi Robb,

Even we have a same requirement like (Who certified for the respective entity with Time stamp), i have used the above Dash Board data set rule. followed the below steps to get the grid view

Attached the Business rule to Data Adapter
Attached the Data Adapter to BI Viewer.

But here i am getting two certified status for the entity. we need the latest certified status. can you please help us to resolve this.

RobbSalzmann

ShivaPrasad, This cannot be analyzed without seeing the code. in particular, the queries used. Also, start a new post for your new question. This post is marked solved.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded