Hey, today’s my birthday (um, well it was my birthday when I started this blog post a week ago. You tend to move slower when you get older, it seems). This was a wonderful birthday week where I learned that bald men are a higher risk of heart disease (which stinks for those of us who don’t have Michael Washington’s Fabio-esque hair) and the newest version of Visual Studio LightSwitch was released. Okay, only one of those qualifies as good news (unless you suffer from a severe case of Peladophobia or are Michael Washington). But who cares about the snazzy new HTML5 client? Let’s talk Silverli– wait, come back!
Alright look – I know no one likes to talk about the, um, OTHER client in Visual Studio LightSwitch anymore. But much like the 1986 Philadelphia Eagles, I still need the old veteran to lead the team while the young, exciting backup gets up to speed and just plays on 3rd down for now. And yes, someday the HTML5 client, much like Randall Cunningham, will provide a lot of exciting moments on its way to three straight playoff losses. Then, it’ll blow out its knee , destroying any chance the Eagles have of winning the Super Bowl in 1991 and leading to a lifetime of what-if’s and flinching whenever I hear the name Bryce Paup.
Now that I’ve shoehorned that reference to the Eagles teams of my youth, we can get down to talking about the Van Der Haegen conundrum, named of course for Jan Van der Haegen, a superstar LightSwitch developer and blogger. So what exactly is the Van Der Haegen conundrum? It’s what you face the sad reality when you can’t build cool dashboards in LightSwitch like Jan yourself! I mean, just look at this stuff – this is fantastic! And he’s giving away free Syncfusion software licenses? What’s a person to do if they aren’t blessed with these blogging and development skillsets but they want to build a cool dashboard? (And no, it is not my wife’s answer, which is to quit typing this up and go clean the basement).
I know I’ve been promising that actionable BI post almost as long as I’ve been promising to clean the basement. But like the basement project, it’s going to have to wait a couple days. I’ll instead lead into it with a poor man’s solution to build something similar to what Jan has built by leveraging the brand new Power View capabilities in Excel 2013, LightSwitch, and the same Northwind Data feed that Jan is using, which can be here – http://services.odata.org/Northwind/Northwind.svc/.
I was going to use an SAP oData feed again from Netweaver Gateway, but since I’m already in the wayback machine with the Eagles, why not use sales data from Access courses in 1997 like Jan did?
Because it is an oData feed we’re using as a data source, we don’t even need to pull this data into LightSwitch for this particular solution, since I’ve covered previously how you can pull it directly into Excel. So let’s pull all the items into Excel using PowerPivot first.
There were three items we needed to add to the dashboard –
Top 5 Sellers
Top 10 Products
Top 15 Regions
Now, Jan showed a cool way to have LightSwitch group these and show the count for the dashboard. Since we aren’t Jan and we’re doing it the poor man’s way, I’m going to
do this in my PowerPivot data model. The first piece is to add the count of ShipRegion as a new field – I can do this in DAX with the following statement –
Count of ShipRegion:=COUNTA([ShipRegion])
That gets me part of the way – but how do I limit it to the
top 15 results? Again, a very simple DAX
expression allows me to add rank -
Region Rank:=RANKX(all(Orders[ShipRegion]), [Count of ShipRegion])
This adds a column which ranks each of the items from the highest count to the lowest. That way, when I go back to my Power View, I can simply set a filter on the rank field to always show the top 15, even if the underlying data changes.
Now that we have all the proper fields and filters for the Top 15, let’s pull them together in Power View
Select ShipRegion and Count of ShipRegion (You can pretty this field name up a bit in PowerPivot if you want) –
By default, this will put it into a table format. But I like pretty colors! So I’m going to change my table to a column chart by simply going up to the toolbar and selecting Stacked Column under the Column Chart dropdown (please forgive the sloppy circle – this Microsoft Wedge Mouse isn’t designed for someone with feet for hands)
I’m going to sort it by Count of ShipRegion by selecting that from the sort options in the upper left corner of the chart
I’m then going to add the rank filter to the filter area by simply dragging the Region Rank field I created over and setting the slider accordingly -
That gives me the following chart –
We’re close to Jan’s chart, but still a couple more items to add. The first is to add data labels to show the count numbers overlaid onto the chart by going to the layout tab and turning on the data labels
Then I’m going to add the ShipRegion as a legend for the chart by dragging that field into the legend item like so
Then let’s hide the legend from view because I just want pretty colors for each item, not some stupid chart legend hogging up valuable screen real estate. Ta-da, we’ve matched the chart Jan had, but with cooler colors (okay fine, they aren’t cooler, more like they are the default option in Power View).
And we’re done!
The other two charts are derived from the Order_Details_Extendeds entity (for the Top 10 Products count) and, uh, well actually, I assume the pie chart is based on the Invoices entity, but couldn’t figure out exactly how Jan was calculating who was considered top 5 (I’m guessing it’s something like total deal value divided by number of distinct deals for each sales rep, but that didn’t work, so I gave up). I’ll just use sum of total sales since I am out of coffee and figure you get the gist at this point. My final dashboard then looks like this –
This turned out pretty nicely! But you’ll see I couldn’t make it look EXACTLY like Jan’s (there’s a reason this is called the poor man’s solution) – I couldn’t use checkboxes to select the salespeople above the chart, but I could use Power View’s filter area to accomplish the same thing
I can see all of you also pointing out this doesn’t get it into a LightSwitch project either. Well, that’s where you can take advantage of your Sharepoint 2013 or Sharepoint Online/Office 365 instance. If I create a new LightSwitch project and use Tim Leung’s blogpost from back in the day on how to embed a webpage inside of LightSwitch, I can upload my Excel to Sharepoint, grab the URL of the document, and then display it like so –
Hey, that’s pretty cool, right? Yes and no.
I’d normally load the PowerPivot data model into Sharepoint and THEN build a Power View report on top of it instead of doing it in Excel. I can then do automated data refresh and not have the user forced to open the file in Excel to refresh the data. Unfortunately, this isn’t an option right now with Office 365/Sharepoint Online, and it’s not an option at all if you create the Power View report in Excel as opposed to Sharepoint – what you can and can’t do is outlined here. It also could create additional overhead around security and if you don’t have Sharepoint 2010/2013 or Office 365 as an option, then this blog post was fairly useless to you and you are cursing me right now for having wasted your time.
On the other hand, my experience has generally found any dashboard I create has people wanting variations thereof almost immediately upon rolling it out, and this obviously gives users enormous flexibility to potentially download a local copy of this file and roll their own dashboards in Power View against the data model I have setup. It also requires basically no code and would even work with the previous version of LightSwitch while showing off Sharepoint Online/Office 365, which hopefully helps keep me in Visual Studio Overlord benevolent Microsoft collaborator Beth Massi’s good graces.
So that’s my solution to the Van der Haagen conundrum – a much better one would be simply hire Jan, but that would be a fairly lame blog post (Hire Jan and um, see you next week!). Coming next week will be one of these topics – How you can tie this Power View report into an actionable BI scenario OR How not cleaning your basement after being reminded to do so for several weeks leads you to have to sleep in said basement since you aren’t allowed to sleep in your normal bed because your wife says you should have done it the first time she asked back in February. I’ll work on shortening the title if I need to go with option B. Have a nice weekend!