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…Smile

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,image 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 https:// 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. imageLaunch 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.

You might also be interested in this post on using symbolic fonts in slicers and this post on using hyperlinks for drill across.

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. Lists/Crush/NewForm.aspx?Title=MNA14053


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 Smile

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.

The first step is going to be to get some javascript onto your SharePoint page.  I’m a fan of jQuery, so that is what I use.  The easiest way to get some script onto your page is to add a Content Editor Web Part to your page, then point it to a .js file that you uploaded to your site, then Test It.

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” Smile


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. Smile

What “real” stuff can you do?  Well, I think that is limitless.  

SharePoint has a very rich javascript api, so you can certainly interact with SharePoint to create list items, pages, sites, whatever.   In our example above, it would be cool to avoid navigating to a separate UI experience and just directly add a crush date to the selected car.

The excel web services also have a javascript api… so, you could trigger a “download snapshot” or reset the slicers.   Really, you can call any web service you feel like… say, to read records from a database.  Maybe those records store some kind of “report list” in your database and clicking the hyperlink takes you to the next report… and it is different for each user, because you are fancy!   Or, maybe it just pops a dialog where you can insert some notes about the report (or a row of the data) and stores it in a database.

I suppose the JavaScript could impact web page elements and styles, though, changing your background to pink from a hyperlink in your excel workbook seems a tad odd… I won’t judge.Smile

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!