Workflow Certified Status with User Details

Forum|Forum|1 year ago
May 12, 2025
3 replies
2 views

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

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.

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"))

' 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"))

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)

brapi.ErrorLog.LogMessage(si,"4")

Return CombinedTable

End Function

End Class

End Namespace

R

RobbSalzmann

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

S

ShivaPrasadAuthor

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.

S

ShivaPrasadAuthor

Hi Robb,

Can you please help us to resolve above issue?

R

RobbSalzmann

'The gymnastics onestream expects you to somehow know, to get...
Dim wfUnitClusterPk as WorkFlowUnitClusterPk = si.WorkflowClusterPk
Dim wfProfileKey as Guid =  wfUnitClusterPk.ProfileKey
Dim wfProfileInfo as WorkFlowProfileInfo = BRApi.Workflow.Metadata.GetProfile(si, wfProfileKey)
Dim wfScenarioId as Integer = wfUnitClusterPk.ScenarioKey
Dim wfTimeId as Integer = wfUnitClusterPk.TimeKey

'... the values you actually need:
Dim wfName As String = wfProfileInfo.Name
Dim wfScenario As String = ScenarioDimHelper.GetNameFromID(si, wfScenarioKey)
Dim wfTime As String = BRApi.Finance.Time.GetNameFromId(si, wfTimeId)

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded