Dynamic expansion based on 2 dimensions, send to property of 3rd while allowing context change of 3rd dimension.

[brett_hutchinson]

Hello:

We are in the process of upgrading to BPC 10.0 NW .

I’m running into a requirement that was fairly easy to accomplish in 7.5, but I don’t know how to do it in version 10. The requirement is to be able to filter data based upon user selections in the context for 3 different dimensions i.e. Entity (Fund Center), Account (Cmmt Item) and Fund (user defined). However, we only want to send data based upon 2 of the dimensions (Fund Center and Cmmt Item), while the 3^rd  (Fund) should be sending data based upon a property of Fund Center. Every Fund Center has only 1 fund and is represented by a property of the fund center, but the fund is also a dimension. The reason we did it this way is because if you allow expansion based upon 3 different dimensions, then we have a lot of possible Fund Center, Cmmt Item and Fund combinations that shouldn’t happen. The only possible combinations should be Fund Center and Cmmt Item while the Fund is always directly related to the Fund Center.

For example, Fund Center 99999-110 has a fund of 110. No other data should ever be saved to any other fund, so it doesn’t make sense to include Fund in the expansion. Using a linked property of the Fund to Fund Center, works great when we send the data. However, if the user wants to restrict which funds are retrieved by altering the context (I’m using epmcontextmember() for this), adjusting the context will not restrict which funds are retrieved since we did a linked property for the row expansion of fund. This means that if a department is wanting to retrieve all of their fund centers, but doesn’t want to see a certain set of funds, then that requirement is not possible doing it this way. Alternatively, as already stated above, using BAS members of fund would allow more combinations than should be allowed. I've tried a few things using axis override as well, but haven't had much success.

We did this in BPC 7.5 by creating 2 EVDREs. One that would expand based upon only 2 dimensions (FC and CI) and the 2^nd EVDRE would be responsible for sending the data to all 3 dimensions by using an EVPRO function to retrieve the fund from the fund center. How can we accomplish this in BPC 10? Please see attachments for illustrations.

Any help is greatly appreciated. Thanks

Edit: I have also thought about hiding the "extra" rows with VBA and using EPMSaveData to save the data to the correct intersections. I would like to avoid those 2 fixes if possible.

[former_member186338 · Accepted solution]

Sorry but not clear...

IF you have fund linked to the property of fund center then you have to provide selection for fund center only...

[former_member186338 · Accepted solution]

IN the linked by property report you can only use dimension override for fund center dimension....

P.S. And all method used in BPC 7.5 evdre reports are not useful for epm reporting...

[brett_hutchinson · Accepted solution]

Thanks Vadim. That is what I'm experiencing and what I was afraid of. Do you know of any other options to accomplish something like this? In 7.5 it was easy to develop, so we used it a lot. 1 EVDRE to retrieve 2 dimension combinations and 1 EVDRE to send to a 3rd row dimension based on a property. The only 2 options I can think of would be to hide the fund center/fund combinations that shouldn't exist with VBA after the refresh OR to create EPMSaveData local members for every inputable column.

Thanks again

Brett

[former_member186338 · Accepted solution]

You have to explain requirements...

If the user want to see only some funds you can first select the funds by

then you can create epmdimensionove for fund center by fund property.

[brett_hutchinson · Accepted solution]

I tried to explain, but it is quite complex to write out. Definitely not my strong suit either. Sorry. The .JPG's on the first post help somewhat I think? At least to see what we have done in the past with 7.5. I have actually already tried the EPMselectmember and then override based upon the selected member.

=EPMAxisOverride("000",TRUE,"FUND_CTR","BAS("&M9&")","CMMT_ITEM",$M$10,"FUND","BAS("&M8&")")

Where M8, M9 and M10 are all EPMselectmember functions.

It works to a certain degree, but if the user un-suppresses the rows, then the result will be like the screen 4.jpg included above where there are fund center and fund combinations that shouldn't happen.

Thanks again.

[former_member186338 · Accepted solution]

Please read the discussion provided in my previous answer.

It's possible to select by user number of funds and to create the formula to override the fund center dimension using fund property. Do you understand the idea?

