5 Interactive Chart Techniques Come Together

October 2, 2014

by Matt Allington

Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from powerpivotpro.com, from searching the Web, as well as some of my own ideas.  While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated.  I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user.  There is a link to this workbook at the end of this post.

The techniques I have used are:

  1. Disconnected slicers used to create interactive chart series
  2. Cube formulae and standard Excel to make an interactive chart title

I love these 2 tips I learnt from Rob – so user friendly.  However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.

  1. Cube formulae and standard Excel to make an interactive legend
  2. VBA and “link to source” for interactive axis formatting
  3. Excel VBA to change which Axis the series appears on.

I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below.  I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.

Now let me call out the key techniques I have used to make this workbook rock.
Read the rest of this entry »


Leverage SharePoint for Rich User Interaction

September 25, 2014

Guest Post by Scott at Tiny Lizard

Hold onto your hats, my friends.  We have some pretty advanced stuff for you today!

If you host your workbooks on SharePoint, you are about to read some powerful techniques, and hopefully give you some “brain-fodder” for related ideas.  Even if you aren’t using SharePoint today… it’s worth reading to see the types of things possible with SharePoint, then you can refer back when SharePoint enters your life.

I am going to show two techniques to allow end-users to have some level of interaction outside the bounds of the workbook… say, to drive data into the underlying data sources.  The first technique is not nearly as fancy as the second…Smile

Read the rest of this entry »


Guest Post: Our Power View Story (and Power Pivot Settings Cheatsheet)

December 12, 2013

By Avichal Singh www.linkedin.com/in/avichalsingh

You read about my Power Pivot journey in my first blog post and in my subsequent blog post I elaborated on migration to Analysis Services Tabular Model (SSAS Tabular). I realize now though, that I did things out of order and need to address that in some way. As my journey outlined, before we switched over to SSAS Tabular, we moved our Power Pivot workbooks to SharePoint and started using Power View Reports. And Power View has been a key element of our success. For this post I’ll go back to the future and speak about

- Our success with Power View

- All the settings in Power Pivot related to Power View

p.s.: When I refer to Power View I am referring to Power View on SharePoint. I am not referring to Power View functionality built in to Excel 2013, since that is a fairly different experience than Power View on SharePoint.

Power View Success Story

I love Power View, except when I don’t. It can feel limiting at times and frustrating, especially to an excel user (which is all of us Smile). After demonstrating a really slick Power View report with all the bells and whistles (check out a sample from Microsoft BI at Power View Demo. Mine don’t look as good as this), the first question I often get from the user is, “Great, now how can I export this to Excel?” And my answer is – you can’t Sad smile
“Export to Excel is the third most common button in data/BI apps…after Ok and Cancel” (click for a real fun post!), and Power View does not have it. Yet! If the powers that be are reading, I think it’s feasible that an icon appears when you hover over Power View report elements, to export the underlying data in excel in a simple table format. Please consider that for the next release. Now that I am in begging mode might as well ask for – ability to re-label measures/column names in Power View Report and show numbers as Percentage of Total (like in Excel Pivots). The latter is doable using DAX but not easily so.

Okay, now let’s move on to some love ©©©

  • Power View reports are easy to build, maintain and use
  • Shared Power View report give you a Single Version of Truth (kinda)

Read the rest of this entry »


Monday Bonus: Last Week’s Big Data NYC Slides

October 15, 2012

 
Last week’s post proved to be very popular, and I received many requests for the slides.

Well the slides didn’t really capture everything – so much was covered purely in the demos or my talking points.

So I just invested an hour or so in capturing some of that extra stuff into the slides themselves.  Still not perfect of course, but…  closer.

View and download the slides here.


Big Data is Just Data, Why Excel “Sucks”, and 1,000 Miles of Data

October 9, 2012

 

***UPDATE:  Slides uploaded here.

image

One of My Slides From Last Night – Equally Relevant to Excel, BI, DB, and Big Data Pros

Had a great time last night at the NYC MSBIgData group.  I’ve never spoken to a group quite like last night’s, but I struggle to explain how they were different.  It’s easier to explain what they were not.  Even though the user group is a Big Data and BI user group, they were not a Hive/Hadoop crowd, which shouldn’t have surprised me – there aren’t enough Hive/Hadoop people in the world to really have crowds of them laying around, at least not yet.

But there also wasn’t critical mass of seemingly any other discipline – not BI, not Excel, not DBA, not SharePoint, not programmer.  There were some people from each of those backgrounds but no more than 10% of each.

I think my best assessment is that they were simply a group of people who DO things.  A very pragmatic collection of flexible people.  People who happily use different tools to solve different problems.  I find that fascinating all on its own.

