I have a pivot table , now i want to add a functionality in table like global search in table, user write something in text box and on table get filter out and only return those row where particular keyword is matched in row.
Is there trick /hacks that can help me here?
Hi @mohitkaushik1, does creating a parameter control and use that parameter to filter the pivot row field achieve your goal?
- Create a string parameter
- Add a text field control for the parameter
- Create filter for the pivot row field(s)
- Set filter type and filter condition to custom filter and contains respectively, and associate the parameter from #1.
If you don’t want the filtering to affect the aggregation inside pivot table, you need to use calculated field to create new pivot row field and use that for filtering so the filtering applies post aggregation phase.
Hii ytakahr, i need a global search that search in whole table (in columns name,row name ,in cell) or we cam say in each and every cell of pivot table .Not seach in particular field.
Thanks for the clarification @mohitkaushik1.
I have an idea. I know it doesn’t fully satisfy your needs, but at least I want to share with you.
For the ease of explanation, let me start off with a table visual instead of pivot.
You can use Concat function to concatenate all the fields into one long string, and use that for a filter linked to a parameter control. This way, user can search across all the fields for a specific word. For example, in the following image, I put “colorado” in the search box and the rows containing “colorado” in any of its fields are returned. (Not just the state column)
Please note that “contains” filter is case sensitive, so I created two calculated fields: one that concatenates in original case (yellow above) and the other that concatenates in lower case using toLower function (blue above). I used these calculated fields in “contains” filters respectively and combined in OR condition so the search can be less case sensitive.
The calculated fields are placed inside the table for clarity, but they don’t have to be there for the filter to work.
There is one limitation, however. You can only search across the cells, but not the table header as the filter only works for the field data.
Now, back to your original request - pivot. Since pivot organizes the rows and columns in hierarchy, it is less intuitive than a table in its look, but still the records that contains the search key word is returned. For example, the following pivot is not just looking up the state field but across the entire fields and showing matching records only.
Unlike table, it is hard to understand which rows or columns are matching the search so it can create confusion to users. So I’m now sure if this is what you want, but at least this is the idea from technical standpoint.
Thanks ytakahr it helps me .