Searching for specific elements of large hierarchies can be a real pain in many Analysis Services client tools, and we often hear of it as a major frustration in Pivot Tables where dialogs can be cumbersome and prone to locking up.
XLCubed has both a Quicksearch and an Advanced search in the Member Selector, but in this blog we’ll show how to link the search dynamically to an Excel cell (or a web entry cell on a published report) and to retain the search as a dynamic part of the report rather than a point in time selection.
Let’s say we are a retailer with a large product hierarchy running to tens or hundreds of thousands of products. The naming convention means groups of products can be searched by a partial match on their name, and as a report designer we’d like the users to be able to type the search in as quickly and easily as possible rather than go into a custom search dialog. Here’s how:
Below is the final result in Excel, a simple list-report where the user just types the text they want to search the hierarchy for, and matching products are shown on the rows of the report.
We start with a regular grid, putting Product Categories on rows, and then in the Member Selector we can either select a specific level or set of data to be searched, or go to the Advanced tab and select the whole hierarchy as shown below.
In the advanced dialog, click on the binoculars:
to add a search, and then in the dialog below you can either type a search term directly in the ‘Search Value’ or reference an Excel cell, in this case $C$3. ‘Search By’ allows you to specify exact match, begins, contains etc.
At this point it’s worth mentioning that while in this case we are just searching by the name of the product (MEMBER_CAPTION) we could also chose to search by any member properties which exist.
So having done that we simply type the search string into $C$3 and we get the matching products straight away – couldn’t be easier.
To make this available for web deployed reports there are two additional steps:
- Make $C$3 available for web input. To do that right click on the cell and choose Format cells, and then on the protection tab uncheck ‘locked’.
- Add a search or refresh hyperlink or button so that the web user can refresh the report when they’ve typed the search term. This can be handled using either XL3Link() or XL3Picturelink and the process is described in our previous blog.
The web version is shown below: