Tag Archives: Dashboard

Report Flexibility, with Control

Sometimes we want to let report users modify the structure of a report but to govern exactly what they can and can’t do. While Grids can be restricted at a granular level to enable and disable functionality, that approach still requires some degree of product knowledge by the user.

XLCubed provides the XL3SetProperty() formula, which enables manipulation of many of the core objects such as Grids, Slicers and Small Multiples. It means report users can have simple slicer selections to change the structure of a report, what’s being displayed in a chart, or to vary the chart type. It gives flexibility within the report, but requires no product knowledge from the end user which can be crucial when delivering web reports on a widespread basis.

One common example of usage is where the hierarchy to be viewed in a grid needs to change based on the measure a user selects (depending on the structure of the cube some measures may not be applicable for all hierarchies). Typically that would need to be handled in two Grids, but we can use XL3SetProperty to bring this together, and also to give user choice on the associated Small Multiple Chart view.

The final published report is shown below:

 

S1

 

If the user selects an “Internet” measure, we show Customer Geography on rows, whereas a “Reseller” measure should show Reseller Type on rows. The same logic applies to the Small Multiple chart. In the screenshot below, the user has selected Reseller Gross Profit as the measure, and ‘Stacked Column’ as the chart type. You can see that the hierarchy on rows has been switched, as has the split within the individual charts, allowing the user to easily vary their view of the data with simple button selectors.

 

S2

 

This is implemented through the following key points:

  • A lookup table in Excel to determine what hierarchy is applicable for each measure
  • An Excel list showing the available chart types – this is used in the Chart Type slicer
    • The chart slicer outputs its selection into cell $AG$10
  • The measure slicer is linked directly to the grid and the small multiple, but also outputs its selection to an Excel cell ($A$B4)
  • A vlookup determines which hierarchy to use based on the selected measure
  • Three XL3SetProperty() formulae now control what is displayed based on user selections:
    • $AB$7 – sets the grid rows
    • $AB$8 – sets the small multiple columns
    • $AB$7 – sets the chart type

 

Formulae

 

The approach gives a deep level of access to the key XLCubed reporting objects, and enables controlled flexibility within web and mobile-delivered reports. No programming is needed, just a mid-level understanding of Excel itself, and XLCubed.

This is just one example of what the approach can achieve – it’s really limited only by imagination. See XL3SetProperty() for more detail, or contact us if you’d like the example workbook.

Finding a needle in a haystack – Member Searching made easy!

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.

Search1

 

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.

EditHierarchy

 

 

In the advanced dialog, click on the binoculars:

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.

Search2

 

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:

Websearch

XLCubed as an alternative to ProClarity

With the launch of 7.1 of XLCubed Excel Edition we introduced the ability to import ProClarity Briefing Books – with support for ProClarity ending this year and many customers looking for a replacement, now is a great time for us to show you how the import works to help move users from ProClarity to an alternate solution.

Importing

Let’s start with importing from ProClarity, we’ve built a simple example briefing book based on the usual AdventureWorks sample cube, it includes a sample grid:

 

a performance map:

 

and a chart:

 

To get to the import option we load Excel and select XLCubed -> Extras -> Import -> Import ProClarity Briefing book. After selecting the file to import we are given a summary of each item that is going to be imported:

 

At this point you can control the resulting worksheet name, as well as switching the type of XLCubed object you’ll end up with. Clicking “Import” will now give us 1 sheet for each briefing page:

 

You’ll notice that the import process has created any required slicers so the report is good to go. You could now also spend a bit more time adding any extra XLCubed functionality to the report such as Incell charts or Excel calculations to leverage the power of Excel or publish to XLCubedWeb for consumption by a wider audience.

The import process is very straight forward and we have some great feedback from our customers regarding the speed and ease that they have been able to migrate users’ reports into XLCubed.

Look out for some more blogs showing other features of XLCubed that will help users transition from ProClarity!