topic RE: About Multiple filter in pivot table in University Forum

About Multiple filter in pivot table

TejashriNevase — Fri, 17 Jun 2022 08:06:00 GMT

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
------------------------------

RE: About Multiple filter in pivot table

michaeloneil — Fri, 17 Jun 2022 17:57:00 GMT

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
------------------------------

RE: About Multiple filter in pivot table

TejashriNevase — Mon, 20 Jun 2022 05:49:00 GMT

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
------------------------------

RE: About Multiple filter in pivot table

michaeloneil — Mon, 20 Jun 2022 08:25:00 GMT

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
------------------------------

RE: About Multiple filter in pivot table

TejashriNevase — Mon, 20 Jun 2022 18:03:00 GMT

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
------------------------------

RE: About Multiple filter in pivot table

devneetmohanty07 — Mon, 20 Jun 2022 20:55:00 GMT

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

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

RE: About Multiple filter in pivot table

TejashriNevase — Tue, 21 Jun 2022 01:39:00 GMT

@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
------------------------------

RE: About Multiple filter in pivot table

devneetmohanty07 — Tue, 21 Jun 2022 03:27:00 GMT

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

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

RE: About Multiple filter in pivot table

TejashriNevase — Tue, 21 Jun 2022 03:29:00 GMT

Hii @devneetmohanty07

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

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

RE: About Multiple filter in pivot table

devneetmohanty07 — Tue, 21 Jun 2022 03:33:00 GMT

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

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

RE: About Multiple filter in pivot table

TejashriNevase — Tue, 21 Jun 2022 03:55:00 GMT

hii @devneetmohanty07
This is code stage i am using. I used the same as you show.

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

RE: About Multiple filter in pivot table

devneetmohanty07 — Tue, 21 Jun 2022 04:04:00 GMT

Can you once show us the original table data that you are using and indicate which column do you need to apply filter on and what column number you are passing in the action as well?

Also, I would like to see the other parameter values as well such as criteria collection and sheet name.

------------------------------
----------------------------------
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

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

RE: About Multiple filter in pivot table

TejashriNevase — Tue, 21 Jun 2022 04:06:00 GMT

Hii @devneetmohanty07
I just change the input name as Column_Name And Pivot_Table_Name as Column Name and Pivot Table Name in your input. All I just follow all your instruction

And also copy your code as same.
I will share criteria collection . as I add field as 'Criteria Name' and add Two criteria.

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

RE: About Multiple filter in pivot table

devneetmohanty07 — Tue, 21 Jun 2022 04:11:00 GMT

Hi @Tejashri Nevase,

Can you please show us the excel file with the original dataset not your pivoted data and also the column number value that you are passing as well with the information that you have shared so far?

------------------------------
----------------------------------
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

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

RE: About Multiple filter in pivot table

TejashriNevase — Tue, 21 Jun 2022 05:54:00 GMT

Hi @devneetmohanty07,

I will work fine with the number criteria.but it throw the error when i pass the character in the criteria collection.
This is working fine.

But when i pass the above criteria collection i got the same error as above.

Why this is happening.? Is there need to change the code for Character?

Thanks

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

RE: About Multiple filter in pivot table

devneetmohanty07 — Tue, 21 Jun 2022 06:10:00 GMT

Hi Tejashri,

I have checked with the characters as well and it works. You are passing the wrong column number most likely and hence this issue is coming up. Check the below results for your reference:

As you can see in my pivot I made a small change where I am taking the Employee Name as a filter this time and passing the appropriate criteria for the same as well. But since I am taking employee name as a filter, my column position will change from 1 to 2 since it is the second column in my original worksheet:

As you see here, my criteria values should consist of the values among the available values for that column in my original dataset for which I wish to put on the filter along with the column position also where it lies exactly. Since, I want to filter on Employee Name and that is the second column in my original dataset. I will provide the column position as 2 and values also will be among the values that are present in that column only.

Like I was saying before, you can show us the original data and the column number that you are passing for me to troubleshoot it for you.

------------------------------
----------------------------------
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

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

RE: About Multiple filter in pivot table

TejashriNevase — Tue, 21 Jun 2022 06:25:00 GMT

Thank You soo much @devneetmohanty07, You solved my problem Actually i am passing the column number as in the pivot table. but we have to pass column number from original data.

Solved .
Thanks a lot.

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

RE: About Multiple filter in pivot table

devneetmohanty07 — Tue, 21 Jun 2022 06:29:00 GMT

Glad it got solved for you Tejashri ! Do let us know in case of any issues or doubts you have in future. We are always happy to help you out.

Hope to see you more in the community and have a great day ahead 🙂