Forum Discussion

ShivaPrasad's avatar
ShivaPrasad
New Contributor II
2 months 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

 

BI Viewer
Dashboards

2 Replies

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    2 months ago

    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
    2 months ago

    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.