“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
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?”
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:
Where does that link take me?
It opens a new browser tab, and a completely different PowerPivot workbook, parameterized to the selections you made above.
Destination Report Shows Me the List of Stores, With Addresses,
Based on the Selections Made in the Original Report
And the link at the top of that report takes me to the opposite list: Target stores that compete with “my” CVS stores:
Second Page of the Destination Report Shows
the Corresponding List of the Other Retailer’s Locations
Other Improvements Made
Since the last revision, the application now includes Wal-Mart, Whole Foods, Costco, and Trader Joe’s locations.
How is this done?
This post is already getting too long for me to explain every detail, but here are the highlights.
First off, there’s no custom code here, no programming. It’s all done using built-in features of Excel combined with built-in features of SharePoint.
Glimpse at the Innards of the Source Report, and How the Hyperlink is Constructed
Same Field From the Slicer is Duplicated on Report Filter
of the Pivot. HYPERLINK Formula Then References Cell D9.
(And then Rows 8-10 Are Hidden)
This results in a hyperlink that looks like this:
https://insights.hostedpowerpivot.com/sites/demo/pages/Retailer-Locations-Drilldown.aspx?
Retailer1=[Retailers1].[RETAILER].%26[CVS%20PHARMACY]&
Retailer2=[Retailers2].[RETAILER].%26[TARGET]
The first line of that hyperlink is the same no matter what I select on the slicer (and which link I click), but the next two lines are constructed by referencing the row labels area of the pivot and the page filter of the pivot.
The %26 is CRUCIAL. It represents the “&” character which is required in front of the “[TARGET]” for instance otherwise it’s not a valid PowerPivot selection id. And since “&” is also used to separate between different parameters in the URL, browsers get confused if you use “&” in front of “[TARGET]” – so you “trick” it by sending the %26 code equivalent instead.
Took me awhile to figure THAT one out.
Parameters in Excel
Keeping it brief: your destination pivot has to have page filters for every parameter it needs to receive:
Add Page Filters and Named Ranges to “Destination” Workbook
Now Add a Parameter for Each of the Named Ranges, Then Save Workbook
Last Step: Filter Web Parts
This is a built-in feature of SharePoint. I can add a “web part” to the page that “harvests” parameters from the URL and sends them to things like the destination Excel workbook.
With Page in Edit Mode, Add This Web Part
