Control sheet excel inputs from sql

August 3, 2013 4:08 AM

I am excited by the developments of BI capabilities in excel this year and I want to use the power explorer, view, mapping tools with my sql database. I have two questions really... 1. Can I extract individual calculations and data from the sql/data model rather than always through a pivot 2. If yes, or no, what would be the best way to build a control excel spreadsheet which monitors performance across key criteria (leads per day, orders per day, cancellations, etc) across regions so that I can see where the business is not operating as expected and act accordingly. Thanks Tc
posted by trashcan to Work & Money (6 answers total)

Best answer: I haven't the references handy, but once you build your (power)pivot based on an SQL-server source there's an option to convert it to excel formulae. The pivot table you have will change into a bunch of powerpivot-specific functions, and you can learn to build your own from there.

Wish I had more details for you, but I'm on a phone in an airport....
posted by pompomtom at 4:59 AM on August 3, 2013

Here's one guide.
posted by pompomtom at 5:06 AM on August 3, 2013

(TBH you could always do this with msquery and data connections, it was just more painful/SQL-intensive)
posted by pompomtom at 5:08 AM on August 3, 2013

There's a gremlin in your link pompomtom.
posted by mnfn at 7:55 AM on August 3, 2013

Response by poster: Thanks pompomtom, I'll see if i can find the references.
posted by trashcan at 10:31 AM on August 3, 2013

Also the GETPIVOTDATA function is worth looking into, but it's not (IIRC) one of the special cubey functions that you'll get from converting a cube sourced pivot into formulas.
posted by pompomtom at 2:19 AM on August 7, 2013