Happy Fourth of July. I know, two blog posts in one week – stunning, I know, since I don’t think it’s happened in, sheesh, I can’t even remember. But I thought this one would be of special interest to an audience I used to cater to – you know, people who actually are building stuff with LightSwitch.
One of the great topics of debate for some around LightSwitch has been it doesn’t come with an out of the box reporting solution. I have always felt this was way overblown, since I have you have the ability to leverage the oData feeds LightSwitch generates automatically in Power Pivot for Excel (or even just through the data tab in Excel 2013).
“But I need something like an SSRS report!” they’d reply, “Not just pivot tables! And since I can’t use oData in SSRS like I can in Excel, I can’t leverage all the security/business rules I wrote for the users and what they can see.” I had some sympathy for this argument until I met Rob Collie, the King of Power Pivot.
Hail to the King, baby!
I’d read Rob’s blog for quite some time, and it was one of the reasons why I’d embraced Power Pivot like I have the past few years. And I’ll often point folks looking to learn more about Power Pivot (WHICH SHOULD BE EVERYONE) to read his blog, buy his books, and attend his course if they really want to up their game around Power Pivot and how completely changes the game when it comes to Business Intelligence. (On a total side note – I’d argue LightSwitch is to LOB Apps what Power Pivot is to Business Intelligence. And they both came from Microsoft. Yay us!)
“Great, you’ve got a man crush on Rob Collie. Get the point.” – what Michael Washington is thinking as he reads this.
First of all, Shut up! No, I don’t – you do!
Second, here’s the point – I’ve gotten to know Rob over the past several months a bit, and he showed me something that I was aware of, and that he’d blogged about YEARS AGO, but I frankly never thought about or used, and shame on me. And that of course is Cube Formulas in Excel.
Because Power Pivot is an OLAP data source (FYI, this technique works in Excel with SQL Server Analysis Services as the data source as well), you can basically blow up any pivot table you want that uses it as a data source into a bunch of individual pieces by converting them to cube formulas, move them around however you want, yet still keep them tied to a live data source and even to the slicers that you were using for that pivot table. Let me briefly show you what I mean -
Here is a sample Excel Pivot Table running against a Power Pivot model. Imagine your manager comes to you and says “I need to add two blank cells under each country – one where I am going to put in a value of what I think the sales amount will be for the quarter, and the other showing the difference of that value from the number I entered. And it has to stay laid out exactly the same way, since I want to enter the values right there while I am talking to my folks and not spend all this time flipping back and forth.”
This request would have made me queasy in the past, but not now. Since the entire table, has been converted to cube formulas, I can literally just insert two rows, add the custom labels, and I’m done with the request, and I’ve kept everything else tied to the live data source.
You may think this was a “simple” request – it didn’t involve slicers, filters, blah blah, blah. First of all, go ahead and try and do that in something other than Excel – no seriously, go try and add two cells to a custom formatted report that allows the end-user to interact with the report at run-time by manually typing in data and have it do a calculation on the fly like I just did. Oh have it take 60 – 90 seconds. And save their results so it shows up the next time they open the report. And it can’t cost anything in terms of custom development. Let me know how it goes.
Second, I am purposely ignoring the magic that happened behind the scenes in the first place with Power Pivot, where I combined an oData feed with thousands of records changing every day against a static text file that contained target values for each region/country. Not to mention the row level security I am able to apply to the data that LightSwitch provides me (this is something I am guessing most Power Pivot users haven’t used or know they can use, so I will do a longer blog post about this on Rob’s blog (if he’ll let me) at some point.)
To be clear, to say I have just described Cube Formulas and the potential it represents in terms of the types of reports you could suddenly build in Excel using them is laughable. I have given you a very, very brief taste of what you can now do – corporate scorecards, report templates you can include with an app, etc. All with your business rules, thanks to LightSwitch and oData. Read all the stuff Rob has on his blog about to learn much, much more -
People wonder why I am so passionate about the type of value they can get from Microsoft and our tools – this type of stuff is why.
So thanks Rob, thanks Microsoft, and thank to you for reading!