PowerBI Custom Data Adapter
I have built a OS data adapter off of a cube view that has a parameter that prompts for entity. I subsequently built PowerBI query to pull data via this data adapter. I can get the PowerBI query to pull data if I hardcode the Entity parameter value in the Custom Subst Vars in PowerBI. What I would like to do is replace hardcoding the Entity value with list prompt for the user. That will let the user decide which entity they want to pull when they refresh the query. Any ideas on how to create this functionality? #PowerBI
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 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")) 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 component and OneStream 9.0.0
Hi all, We upgraded one of our OS instances to 9.0.0 After a first look, it seems BI Viewer does not work anymore from Desktop app, but charts are showing from Modern Browser interface, any idea? Can't find a clue on Know Issues. On Desktop app, only the layout shows, no content: Same Dashboard from Modern Experience shows fine (I blurred data here) Any advice?
BI Viewer | Pivot Grid Formatting on Export
Hi everyone! I am working with a Pivot Grid dashboard item in the BI Viewer. I am investigating about the possibility of formatting the Excel export of this component. Currently, the exported file does not retain the formatting applied within the BI Viewer, resulting in a very non-user-friendly layout, with fixed column widths. Are there any options available? Any insights will be highly appreciated! :) Thank you! Have a nice day, Andra
Bi Viewer - Prior Time Totals
I have a daily report in bi viewer. This works by selecting the Week from a combo box and then it displays the days within that week (based on a sql table). I want to have the prior weeks show up as totals. Can't figure out how to do this within BI viewer or if I'll have to create a more defined sql query. Below are the screenshots from current setup. In this example I want to also see W1-4 but at their totals. Select Week combo box:Populating Future period in a Cube View
Hello, I'm trying to create a cube view that grabs actuals from People Planning and populates the future periods with the latest month of actuals. e.g. in M3, M1=M1 Actuals, M2=M2 Actuals, M3=M3 Actuals, M4-12=M3 Actuals. I need this in a cube view because we need to be able to show the data in both local currency and USD. People Planning is currency agnostic. I tried this directly querying XFW_PLP_Plan with SQL and a biViewer, but ran into the problem of not being able to bring in FX rates. I can only show local currency. Has anybody done this or have any ideas? Perhaps something with UD8? Perhaps there is something simpler I'm overlooking? There is SO much to learn with OneStream and I know I'm only scratching the surface.How to pull Workflow Parent in CV or QV
Hello Team, Generally, User will open a form through workflow and input data and certify that. i need pull a data Like, which user has certified the form for respective site. and in which table those User certified details will be store. Can you please help us on this.
Adding Gross Profit as a Column in a Bar Graph (BI Viewer)
I was wondering if it's possible to include Gross Profit as a column in a bar graph in BI Viewer. Specifically, I want to calculate Gross Profit as the total Revenue minus Expense accounts and display it as a separate bar in the chart. I've tried using Calculated Fields, but I haven't been able to get the total to appear correctly in the visualization. Has anyone successfully done this in BI Viewer, and if so, what approach did you use? Any help would be greatly appreciated!
