About Multiple filter in pivot table

[TejashriNevase]

Hii All,

I have to add multiple criteria to page field filter filters in the pivot table for that i am using Excel Extended VBO.
When i used multiple action it replace first criteria and add the new filter for pivot.
So how we can add multiple criteria to pivot filter field?

Thanks in advance.

------------------------------
Tejashri Nevase
------------------------------

[michaeloneil]

Hi Tejashiri

I think the code you are using might look a bit like this ws.PivotTables(Pivot_table_name).PivotFields(Pivot_field).clearall if you remove the last part .clearall it should work for you.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[TejashriNevase]

Hii Michael,

Actually i am using excel extended VBO not any code stage. So could you explain more.
I want to add multiple filters.
I am sharing a image in that I am adding 0 and 11 as filter so how can add this multiple items to filter throught blue prism using excel extended VBO.

------------------------------
Tejashri Nevase
------------------------------

[michaeloneil]

Hi

When I say code stage I mean within the vbo you are using. If you open the excel extended vbo and go to the action you are using to set the filter you will see the code stage for this. I think the issue is in here in that each time you run the filter action it first clears all filters and then applies the new one. To apply multiple filters I think you would need to update this stage so its not clearing filters. You could make a duplicate of the action so you aren't impacting anyone else using it and then apply the change to the new action. 



------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[TejashriNevase]

Hii Miechal, 

Actually i already apply the filter for pivot table but i want to select the multiple item in that filter .i am shared the screenshot above. I want to just add multiple item to that filter so how can i add that multiple item? 
When i am using the add criteria to page filed it add one criteria means it will select the one item .when I again add same action with different criteria it ovrride the first but i want to add multiple item so there is other way to add multiple item?

please help...

Thank you soo much.



------------------------------
Tejashri Nevase
------------------------------

[devneetmohanty07]

Hi @Tejashri Nevase,

You can achieve this functionality in the current by extending either 'MS Excel' or 'MS Excel - Extended' VBO by creating a new action within the object.

NOTE: I would recommend create a duplicate object of these objects and make your changes there so that you always have a backup with you in case anything goes wrong.

Solution Explanation:

Now, you need to create a new action in your already existing Excel VBO business object named 'Add Multiple Filters To Pivot Table Field' and provide the following input parameters to your action:

Handle : This is a number type data item which will hold the current session dictionary value.

Workbook : This is a text type data item where you need to pass the workbook name that you might get from 'Create Workbook' or 'Open Workbook' action.

Worksheet: This is a text type data item which indicates the name of the worksheet in your excel workbook file where the pivot table exists.

Column Number: This is a number type data item which indicates the column number in your original dataset from where you have created the Pivot table. The column number begins from 1 to N.

Pivot Table Name : This is a text type data item which indicates the name of the Pivot Table.

Criteria : This is a collection type data item which consists of a single column called as 'Criteria Name' of text data type and this column will consists of all the filter values that you need to have in your page filter in each individual row.




Now, you can add a code stage named 'Add Multiple Filters To Pivot Table Field' and add the following inputs parameter and map the data items to it:


Code:

Dim ws As Object
Dim lstAppliedFilters As List(Of String)

lstAppliedFilters = New List(Of String)()

ws = GetWorksheet(Handle, Workbook, Worksheet)

ws.Activate()

ws.PivotTables(Pivot_Table_Name).PivotFields(Column_Number).EnableMultiplePageItems = True

For Each criteriaItem In Criteria.Rows
	

	For Each pivotFieldItem In ws.PivotTables(Pivot_Table_Name).PivotFields(Column_Number).PivotItems

		If (Not criteriaItem(0).ToString.Equals(pivotFieldItem.Name)) Then 

			If (Not lstAppliedFilters.Contains(pivotFieldItem.Name)) Then

				pivotFieldItem.Visible = False

			End If

		Else
		
			If criteriaItem(0).ToString.Equals(pivotFieldItem.Name) Then

				pivotFieldItem.Visible = True
				lstAppliedFilters.Add(criteriaItem(0).ToString)

			End If

		End If

	Next

Next

You should have your workflow ready as shown below:




Testing For Solution:

Now, publish your action and then use all the actions of the same business object where you have done the changes in your process studio while interacting with the excel file in order to avoid any exceptions. I have a sample process studio workflow shown below:



So just to explain you a sample scenario which I have picked up, I basically have an excel file with below original table data:


I have also created a pivot table with the name, 'PivotTable1' before hand from start in a separate sheet called as 'Pivot' as shown below:



So what I want here is to add multiple filter values for the employee ID field (which is the column number: '1' in my original dataset) which would be 2,8 and 9 respectively. Hence, I am creating an excel instance, then opening my excel file, then making it visible on the screen and then I use my new created action which has the below parameters:




Post execution of the workflow I get the below results:



So here as you can see you can provide as many values as you want in different rows of your criteria collection with appropriate column number and pivot table name to have multiple filter criteria selected. ​

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[TejashriNevase]

@devneetmohanty07
Thanks you for your solution But i have doubt in your proces why you are used multicalc stage there in your process as set parameter. can you just just share the screenshot of your multicalc stage.

and your response really help me in my project. Thank you very much!​

------------------------------
Tejashri Nevase
------------------------------

[devneetmohanty07]

Hi @Tejashri Nevase

I have used multi calculation stage in order to set up my file path and sheet name which I am going to use in my actions going forward. This you can also maintain in an environment variable or a configuration file as well or simply hardcode the values.



​As you can see the file path of my excel file and the sheet name that I have set are then just simply being passed over to the below actions in form of a data item:



------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[TejashriNevase]

Hii @devneetmohanty07

I am tried your solution but i got the below error. what is reason for that

​

------------------------------
Tejashri Nevase
------------------------------

[devneetmohanty07]

Hi @Tejashri Nevase,

Can you show me the code stage once, which you are using?​ This issue will generally appears in the highlighted lines:



One reason can be if you are not passing the right filter value in that criteria collection. By that I mean if a value does not exists in your excel worksheet then you will get this error and the other reason can be with the code that you are using.

Also. check the column number here. If that has been provided correctly or not?


------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------