P.S. NO reason to use epmaxisoverride...

P.P.S. Play with epmdimensionoverride for FUND_CENTER with filter by property like FUND=F1,FUND=F2,FUND=F3

[Former Member · Accepted solution]

Hi Brett,

Couple of questions.....

1.) what is the reason of giving FUND as a context selection to user? Functional understanding...I can see ALL_FUNDS in one of the screenshots.......Please explain if he selects one fund center from context and multiple then what should happen?(confused with that) since I understand fund expansion based on linked to property is working fine for you.....

2.) Are the members count of Fund dimension and the fund property equal?? or some plus minus...

3.) Is your this report acting as a input as well as a report?

4.) Explain this point....

This means that if a department is wanting to retrieve all of their fund centers, but doesn’t want to see a certain set of funds, then that requirement is not possible doing it this way.

5. Fund center and Commt Item are context selection? Single or multiple?

P.S explanation with a simple screenshot would be better....

Regards,

JP

[brett_hutchinson · Accepted solution]

Thanks for the reply!

1) So, I'll try to give some background. We are a public sector organization where different funds are unique expenditure/revenue sources. For example, we have a set of general funds that is paid for by tax payers. We also have a set of grant funds that are federal/state grants that we can apply for and get if we are approved for those grants. So, let's say a user wants to enter in a set of % indices that will determine year of year growth/loss for our financial plan and all of the general funds will be at 1 rate and the grant funds will be at a different rate. Allowing them a way to filter on the fund is an easy way for them to plan indices quickly. Providing the user with an easy way to filter on fund was requested as a requirement in 7.5 and has been working this way for 3 years. It is also important to note that we have a "filter blanks" checkbox that is checked by default to eliminate intersections that are empty. However, we have to allow users that ability to keep those empty rows (by un-checking the box), so that if a new fund center comes along, they can enter in data for that new fund center.

2) Each Fund Center can only have 1 Fund. Each Fund can have multiple Fund Centers.

3) This input template and others like it do have some reporting in them as well.

4) Related to numbers 1 and 2 above, a user may want to see all of the Fund Centers in their department (department is just a parent level in one of the Fund Center hierarchies), but as mentioned before, they only want to see grant funds (which is a parent node in the Fund hierarchy). So the user may select Grants as the fund selection and their department as the Fund Center selection. That would retrieve a series of Fund Centers that are restricted to only a certain set of funds. Please see the attached screen shots for more details. Along with what is in the screen shots, I have played around with EPMAxisOverride and EPMDimensionOverride with little success, although, I will be exploring the latter in more detail as Vadim has suggested.

5) For this report, users can only select 1 commitment item at a time, but for others, users can select single or multiple according to the hierarchies available. For Fund Center and Fund, users can select single or multiple according to the hierarchies that are provided.

Thanks again!

Brett

[brett_hutchinson · Accepted solution]

Thanks Vadim. Sorry. I misread your EPMdimensionaxis post earlier. I failed to menation that I have also tried the dimension override as well, but will explore that some more today. I'm fairly new to BPC 10, so I may just have some incorrect syntax going on. Although, axis and dimension override seem very similar to me, so I'm not sure what the difference is other than you can do multiple overrides with axis? Also, please see my reply to JP. I hope I have provided more detail.

Thanks again

[former_member186338 · Accepted solution]

Sorry, but you don't understand me. Due to the property relation between fund and fund center (fund property) don't do anything with fund dimension. It has to be linked to fund center property and nothing more.

You have to filter fund center dimension using epmdimensionoverride and use property in the filter condition.

Is it clear?

[brett_hutchinson · Accepted solution]

Yes. It is clear. I am trying to use the fund property of Fund Center for the dimension override of Fund Center. I'll let you know how it goes.

Thanks

[brett_hutchinson · Accepted solution]

Hey Vadim:

