Application Workflow Status Report

[DmitriyTop]

Is there a way to view application Workflow Status reports in excel rather than having to use a dashboard in the application that shows if an entity is certified/locked or not? Something that is easily refreshable so that you don't have to leave the current workflow you're in to view all entities workflow status? 

[Krishna]

@DmitriyTop  - The Attached Code will provide the status of the WF. The code takes the parameters, and you can update it accordingly It is a Dashboard Data Set. It has to be called in Data Adapter with Method Query & Business Rule. BTW there is also out of reports from OS Solution Exchange. Application Reports.

Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
							
							'Create Tables in Memory
							        Dim WFTable As DataTable = New DataTable("WFSTATUS")
        							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 WFTime As String = args.NameValuePairs("ParamYear")
							Dim WFScenario As String = args.NameValuePairs("ParamScr")
							Dim WFName As String = args.NameValuePairs("ParamName")
							Dim methodTypeId As String = XFCommandMethodTypeId.WorkflowStatus
							'Dim methodQuery As String = "{Total Corporate}{Actual}{" & WFTime & "}{Import Status}{Descendants}{Type ='InputImportChild'}"
							Dim methodQuery As String = "{" & WFName &"}{" & WFScenario & "}{" & WFTime & "}{Import Status}{Descendants}{Type ='InputImportChild'}"
							Dim resultDataTableName As String = "WFSTATUS"
							Dim customSubVars As New Dictionary(Of String, String)
							customSubVars.Add("Name","")
							customSubVars.Add("Value","")
							

							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("ProfileName"),Row.Item("ScenarioName"),Row.Item("TimeName"),Row.Item("StatusText"),Row.Item("LastExecutedStepStatus"),Row.Item("LastExecutedStepTime"),EST)
				                   Next 
									Return WFTable
							 
						End Using
							
						End If

[DmitriyTop]

Thanks Krishna for the syntax, I'm not as familiar with how we would get the workflow status reports that are in the application, in excel using Data Adapter with Method Query & Business Rule. Are these excel add-ins? Different applications? 

[DanielWillis]

Hi DmitriyTop,

Before answering your specific question I wanted to check if there was a simpler option (i.e. is your requirement simply that you want to be able to see a workflow dashboard while you have a workflow step open?)

If so, there is a secret feature (I call it secret because I feel nobody knows about it till someone else tells them) where you can open up another tab of the same type of screen within OneStream (e.g. workflow screen, business rule, workspace area).

All you have to do is click 'New' down the bottom and then navigate to the dashboard in the new tab.

If that doesn't satisfy your needs then you are able to view any data that you have in a cube view or data adapter from Excel. Table Views are able to show data from virtually any source. They do get a bit techy to set up but if you're only retrieving data (not writing) then it isn't too much effort and not a bad mini learning project if you like that sort of thing!

[DmitriyTop]

Thanks Daniel, basically, I am trying to take an existing dashboard application report that is viewable in the application and retrieving the work status in excel. For example, the application report shows all entities and their workflow status. In this case, I want to be able to hit refresh in excel, in order to see what entities have not been certified or locked rather than going into the application. It's just to view data/workflow status so it sounds like the data adapter from excel is the route to go but I'm not even sure where to start with how to build something like that. More than interested in this mini project but just don't know how or where to start with Data Adapters. 

[DanielWillis · Accepted solution]

There is a good chance the report you're trying to get into Excel is already utilising a Data Adapter and you don't even need to set one up. Alternatively there might be one set up but its not quite right for your need and you can copy and tweak. Do you know how to open up a Dashboard in design mode and investigate the components?

Once you've got the data adapter set up you can look at getting it into Excel.

It looks like there is more in the docs than last time i looked for table views: https://documentation.onestream.com/release_8.2/Content/Table%20Views/Table%20Views%20Spreadsheet%20...