Member Searching To Create A Product Details Page

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.

A product details page in Excel with the ability to search for and select a product

 

Search fields

The core searching functionality is initiated through the search and filter fields:

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

Search results grid

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.

Grid designer

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.

Grid named range

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.

Product details

 

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.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.