(If you were at last night’s talk, please replace every instance of the word “they” above with the word “you.” Smile)

Big Data is Just Data, and Hadoop is Just a Way to Store Lots of It

Read the rest of this entry »


Early Tuesday Post: The “Hero Report”

August 6, 2012

 

image

A Post That Just Refused to Wait for Tuesday

I’m Looking for a Few Good Pivot Pros

If you’re an Excel Pro (which I define simply as “one who creates PivotTables”), and you’ve been using PowerPivot, I want your help for a semi-radical side project I’ve been thinking about.

I want to ask you a few questions, either in email or on the phone.  That’s it – basically I need a focus group off of which I can bounce a few ideas before making those ideas public.

If you’re selected to participate, there WILL be compensation.  That will either be a $50 gift card, some free Pivotstream services, and/or a direct line to me for some PowerPivot questions.

So if you’re interested, please drop me a note at the following address:

***UPDATE – the survey program is closed to new participants at this time, the response was overwhelming!

What’s a Hero Report?

I love this term, but I didn’t invent it.  Credit goes to John, one of my colleagues at Pivotstream.  He talks to a lot of Excel Pros every day, even more than I do, and he tells stories like the following all the time:

Read the rest of this entry »


Mini-Post 1 of 2: Sharing Creative-Font Workbooks

July 12, 2012

 
In Tuesday’s post, I showed how WingDings and other symbolic fonts can be used on slicers for an interesting effect.

Question is:  what happens when you send the resulting workbook to someone else, or publish it to SharePoint?

Here are the quick results of my investigation so far.

Read the rest of this entry »


“Drill Across” in PowerPivot – Live Demo

May 24, 2012

Hyperlinks in a Pivot

“I’m telling you there are monkey-fighting hyperlinks in this Monday-to-Friday pivot!”

(Seriously this is how they cleaned up his line for TV, with “monkey-fighting” and “Monday to Friday”)

***UPDATE:  I am no longer working at Pivotstream and do not endorse their services.  All links are removed from this article but feel free to look them up if you are interested.

 

 

 

 

Retailer Competitive Overlap Application – New and Improved Live Demo

PowerPivot Retailer Competitive Overlap Application With Drill Across

Revamped/Simplified “Retailer Competitive Overlap” Application
(Note that the Row Labels Area of the Pivot Contains Hyperlinks!)

Clicking an Item to Get More Detail

The retailer overlap application is one that I’ve covered before, in my post announcing our live PowerPivot demo site, but I’ve recently spent some time improving it based on customer feedback and requests.

Specifically, our retail customers have asked the following:  “It’s great that I can see that Retailer X competes with me for our senior citizen customers much more aggressively than we thought, but can I get a list of the actual stores that overlap, with addresses?”

 

image

But WHICH Stores?  I Want to See the Addresses!

Hyperlinks in a Pivot!?

Let’s zoom in on the row area of the pivot pictured above:

image

Read the rest of this entry »


Cloud PowerPivot: Free Trials for the Public

April 26, 2012

image

image

Actual Web Browser Screenshots of PowerPivot Sites:
Example of Homepage (top) and Report Page (bottom)

***UPDATE:  I am no longer working at Pivotstream and do not endorse their services.  All links are removed from this article but feel free to look them up if you are interested.

Want to see (and share) your workbooks on the web?

For a few months now we’ve been running free 30-day free trials where you can:

  1. Upload workbooks to the Pivotstream cloud
  2. Interact with them in the browser
  3. Securely share them with colleagues, even if they don’t have PowerPivot or Excel 2010 installed

Read the rest of this entry »


My Experiences with Hosted PowerPivot, Part One

February 21, 2012

Guest post by David Churchward

Pivotstream Dashboard Application

One of Azzurri Communications Ltd’s PowerPivot
Applications Running in the Browser

Six Months Ago:  The “Lightbulb” Moment

Almost exactly six months ago, after being a long time reader of this blog, I emailed Rob and asked him a question regarding something that I just couldn’t get my head around in DAX – Banding!  He kindly responded, and his answer solved my problem, so I asked him if I could return the favour somehow.  He asked if I’d mind writing it up as a guest post, which I did.

Now, double-digit guest posts later, I’m amazed at how far I’ve come in short order.  Something definitely “clicked” for me that day, and my grasp of PowerPivot’s capabilities expanded rapidly.  It felt like that moment that I imagine Pianists reach where they can suddenly play by ear, because whilst I could conquer most things in DAX, it didn’t seem to quite “flow” – until that day!