Here is the syntax I am using and please also refer to the new .jpg for a visual (screen 7). It works fine if I only select 1 fund, but the requirement is to allow the user to select multiple funds from the epmcontextmember function via a hierarchy. Please see screen 5 in in my reply to JP for a view of the fund hierarchy. When selecting a parent node of the fund, no data is retrieved because there are no fund centers with a fund property that equals a parent node of fund (only base members). Is there some further syntax that I could use, so that users could still select multiple funds this way? Is there a way to grab a list of base members from a known parent node? I could than concatenate a long string together for each fund that should be included. If that is possible, is there a limit to the number of members you can pass?

=EPMDimensionOverride("000","FUND_CTR","BAS("&O9&") AND FUND="&O8&"")

This syntax below works fine and selecting a base member using epmmembercontext for the fund works fine with the above formula.

=EPMDimensionOverride("000","FUND_CTR","BAS("&O9&") AND FUND=SC01110")

Thanks

Brett

[former_member186338 · Accepted solution]

Please search the forum there are a lot of examples.

" Is there a way to grab a list of base members from a known parent node?" - no easy way, you can create another epm report to perform expansion in row axis of base members of some parent. Then using local member you can concatenate string....

[brett_hutchinson · Accepted solution]

I would not post here without searching the forums first. I actually used contracted time with a consultant before posting here to ask this very question as well and we found no resolution.

I also found that link as well, but only found 1 link where someone asked if you could retrieve all children from a parent node (it didn't help me). The link you included above is why I alluded to a solution that involves retrieving a list of the bas members of the fund parent node that the user selects. Using that list I could then concatenate as many funds together as are needed to retrieve the funds that should be included. I actually tried a small test with this (formula below) and again, everything works fine until you un-filter the blanks (which was turning on and off suppression in 7.5). I do this now with the below VBA code.

What I found is that this issue will not simply be solved by using multiple properties. When I un-filter the blanks, I again get combinations of funds and fund centers that should not exist. You can refer to screens 4 and 6 if you need to see what the output looks like.

I guess I'm going to go with removing fund from the rows and adding local members to use with EPMSaveData formulas. Thanks for all of the replies.

Tested formula with multiple funds (09 is parent node of fund center hierarchy and 08 is single fund).

=EPMDimensionOverride("000","FUND_CTR","BAS("&O9&") AND FUND="&O8&" OR FUND=SC01110")

VBA - blank_check is a named range for the cell link of the filter checkbox.

     If Range("BLANK_CHECK").Value = True Then

        FILTER.SetSheetOption ThisWorkbook.Worksheets("Forecast Planning"), 7, True

    Else

        FILTER.SetSheetOption ThisWorkbook.Worksheets("Forecast Planning"), 7, False

    End If

[former_member186338 · Accepted solution]

=EPMDimensionOverride("000","FUND_CTR","BAS("&O9&") AND FUND="&O8&" OR FUND=SC01110")

Incorrect formula - first condition:

BAS() AND FUND=something

second condition - only

FUND=SC01110

Try something like:

BAS(...);FUND=something,BAS(...);FUND=SC01110

P.S. Look on the example 5 in the provided link

[brett_hutchinson · Accepted solution]

The output is still the same when un-suppressing empty rows. Every row in the attached screen would be an invalid entry if the user were to send data to any of those intersections. The only intersection that would be valid for fund center 38001-274 is fund 274 (not 236,238,239,231, etc.), which is what should be retrieved. Output is in screen 8.

Thanks

Brett

[brett_hutchinson · Accepted solution]

If I can eventually find the right combination to get the output to work right when keeping the empty rows/un-suppressing, do you know of a way to retrieve a list of base members based upon a given parent node? So, that when they select a parent node, I can concatenate the needed funds into this dimension override formula. I suspect I'm just not doing something right with the syntax in the dimension override and will figure it out eventually, but it will be all for naught if I can't retrieve the list of base members based upon a parent node in the "select fund" box anyway.

Thanks for all the help.

[former_member186338 · Accepted solution]

Sorry, but what do you have for fund dimension? Has to be property relation to fund center!

[brett_hutchinson · Accepted solution]

With your help, I was able to get the filtering part to work correctly yesterday. I'm on to writing some VBA now that will get me all the members of the chosen hierarchy node. I am currently using a combination of the GetHierarchyMembers VBA function and the GetPropertyValue function to retrieve a list of funds, if the current member in the loop is not a calculated member. Then I will create some logic to concatenate all of the funds I need together in the override formula.

I have 2 concerns:

1) Is there an easier way to grab all of the base members of a given hierarchy? I didn't see an EPM function for this. I looked through all of the VBA functions as well and didn't see anything specific, but could be missing something. Currently, I would have to loop through each level in the hierarchy if the user chooses the top node to eventually get to the base members. There are only 112 master data funds (including nodes), but there are 7 levels in some instances in the hierarchy.

