THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Create Excel Dashboards working on Excel for iPad #excel #ipad #dashboard

I recently tried Excel for iPad and tried opening several workbooks. The results are pretty good, but I’ve found that it wasn’t possible to display certain workbooks. For example, opening a workbook that contains many CUBEVALUE formulas, I should have seen this result:

Dashboard-Ok

However, sometime I’ve got an error saying that the workbook cannot be updated (the screenshot is in Italian language because the test was made on an iPad configured with such a language)

Dashboard-fail

What’s happened? Thanks to Dan Parish (Microsoft), I realized that the problem was that opening the workbook, an automatic refresh was happening, and this stumbled into the CUBEVALUE function I extensively used. If this happen, you have two possible workarounds:

  1. Disable automatic refresh
  2. Replace CUBVALUE with GETPIVOTDATA

The reason why conditional formatting is going away is because conditional formatting doesn’t work on error cells, and neither do charts.  If opening the workbook the automatic recalc starts, every CUBE* function fails (it’s not supported on Excel for iPad, because of the lack of support for external data in this version of Excel) because they can’t fetch any data. At this point I wondered why the automatic recalc was happening, because I didn’t enabled such a condition in the Excel workbook. However, Dan’s explanation is: The reason it wasn’t happening for me is because Excel (all the Excel’s across all platforms) also have very complicated logic to determine when they need to recalculate.  If you saved this from a previous version of Excel (earlier than 2013), or one of several other things occurred, we’ll recalculate it on open.  In some cases where we feel it’s safe not to however, we don’t as a performance improvement.  That’s what I was seeing.

I am not sure that the different locale settings were the reason why automatic refresh was happening. However, when I set the calculation mode to Manual (Formulas ribbon –> Calculation Options –> Manual) the problem of automatic refresh went away, and I was able to open the workbook. If you don’t want to rely on disabling automatic refresh (after all, changing the setting to Manual affect usability when you open the workbook from Excel on Windows), another approach is relying on GETPIVOTDATA functions instead of CUBE*. The operation can be time consuming, but in reality it’s not so different than using CUBE* functions if you start designing the dashboard in this way. This will be a topic for a future, longer article. But there is an interesting advantage of using GETPIVOTDATA if you use Power Pivot or Tabular: performance are better than CUBE* functions (the opposite is true for OLAP cubes).

Published Tuesday, April 15, 2014 11:44 AM by Marco Russo (SQLBI)
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement