Apply Filter VBA to EPM report

[Former Member]

Hi Experts,

I am trying to apply filter through VBA in one of the EPM reports to make the filter dynamic.

Below is the syntax to call ApplyFilter (one of the functions available in FPMXLClient library)

ApplyFilter(reportID As String, xmlFilter As String)

System is issuing an error when "Fiscal Year 2016 > 10000" is passed to xmlFilter parameter. It appears that value for parameter needs to be passed in XML format. Any help on how to pass value to xmlFilter is appreciated. (Note: 'Fiscal Year 2016' is column in EPM report)

Regards

Monisha

[former_member186338]

For example you have a report on Sheet1 with single column Mar-16

And you have defined the simple filter to show only rows with the value >100

And you want to change it from 100 to 23471 using VBA:

Dim epm As New FPMXLClient.EPMAddInAutomation

Public Sub ChangeFilter()
Dim filter As FPMXLClient.Filtering
'Read current filter
Set filter = epm.GetDataFilteringInfo(ThisWorkbook.Worksheets("Sheet1"), "000")
'Change ">" value
filter.Criterias()(0).Value = CDbl(23471) 'CDbl conversion required!
'Update filter
epm.SetDataFilteringInfo ThisWorkbook.Worksheets("Sheet1"), "000", filter
'Refresh Worksheet to apply the changed filter
epm.RefreshActiveSheet
End Sub

[Former Member]

Vadim,

Thank you so much for this example.Here I am trying to change the member name from 2016.YR to 2017.YR,so tried applying the above code for the member name as well,but for some reason it is not getting applied to the filter.Below is the screen shot of it, can you please tell why the code is not working for the Member names.

Regards

Monisha

[former_member186338]

Result of undocumented methods usage 🙂

[former_member186338]

First: ApplyFilter is not documented API function!

Second: What do you want to achieve with this function? Please describe what do you want to filter!

[Former Member]

I have a simple report that looks as follows. I could set filter on report to only display rows exceeding 10,000 by using filter functionality in EPM editor but instead I would like to be able to set this filter dynamically using VBA using ApplyFilter function so that report would work without need for modification when column displays data for 2017. This function is documented in Object Browser of VBA with no example or sample code. Therefore hoping someone in this forum might know how to pass parameters to this function. Thanks in advance.

Regards

Monisha

[former_member186338]

Sorry, but "This function is documented in Object Browser of VBA with no example or sample code." - means - NOT DOCUMENTED! In the Object Browser you can see only declaration of this function (and for many other technical functions).

Documented - means described in EPM help API section 🙂

And I am not sure that ApplyFilter is doing what you want!

Actually 2 other undocumented functions are used to work with data filtering!

[Former Member]

Can you please tell me what the other 2 undocumented functions used to work with data filtering, with an example.

Regards

Monisha