FilterMembers syntax (: vs ,) and base clearing when using GetDataBufferUsingFormula
I’m working on a custom clear process that clears cube data using GetDataBufferUsingFormula() The goal is to dynamically clear data for all base members under the current POV (Entity, UD1-UD4) by expanding any parent members with the .Base suffix. In other words, I want to make sure every dimension in the POV (Entity and UDs alike) is ultimately resolved down to base members before the clear runs. Here’s a simplified idea of what I’m doing: Dim filterString As String = String.Join(":", intersectionParts) Dim fullFilter As String = $"FilterMembers({filterString})" Dim sourceBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula(fullFilter, DataApiScriptMethodType.Calculate, False, destinationInfo) The process builds a FilterMembers() expression dynamically, retrieves the buffer, and sets all cell amounts to zero to perform a clear. However, I’m facing two issues: Some child members aren’t being cleared, even when I use .Base on their parent. I’m not fully sure about the correct syntax for FilterMembers, should the dimensions be separated by colon (:) or by comma (,)? What’s the real difference between the two, especially when dealing with multiple parent members or nested intersections? For example, if I have three parent members in my intersection, I might build something like: FilterMembers(E#MyEntity.Base:U1#ProfitCenter.Base:U2#Location.Base) But it’s unclear whether that colon syntax is correct, or if it should be comma-separated, or another thing is, the use of square bracket...since some base-level data doesn’t seem to clear. Could this be a syntax issue with FilterMembers, or could the problem come from how I’m constructing or passing the intersection into GetDataBufferUsingFormula()? Any clarification or working example for multi-parent .Base expansions with FilterMembers() would be a huge help. Thanks in advance!
Create a parameter to determine the initialization status of each forecast?
Hello everyone, We have set up a monthly forecast that is compared to the budget or actual figures in different cube views. We currently use a parameter to select the forecast month in each cube view, but I would like to remove it and have the latest forecast for the year or year N-1 displayed automatically. I have created an XFBR that retrieves the holding company's turnover for each of the 11 forecasts (January, February, etc., November) and displays the forecast closest to December with a turnover. The problem is that not all users have access to this company, so the BR does not work for them. I could change the BR to add companies to it so that it works for all users, but I find that cumbersome. Is it possible to save a value (X or 1) in a variable when initializing the forecast so that the BR can query this variable based on the Time scenario and thus know which forecast has been initialized? How do you do this in your OS?
How to get the sum of multiple entities in a GetDataCell or in a Calculate
Hi Everyone. I want to get the sum of multiple entities, like in the example below, but for now I'm only getting errors or values with 0 'First option Dim strList As String = String.Join(", ", (From l In lstMI Select "" & l.Member.Name & "").ToArray()) Dim lstMI As List(Of MemberInfo) = BRapi.Finance.Members.GetMembersUsingFilter(si, api.Pov.EntityDim.DimPk, "E#Kazakhstan.Base.Where(Text1 = 'M_S_KAZAKHSTAN')", True) api.Data.GetDataCell("A#411000:E#Root.list([" & strList &"]):C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS").CellAmount 'Second Option api.Data.GetDataCell("A#411000:E#Kazakhstan.Base.Where(Text1 = 'M_S_KAZAKHSTAN'):C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS").CellAmount What I ultimately want to do is to get those values so I can change the following, so that the entity includes all those base entities that meet the filter, and I can get the correct value and use it in my Calculate. Dim DBICtry As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#RETBUYBACK:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS + A#423100:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS + A#411000:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS)") api.Data.FormulaVariables.SetDataBufferVariable("DBICtry",DBICtry,False) api.Data.Calculate("A#P_EBIPrct:O#Import:U1#D0000:U2#P0000:U3#LOCAL_DATA:U4#PJ00000 = Divide($DBICtry, A#411000:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS)") Thanks for your help and I appreciate the help.
Syntax to pull entity description where entity NOT the POV entity
Is there a syntax to lookup the description of an entity that is NOT the pov entity? In the example below the pov_entity is XXXXXXX_Canada, but the entity I want to pull a description from is 00250. I am writing a rule in a ud8 member to try to pull this. Thank you,FdxExecuteCubeView cannot export dynamic calculations in V#Annotation
Hi All, I'm working with FdxExecuteCubeView and cannot export the dynamic calculations in V#Annotation even though I have includeCellTextCols as True. The OS documentation also says that it's possible to extract Dynamic Calc results. In my CubeView I have a dynamic UD8 which returns the value kilogram in this case: Here's the code I have right now: Dim nameValuePairs As New NameValueFormatBuilder(String.Empty,customParams,False) Dim filter As New System.Text.StringBuilder Dim parallelQueryCount As Integer = 8 Dim logStatistics As Boolean = False Dim includeCellTextCols As Boolean = True Dim useStandarFactTableFields As Boolean = False Dim dt As New DataTable dt = BRApi.Import.Data.FdxExecuteCubeView(si, cubeViewFilter, "","","","","", nameValuePairs, includeCellTextCols, useStandarFactTableFields, filter.ToString, parallelQueryCount, logStatistics) Return dt And the resulting data table shows 0.0 instead of kilogram: Am I missing something here? Thanks for the help!How to pull a field from Custom table into a Cube view
Hi Everyone, I have a requirement to pull the data in a particular format in my cube view. I have entity dimension members in a row which gets pulled from the cube. I have to pull the respective opening dates of the stores from the custom table in the cube view as a column. I have created the custom table (with store and opening date) and used a business rule to pull the opening date from the custom table. Now I want to display this in the cube view w.r.t the stores we are pulling in rows. For ex if we have a row with Entity (store) tx0001 in the cube view which has an opening date for the store in the custom table. This opening date needs to be displayed in the column w.r.t to the store in the row. How to achieve this? Thanks, Jeevan
Entity Parent Business Rules not working
I using UD8 to call master data of entity. Other master data is work, but there is issue when I call entity parent. Seem like the business rules is not working. Not sure where the missing part. If api.View.IsAnnotationType() Then ' Dim EntityID As Integer = api.Pov.Entity.MemberId ' 'Return the Account name setting for this text-based View member '' Return api.Entity.GetLocalCurrency(EntityID).Name ' 'Dim ParentId As Integer = api.Pov.Parent.MemberId '' return api.Entity.par ''Return api.Pov.Parent.MemberId.ToString 'Dim objList As = BRApi.Finance.Members.GetParents(si, dimPk, entity, includeRootMemberInResult, dimDisplayOptions) 'Dim oblist2 = BRApi.Finance.Members.GetParents() 'Dim ParentId As Integer = api.Pov.Parent.MemberId ' Dim ParentName As String = api.Members.GetMemberName(DimType.Entity.Id,ParentId) 'Return ParentName 'Else ' 'Return a NoData Datacell ' Return api.Data.CreateDataCellObject(0.0,True, False) End If
