In this post we will walk through how to use XLCubed’s dashboard sheets to create a responsive dashboard within Excel, that will work on any device!
Want to view your Excel report on a mobile device? Tired of having to zoom in on your data? Well then look no further, XLCubed has you covered!
With the XLCubed Mobile App, you can take your Excel reports with you. Workbooks published to XLCubed Web can be accessed by other users on any mobile or tablet device. Furthermore, with XLCubed’s dynamic dashboards, you can optimise the display for any size screen. This eliminates unusable cut-off screens, zoomed-out views, and large amounts of scrolling.
Creating a mobile accessible report from an existing Excel spreadsheet is easy to do. Here I will show you how quickly it can be done.Continue reading
I have been working with XLCubed for approximately two weeks now and when I first started, I wasn’t sure what to expect. I had come from a Mathematics background and so had no in-depth technical IT background.
Over the last two weeks, my main priority has been to get to know XLCubed like the back of my hand. At first, I was advised to run through all the XLCubed YouTube Tutorials and I must admit, it was a nice way to start learning this brand-new software. The videos were not complicated and gave a good introduction to all the different features. While watching the videos, I would try and recreate some of the things shown in the video such as grids and slicers, etc. and so was able to get a good feel for the software. However, the videos alone were not enough to get a full grasp of XLCubed and this was where the Partner Packs came in very useful. These are sample reports we provide for our partner network, and the steps to build them are documented.
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.
Dashboard cards are a great way to group related data and make key information more accessible. The flexibility of Excel and XLCubed means there are a number of ways you might choose the incorporate a card-based design into your dashboards. In this blog we will consider one way to do this using XLCubed dashboard sheets and paged viewports.
Ever wondered how Santa Claus keeps track of the North Pole’s toy production all year round? He uses XLCubed of course! Continue reading “Christmas Dashboard”
Many would believe that Excel is a static environment and not suited for making responsive mobile reports. XLCubed users know better. With XLCubed’s Dashboard Designer, it is possible to create dashboards that are not only interactive and data-connected, but also responsive to fit any device.
Slicers are normally used to change filter selections in a report, but a less well-known use is for report navigation, to allow users to jump to another location in the workbook.
Dashboard sheets were introduced with V9, primarily as a way to deliver mobile-friendly reporting with a responsive UI to auto-fit any screen size. Specific Targets which define the layout can be defined to optimise the layout for different devices and are automatically applied depending on the device type.
Another use-case which is less obvious but can also be very useful is to allow users to choose between a number of predefined layouts.
For example, on a specific report there may be just 3 or 4 slicers which are typically used, but occasionally users may need access to a much larger list of slicers to filter by. It would be a shame to clutter the report for everyone permanently with all the slicers as it makes the selection process less intuitive, and probably forces us to use only combo boxes to save space. Ideally we’d like users to be able to switch from a ‘Quick Slicer’ view to an ‘All slicer’ view.
Another example would be where users want to include additional dashboard items, or remove items to get a larger view of a data table.
These scenarios and others can be handled by giving users control over which Dashboard Target is active via a slicer.
In the example shown below the button slicer allows switching between a ‘Quick slicer’ view with the 3 primary slicers shown as list boxes, an ‘All Slicer’ view with all 9 slicers available as combo boxes, and a ‘Table View’ which maximises the space for the data table and removes the charts.
So how do I…?
Firstly, you’ll need to define the various Targets which you want the user to choose between (see here for the details).
Next you need to add a slicer allowing the user to select between Target layouts. This slicer will be based on an Excel range, and will output its selection to another cell which you specify. It’s easiest if the input range for the slicer exactly matches your Dashboard Target Names (otherwise you can use vlookups to cross-match). Of course you’ll need to enable that slicer on each of the targets to allow the users to switch views.
Finally, we can use the XL3SetProperty() formula to set the active Target for the dashboard, based on the output of the slicer we just set up. The syntax is:
XL3SetProperty(Object Type, Object Name, Property to set, value to set the property to)
The screenshot above shows the slicer and formula setup – hope it proves useful for some of you!
Version 9 introduced an embedded icon library and XL3PictureLink, which together make the creation of icon-led navigation and filtering simple.
XL3PictureLink provides the same parameterised navigation capabilities which the XL3Link() formula has done for years, but with an added visual aspect. Users can choose from one of the thousands of icons provided, in any colour, or choose a custom image as required.
The images can then be used as an intuitive way for users to navigate to another sheet within the report, while passing a dynamic parameter to ensure the data is in-context. Alternately, where there are a small number of selection choices they can be used as visually appealing slicers.
Insert PictureLink is available from the Insert Formula tab on the XLCubed ribbon.
Click the drop-down and search for an appropriate image from the picture library – you can also specify the colour by clicking the Colour drop-down.
You then select the required image, and enter the destination cell location in the “Link to” box – this is the location where the user will be taken when they click on the image (can be the same sheet or a different sheet in the report).
You can also parameterise the image – “Value” is the content which will be inserted into the cell specified in “Range to Set” (can be text or a cell reference).
To edit a Picturelink once it has been inserted, hold down Shift and then click the image.
Note that PictureLinks do not need to pass parameters – they can used as a simple link to another location:
Lastly, on web reports XL3PictureLink can also be used instead of the standard ‘Submit’ toolbar button.
There is more information on that here.