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…
Pushing Data Into a SharePoint List
This example comes from a client referenced in my Previous Post on making your reports “actionable” and “verb-like”. We are trying to determine if a car should be “Crushed” for scrap metal. However, after folks agree with the report and schedule the car for massacre, it would be great if that could be reflected in the report.
See that last column of ugly calendar icons? That’s your hotness. It allows us to mark the car as scheduled… by just adding an entry to a SharePoint list.
That is just a character from the Webdings font, hooked up to an =HYPERLINK call, which does indeed work just fine in SharePoint.
Symbolic Character Hyperlinks
In excel, there is a hyperlink function. The first parameter is for where you are navigating to, which must start with http:// or you aren’t going to get a real hyperlink on SharePoint. The second parameter is the text to display, in our case… the cute icon.
To get the cute icon, I actually used the character map tool that ships with Windows. Launch that sucker, flip to the WebDings font, double click an icon and it will magically be on your clipboard. Paste that into the 2nd parameter to HYPERLINK. Lastly make sure the whole cell is flipped to WebDings and you are done.
You can see in the screenshot at left the formula looks weird (shows a vertical pipe for the text), but on the sheet it will look fine.
Use The Hyperlink to Add a List Item
The next part of the magic is to know that if you append NewForm.aspx to the URL of a list… that full URL will pop up a dialog to add a new entry to the list. You can also add a url parameter (maybe others, not sure) “Title” that lets you default the value… in this case I pre-populated the stock number of the vehicle.
So, we now have a way for people viewing our report to easily add list items, related to a specific car/row from our report, into SharePoint. Then, all we have to do is add the SharePoint list as a data source in our Power Pivot model, and these items can feed back into our model! In the screen shot, that is where the “Scheduled Crush” is coming from.
Obviously, there is a “delay” here. The report won’t reflect this data until the next refresh. Note that there is an option on the Connection Properties to “Refresh data when opening the file”… and in 2013, I think this would cause the change to be reflected just by forcing an update of the page, but I admit to not trying it. Because I’m a bad person. Somebody let us know in comments
Fancy jQuery Madness
So, let me be honest right up front… this is not fully fleshed out. But, for the super geek amongst you that live for super-powered SharePoint and Excel interaction, I suspect this could be really cool.
Okay, we can get some jQuery into SharePoint… how does that help us? Well, how creative are you? Apparently I am “display a message box creative”
Because I’m a lazy butt (well, and it’s a good demo of how to “play around” with the environment), I’m just using the development console in my browser to throw a bit of code on the SharePoint page, instead of uploading a .js file and linking into into my publishing page via the content editor web part.
My code just says “Hey, find me a hyper link with ‘Super Neato Hyperlink’ in the text and attach an event handler to it that… displays an alert. I know mind blowing.
What “real” stuff can you do? Well, I think that is limitless.
Share Your Ideas!
This is one of those things where I suspect people can think of some really cool ideas. If you come up with something amazing, please share in the comments!