Refresh Archives | XLCubed Blog

Auto refresh in XLCubed Pivot-views and Power BI

XLCubed/ 20th February 20174th January 2019/ Data

Version 9 of XLCubed introduced Pivot-views, which allow our powerful grids, small multiples and slicers to be driven directly from lots of different relational sources.

We also added support for Power BI as a data source, and many of our customers are now using XLCubed on Power BI data for a better in-Excel experience and slice and dice.

Many of you will already be familiar with the data refresh options in Power BI, essentially either a scheduled refresh process, or through the Enterprise Gateway.

With pivot-views we wanted to ensure that the data refresh process is very straightforward, and to enable business users to benefit from them without worrying about scheduling data flows or getting involved with discussions about gateway configuration. Fundamentally, the data will automatically update when the report is opened or if any of the query parameters change (either by updating a slicer or if an Excel cell-based parameter value changes).

This means the report builder doesn’t have to worry about the latest view of the data as that is all handled automatically without any custom code, manual intervention or IT assistance.

Here is how it works in detail.

First insert a grid and select a relational database as the source.

We are presented with the query designer, we can pick a table and setup a parameter by entering “@productgroup”:

This gives us an option to link that parameter to a cell or directly to a slicer:

We can now design the grid we want to see:

And view the result.

Now, if I update the driving cell:

The query is re-run and the grid (and anything else driven from the data has updated to reflect the new query results):

This automatic handling of the data refresh continues once the report is published to XLCubedWeb. Whether accessing from Excel, a browser or the XLCubed Mobile App, the report is based on current data without the need for any additional handling or refresh processing… One less thing for the report designer to worry about, one less thing to go wrong!

Click & Submit!

XLCubed/ 29th April 20154th January 2019/ Publication & Distribution, Reporting & Analytics

We’ve had a few queries recently where customers want to provide web reports with a number of slicer choices, and to have the report refresh just once when all selections are made, rather than the default refresh after each selection. It can be achieved in a couple of ways in XLCubed, read on for more…

The key to this approach work is the ‘Wait for Submit on Web’ option on the slicer properties, shown below on the Behaviour tab of the slicer designer:

This means when the slicer is changed it does not refresh the report straight away, and if you set this on multiple slicers users can then press the ‘submit changes’ button on the toolbar shown below after they’ve made their selections.

Alternatively, and to make it more obvious for web users you can have them click on some text or an image in the report itself to call the refresh, as in the examples below.

I’ve created a simple report below with five different slicers. Note the “Refresh“ to the right, created using XL3Link().

The XL3Link statement is available from the Insert Formula menu on the XLCubed ribbon:

It’s most often used to move the focus to another area of the report while passing parameters to enabled linked-analysis in a multi-sheet report. However, here we can use it to call a refresh.

We can leave the “Link to” parameter blank, and also the Target and Value cells. The last parameter, LinkType calls SubmitChanges on the web, so the syntax will look like below (you will need to update the XL3Link statement to include this parameter):

=XL3Link(,”Refresh”,3)

There is more guidance on the general use of XL3Link on our Wiki at: http://www.xlcubed.com/help/XL3Link

So when we publish our report to our web server we can change the slicer choices as required but it’s only when we click the Refresh button that the report is refreshed.

If we’d prefer to display an image for the user to click on rather than text we can use XL3PictureLink in a similar way. When using XL3PictureLink we can display any picture – we’ve used a generic refresh icon but it could easily be a more corporate-applicable image:

XL3PictureLInk is also available from the Insert Formula menu on the XLCubed ribbon:

Browse in the window above to locate the Picture file to insert and remember to check the Perform a Submit Changes on Web box.

There is more guidance on XL3PictureLink on our Wiki at: http://www.xlcubed.com/help/Picture_Links

This is the published report using XL3PictureLink, the user makes the required selections and clicks refresh.

So it’s as easy as that – two ways to ensure that your users can change multiple slicers on web-published reports before calling the refresh, and without you having to direct them to the standard submit changes on web button.

Some Excel BI myths debunked – #5 Real-time data exploration

XLCubed/ 11th September 201310th January 2019/ Data

#5: Lack of Real-time data exploration

The argument is often made that Excel is too inflexible to answer spur of the moment questions quickly and effectively. The scenario given is that you’re in a meeting with your Excel workbook, and someone asks a related question not currently accounted for. How embarrassing to have to look at your back up folder of printed Excel workbooks… Really? It may escape the attention of some, but Excel is actually an electronic product too so as a first point you wouldn’t need to dust off an enormous binder of printed reports.

That aside, the overall argument has some merit in specific cases. If Excel is acting as both the datastore and the presentation tool you have a problem. If the data you need isn’t in the workbook, you’re bang out of luck.

There are two key requirements to address the issue in Excel. Firstly the data needs to be stored outside the workbook; in the case of XLCubed that’s in AS cubes or tabular models. This means when the data isn’t currently visible in the workbook it can still easily be queried and brought into play.

Secondly, while it’s a huge step having the data in a cube, that in itself isn’t enough. You need to be able to get it out quickly, easily and flexibly and to display it as information rather than just data. There are significant limitations with pivot tables when used to report on a cube and XLCubed addresses these while adding a lot more capability on top. The additional data you need to answer the question is readily available, and you have tools to do something meaningful with it using slice and dice tabular reporting, interactive charting and straightforward user calculations.

So when someone asks the question in meeting, you can explore it interactively and on the spot. And in Excel.