Up until that point I had viewed PowerPivot as a “private” tool – something that was useful for me in my work, a supplement to other tools and methods.  But starting six months ago, I started to understand that PowerPivot could, and SHOULD, be used to improve or replace most of our existing Business Intelligence and Analytics tools.

Step One:  Azzurri Deploys its First “On Premise” SharePoint Server

At Azzurri, I am fortunate to enjoy two critical flavours of support:

  1. Our executive team is open-minded to progressive ways of doing things (so long as there is a solid value proposition).
  2. My tech team is a crack outfit who will bring me the moon if I ask for it, but tend to make reasonable alternative suggestions such as building data warehouses.

In other words, Azzurri is the perfect sort of place to deploy PowerPivot for SharePoint, bordering on a textbook example.  There aren’t many companies of Azzurri’s size where I could explain the benefits of a PowerPivot server, win people over, and have a server deployed two weeks later.  But that is precisely what happened Smile

Step Two:  Start Emailing Rob Again Smile

We didn’t just deploy the server, we immediately began USING it for serious work.  And that led to questions – questions about performance.  Questions about hardware.  Questions about customisation.  Questions about refresh.  Questions about “core and thin” workbooks.

Rob and I had a friendly correspondence going at that point, so I started sending those questions over.  I even looped him into email chains with our tech team, and we talked through a number of issues and optimisations.

Step Three:  Try Out Hosted PowerPivot in Parallel

Everything I do in Systems Development, especially with my Finance background, is about Cost V Benefit, ROI, IRR and payback.  With this in mind, I started wondering whether it made sense for us to develop PowerPivot for SharePoint expertise of our own.

We had originally decided to go with our own SharePoint deployment because we had the required licences and a particularly clever team who I had every faith could deliver.  This seemed obvious as SharePoint was already in operation at Azzurri.  My initial view was that it must be relatively straight forward to bring all of the BI tools into the equation.

Two weeks into the process, however, I was already seeing that things might not be as straight forward as I had first hoped.  Performance was the first major barrier that I hit and I couldn’t be entirely sure what kind of investment in hardware might be required to alleviate this.  Out of nowhere, PowerPivot gallery started playing games which turned out to be an IE9 issue and then I was introduced to Kerberos which, it turns out, isn’t a breakfast cereal that I was yet to encounter!

I knew about the Pivotstream Hosted Solution of course, and I still wasn’t ready to commit to hosting, but I decided that running a trial in parallel made a lot of sense, especially since I was particularly aware that my tech team needed to be doing other things.

I’m very glad that we decided on a trial, because step four was to switch over full-time.

Goodbye “Do it Yourself”, Hello SaaS

The journey I’ve been on as a customer of Pivotstream has validated for me that the SaaS model together with the capability of PowerPivot makes for a more compelling business solution for reporting and BI than any alternative that I can find.

I’ve been particularly conscious of making sure that my tech team spend their time where they can really drive business value – building Data Warehouses, ETL and efficient business processes.  It was clear that time spent developing SharePoint Server was time not spent adding value elsewhere.  There’s no doubt in my mind that they would have delivered, but I knew that they could deliver more value elsewhere to more than offset any cost of hosting.

Summary of Our Experience

Once I had taken the decision to try out Pivotstream’s hosted solution, it became clear that “elapsed time” taken was no longer going to be a constraint to the project.  On that same day, Azzurri had it’s own Pivotstream site in full working order with admin and consumer accounts setup for the trial.  It was now down to me to start making this a fully functional dashboard.

Naturally, I had workbooks at the ready and I loaded a few up immediately.  I started sniffing some of the additional features that I could now start playing with.  Before I knew it, I was canvassing Pivotstream for direction on Query String URL filtering (an awesome attribute to drill across to other dashboards).  A handy guidance document found it’s way into my inbox and I was away.

I was supplied with a program to split core and thin workbooks, another gem that just saves time and aggravation.  I served up a query with web part layouts and, next day, I get a new page layout deployed straight to our site.

Immediately, the focus of what I was delivering was about end user usability as opposed to finding ways around potential (and in some cases very evident) performance issues.  Performance was immediately apparent on the Pivotstream solution, as could be immediately seen by some of my more “chunky” analysis that didn’t even make it flinch.  My in-house SharePoint Server could take upwards of a minute to open these workbooks whereas the hosted solution barely registers seconds.

Within a matter of days, I realised that the limits of this solution only existed to the extent of the limits of my imagination in creating dashboards.  All of a sudden, my focus was turned on making sure that full value was derived and, to that end, I started spreading the word within Azzurri.  Some initial training took place and I immediately recognised that these clever individuals that I was working with had even more insight bursting to get out and the fact that we were playing in Excel meant that they could immediately relate to what they were being shown.  I had hit that fantastic point in the project where momentum starts taking over and this is probably less than two weeks after starting the trial.

