Reload data into excel with user's filtering input

[Former Member]

Hi All,

I am using BPC 10 and I have manage to load the data into an excel sheet.

What I am trying to do now is allow user to filter the result based on user's input e.g. user can select a category then input the value and it will filter accordingly.

What I have in mind is reload the data based on user input through VBA API I have tried all the API available in the BPC guide but didn't manage to find any suitable API.

Anyone have any idea how I should approach this filtering ??

Regards,

[former_member186498 · Accepted solution]

Hi,

maybe I don't understand. but if you want to filter some dimension (entity, costcenter, etc.) differently for a single user or team than it's better to create a specific "member access profiles" that allow to filter it.

So every user or team related with here profile can see and manage the desired data.

To do this go to admin console/security/member acces profile and add a new m.a.p. specifying the filter (f.e. a single entity, or all but one or two denied ...)

This activity are all inside BPC and you don't need VBA code.

Kind regards

Roberto

[Former Member · Accepted solution]

Hi Roberto,

Sorry for the confusion.

This solution is for all user.

Just a example for what I mean user can select the filtering.

When the data is loaded into excel sheet, User will see too many data.

What I trying to do is allow user to filtering the data based only on data they wanted.

e.g

Company a        10
Company b        10
Company a        10
Company b        10
Company a        10
Company b        10
Company a        10
Company a        10

User just wants to see only company a. User can select company a from EXCEL UserForm and just click filter button.

and BPC will reload the data into EXCEL sheet with only company a.

Result

Company a        10
Company a        10
Company a        10
Company a        10
Company a        10

Hope I make it clear this time.

Regards,

Kang

[former_member186498 · Accepted solution]

Hi Kang,

you can put the company as parameter in the report/input schedule giving the possibility to select the company with a EVCVW and connecting this cell with the company in the pagekeyrange area, after the expand all/refresh (depends if dynamic or not) you will see the data filtered by the company.

Kind regards

Roberto

[Former Member · Accepted solution]

Hi Roberto,

Edit** EVCVW = EPMContextMember

I manage to put in a pagekeyrange and EPMContextMember into different cell.

Can you please explain more about connecting the cell with company in the pagekeyrange area.

Correct me if I am mistaken, so the idea is to change all the company in the pagekeyrange area into the selected company and refresh the excel so BPC will load data just on the selected company ?

Regards,

Kang

Edited by: HK Kang on Jan 10, 2012 4:53 AM

[former_member186498 · Accepted solution]

Hi Kang,

suppose that your dimension that contains company is named entity, give a name to the parameter, f.e. CV_Entity and put inside the cell EVCVW(application, entity) and in the area referred from the pagekeyrange substitue the value of the entity dimension with "=CV_Entity", so when you change the entity (company a or b) even the dimension value will change and the data after refresh/expand all will change accordingly.

Kind regards

Roberto

[Former Member · Accepted solution]

Hi Roberto,

Just to clear up some confusion.

What you mean is put a parameter for my dimension entity eg. company = CV_Entity ?

Then put the parameter into EVCVW E.G. =EVCVW(application,CV_Entity) ?

then put the value of the entity dimension with "=CV_Entity" eg. =EVRNG(=CV_Entity) ?

So if for BPC 10,

it would looked like this

company = CV_Entity

=EPMContextMember(,CV_Entity) ?

=EPMCellRanges(=CV_Entity) ?

Regards,

Kang

[former_member186498 · Accepted solution]

Hi Kang,

sorry but I don't know the BPC 10 so if it's different you must translate from 7.5 (f.e. i don't know "EPMContextMember").

What you mean is put a parameter for my dimension entity eg. company = CV_Entity ?

no, give a name on the cell that contain the EV_CVW

Then put the parameter into EVCVW E.G. =EVCVW(application,CV_Entity) ?

no, EVCVW(application,=link to the entity dim (the name entity) in the pagekeyrange area)

then put the value of the entity dimension with "=CV_Entity" eg. =EVRNG(=CV_Entity) ?

no, put in the entity value cell of the pagekeyrange area the string "=CV_Entity", so the entity value will change automatically when you change the parameter.

Kind regards

Roberto

Edited by: Roberto Vidotti on Jan 10, 2012 11:47 AM

[Former Member · Accepted solution]

Hi Roberto,

All the suggestion is formula based.

Is it possible to execute the formula through a VBA button ?

What I have in mind is:

After user select a company, the filtering would happen after user click on a VBA button(filter button)

or

User click on VBA button(filter button) then the Member selector form would show up.

Regards,

Kang