Filter IC Dimension by Entity Property
A common requirement for reporting is to be able to filter the IC dimension by some property that exists only on the original Entity members. This can be achieved with a custom Member List defined in a Finance business Rule. Select Case api.FunctionType ' MemberListHeaders support is optional but good practice Case Is = FinanceFunctionType.MemberListHeaders Dim mListHeaders As New List(Of MemberListHeader) ' add the name of your list: mListHeaders.Add(New MemberListHeader("withText1")) Return mListHeaders ' Here we do the real work Case Is = FinanceFunctionType.MemberList If args.MemberListArgs.MemberListName.XFEqualsIgnoreCase("withText1") Then ' this list of members will be populated later Dim ICs As New List(Of Member) ' amend parameters as necessary here Dim dimensionName as String = "CorpEntities" Dim memberFilter as String = "E#Root.Base.Where(Text1 <> '')" ' filter the Entity dimension by some criteria Dim entities As List(Of MemberInfo) = brapi.Finance.Members.GetMembersUsingFilter(si, _ brapi.Finance.Dim.GetDimPk(si, dimensionName), _ memberFilter, _ True) ' retrieve IC members corresponding to the selected Entity members ' and push them into output list For Each entityMInfo As MemberInfo In entities if entityMInfo.getEntityProperties().isIC then ICs.Add(brapi.Finance.Members.GetMember(si, dimtypeId.IC, entityMInfo.Member.Name)) end if Next ' wrap with the MemberList object and return Return New MemberList(New MemberListHeader("withText1"), ICs) This can then be referenced in CubeViews and elsewhere like this:1.1KViews7likes0CommentsDashboard: Open Dashboard File Resource
Open a Dashboard file resource and retrieve its raw contents. Note: This snippet is for OneStream 7.4 and above. Previous releases can use similar logic, just without the WorkspaceID machinery. ' *** parameters to customize to your needs ' Specify the name of the dashboard file we want to retrieve Dim fileResourceName As string = "FileName.txt" ' Specify the name of the Workspace containing it Dim workspaceName as String = "My Workspace" ' set this to True if your Dashboard is in the System pane Dim isSystemLevel as Boolean = False ' *** end parameters ' the following chunk will find the ID of the workspace containing the file Dim workspaceID = Guid.Empty If not workspaceName.XFEqualsIgnoreCase("Default") then Dim workspaceID as Guid = BRApi.Dashboards.Workspaces.GetWorkspaceIDFromName( _ si, isSystemLevel, workspaceName) end if ' Now we retrieve the FileResource object, representing our file Dim fileResource As DashboardFileResource = BRApi.Dashboards.FileResources.GetFileResource( _ si, isSystemLevel, workspaceID, fileResourceName) ' If the file contains text, you can retrieve it right away with the following line. ' NOTE: if it's some other type (zip, image, etc), you will need different handling. Dim fileContent As String = system.Text.Encoding.UTF8.GetString(fileResource.FileBytes)1KViews1like0CommentsDashboard: Get or Set Literal Parameter
Literal Parameters are, effectively, application-wide variables that can be used to drive Dashboards and Cube Views. Their values can be set or retrieved in code as shown below. From version 7.3 onwards, they are contained in a Workspace. That means that the relevant calls now require the ID of the Workspace where the Parameter is located. Note that the ID of Default Workspace is an empty Guid. Note: Literal Parameters are effectively shared between all users; which means that, if a Dashboard Action triggered by "User1" modifies a Literal Parameter value, "User2" will also receive the changed value in any Dashboards they are using (at the first refresh). OneStream Version 7.3+ Dim parameterName As String = "MyParameterName" ' if Parameter lives in Dashboards under "System" tab, set this to True Dim isSystemLevel as Boolean = False ' retrieve workspace ID. If it's Default Workspace, you can just use Guid.Empty instead Dim workspaceID As Guid = BRApi.Dashboards.Workspaces.GetWorkspaceIDFromName( _ si, isSystemLevel, workspaceName) ' --- Set literal parameter value Dim newValue As String = "New Literal Value" BRApi.Dashboards.Parameters.SetLiteralParameterValue( _ si, isSystemLevel, workspaceID, parameterName, newValue) ' --- Retrieve literal parameter value Dim pValue as String = BRApi.Dashboards.Parameters.GetLiteralParameterValue( _ si, isSystemLevel, workspaceID, parameterName) OneStream Version 7.2 and below Dim parameterName As String = "MyParameterName" ' if Parameter lives in Dashboards under "System" tab, set this to True Dim isSystemLevel as Boolean = False ' --- Set literal parameter value Dim newValue As String = "New Literal Value" BRApi.Dashboards.Parameters.SetLiteralParameterValue( _ si, isSystemLevel, parameterName, newValue) ' --- Retrieve literal parameter value Dim pValue as String = BRApi.Dashboards.Parameters.GetLiteralParameterValue( _ si, isSystemLevel, parameterName)2.2KViews0likes0CommentsDashboard: XFBR String
Business rules written and used as Parameters that return a specific value based on the defined inputs. This Business Rule can be applied to any Dashboard or Cube View property where a Parameter is used. Imports System Imports System.Data Imports System.Data.Common Imports System.IO Imports System.Collections.Generic Imports System.Globalization Imports System.Linq Imports Microsoft.VisualBasic Imports System.Windows.Forms Imports OneStream.Shared.Common Imports OneStream.Shared.Wcf Imports OneStream.Shared.Engine Imports OneStream.Shared.Database Imports OneStream.Stage.Engine Imports OneStream.Stage.Database Imports OneStream.Finance.Engine Imports OneStream.Finance.Database Namespace OneStream.BusinessRule.DashboardStringFunction.XFBRStringExamples Public Class MainClass Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As Object '------------------------------------------------------------------------------------------------------------ 'Reference Code: GetCalcStatus ' 'Usage: This shows different examples of BRString usage in Cube Views and Dashboards ' 'GetCalcStatus ' Parameter Example: ' BRString(XFBRStringExamples, GetCalcStatus, ParamCube = GolfStream, ' ParamEntity = [Houston Heights], ParamParent = Houston, ' ParamCons = USD, ParamScenario = Actual, ParamPeriod = 2011M1) ' 'GetUserIsAdmin ' Parameter Example: BRString(XFBRStringExamples, GetUserIsAdmin) ' 'EntityDesc ' Parameter Example: BRString(XFBRStringExamples, EntityDesc, ParamEntity=[|!MyEntity!|]) ' 'Created By: OneStream 'Date Created: 12-18-2017 '------------------------------------------------------------------------------------------------------------ Try Select Case args.FunctionName Case Is = "GetCalcStatus" 'Get the Passed in parameters Dim paramCubeValue As String = args.NameValuePairs("ParamCube") Dim paramEntityValue As String = args.NameValuePairs("ParamEntity") Dim paramParentValue As String = args.NameValuePairs("ParamParent") Dim paramConsValue As String = args.NameValuePairs("ParamCons") Dim paramScenarioValue As String = args.NameValuePairs("ParamScenario") Dim paramPeriodValue As String = args.NameValuePairs("ParamPeriod") Return BRApi.Finance.Data.GetCalcStatus(si, _ paramCubeValue, paramEntityValue, paramParentValue, _ paramConsValue, paramScenarioValue, paramPeriodValue) Case Is = "GetUserIsAdmin" 'Check to see if the user is an administrator ' (Can be used to hide objects that are administrator only) Return BRApi.Security.Authorization.IsUserInAdminGroup(si) Case Is = "EntityDesc" 'Get the Passed in parameter Dim myEntity As String = args.NameValuePairs("ParamEntity") Dim myEntityDesc As String = BRApi.Finance.Metadata.GetMember( _ si, 0, myEntity).Member.Description Return myEntityDesc End Select Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function End Class End Namespace3.3KViews2likes0CommentsDashboard: Data Sets
DashboardDataSet Rules are used to create programmatic query results. This rule type enables the rule writer to combine multiple types of data into a single result set using the full syntax capability of VB.Net. '------------------------------------------------------------------------------------------------------------ 'Reference Code: SampleDashboardDataSet ' 'Description: Creates an AOD.Net DataTable that selectively returns help desk tickets based on user ' level, ticket status and ticket priority. 'Usage: Used as custom method query in a Dashboard Data Adapter with the following parameter structure. 'Parameter ProtoType: {SampleDashboardDataSet}{DataSetFunctionName}{Param1="Paramater Value to be used in Data Set"} 'Parameter Example: {SampleDashboardDataSet}{DataSetFunctionName}{Param1="Value1"} ' 'Created By: OneStream Software 'Date Created: 06-1-2016 '------------------------------------------------------------------------------------------------------------ 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.GetDataSet If (args.DataSetName.XFEqualsIgnoreCase("TicketAnalysis")) Then Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si) 'Get passed in parameters Dim paramValue1 As String = args.NameValuePairs.XFGetValue("Param1", Guid.Empty.ToString) 'Execute a SQL query that filters tickets by user name for Non-Administrators Dim sql As New Text.StringBuilder Dim whereClause As New Text.StringBuilder sql.AppendLine("Select * From TableName ") '<- Update SQL Query 'Check Param1 If Not paramValue1.Equals("XXXX",StringComparison.InvariantCultureIgnoreCase) Then '<- Update tested value 'Update where clause based on parameter value whereClause.AppendLine("And Field1 = 'XXXX' ") '<- Update SQL Else 'Update where clause based on parameter value whereClause.AppendLine("And TicketStatus = 'XXXX' ") '<- Update SQL End If sql.AppendLine(whereClause.ToString) 'Return results of DataSet SQL Return BRAPi.Database.ExecuteSql(dbConnApp, sql.ToString, True) End Using End If End Select Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function End Class1.9KViews1like0Comments
Filter IC Dimension by Entity Property
A common requirement for reporting is to be able to filter the IC dimension by some property that exists only on the original Entity members. This can be achieved with a custom Member List defined in a Finance business Rule. Select Case api.FunctionType ' MemberListHeaders support is optional but good practice Case Is = FinanceFunctionType.MemberListHeaders Dim mListHeaders As New List(Of MemberListHeader) ' add the name of your list: mListHeaders.Add(New MemberListHeader("withText1")) Return mListHeaders ' Here we do the real work Case Is = FinanceFunctionType.MemberList If args.MemberListArgs.MemberListName.XFEqualsIgnoreCase("withText1") Then ' this list of members will be populated later Dim ICs As New List(Of Member) ' amend parameters as necessary here Dim dimensionName as String = "CorpEntities" Dim memberFilter as String = "E#Root.Base.Where(Text1 <> '')" ' filter the Entity dimension by some criteria Dim entities As List(Of MemberInfo) = brapi.Finance.Members.GetMembersUsingFilter(si, _ brapi.Finance.Dim.GetDimPk(si, dimensionName), _ memberFilter, _ True) ' retrieve IC members corresponding to the selected Entity members ' and push them into output list For Each entityMInfo As MemberInfo In entities if entityMInfo.getEntityProperties().isIC then ICs.Add(brapi.Finance.Members.GetMember(si, dimtypeId.IC, entityMInfo.Member.Name)) end if Next ' wrap with the MemberList object and return Return New MemberList(New MemberListHeader("withText1"), ICs) This can then be referenced in CubeViews and elsewhere like this:
