Skip to Content
avatar image
Former Member

Apply Filter VBA to EPM report

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Mar 14, 2017 at 10:39 PM

    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
    Add comment
    10|10000 characters needed characters exceeded

  • Mar 14, 2017 at 08:16 PM

    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!

    Add comment
    10|10000 characters needed characters exceeded