XLCubed is a great tool when it comes to member searching: it allows you to quickly and easily find members as well as dynamically update a report based on search parameters. Take a read of our last post to get started with this functionality.
The example in this post uses these searching methods to create a product search page. The left side provides search functionality for all the products contained in the cube, with a link to select the product which displays the full details on the right side.
Search fields
The core searching functionality is initiated through the search and filter fields:
This consists of
- An empty Excel cell which is ‘unlocked’
- Two XLCubed slicers
- A picture link to trigger the search
Search Results Grid
On a separate sheet is a grid which will be filtered by the values submitted here.
A member search is being applied to the ‘Product’ hierarchy. The search value is the empty cell we created earlier.
The hierarchies in the ‘filters’ section are driven by the two slicers.
Format the grid as desired, hide any unwanted columns, and add a column calculation that will allow users to click a link for any row in order to display the product. This uses an XL3Link which does not link to anywhere, but sets the product name (in $B9 in the below example) to the relevant cell on the main page ($K$2):
=XL3Link(,"...",,XL3Address(Dashboard!$K$2),$B9)
It is also worth setting up a named range for the grid so that we can create a viewport that will display the whole grid no matter how may rows are returned.
Refresh options
You should now have a grid that updates according to the search and filter values. If you wish to have a button that triggers the search like in this example, turn off “Refresh when driving cells change” (and optionally “Refresh when slicers change”) in the grid properties. You will then need to create a XL3 formula (such as an XL3RefreshSheetObjects) that will refresh the grid when the picture link is pressed (the picture link sets a range to TRUE – this range is the trigger for the refresh formula).
Displaying The Selected Product
Selecting one of the XL3Links in the search results grid passes the product name to a cell on the main sheet. The example uses this cell as a parameter for an XL3PropertyLookup formula, but any design could be created here.
This can be a really great way to provide a high level overview and low level detail to a report or dashboard. It also provides interesting opportunities for optimised report design (our example uses different font sizes/weights to indicate importance, icons for quick scanning, and data visuals in the form of a chart) within Excel.