Speed (both of implementation and application), elimination of complexity and additional value adding applications delivered in a scalable data-centre model with an OPEX cost model sums it up for me.  Now, it’s just about making the dashboards deliver the real benefit to the business – insight!

As I’ve been writing this, Rob’s reminded me of a comment I made back towards when this whole thing started:

“My key driver is laziness so I’m always looking for quicker and better ways to do things. In doing so, I find myself working non-stop so I may have my driver wrong or I’m failing miserably!”

The reality is that Hosted PowerPivot does do it quicker and better.  I’m working non-stop because the results speak for themselves and I fundamentally “get it”.  The reality is that my driver was wrong!


In the Browser, Aesthetics Yield a Greater Return

January 10, 2012

 

A PowerPivot Doc in the Browser is an Application   
A Spreadsheet in Excel Services Is No Longer a Document,
It’s an Application

I thought today was going to be a “handful of mini posts” kind of day but then this post blossomed into a bit more than I thought it would, which is a good thing.

Normal Spreadsheets are Usually Ugly and That’s OK

It’s true:  most Excel pros are not artists.  I certainly am not.  We’re number folks first and foremost, and our jobs haven’t historically placed top-level emphasis on aesthetics either.  So we don’t spend much time on it, typically.

Besides, Excel itself isn’t pretty.  Back in the 80’s or 90’s, even if you made a spreadsheet look fantastic, well, it was still loaded in Excel.  So you got all those lovely “battleship grey” toolbars, the title bar, etc.:

Spreadsheets of Yore Were Also Made Ugly Just by the Excel Frame

Spreadsheets of Yore Were Also Made Ugly
Just by the Excel Frame Itself

I want to be clear:  This is NOT a post that is going to encourage you to run out and start putting lipstick on all of your spreadsheets. 

Instead, I am going to make the case for why the game changes significantly (for the better too) when you switch to using a server (or a cloud hosting service like ours) to share your work.

The “Excel Frame” Has an Enormous, Underappreciated Impact

Hey, you might say that the old-style Excel screenshot above is an unfair example, since Excel 2007 and 2010 replaced menus and toolbars with the Ribbon.  But in a crucial way the Ribbon is NO different really – the point is that either way (ribbon or menu/toolbars) the Excel frame is NOT YOURS.  It belongs to Excel.  And no matter how much work goes into the document itself, the user of that document still thinks you made them a document.

Let that sink in for a minute.  Because the user of your spreadsheet thinks they are “using Excel” and not “using an application built by my favorite Excel pro,” you are receiving a hidden benefit AND a hidden penalty:

  1. There are many things you will never be blamed for as long as the consumer thinks of Excel as the application.  Hey, the overall experience just feels kinda clunky.  “No big deal, that’s just how Excel docs always are.”
  2. But you also don’t get nearly enough credit, psychological impact, or perceived importance that your work deserves.  Seriously, your work drives your organizations.  If everyone thought of you as a programmer (which you are, even if you don’t write macros), you’d be viewed differently.  But people who produce documents are often viewed as “Excel Monkeys.”  Honestly I think Excel pros are, for the most part, underpaid relative to their true importance.

When you switch from mailing spreadsheets around to publishing spreadsheets on SharePoint, well, both of those go out the window.  Well, if you do it right, anyway.

SharePoint Brings Its Own Frames!  Ack!

OK, so you switch over to using SharePoint as your publishing mechanism.  Does that get rid of the Excel frame?  Well not really.  It just gives you a new Excel frame in your browser:

See all of those highlighted elements?  Just another Excel frame, ported to the browser.  Complete with File tab, toolbar, the .XLSX extension blazoned across the top, and even a warning bar.

Not to be left out, SharePoint adds some of its own at times:

So, you gotta get rid of those.  And that means customizing SharePoint.  If you’re a SharePoint pro that’s mostly not too difficult, but even then it likely will take you some time to get it tuned just right (we’ve been making tweaks now for two years).  And if you’re not a SharePoint pro, well, you are going to need one.

(For more info on the details of these SharePoint elements and what we’ve done to modify/suppress them, see this post and this post).

Going Frameless Turns “Document” Into “Application”

In our Hosted PowerPivot offering, we’ve got all of that suppressed, and the only “frame” you see is just the browser and a typical web header.  An example:

PowerPivot Application Built by Pivotstream

The Consumers of This Application Neither Know NOR Care that it Was Built in Excel

