Forum Discussion

ShivaPrasad's avatar
ShivaPrasad
New Contributor II
21 days ago

Workflow Certified Status with User Details

Hi Team,

We have a requirement like, Finance team wants to have one report with the details of Workflow Status, who certified the respective entity (name of the user) along with certification time stamp. 

 used the below Dash Board data set rule. followed the below steps to get the grid view

  1. Attached the Business rule to Data Adapter
  2. 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.

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
 
Namespace OneStream.BusinessRule.DashboardDataSet.WF_Certification_Status
    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 = "ASIA"
            Dim WFScenario As String = "Actual"
            Dim WFTime As String = "2025M1"
            Dim methodTypeId As String = XFCommandMethodTypeId.WorkflowStatus
            Dim resultDataTableName As String = "WFSTATUS"
            Dim methodQuery As String
            Dim customSubVars As New Dictionary(Of String, String)
brapi.ErrorLog.LogMessage(si,"1")
            methodQuery = "{" & WFName & "}{" & WFScenario & "}{" & WFTime & "}{AllProfiles}{Descendants}{ProfileName like '*.Confirm*'}"
'methodQuery = "{" & WFName & "}{" & WFScenario & "}{" & WFTime & "}{'%Confirm_Certify'}{Descendants}{ProfileName like '*.Confirm*'}"
            Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
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")
 
                Dim objDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(dbConnApp, methodTypeId, methodQuery, resultDataTableName, customSubVars)
'Dim FilterData As Datarow() = WFTable.Select("ProfileKey", "ProfileName","ScenarioName", "TimeName", "StatusText", "LastExecutedStepStatus","LastExecutedStepTimeUTC","LastExecutedStepTimeEST Desc")
                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
' For Each row As DataRow In FilterData
' 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
brapi.ErrorLog.LogMessage(si,"2")
            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
brapi.ErrorLog.LogMessage(si,"3")
 
            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
brapi.ErrorLog.LogMessage(si,"4")
            Return CombinedTable
        End Function
    End Class
End Namespace

 

2 Replies

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II

    It looks like you need to filter on the latest certifications - see the *** code sections:

    Private Function GetWFStatusandCertificationF(ByVal si As SessionInfo, ByVal args As DashboardDataSetArgs) As DataTable
        ' Table declarations
        Dim WFTable As New DataTable("WFSTATUS")
        Dim CertTable2 As New DataTable("WFCertSTATUS2")
        Dim CombinedTable As New DataTable("CombinedWFandCertStatus")
    
        ' Columns for final output
        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")
    
        ' Parameters for WF query
        Dim WFName As String = "ASIA"
        Dim WFScenario As String = "Actual"
        Dim WFTime As String = "2025M1"
        Dim methodTypeId As String = XFCommandMethodTypeId.WorkflowStatus
        Dim resultDataTableName As String = "WFSTATUS"
        Dim methodQuery As String
        Dim customSubVars As New Dictionary(Of String, String)
    
        ' Construct WF query
        methodQuery = "{" & WFName & "}{" & WFScenario & "}{" & WFTime & "}{AllProfiles}{Descendants}{ProfileName like '*.Confirm*'}"
    
        ' Set up WFTable columns
        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")
    
        ' Fetch workflow status records
        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
    
        ' Set up Certification status table
        methodTypeId = XFCommandMethodTypeId.CertificationForWorkflowUnit
        methodQuery = "{" & WFName & "}{" & WFScenario & "}{" & WFTime & "}{True}{}"
    
        CertTable2.Columns.Add("ProfileKey")
        CertTable2.Columns.Add("ScenarioKey")
        CertTable2.Columns.Add("TimeKey")
        CertTable2.Columns.Add("SignOffState")
        CertTable2.Columns.Add("UserName")
        CertTable2.Columns.Add("TimeStamp")
    
        ' Fetch certification status records
        Using DBConnAppForCert2 As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
            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
    
        ' Loop through each workflow row and attach the most recent certification row (by TimeStamp)
        For Each wfRow As DataRow In WFTable.Rows
            Dim profileKey As Object = wfRow("ProfileKey")
    
            ' *** Select matching certification rows for only this profileKey
            Dim certRows As DataRow() = CertTable2.Select("ProfileKey = '" & profileKey.ToString() & "'")
    
            ' *** Added to Get only the latest cert row by TimeStamp
            Dim latestCertRow As DataRow = certRows _
                .OrderByDescending(Function(r) Convert.ToDateTime(r("TimeStamp"))) _
                .FirstOrDefault()
    
            If latestCertRow IsNot Nothing Then
                Dim newRow As DataRow = CombinedTable.NewRow()
    
                ' Copy WF data
                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")
    
                ' Copy only the latest certification row
                newRow("SignOffState") = latestCertRow("SignOffState")
                newRow("UserName") = latestCertRow("UserName")
                newRow("TimeStamp") = latestCertRow("TimeStamp")
    
                CombinedTable.Rows.Add(newRow)
            End If
        Next
    
        ' Return final combined result
        Return CombinedTable
    End Function

     

  • ShivaPrasad's avatar
    ShivaPrasad
    New Contributor II

    Hi Robb,

    Its working fine, But how can we make "WorkFlow Name, WF Scenario & WF Time" as dynamic(it should work based on my Work flow selection). as of we have hard coded the values as mention below image. can you please help here to make the dynamic.