Tag: sharepoint

CRM + SharePoint * Excel Services = Epic Awesomeness

What’s the best way to get someone to eat their vegetables? Force feed them 😉

All kidding aside, CRM can be a pretty powerful tool and when people don’t want to use it we have to find creative ways to get them to use CRM. In addition to CRM usage, we have people who are absolutely married to Excel. And these aren’t your typical Excel files. These are files that legends are made of. Crazy formulas, vlookups galore; you name it, we use it. To make matters worse, they e-mail these Excel reports around all day, every day as attachments. So let’s kill two birds with one stone. We stop a big group of people from e-mailing Excel attachments and we get them to use CRM. Win-win for everyone (or at least that’s the hope).

  1. Save Excel file in an easy to find, easy to access place in SharePoint – doing this in SharePoint gives us all the doc mgmt benefits that we’ve come to know and love
  2. Configure your Excel REST API URL – I’ve made it pretty clear that I love SharePoint’s REST APIs in the past and the Excel API is no exception. You can read more about it here: http://msdn.microsoft.com/en-us/library/ee556413(v=office.14).aspx
  3. Create a new Web Resource in CRM – we’re going to iframe our Excel REST API URL call
  4. Choose Web Page (HTML) as the Type and then click “Text Editor”
  5. Click the Source tab
  6. Paste in your iframe code between the <body> tags. Your code should look like this: 
    https://dude.com/sites/site1/_vti_bin/ExcelRest.aspx/Documents/Document.xlsx/Model/Ranges('Scorecard')?$format=html
    • Note the frameborder, height, and width attributes. These are needed to eliminate the nasty border and to make scrolling work correctly. iframe’s aren’t perfect and getting them to work feels “hacky” but the user won’t know the difference and it should perform relatively seamless in all browsers.
  7. Click Publish
  8. Now, navigate to the desired dashboard and add your new Web Resource, click Save, and Publish.

Users should now see the Excel spreadsheet in their dashboard:

If users do not have access to the spreadsheet they should encounter an Error:Access Denied prompt or a blank screen depending on the browser they use.

Extra Credit

In our case, the Excel spreadsheet scrolled FOREVER. I wanted to give users a pleasurable experience but I also didn’t necessarily want them resorting to Excel on the client right away. I added a “Click to View in a separate Window” link in the iframe Web resource. Here’s what my code looked like:

<p><a href="https://dude.crm.dynamics.com/WebResources/new_iframe2">Click to View in separate Window</a></p>https://dude.com/sites/site1/_vti_bin/ExcelRest.aspx/Documents/Document.xlsx/Model/Ranges('Scorecard')?$format=html

All HTML Web Resources are web pages, so I linked directly to the web page. But notice I linked to new_iframe2? I didn’t want users seeing “Click to View” on every page so I made an identical web resource, except I removed the hyperlink from the top, making for a seamless experience for the user. There’s all sorts of other things I could have done on the new_iframe2 page. I could have linked to the Excel Web Access or even directly to Excel itself, but we’ll leave it like that for now.

Ultimately, I’ve gotten the report builders to stop e-mailing this specific report as an attachment, and now the audience of the spreadsheet has to go to CRM to view it rather than getting it e-mailed to them. Awesome.

Mac Experiment Week: Day #1

So this week I’ll be using a Mac as my primary device at work. Figured I should see how the “other” half lives so I can help them out when they have SharePoint issues.

Initial reactions:
1. I am a hotkey fanatic. More so than I ever thought.
2. Links lists in My Content are an extraordinary idea. Maybe I should take my own advice from time to time.
3. CoRD is one of the best RDP managers I’ve used regardless of OS.

I’ll continue to chronicle my experiences this week here and on Twitter. Any helpful software, hotkeys, advice, etc. you can provide would be much appreciated.

My first 2 weeks @ Trek: Setting up a Development Environment with Production Data

So I started as the new SharePoint administrator at Trek Bicycles (in Waterloo, WI for those of you keeping score at home) last Monday. It’s been quite the whirlwind thus far. The biggest learning curve I ran into was setting up our development environment with a snapshot of production data. In all my prior gigs other people were responsible for that. Here at Trek I’m the lone ranger. So I foraged ahead with my admin handbook, navigated to my dev environment’s Central Admin, and clicked on “manage web applications.”

Note: This post assumes you’ve got an available (and functioning) SharePoint 2010 environment, a breathing database administrator, and working knowledge of Central Admin.

Click New in the Ribbon and you’ll get this mean nice form.

Add a New Web App

 You see 10 sections and you think “Gee, how hard could this be.” Well, it’s not hard per se, but it ain’t easy either.

Authentication

Depending on your scenario, you’ll generally pick Classic Mode Authentication.

IIS Web Site

On to the IIS Web Site section.  Here it is important to note that you need to have your domain (i.e. your web address) already setup and have it pointed in the right direction (i.e. pointed to the right IP address). In my case, I only have 1 Dev server so it’s not that complicated. I e-mailed the person in charge of IP addresses, told her I needed a new A Record (the web address), gave her the IP address of the server, and we were off and running.  You’ll want to create a new IIS web site. Name it something you’ll easily recognize in IIS. Use port 80 (http) or 443 (https). If you use any other port you’ll have to add it to the end of the URL. For example, if you use port 4455 and your address is https://www.getaclue.com, you’ll need to have users add :4455 to the end of their URL in order to hit the site correctly (https://www.getaclue.com:4455).  Shout out to Brian Winkers for the help on that.

Next you’ll need to add a host header. Using the example above you would add [www.getaclue.com] (note: no brackets). You can leave the Path alone. In fact, I recommend you don’t touch it. Once that’s done you’ve completed the IIS section. Moving on . . .

Next half of the form:

The rest of the form

Security Configuration

 These are 3, subtle, yet oh-so-imporant radio buttons. For now, let’s just leave the first 2 alone. Kerberos is a beast and if this is for an internal network you wouldn’t want anonymous access. If you’re using port 443 it’s probably a good idea to use SSL, especially if it’s internal. You’ll need to buy an SSL certificate from an external vendor (I recommend RapidSSL).  The certificate is installed on the server(s) in your environment via IIS, but that’s a topic for another blog article.

Public URL

Here you put in your URL. Pretty straightforward here.  I don’t know what Zone does, and I’m sure it’s important, but this blog is called SharePoint Rookie; not SharePoint Expert. 

The rest of the form

For the rest, I would just leave it alone unless you do not have an Application Pool account in the App Pool section. Again, topic for another blog.

Now hit OK at the bottom of the form and wait for SharePoint to work it’s magic. It’ll take 30 seconds or so, but once it’s done the Web Applications page will refresh with your brand-spankin new web app provisioned.

The database

Last step is to connect the copy of production’s database to your newly minted Dev web app. Contact your Database Administrator (DBA) and ask him/her to put a copy of the production content database on the Development SQL server. It’s important to have a good idea of what’s in Production because you’re going to reproduce it on a different server. Depending on where you work or where you’re doing this, this may be a bad idea. Check with whoever (boss, director, higher-power) you may need to and make sure this is okay. Once you get the go ahead, do the following:

  1. Click on Application Management in the left-hand navigation
  2. Click Manage content databases
  3. Make sure the correct web application in the dropdown is selected (This would be the one you created above)
  4. Click “Add a content database”
  5. Add the correct database server name and the correct database name

Now if you and your DBA did everything right (and I didn’t forget to mention anything here) you should have a production copy of your team site(s) now running in dev. Cheers!