The idea for this post struck me yesterday as I was putting together this sample workbook (based on Microsoft’s AdventureWorks data set) that we are going to start including in all of our HostedPowerPivot sites as a tutorial:

Sample Hosted PowerPivot Workbook

Sample Workbook v1 For Hosted PowerPivot

Or try this humorous example (based on real data) that examines UFO sightings – click image to view the application on Mr. Excel’s HostedPowerPivot site.

Live PowerPivot Application on HostedPowerPivot

Click Image to View the Live APPLICATION

Completing the Illusion:  A Few Simple Steps

Once you’ve gone frameless, there are a few simple things you can do to complete the transformation from document to application.  Neglect these and your “frameless” would-be application screams “spreadsheet” to the audience.  Follow them, and even if you’re not terribly artistic, your work will be perceived very differently:

  1. Turn off gridlines and headers.  It’s not hard.  Two checkboxes on the View tab of the ribbon, but do that for EVERY sheet the consumer sees.  Every single one.  Crucial.
  2. Hide or delete all sheets you don’t want them to consume.  Don’t leave extra blank tabs in there named “Sheet3” OK?
  3. Insert some images.  So important!  Your company logo.  Your client’s logo.  Something.  And make sure you use the Insert ribbon to do this!  Simple copy/pasting an image into a worksheet, in my experience, seems to result in that image NOT showing up in the browser.
  4. Line up slicers, charts, and tables.  Takes just a minute or two to make sure the top of your slicers are even with the top of your chart, etc.
  5. Don’t neglect number formatting.  If something is a currency, format it as a currency.
  6. Use conditional formatting.  No need to overdo it, but conditional formatting turns a boring black and white grid (a pivot) into an inviting surface that is actually fun to look at.  Plus, trends, patterns, and outliers jump off the page much more readily.  I’m especially fond of data bars, color scales, icon sets, and when I have the time, sparklines.
  7. Create a Menu sheet (Table of Contents), and use hyperlinks for navigation.  Yes, the sheet tabs are visible.  But why force people to use them?  There are a million reasons why sheet tabs are disproportionately old fashioned and cognitively difficult.  When it comes to navigating around a web application,  nothing comes close to a hyperlink. 

“I’ll Take ‘Hyperlinks Between Sheets’ for the Win”

Let’s focus on that last one.  Did you know that you can hyperlink between sheets in a workbook?  I worked on Excel for years and never really realized this.  Our CEO at Pivotstream pointed that out to me, and it works on the server too.

Hyperlinking Between Sheets in Excel

Hyperlinking Between Sheets in Excel

This lets you create menu sheet like the AdventureWorks sheet above, as well as this one at the beginning of the post:

This Is Actually a PowerPivot Menu Sheet aka Table of Contents

This Is Actually a PowerPivot Menu Sheet (aka Table of Contents)

Cool huh?  Those chart thumbnails are IMAGES.  The hyperlinks above them take you to the full-page interactive sheets that host each of those chart views.

Formatting Macros

I have a number of macros that help me do some of this stuff, and in an upcoming post I will share some of them, once I have time to organize them a bit.

In the meantime, here’s a real simple one whose intent should be obvious:

Sub HideGridAndHeadersOnAllSheets()

    Dim oSheet As Worksheet
   
    For Each oSheet In ActiveWorkbook.Worksheets
        If oSheet.Visible = xlSheetVisible Then
            oSheet.Activate
            ActiveWindow.DisplayGridlines = False
            ActiveWindow.DisplayHeadings = False
        End If
    Next

End Sub

How many of you use macros, by the way?  I’m really curious.  Drop me a comment if you would and just say yes/no.  I will also put up a survey at some point if I get industrious.

Conclusion

I know that not everyone who has embraced PowerPivot for Excel has started using PowerPivot for SharePoint yet.  That’s changing, but it takes time.

For those of you who are starting that transition, I’m very excited for you.  I know it sounds weird.  But you cannot appreciate how much more impactful your work “feels” as a web application until you see it in action.  And this post is aimed at helping you reap that benefit.

For those of you who are yet to start down that path, file this one in the back of your mind for later.


Rackspace Webinar from June

August 18, 2011

A few people have asked me if the SharePoint Saturday presentation will be recorded this weekend.  I am 99% sure the answer is no.

But if you’d like to see something similar, the webinar I did with Rackspace back in June covers many of the same points:

http://www.rackspace.com/knowledge_center/pivotstream_webinar

I’ll warn you though that we hit two distinct technical issues in the presentation – one small SharePoint glitch which was not a big deal, and a problem with the webinar software itself that basically wiped out 4-6 slides and frustrated me to no end.

Up until that point though, it was going great Smile