Report publication, sharing and saving.
//We’ve previously shown how to setup Single sign on for Okta, and with V9.2.41 we have added support for Azure Active Directory. This includes using the “Application Roles” feature to control which Analysis Services role to use for each user/group.
Continue reading “Microsoft Azure AD Single Sign on with XLCubed Web”
//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
//Many BI and reporting tools now allow users to collaborate directly with a report. This avoids the cumbersome backwards and forwards of email chains or the need to meet in person.
As useful as this is, some organisations have concerns about the ability to monitor comments made. We will discuss this aspect in more depth after a brief introduction to comments and collaboration.
Continue reading “Monitoring Data Comments”
//We previously briefly covered the updates to XLCubedWeb to support single sign-on using SAML 2.0, in this blog we will go through the process needed to setup SSO in XLCubedWeb using Okta and the steps required to map the users through to cube-defined roles in Analysis Services.
Continue reading “Okta and SAML 2.0 SSO support in Analysis Services with XLCubedWeb”
//With the upcoming maintenance release of V9.2 we are introducing support for Single sign-on in XLCubedWeb, this can be configured to use any SAML 2.0 identity provider and provides some useful functionality for integrated user-level data access in Analysis Services.
Continue reading “SAML 2.0 authentication with Analysis Services and XLCubedWeb”
//Over the last year or so several customers have asked about the ability to export XLCubed reports directly into PowerPoint. They were doing this manually as part of regular monthly reporting cycles, and wanted to automate what was a fairly tedious process, and to save time. We took these requests on board and are pleased to announce PowerPoint integration as a new feature in v9.1.
//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.
//Here we continue our theme on the myths which get propagated about Excel based BI. The next argument is that Excel BI cannot handle widespread report sharing and distribution. Base case we actually agree with this one, and that’s why we invested in developing XLCubed Web Edition specifically to address it.
Understandably, sharing an Excel workbook around hundreds or thousands of users is not something which many companies will consider. A web based distribution approach is much lighter and easier to manage. The drawback is that most web based report design environments lack the flexibility and latent user skill base of Excel. XLCubed provides a simple way to push data-connected reports developed in Excel to a portal based environment, where report consumers don’t require any software installed locally, other than a browser. The reports can also be accessed interactively through our native mobile apps for Apple, Android and Windows phone 8.
XLCubed Web is self-sufficient and does not require SharePoint. For customers with SharePoint and keen to retain it as a centralised environment – no problem, XLCubed Web can integrate so tightly within SharePoint the end users won’t even know it’s there.
Excel based users can become web and mobile report designers in minutes. XLCubed uses Excel as a key part of the BI solution rather than as the entire BI solution, and it’s that which allows us to address the sharing problem, along with the other myths we have identified in this blog series.
from any version of Excel:
…to web…
…to mobile.
//With version 6.2, XLCubed introduces a new feature that we’ve had enquiries about for a while now: the ability to send scheduled reports to email recipients. In this post we’ll go over the basics of how it works and why you might want it.
As report designers and publishers, it’s our job to share our findings with others. In some cases, we have the opportunity to present the information in person at company meetings, and in other cases, just publishing a data-driven dashboard, table or report to XLCubed Web Edition allows our audience to examine the data at their leisure.
In many cases, however, we’d like to provide our consumers with a report on a regular basis. This might be a week end sales report, or perhaps one showing a breakdown of new issues versus issues resolved during the month. By automatically sending an email containing the report, we no longer have to worry about missing that important information.
In some situations we don’t want to have a regular report, but we do want to know when some performance measure is unexpectedly high or low. Under these circumstances it can even be distracting to be updated too frequently: it would be much better to only be notified when the measure is in the unusual condition.
Both of these scenarios are catered for by XLCubed’s new scheduling feature.
As always, the report design step is carried out in Excel. For scheduled reports, there are a couple of additional considerations when designing your report.
As with normal reports, scheduled reports can use web parameters. These are enhanced for scheduled reports, allowing you to place items such as the current time, user or role on the report. This means that the report can use those variables to allow a customised view of the data.
In addition to this, when setting up the schedule you can specify one of the web parameters to be a trigger: the report will only be sent if the value in the parameter cell is TRUE.
Since different scenarios require different schedules, XLCubed makes it easy to control exactly what is sent and when. When setting up a schedule, there will be a few things to choose.
Firstly, each schedule contains one or more reports to send. Each report can be sent as a static Excel file or as an Acrobat document (.PDF), and can be given any parameters that were defined when publishing it. In addition to arbitrary text, there are some special codes that can be inserted as parameters:
| Code | Description |
|---|---|
| %date% | Inserts the current date |
| %time% | Inserts the current time |
| %datetime% | Inserts the current date and time |
| %rolename% | Inserts the database role or roles that are being used |
| %groupname% | Inserts the name of the distribution group |
| %email% | Inserts the email address of the recipient |
| %username% | Inserts the username of the recipient |
| %displayname% | Inserts the display name of the recipient |
| %sendiftrue% | Triggers sending of the report |
The last of these codes is special. Instead of inserting anything into the report, XLCubed examines the parameter’s cell and sends the report only if the value is TRUE. This means that not only can you create a trigger based on your data, but you can create complex logic based on multiple criteria in Excel.
Secondly, each schedule is set to run at particular times on particular days. As you would expect, XLCubed provides a framework that allows you to fulfil a wide range of different requirements, whether you need to run your reports every day, every other week, or even only on the 29th of February.
Lastly, each schedule of course lets you choose who to send the reports to.
XLCubed allows two different ways to set up the recipients for a report. The first is ideal for where the report needs to be sent to just one or two people. Just type in the email addresses and it’s ready to go.
The alternative is slightly more complex to set up, but once set up, it’s easy to make new reports with the same recipients. To use this, you set up a Distribution Group, composed of any number of people. To make it easy to set up the reports as needed, each Group is assigned a Database Role to use and a Locale to format its numbers and dates. One or more Distribution Groups are assigned to each Distribution List, so that your scheduled report can be sent to more than one group at once:
To illustrate this with an example, imagine that there was a particular report that you needed to send to managers in the USA, China and Germany. Since the formatting and roles would be different for each group of managers, you would need a setup something like this:
//You have finished your great looking, very efficient Excel dashboard. Now, how do you share it? How do you make sure the users have a timely access to the latest update? This can be a serious issue, specially if you have a more tech oriented audience, and it must be addressed at the planning stage. Let’s browse the available options.
For the tech savvy crowd out there this is something that doesn’t even cross their minds. But let’s face it, top managers are among the most computer illiterate groups in our society. If these are your users just handle them a sheet of paper to keep them happy and forget about those cool interactive charts. Make sure that your design accommodates this kind of use.
Since printed paper has a higher resolution than computer monitors you may want to create smaller charts and a more detailed answer to the question the dashboard is supposed to answer. I always wanted to create a dashboard to be printed in a A3 (or 11 x 17) folded sheet. Maybe one of these days… Remember that if you anticipate that users will print your dashboard you must test it for color and B&W printing.
Don’t make the mistake of thinking of a PDF file just as an electronic copy of a sheet of paper. User experience is quite different, and you must prepare for it. Users will be able to zoom, pan, add comments, copy a chart to a PowerPoint presentation, etc. Also if, for example, you have a marketing dashboard and it is used for different markets you can set up a simple macro to print a multiple page PDF, and add hyperlink navigation. It is a rich environment and you should take advantage of it.
You can send your Excel dashboard as an attachment to an email message. This will allow the users to save it locally and, unlike paper or PDF, you can add interaction, like selecting different markets, time periods or regions. Do your best to know what monitor resolutions your users have, and make sure that the users have security level settings that allow them to run macros (if your dashboard needs it). A cool thing to do with macros is to greet the user with the last sales data and open the dashboard with the correct market for that user.
File size and network security can prevent you from sending your dashboard as an attachment. Current file size may be smaller than you are allowed to send, but if the file is likely to grow (because you are adding more data) at some point you’ll not be able to send it. You can zip it but the problem remains, and some network security settings may prevent the users from receiving zip files.
If you choose this option the users will have a known location where they can retrieve the latest dashboard version from. You can add some nice touches, like pushing a new version to the user’s computer as soon as he appears online, or automatically send an email when the dashboard is updated (you can do it from the dashboard itself).
Some time ago, I was reviewing a dashboard tool and, although the product was pretty lame, I found that it had a clear advantage over Excel: it could publish dashboards for online access, using Flash technology. This means that it couldn’t manage real-world data sets, but still…
Finding a way to publish online a fully functional Excel dashboard with minimal impact over the way I’m used to do things is something that I’ve been waiting for quite some time. And if you can link the dashboard to the data set, well, that means heaven: you don’t have to open the file, refresh it and save it again.
I was unaware that this functionality even existed until Andreas told me about XLCubed. Last week I was able to install it and start to play with it. I will not tell you that this is a great product (not yet, anyway). But it is a great idea, and my expectations are high. Over the next posts I’ll tell you all about this process of discovery, but just being able to add “web publishing” to your list of options is already remarkable.
There is not a single best option to deliver your dashboard. It depends on your audience, file size, update frequency, implemented features, information infrastructure… That said, we know that the future belongs to web enabled applications and web delivery. Web publishing would be my personal choice, undoubtedly, but I’d try to help less tech minded users to feel more comfortable (like adding a visible Print button).
I like to create charts and dashboards, and I do my best to find solutions that answer user’s problems. But if I can publish a dashboard without worrying about access and updates, well, don’t try to find me here. There is a tropical island waiting for me.
