Sorry for the delay.
Here is a C# equivalent.
This has not been rigorously tested on a customer-like environment with multiple users. Therefore you must test it yourself. Pay particular attention that it should execute only once when saving a cube view. It could have multiple threads attempting to run the event handler at the same time, so please check this doesn't happen in your application. This is why there is a lock statement in the event handler, to prevent multiple threads from the same session trying to run the Event Handler at the same time.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using Microsoft.CSharp;
using OneStream.Finance.Database;
using OneStream.Finance.Engine;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using OneStream.Stage.Database;
using OneStream.Stage.Engine;
// ------ CGL, Aug 2018, revised 2022 ---
// ------ Used to capture the standard cube-view SAVE button on the toolbar, to auto-run a specific Custom Finance BR on save -----
// ------ COMMON MISCONCEPTIONS ----
// --- Q. Why don't you just use a calculate button on a dashboard?
// --- A. Because people will still press the regular toolbar save button and wonder why the auto-calc didn't work
// --- You can hide the standard toolbar to prevent the standard save button, but then you lose all the other toolbar functions
// --- Q. Why don't you use the Form event handler?
// --- A. Because it doesn't fire when you save a cube view that's in a dashboard
// --- Q. Why don't you use the SaveData event handler?
// --- A. Because that runs for every data cell being written to the data unit and even captures calculated cells being written. So your application will grind to a halt when doing a cube calc.
// --- Q. Why don't you use Process Cube button?
// --- A. Because this isn't a typical consolidation use-case, and you only need to re-calculate limited/specific durable cells and have an almost instant calculation.
// --- The last thing you want with an auto-calc-on-save is a status bar every time you press save And clog up your task activity log.
namespace OneStream.BusinessRule.WorkflowEventHandler.WorkflowEventHandler
{
public class MainClass
{
private static Guid gPrevRunId = Guid.Empty;
#region "Class Definitions"
private class RuleAndFuncPair
{
internal string strBRName;
internal string strFuncName;
public RuleAndFuncPair(string b, string f)
{
strBRName = b;
strFuncName = f;
}
}
private class CubeEntityPair
{
internal string strCube;
internal string strScenario;
internal int iScenarioId;
internal string strEntity;
internal int startTimeId;
public CubeEntityPair(string c, string e)
{
strCube = c;
strEntity = e;
startTimeId = DimConstants.Unknown;
}
public CubeEntityPair(SessionInfo si, int cid, int sid, int eid, int tid)
{
strCube = BRApi.Finance.Cubes.GetCubeInfo(si, cid).Cube.Name;
iScenarioId = sid;
strScenario = BRApi.Finance.Members.GetMemberName(si, (int) DimTypeId.Scenario, sid);
strEntity = BRApi.Finance.Members.GetMemberName(si, (int) DimType.Entity.Id, eid);
startTimeId = tid;
}
}
#endregion
private readonly string strKeyValueExcel = "_Excel";
private readonly string strReasonSaveDataCells = "Reason = Save Data Cells";
public object Main(SessionInfo si, BRGlobals globals, object api, WorkflowEventHandlerArgs args)
{
try
{
object returnValue = args.DefaultReturnValue;
args.UseReturnValueFromBusinessRule = false;
args.Cancel = false;
if (!args.IsBeforeEvent && args.OperationName == BREventOperationType.Workflow.UpdateWorkflowStatus)
{
string strWFImpactMessage = args.Inputs[3].ToString();
// In this case , Inputs[3] is what is called internally the "Workflow Impact Message"
// It is constructed by the OneStream.Finance.Engine.DataCellWrite class, and spread over 5 lines of text:
// Reason = Save Data Cells
// Data Entry Type = dataEntryAuditItem.Info.DataEntryType.ToString()
// Cube View Or Filename = dataEntryAuditItem.Info.CubeViewOrFileName
// Data Entry Audit ID = dataEntryAuditItem.Info.UniqueID.ToString()
// Task Activity ID = dataEntryAuditItem.Info.TaskActivityID.ToString()
if (strWFImpactMessage.StartsWith(strReasonSaveDataCells)) {
bool bMustExit = false;
DataEntryAuditInfo da = ConvertStringArgsToAuditInfo(si, strWFImpactMessage);
lock ((args))
{
if (da == null)
throw new XFException(si, "WorkflowEventHandler Error: unable to get DataEntryAuditInfo", strWFImpactMessage);
if (da.UniqueID == gPrevRunId)
bMustExit = true; // -- there is another thread alredy running this save action, so exit --
else
gPrevRunId = da.UniqueID;
}
if (!bMustExit) this.RunCubeBR(si,globals,api,da,strWFImpactMessage);
}
}
return returnValue;
}
catch (Exception ex)
{
throw ErrorHandler.LogWrite(si, new XFException(si, ex));
}
}
public String GetRHS(string str)
{
int EqualsPos = str.IndexOf('=');
if (EqualsPos > 0)
return str.Substring(EqualsPos + 1).Trim();
else
return string.Empty;
}
private DataEntryAuditInfo ConvertStringArgsToAuditInfo(SessionInfo si, string strWFImpactMessage)
{
String[] strLines = strWFImpactMessage.Split(new String[] {Environment.NewLine}, StringSplitOptions.None);
if (strLines.Length >= 5)
{
DataEntryAuditInfo daInfo = new DataEntryAuditInfo();
daInfo.DataEntryType = (DataEntryType)Enum.Parse(typeof(DataEntryType),this.GetRHS(strLines[1]));
if (daInfo.DataEntryType == DataEntryType.CubeView)
daInfo.CubeViewOrFileName = this.GetRHS(strLines[2]);
else if (daInfo.DataEntryType == DataEntryType.Excel)
daInfo.CubeViewOrFileName = string.Empty;
daInfo.UniqueID = Guid.Parse(this.GetRHS(strLines[3]));
daInfo.TaskActivityID = Guid.Parse(this.GetRHS(strLines[4]));
return daInfo;
}
else
return null;
}
#region "Run Cube View BR"
private void RunCubeBR(SessionInfo si, BRGlobals globals, object api, DataEntryAuditInfo da, string strWFImpactMessage) // WorkflowEventHandlerArgs args)
{
if (da.DataEntryType != DataEntryType.CubeView && da.DataEntryType != DataEntryType.Excel)
// -- if it's not a CubeView or Excel then don't do anything
return;
if (da.DataEntryType == DataEntryType.CubeView && string.IsNullOrEmpty(da.CubeViewOrFileName))
throw new XFException(si, "Error, cannot obtain Cube View from Workflow Impact Message", strWFImpactMessage);
RuleAndFuncPair whatToRun = GetBRfromCubeView(si, da);
if (!(whatToRun == null))
{
if (!string.IsNullOrEmpty(whatToRun.strFuncName) & !string.IsNullOrEmpty(whatToRun.strBRName))
{
List<CubeEntityPair> strEnts = GetDataEntryEntities(si, da.UniqueID);
if (strEnts.Count == 0)
throw new XFException(si, "Unable to get entity from saved data, please reselect the entity from drop-down list and try again", strWFImpactMessage);
else
System.Threading.Tasks.Parallel.ForEach(strEnts, e =>
{
string strTimeFilter = this.GetTimeFilter(si, e.iScenarioId, e.startTimeId);
if (string.IsNullOrEmpty(strTimeFilter))
throw new XFException(si, "Error could not get the time periods for which to auto-run calculations", whatToRun.strBRName + ":" + whatToRun.strFuncName);
Dictionary<string, string> nameValue = new Dictionary<string, string>();
nameValue.Add(DimType.Consolidation.Name, ConsMember.Local.Name);
nameValue.Add(DimType.View.Name, ViewMember.YTD.Name);
nameValue.Add(DimType.Time.Name, string.Empty);
nameValue.Add(CustomCalculateWcf.constTimeFilter, strTimeFilter);
nameValue.Add(DimStringConstants.Cube, e.strCube);
nameValue.Add(DimType.Scenario.Name, e.strScenario);
nameValue.Add(DimType.Entity.Name, e.strEntity);
BRApi.Finance.Calculate.ExecuteCustomCalculateBusinessRule(si, whatToRun.strBRName, whatToRun.strFuncName, nameValue, CustomCalculateTimeType.MemberFilter);
});
}
}
}
#endregion
#region "Helper Functions"
private String GetTimeFilter(SessionInfo si, int sId, int iStartTimeId)
{
ScenarioType st = BRApi.Finance.Scenario.GetScenarioType(si, sId);
if (st == ScenarioType.Actual)
return "T#" + TimeDimHelper.GetNameFromId(iStartTimeId);
else
{
// --- for other scenarios such as Budget/Forecast/Plan/LongTerm
// --- generate list of valid time members from now until the end time (as configured in the scenario settings)
// --- caters for varying input frequency by year
int tId = iStartTimeId;
int iLastPerId = tId;
WorkflowTrackingFrequency wtf = BRApi.Finance.Scenario.GetWorkflowTrackingFrequency(si, sId);
if (wtf == WorkflowTrackingFrequency.Range)
iLastPerId = BRApi.Finance.Scenario.GetWorkflowEndTime(si, sId);
else
iLastPerId = TimeDimHelper.GetLastPeriodInYear(tId);
System.Text.StringBuilder sbTimeEntries = new System.Text.StringBuilder();
while (tId <= iLastPerId)
{
BRApi.Finance.Time.GetFirstPeriodInYear(si, tId);
if (sbTimeEntries.Length > 0)
sbTimeEntries.Append(',');
sbTimeEntries.Append("T#" + TimeDimHelper.GetNameFromId(tId));
bool bIsNextYear = true;
tId = TimeDimHelper.GetNextPeriod(tId, out bIsNextYear);
if (bIsNextYear)
{
// --- test if next year has a different input frequency ---
Frequency inputFreq = BRApi.Finance.Scenario.GetInputFrequencyForYear(si, sId, TimeDimHelper.GetYearFromId(tId));
tId = BRApi.Finance.Time.ConvertIdToStartIdUsingAnotherFrequency(si, tId, inputFreq);
}
}
return sbTimeEntries.ToString();
}
}
// ---------------------------------------------------------------------
private RuleAndFuncPair GetBRfromCubeView(SessionInfo si, DataEntryAuditInfo da)
{
// -- Assumes table x_CbViewRules with following columns
// cvName varchar Cube View Name (matches CubeViewItem.Name)
// busRule varchar Finance BR name
// funcName varchar Function name within the BR to execute. Ensure the BR main function has this funcName in its SELECT CASE statement
using (DbConnInfo dbConn = BRApi.Database.CreateApplicationDbConnInfo(si))
{
if (OneStream.Shared.Database.DbSql.DoesTableExist(dbConn, "CVX_CalcOnSave"))
{
string strKey = da.DataEntryType == DataEntryType.CubeView ? da.CubeViewOrFileName : strKeyValueExcel;
string strSQL = string.Format("SELECT [busRule], [funcName] FROM [CVX_CalcOnSave] WITH (NOLOCK) WHERE [cvName]='{0}'", strKey);
using (DataTable dt = BRApi.Database.ExecuteSql(dbConn, strSQL, false))
{
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
return new RuleAndFuncPair(dr["busRule"].ToString(), dr["funcName"].ToString());
}
else
return null;
}
}
else
throw new XFException(si, "Error, solution table [CVX_CalcOnSave] has not been created.", "Remove the WorkflowEventHandler or run the CVX solution setup");
}
}
// ---------------------------------------------------------------------
// --- Gets the list of cube/entity combinations that are impacted by looking up the Audit Entry table with the supplied audit ID ---
// --- so we know for which entity to run the custom finance BR. This could be extended to return a list of entities if cube view could impact multiple entities. '
private List<CubeEntityPair> GetDataEntryEntities(SessionInfo si, Guid gAuditId)
{
List<CubeEntityPair> lst = new List<CubeEntityPair>();
using (DbConnInfoApp dbConnApp = BRApi.Database.CreateApplicationDbConnInfo(si))
{
string strSQL = string.Format("SELECT CubeId,ScenarioId,EntityID,Min(TimeId) as TimeId FROM DataEntryAuditCell WITH (NOLOCK) WHERE AuditSourceID = CAST('{0}' As UNIQUEIDENTIFIER ) GROUP BY CubeId,ScenarioId,EntityID;", gAuditId.ToString());
using (DataTable dt = BRApi.Database.ExecuteSql(dbConnApp, strSQL, false))
{
foreach (DataRow dr in dt.Rows)
{
int iCubeId = (int)dr["CubeId"];
int iScenarioId = (int)dr["ScenarioID"];
int iEntityId = (int)dr["EntityID"];
int iTimeId = (int)dr["TimeId"];
lst.Add(new CubeEntityPair(si, iCubeId, iScenarioId, iEntityId, iTimeId));
}
}
}
return lst;
}
#endregion
}
}
Caution : samples issued from this Community Forum are for guidance only and not to be interpreted as complete solutions. Many snippets/suggestions have been built in a limited OneStream application environment and not representative of a customer production application with high volumes of data and multiple users. The Community Forum is not responsible for consequences if a consultant takes code examples from here but does not rigorously test them in their own customer environment.