2) If the user chooses a node that is higher up in the hierarchy (like ALL_FUNDS), there will be 81 base members in the hierarchy that will have to be concatenated together in the dimensionoverride. Is that even possible?

Thanks

Brett

[former_member186338 · Accepted solution]

1. Try second epm report  with expansion of parent to base member. VBA can also do the job but it's not simple

2. Try yourself to find the limit.

[brett_hutchinson · Accepted solution]

Thanks!

I tried something different because I didn't want to write loops into multiple hierarchy levels. So, I re-purposed an old property of the fund that I wasn't using anymore and created a code for each fund there that corresponds to how the hierarchy flows. It works in most cases, but since we have 2 hierarchies and users can select from either hierarchy, there are some exceptions I have to account for.

After that I used EPMDimensionPropertyValues to retrieve all of the Fund IDs in my report and then just created a small reference table where I used EPMMemberProperty to retrieve the properties I need (see screen shot). Then I wrote some VBA that finds any base level members with the correct property requirements and concatenates them to a final string that is used in the dimension override.

I still need to account for a few specific funds that can't have a code for the property, but that should be fine. I also need to account for higher level hierarchy parent nodes, but I can do that using the same property. Below is the code.

Thanks for all of the help. When I get the maximum selection of ALL_FUNDS working, I will post again to see if I reached any kind of limit.

Public Sub GetChildren()

    Dim FINDCH As New FPMXLClient.EPMAddInAutomation

    Dim FindCALC As Range

    Dim CALCCol As String

    Dim FindCODE As Range

    Dim CODECol As String

    Dim CALCCell As String

    Dim CODECell As String

    Dim lmember As String

    Dim lprop As String

    Dim lfund As Range

    Dim lcurrow As String

    Dim lfinal As String

    Dim linit As Integer

    Set FindCALC = Rows(44).Find(what:="CALC", LookIn:=xlValues, lookat:=xlWhole)

    CALCCol = Left(FindCALC.Address(False, False), 1 + -1 * (FindCALC.Column > 26))

    Set FindCODE = Rows(44).Find(what:="CODE", LookIn:=xlValues, lookat:=xlWhole)

    CODECol = Left(FindCODE.Address(False, False), 1 + -1 * (FindCODE.Column > 26))

    linit = 1

    If Range("FUND_CALC").Value = "N" Then

        Range("SINGLE_FUND").Value = Range("FUND").Value

        Range("OUTPUT").Value = Range("FUND").Value

    Else

        lmember = Range("FUND").Value

        lprop = FINDCH.GetPropertyValue("FIN_PLAN - BPC_BUDGET_LTFP", lmember, "OWNER")

        For Each lfund In Range("FUND_LIST")

            lcurrow = lfund.Row

            CALCCell = CALCCol & lcurrow

            CODECell = CODECol & lcurrow

            If IsError(Range(CALCCell).Value) Then

                Exit For

            End If

            If IsError(Range(CODECell).Value) Then

                Exit For

            End If

            If Range(CALCCell).Value = "N" And Range(CODECell).Value = lprop Then

                If linit = 1 Then

                    lfinal = "BAS(" & Range("FUND_CTR").Value & ") AND FUND=" & lfund

                Else

                    lfinal = lfinal & ",BAS(" & Range("FUND_CTR").Value & ") AND FUND=" & lfund

                End If

                linit = linit + 1

            End If

        Next lfund

        Range("MULTI_OUTPUT").Value = lfinal

    End If

End Sub