EPM Report, filter by Local Member

[Former Member]

Hi Experts,

Is there any nice way to filter the report based on LocalMember?

Thanks.

Regards,

[anil_agrawal · Accepted solution]

Hi Pheno,

Can you elaborate your requirement ?

Thanks

Anil

[Former Member]

You can use Excel to filter on those results post refresh with a macro, but the EPM doesn't have a local member filtering.

here is example code that will automatically execute after a refresh:

SUB AFTER_REFRESH()

     'This macro executes after every refresh by default
     'insert code to apply or reset excel filter


END SUB

[Former Member]

Hello,

can you please share the code as example, so I can do it?

The VBA is not my strongest skill.

Thanks.

Regards

[former_member186338]

Hi Pheno,

Without good VBA knowledge I do not recommend you to go this way... May be you can create some property for sorting instead of local member etc...

Vadim

[Former Member]

Hi,

I will put my effort to do it.

Regards,

[former_member186338]

The issue is that Excel sorting applied to EPM report has a lot of side effects. Try to refresh it even without writing any VBA code!

Vadim

[Former Member]

While I agree with Vadim you should not transform EPM results, hiding a row is lower risk though should be extensively tested prior to wide usage.  Here is the code I would leverage:

Sub AFTER_REFRESH()
For Each Cell in Range("A1:A99") 'range of local member may vary if report expands
       If (cell.value = 999) Then 'or some condition there of
            Cell.EntireRow.Hidden = False 'if condition is met show row
       Else
            Cell.EntireRow.Hidden = True  'if condition is not met hide row
        End If
Next Cell
End Sub

[former_member186338]

Hi Ryan,

I have made a mistake - thinking about sorting, not filtering. But even with filtering by hiding rows you have to use more intelligent methods to find the last row of the refreshed report...

Vadim