Creating a dashboard using excel services data in dashboard designer: Part 1 - Creating Your Excel Data Sources

Welcome to my series on creating a dashboard, using excel services data, all within the SharePoint 2010 dashboard designer.  This is the first in a series of posts that will discuss how you can take SharePoint 2010 PerformancePoint Services and combine it with Excel Services - both very comprehensive and robust features in the SharePoint 2010 Enterprise Services.

Some Background

Where I work, we are a very Microsoft centric operation.  Our infrastructure, software, data analytics and transactions are all Microsoft based.  As such, SharePoint fits into our current situation quite nicely.  We have an analytics team that does an excellent job on creating reports in Excel, connecting to data cubes and other data sources, and providing this data to various members of our enterprise.  The problem is that.. well.. it's all Excel based.  The analytics team provides varying degrees of changes to the reports each week, and have macro's scheduled to email the reports on a periodic basis.  It's not that this situation is "bad", it's just that it's... well.. 2012.. and we can certainly do better than that, right?

Of course we can!  That's why we're here after all, to take a good thing, add a pinch of Internet, a dash of SharePoint, and make it a little better :)  With the above points in mind...

Let's get started

(This article assumes that you have already configured and enabled PerformancePoint and Excel Services within your SharePoint 2010 environment.)

Before we even start with PerformancePoint services, we want to make sure that we know where our data is.. which is within our Excel workbook, but that's not specific enough.  Excel Services allows us to create named ranges, tables, and charts within Excel, provide names for those items, and consume them on the web.  This is what we want to identify in this step.. what data do you need access to?

Here's an example of a basic chart of information, just some sales figures for some regions.  Pretty simple stuff.  Notice the last column, Variance, this is a calculated column, dividing Last Years Totals over Total (example, H3/G3).  Be sure to include that calculated column here as well.

 

 

As I said, we want to expose this data.  The good news is that the data ranges are easy, the bad news.. the sparklines aren't supported in Excel Services :(  But that's OK, given the power of the web, we can drill down into that information.. more on that in another post within this series.
So how do we get at this data?  Simple: Provide a named range for your data ranges.  To do so, simply highlight the data you want GROUPED together as a data source, move the cursor to the Name Box, provide a name, and hit Enter.

 

 

As you can see, I typed the word GROUPED in caps above.  Why?  Because if we wanted to, we could have highlighted just the NE Region Row, or just the SW Row.. or just the sales quarters 1-4.  All we're doing here is defining our data sets.

Let's publish it

OK, so let's pretend that this is all the data we wanted to expose, easy enough, right?  Now, just save your workbook item's to SharePoint.  To do so, we can publish the Excel data to SharePoint - choosing only the item's we want to expose on our SharePoint web.  At this point, you just need a document library that is a trusted location for Excel Services (http://technet.microsoft.com/en-us/library/ff191194.aspx).  Since were working with PerformancePoint, I recommend you stash your Excel document within a document library within your PerformancePoint site (that I assume you have already created).

Once you have your trusted location, within Excel, choose File -> Save and Send.  The menu will show the option Save to SharePoint. 

We'll use this option to publish our Excel data, but before doing so.. we'll want to choose what data to expose first.  To do so, click the Publish Options button to the upper right:

You can choose everything here, if you wanted to.  Why wouldn't you?  Well, the items you choose here are BROWSABLE via Excel REST services, which makes each object available via a URL.  So if you had some sensitive data that you don't want your users browsing, because they happen to know the URL where your document is stored.. and they have access to read into that library.. then don't make that data web accessible.  In our case, we'll just choose our Sales Data, and click OK.

Now, click the "Browse For Location" button, click Save As, and enter the URL to your document library with enabled excel services.  Then just click Save, and if all works well, you should have a published Excel document within your SharePoint environment:

Let's use it

We have a published workbook at this point, now let's access the data.  Access your PerformancePoint site, and start the Dashboard Designer (or, if you have already used Dashboard Designer, just enter it into your Start Menu, since it's locally installed client software).
With Dashboard Designer open, within the Workspace Browser, right click Data Connections, and choose New Data Source:

When asked for a Data Source Template, choose Excel Services and click OK.  Now, fill in the value's presented to you.  For SharePoint site, enter the URL to the site where your document library is located, NOT the path to the document library itself.. you choose the library in a separate field.  After you enter the URL, click into the Document Library field, where a drop down list will allow you to choose the appropriate library within your site.  Then, choose your Excel Workbook within the workbook field, and finally, choose your data source.  Now, you can see why you may want to name different ranges within your worksheet, as they all become individually exposed data sources. 
Here's a sample of how my data source looks when all fields are complete:

With our data selected, the last thing we have to do is define our data, so it's usable within our dashboard.  While still within our data source, choose the View tab.  Here you can see your data source.. pretty helpful, eh?

 

As I said, we have to define the data.  No worries, each column just needs a name (for display), a unique name, a type, and an aggregation.  First, do yourself a favor and click "Preview Data", this will show you the data you have available.

 

Much better..  Now, for each column of your data set, edit the properties (to the right) to provide a name, unique name, type and aggregation. 

IMPORTANT: You're working with a data source.  As such, you need to define what your "rows" are within the data source.  By default, Dashboard Designer will attempt to choose what the rows are by indicating a column type of dimension on the first item in the list.. in the case of this article, that's correct.  But be aware of what the different column types are, as they'll play a role in your usage of this data at a later point.

Here you can see that for each column, Im providing a name and unique name (keeping them the same, no reason for me to get fancy), making sure the column type is a Fact, and choosing Aggregation as None, since the figure is a flat figure and I dont need that column data aggregated. 

When all was done, my data source looked like this:

 

Finally, choose the Properties tab, enter an appropriate name, and make sure you enter a Display Folder for your data source as well.  Dashboard Designer has a relatively painless approach to letting you create\switch folders, but this is a CRITICAL step, as when you continue to create data sources, KPI's, dashboard, scorecards and reports, your workspace can get cluttered pretty fast.

And that's it!  Now you have a data source to drill into within your PerformancePoint dashboard.  In our next article, I'll walk through creating metrics against this data.

SharePoint 2010 - Connecting a KPI Scorecard to Display Charts all via Excel Services

Where I work, the executives receive a sales summary report that shows current sales totals, compares with the previous months sales totals, and also YTD totals.. some pretty standard stuff.  With our SharePoint implementation, I'm trying to web enable the report to provide some rich features to the report.  Once such feature is adding a chart.  The current report layout doesn't provide much space for performance charts (other than some Sparklines), but thankfully, the web is made for this stuff.  Currently, I already allow a user to view a sales performance KPI scorecard.  Additionally, my report is uploaded into SharePoint so I canus it as an Excel Services data source for my dashboard.

So my end goal here was pretty simple. What I wanted to add was the ability to choose a row - which contains my sales territories - and have the appropriate regional sales chart.  Sound's simple enough, right?  It is, but using Excel Services caused a few wrinkles, which we'll discuss in this blog post.
(This article assumes you already have a KPI scorecard published to your Dashboard.  If you don't, go ahead and do that first.  There's lots of stuff you can search for, but here's a good starting point: http://blogs.msdn.com/b/performancepoint/archive/2011/09/28/creating-simple-budget-kpis-from-sharepoint-lists.aspx)

Step 1 - Publish your data
The first step to this scenario is to make sure you have your chart's published.  In Excel, create your charts, and name them appropriately.  Then, save them to SharePoint.  You'll need to know your chart names for our step's down the road.
For more information on publishing data from Excel to SharePoint, the fine folks at Microsoft did a good job  explaining this:
http://blogs.office.com/b/microsoft-excel/archive/2009/11/23/uncovering-publish-to-excel-services-in-excel-2010.aspx

Step 2 - Create A List
OK, we need a list for this process.  Why?  Well, you'll see in the steps below that we need a way to MAP our sales territory name to a territory chart.  This list will provide that purpose.

• In SharePoint, create a new Custom List - call it whatever you want.  If you want to copy me, call it Chart Lookup. (Real creative, eh?  That's what you get for copying me). 
• Go into the List Settings, and change the  name of your TITLE column to "Territory". 
• Here's the cool part.  Now, add another column, and for type, choose Picture or Hyperlink.  Name it something (I called mine IMG), and for "Format URL", choose "Picture". 
• Why do this?  When the list renders, the URL to your image will render as well.. so you're essentially providing the source for an image tag.

Click OK, you're done editing your list.

Step 3 - Fill Your List with Data Goodness

What good is a list if it has nothing to list!?

• Add a new item to your list, for the Territory, give it your Sales Territory Name. 
• IMPORTANT: The name you provide here need's to match your Territory name from your KPI scorecard.  So if your Territory list's as "North West" on your Scorecard, enter North West.
• For the Image URL, use your Excel REST URL to the appropriate Territory chart.  (If you don't know what your REST URL is, start here: http://blogs.office.com/b/microsoft-excel/archive/2009/11/09/excel-services-in-sharepoint-2010-rest-api-examples.aspx) 
• As an example, my REST URL is http://intranet.company.org/BI/_vti_bin/excelrest.aspx/Excel%20Data%20Sources/Report.xlsx/model/Charts('Chart_Territory_NorthWest')?$format=image

You entered your Territory and a URL to your chart, that's all there is to it.  Just wash, rinse and repeat for every territory.

Step 4 - Create a view to view the charts you want to view
I want you to trust me on this... open SharePoint Designer.  I promise, this won't be that bad. 

• Once within SharePoint Designer, open your BI site, choose File, Add Item, and select More Pages. 
• Add an ASPX page, and give it a name (mine's regional charts)
• If the page doesn't automatically open in Edit Mode, choose to edit the file. 
• If you receive the message "This page does not contain any regions that are editable in safe mode.  Do you want to open this page in advanced mode?"  Click Yes.
• Click the Insert Tab, and choose Data View
• Under Data Sources, choose the List you created in Step 2

At this point, you should now see your chart's show up within SharePoint designer.  Now we have a view, linked to a page, within your BI site.  All we need to do now is filter the chart that you show.  The following step's were followed from Jason Burns, who did an excellent job demoing this feature at TechEd 2011 - http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI321?format=auto - start at 27:00, but really the whole presentation is worth watching.

Step 5 - Add a filter to the view to only view the chart you want to view

• Still within SharePoint Designer, choose Filter within the Ribbon and add a new filter.
• For Field Name, choose Territory
• Set Comparison to Equals
• Open up the drop down list for Value and choose Create a New Parameter
• Enter a name for your parameter, such as Territory
• Set Query String as your Parameter Source
• Important: For Query String Variable, enter the value EndPoint_URL.  By default, this is how SharePoint chooses to share data via query string.
• Click OK.

Now, we have a page that displays our territory sales charts, and it's filtered to only show one chart at a time, based on querystring.  Go ahead and give it a shot in your browser, browse to the page you just created, and add ?EndPoint_URL=North West (or whatever territory you want to show).  You should only see the chart for that particular territory.

Step 6 - Add your filtered page to your Dashboard.
We're in the home stretch, all we have to do now is wire our scorecard to our filtered page.

• Open up Dashboard Designer for your BI site. 
• Choose the create tab, and choose Other Reports.
• For the report template, choose Web Page
• Within Report Settings, enter the URL to the page you just created in SharePoint Designer, WITHOUT the EndPoint_URL parameter... as I said above, SharePoint add's that for you! 
• Save your "report"
• Find the Dashboard page with your KPI scorecard, and add the web page report to the page.. any zone is fine.

Step 7 - Connect the parts, fa-la-la-la
At this point, you have a KPI scorecard, and a report that displays a chart based on a querystring parameter.  Now we'll just add a connection from your scorecard to the report, so the appropriate report will show when a territory's row is clicked (or tapped, if we're using a tablet).

• On your dashboard page, hover over your scorecard and find the connection DISPLAY VALUE under ROW MEMBER - this is important, you want the Display Value for Row Member since your territory name is your trigger.
• Click and drag that connection to your Web Page report.  You won't have any choice here, the value will want to connect to the report's URL.

That's it, just click OK, and now you have a web part connection.  Save all your dashboard changes, publish your dashboard, and select a territory.  You should see your chart's change based on the territory selection.

SharePoint 2010 Dashboard Designer - Hide KPI Target Values in the Scorecard

My KPI target's on my dashboard scorecard's are based on a calculated metric.  For example, my target value is a 1, and I divide some numbers and compare that value to my target (1), if it's above a 1, that's good, if it's below, that's bad.

The SharePoint Dashboard Designer for a BI site is really good for stuff like this (it better be, because that's what it's designed for, actually).  I had one issue plaguing me on this, and after some random clicking, I was able to figure it out.

As I mentioned, my target value is always a "1".  My user's don't need to see, or even care about, my target value in this case, they just want to see a colored arrow indicating whether or not they should plan on firing someone.  Nevertheless, I was getting this stupid, annoying, aggrevating value!

 



Maybe it's because I'm unaccustomed to some BI and\or SharePoint terminology, but I couldn't find out how to remove this without some errant right-clicking.  In the end, removing this value is done in the scorecard designer itself, and not in the KPI setup.  To remove the target value, right click the KPI cell, select Metric Settings, and set Data Value to None.  Hope this helps!

Hmm, so you CAN validate an Excel files compatibility with SharePoint 2010

Well, I stand corrected.  In my previous post on Excel Data Validation as an unsupported feature in SharePoint and the perils of hidden worksheets, I may have griped a little and said

So I moved one of our report's into SharePoint, which I received the following error (side note.. wouldn't it be great to validate the file before publishing it Microsoft?)The workbook cannot be opened because it contains the following features that are not supported by Excel in the browser:

- Data validation

Would you like to try and open this file in Excel?
 

Well, I stand corrected!

 Clicking the File Menu in Excel (2007\2010), Under the Info tab, you have the option to "Check for Issues".

Once clicked, you can choose to "Inspect" your document, where all the bad stuff that SharePoint yells at you for can be stripped out.

Side note: It's shocking how often sensitive\personal information is attached to your documents.  You should use this feature in all Office document types to scrub your files before you start sending them around.

Excel Data Validation as an unsupported feature in SharePoint and the perils of hidden worksheets

Several, if not all, of my posts so far have been based on SharePoint.  We have several teams where I work that publish and analyze reports, and several others that rely on the data of said reports.  As such, Im tasked with taking much of our business data and making it more "web enabled" (what a novel concept!).

So I moved one of our report's into SharePoint, which I received the following error (side note.. wouldn't it be great to validate the file before publishing it Microsoft?)

The workbook cannot be opened because it contains the following features that are not supported by Excel in the browser:

- Data validation

Would you like to try and open this file in Excel?

OK, after a few minutes of Googling, I found that there's a command to find data validation conditions.  In each workbook, choose Find and Select from the Home tab, Go To Special, and Data validation.  If the search finds something, it'll move to the appropriate cell, otherwise you receive an alert that there is no validation on the worksheet.

Once you happen upon your cell with validation, choose the Data tab, click Data Validation, and choose clear all.

Just repeat the steps listed above for each worksheet to remove all your validation.

Make sure you scrub ALL your worksheets..

Upon completing these steps, I published the file again, but still receive the same error.  I re-checked for data validation, published as different file names, restarted IIS, anything thinking it wasn't my excel spreadsheet... because I couldn't find the data validation anywhere else. 

Then, I found out that there were HIDDEN worksheets in my workbook too.  I right clicked the worksheet area, clicked unhide, and chose the hidden worksheets.  Wouldn't you know it, the hidden worksheets also had data validation.

Simplify, simplify, simplify:

So in closing, I learned the following:

• You can "find" data validation in Excel via the Find and Select command
• You need to "guess" if worksheets are hidden, and be sure to unhide them when removing any type of "unsupported features" for Excel Web Access in SharePoint
• When publishing a workbook to SharePoint, the unsupported features must be removed from EVERY worksheet\item\range in the file, not just what you are choosing to "publish".

SharePoint Backup Error Object Index Partition 0 failed in event OnPrepareBackup

My SharePoint site was installed without the SP 2010 SP1 service pack, so before installing the SP, I wanted to perform a backup, lest I allow Mr. Murphy to grab the empty seat next to me in my cubicle and hang out for a few days laughing at me and drinking my coffee.  (Anyone can laugh at me, but you DO NOT drink my coffee!).

So I went into the SharePoint Central Admin, chose the backup option, ticked off the Full option (as opposed to differential) and chose the root Farm node for the backup choices.  Once I chose to start the backup, almost immediately I found that the error "Object Index Partition 0 failed in event OnPrepareBackup" was reported.  I googled\researched\rebooted\re-tried my backup, but all to the same conclusion.

Down The Rabbit Hole

It wasn't until after I looked more closely at all the gobbledygood in the error logs that I found the following:
[10/8/2012 9:34:39 AM] Verbose: Starting object: Search Service Application.
[10/8/2012 9:34:39 AM] Verbose: Saving SPPersistedObject State
[10/8/2012 9:34:39 AM] Verbose: [Search Service Application] Stored this object's parent type: Microsoft.Office.Server.Search.Administration.SearchQueryAndSiteSettingsService
[10/8/2012 9:34:39 AM] Verbose: [Search Service Application] Stored application pool name: SharePoint Search Crawl App Pool
[10/8/2012 9:34:39 AM] Verbose: [Search Service Application] Storing the process account username 'DOMAIN\serviceaccount'.
[10/8/2012 9:34:39 AM] Verbose: [Search Service Application] Stored default endpoint name: http
[10/8/2012 9:34:39 AM] Verbose: Starting object: http.
[10/8/2012 9:34:39 AM] Verbose: Saving SPPersistedObject State
[10/8/2012 9:34:39 AM] Verbose: Starting object: https.
[10/8/2012 9:34:39 AM] Verbose: Saving SPPersistedObject State
[10/8/2012 9:34:39 AM] Verbose: Starting object: Search_Service_Application_DB_ad1fa25e6f84a6e952.
[10/8/2012 9:34:39 AM] Verbose: Saving SPPersistedObject State
[10/8/2012 9:34:39 AM] Verbose: Starting object: Admin (C: on SERVERNAME).
[10/8/2012 9:34:39 AM] Verbose: Saving SPPersistedObject State
[10/8/2012 9:34:39 AM] Verbose: Starting object: Search_Service_Application_CrawlStoreDB_579164d3.
[10/8/2012 9:34:39 AM] Verbose: Saving SPPersistedObject State
[10/8/2012 9:34:39 AM] Verbose: Starting object: Crawl-0 (C: on SERVERNAME).
[10/8/2012 9:34:39 AM] Verbose: Saving SPPersistedObject State
[10/8/2012 9:34:39 AM] Verbose: Starting object: Search_Service_Application_PropertyStoreDB_33d0b9.
[10/8/2012 9:34:39 AM] Verbose: Saving SPPersistedObject State
[10/8/2012 9:34:39 AM] Verbose: Starting object: Index Partition 0.
[10/8/2012 9:34:39 AM] Verbose: Saving SPPersistedObject State
[10/8/2012 9:34:39 AM] FatalError: Object Index Partition 0 failed in event OnPrepareBackup. For more information, see the spbackup.log or sprestore.log file located in the backup directory.
 NullReferenceException: Object reference not set to an instance of an object.

Interestingly enough (to me, anyway) is that all these error's seem to surround the Search Service.  When I viewed the results of the backup process in the Central Admin, I found that all other backup tasks completed properly, it was just the Search Service that was causing my headache. 
(At this juncture, I thought hmm.. I don't really need to backup the search service, do I?  Just then, I pictured Mr. Murphy tossing my ibuprophin, acetometaphin and liquor out the window and drinking my coffee.)

Within Central Admin, I clicked around looking for more more tips on this search stuff.  I'll spare you the boring details and cut to the chase.  After some blind luck, I went to Manage Service Applications where I found my Search service.  I chose to highlight the row and click Manage.  There, I found the error message "The search service is not able to connect to the machine that hosts the administration component. Verify that the administration component c8519b74 status -<GUID> in search application Search Service Application is in a good state and try again".  Hooray!, I thought, thinking that all I have to do is configure my search service properly, and away I go with performing my backup again.

Further Down The Rabbit Hole

I followed the steps posted here - http://blogs.technet.com/b/poojk/archive/2011/11/28/sharepoint-2010-search-service-is-not-able-to-connect-to-administration-component-server.aspx - thinking that I just had to enable my search service.  However, after performing these steps, and other troubleshooting, my search service would always indicate that it wasn't active.  Finally, I decided that I should follow some other suggestions and remove the Search Service application entirely, see if that resolves my backup issue, and if so just re-install my search service at a later time.  I gave the service a final blessing, clicked it, and chose Delete.  Central Admin just hung there.. I made a coffee, clicked around the Interwebz, and after 30 minutes or so found it was STILL hanging.

Deeper Further Down The Rabbit Hole

I tried deleting the Search Service, disabling some potentially related services, rebooting the server several times, but in the end, the bloody thing wouldn't delete!  Finally, I came across another handy dandy blog post - http://donalconlon.wordpress.com/2010/08/27/deleting-the-search-service-application/.  Here, it's recommended to use stsadm.  The problem here is that stsadm isn't the recommended method to perform admin tasks, but I was helpless at this point.  Help me stsadm, youre my only hope!

I used the steps provided in the blog post (stsadm.exe -o deleteconfigurationobject -id <GUID>) and the service finally deleted, HOORAY!

Out Of The Rabbit Hole

At this point, I rebooted once more, just to be safe, checked my Central Admin, and saw that the Search Service removed.. which it did.  I tried another backup, and no error's this time.  I went on to install Service Pack 1, and we all lived happily ever after.

Viewing valid certificates in IE 9

Man this issue threw me for a loop. 

So I have my SharePoint 2010 site setup to use a Business Intelligence site, but were running our site on SSL.  Low and behold, the site certificates need to be installed on the server and added to the server farm.  (Thank you Kevin Donovan - http://blogs.msdn.com/b/performancepoint/archive/2012/06/19/leveraging-performancepoint-in-https-enabled-sites.aspx).

His steps worked terrificly, but for one issue, in using IE 9, I couldn't view my site's certificates.  In older versions of the browser, the lock would appear next to the URL.  Or, in some instances, a "Security Report" will be available on the current certificate.  In this case, I had neither. 

The solution - In the end, cant believe this worked.  Right Click the page, view Properties, and click Certificates.  Voila!  Not that this functionality was never there before, because Im pretty sure it was, but atleast now I know you can always get certificate information on the site just because of the properties.

Hope this helps!

Code blocks are not allowed in this file ppssample.aspx

Well this is a new one.  I've done some MasterPage customization for our SharePoint site.  After installing a Business Intelligence site, I tried to start up the Dashboard Designer by clicking the "Start Using PerformancePoint Services" link on the homepage.  Rather than it starting, it freaked out, telling me "Code blocks are not allowed in this file ppssample.aspx".

Fortunately, I have debugging mode turned on in SharePoint, so I received a yellow screen of death.  The yellow screen also shows the link it was trying to open, which is

var designerRedirect = "_layouts/ppswebparts/designerredirect.aspx";

So, on a whim I tried http://mySharepointSite.com/sitename/_layouts/ppswebparts/designerredirect.aspx, and wouldn't you know, the designer opened.

Apparently, once you use a customized version of a MasterPage, your custom MasterPages run in a partial trust safe mode that blocks certain functionality.. including (you guessed it) code blocks.

So you can revert your business intelligence site back to an OOB MasterPage, or try opening the designer using the method described above.

SharePoint 2010 Error: The SharePoint Server Publishing Infrastructure feature must be activated at the site collection level before the Publishing feature can be activated

I tried to publish a new Business Intelligence Center site to my enterprise SharePoint 2010 site, but it failed because of the error "The SharePoint Server Publishing Infrastructure feature must be activated at the site collection level before the Publishing feature can be activated".  What I found really odd was that there weren't too many posts on how to resolve. 

Fortunately, it's a pretty simple fix.  After receiving the error, I scoured my SharePoint Central Admin, looking for any and every link involving the terms "SharePoint", "Server", "Publishing", "Infrastructure" and "Hey Eric this is what youre looking for".  Alas, none of those jumped out at me!

Then, based on some guidance on the web, I found that this setting isnt anywhere within the Central Admin website, it's on the SharePoint Front End. 

• First, you have to make sure your an administrator within your Site Collection. 
• On your top level site, go to Site Actions, and choose Site Settings.
• Underneath Site Collection Administration, choose Site Collection Features
• Towards the bottom of the page, choose Activate on SharePoint Server Publishing Infrastructure

And that's it!  See.. told you it was a pretty simple fix

Converting a SQL Analysis Report from Native Mode to SharePoint Integration Mode

This will cover creating a new data connection and report library, taking an existing report from Reporting Services – Native Mode that uses a Shared Connection, and migrating the existing report to the new SharePoint location

What you’ll need:

• SharePoint Report Library – To hold your published reports
• SharePoint Data Connection Library – To hold your shared data connections
• A report you want to save
• Your data source(s) – you can find these within the report references
• SQL Server Report Builder – This is a local report building client available here: http://www.microsoft.com/en-us/download/details.aspx?id=6116

Let’s start within SharePoint.  Enter the SharePoint site you want visitors to consume reports from.

 

Step 1: Setup your Report Library in SharePoint

If you don’t have a Report Library to save your reports to, these steps will help you set one up

• On your SharePoint site, choose Site Actions and More Options. 
• Filter by Library
• Choose Report Library
• Provide a name for your report library.  If you cant decide on one, just go with “Reports”
• Click Create

 

 

Step 2: Setup your Data Connection Library in SharePoint

Similar to the steps above, we want to create another library on our site, only this time to hold our data connections that our reports use.  This way, we can share data connections with other reports.

• On your SharePoint site, choose Site Actions and More Options. 
• Filter by Library
• Choose Data Connection Library
• Provide a name for your report library, if you cant think of one, go with Report Data Connections
• Select More Options
• (optional) Under Navigation, choose No.  This will suppress the Data Connection Library from showing on the left navigation of your site.  While optional, typically there’s no reason for your users to need quick access to your data connections.
• Click Create

Once you have your Data Connection Library created, we just want to do some housework while we can, to keep things neat and tidy.

• Choose the Documents tab underneath Library Tools in the Ribbon. 
• Choose New Folder
• Create a folder called Data Sources. 
• Repeat this process, and create a folder’s called
• Datasets
• ODCs
• Report Parts
• You may not need all of these folders to convert your report, but now you have the structure in this library to hold all data sources, data sets, office data connections (like connections from Excel) and Report Parts going forward

Lastly, we need to enable some file types in our connection library, to allow us to create new data files within SharePoint

• While within your Data Connection Library, click Library Settings from the Library tab underneath Library Tools in the Ribbon
• Choose Advanced Settings
• For Content Types, choose Yes to allow management of content types
  
• Click OK
• Further down on the Library Settings, under Content Types choose Add from existing content types
• From the Available Site Content Types, choose:
• Report Builder Model
• Report Builder Report
• Report Data Source
• Click Add
  
• Click OK

Step 3: Get your Report

The above steps are a one-time deal, once you have your libraries created, you have a spot to always store this data.  Now, we can look at “converting” your report from Native Mode into SharePoint.

• Go to your existing report server (typically http://<reportserver>/Reports)
• Browse to the appropriate directory that contains your report.  Here, Ive navigated down one level to choose a Claims Paid report
• Hover over the report, click on the arrow, and choose Download..
  
• Once given the option, choose to Save your report.  Obviously, make note of where you saved this report.

Step 4: Gather your report properties to “convert” your report

Have you noticed I put the term convert in quotes a few times?  Its because were really not converting anything, more or less just saving it to a new location.  To perform the rest of the steps, you need to have installed SQL Server Report Builder, linked to at the top of this article

• Browse to the location where you saved your report, and open the rdlc file. 
• When prompted to connect to a report server, just hit Cancel
• In the Report Data section on the left, expand the Data Sources section, to expose the data sources required for this report.
• All were going to do is update the connection properties for this report.  We don’t want to delete it, because doing so would break all references to the Dataset’s associated with this connection
• Take note of the name(s) of your Data Source(s).  Here, I only have 1 DataSource
    
• Return to your Report Server
• Browse to your Data Connections.
• Find the Data Connection identified in the previous steps, hover over the Data Connection, and choose Manage
  
• Within the Properties section, view\copy the Connection String used as a data source
  
• Under the Connect Using section, take note of how\what credentials are used. 
  

 

Step 5: Create your Data Source in SharePoint

• Now, return to your SharePoint Data Connection Library, and choose your Data Sources directory we created earlier
• In the Ribbon, under Documents, choose the New Document Arrow, and choose Report Data Source

 We want to mimic the settings you found earlier on your Report Server

• Provide a Name for your Data Source – preferably something that matches the name of the data source you’re re-creating
• Specify your Data Source Type
• Enter your Connection String
• Specify your Credentials
• Choose to Enable the data source

Basically, the settings for the new data source are basically the same as what was on your report server from Step 4

  

• Click Test Connection to make sure your connection is properly setup
• Click OK to save this data source to your report library

Step 6: Associate your new Data Source

Return to your report in SQL Server Report Builder, now we can associate our new data source with the rdl file.

• Right Click your Data Source, and choose Data Source Properties
• With the Use a shared connection or report model enabled, click the Browse… button
• Within the Name textbox, enter the URL for the path to your Report Connection Library.  Here, I know that my report connection library is located at https://intranet.org.com/Sales/Report Data Connections
  
  
• Next, choose Data Sources
  
• Lastly, choose your rsds data source you created and click Open
• Once complete, you have now associated your shared data source with the rdl file.  Click OK
• Under Views in the Ribbon, choose Run to ensure that your report now works with your newly associated data connection

If the report is working properly, you should have the option to edit your parameters at the top of the report (if you have parameters).  Enter your data, and choose View Report

  

If there is an issue with connecting to your data source, you will be prompted with an error message.  Resolve any errors, and try previewing the report again.  If it’s an issue with the data source authenticating the user at run time, and you think you resolved the issue, you may have to Refresh the report, by choosing the refresh option.
 

 

Step 7: “Convert” (Save) Your Report

All the hard work is done, all we need to do now is just save our report to SharePoint

• Still within SQL Server Report Builder, click the report button in the upper left hand corner
  
• Choose Save As
• In the Name field, enter the URL to the Report Library you created in Step 1.  Here, I know that my report library is located at https://intranet.org.com/Sales/Report Library
• In the Name Field, enter the name of your Report. 
• You can keep it consistent with your existing reports if you wish, and re-enter the same name as the report you are converting.  So, here I chose to enter Claims Paid.
• Click Save
• Return to SharePoint, and enter your Report Library
• Choose your report
• Validate  that is runs within SharePoint

Sharepoint 2010 and IIS on a new drive

For my SharePoint implementation, we had created an OOB web server, and installed SharePoint as a Web Front End (WFE).  However, we wanted to keep the OS drive minimal and separate (for security purposes).  As such, we created a second drive, and the task seemed simple enough - move IIS and SharePoint to the new drive.  Doing so caused quite a few headaches, and it also broke my custom SharePoint Designer Masterpages.  Actually, in the end, it is simple, but as they say, life is not the destination, but the journey.  As you can guess, this post is the summation of that journey.

So how do I do this?

Simply put:

1. Create your new IIS location
2. Install SharePoint
• When given the option, choose to install to your new "inetpub" location.
3. Move all your existing webs to new location (including SharePoint)
4. Update IIS to recognize new webroot for SharePoint web and SharePoint Central Admin

So why was this so difficult that it required a blog post?

Well, if one were to follow the order of operations listed above, then all goes well.  Alas, I was unaware of the order of operations, and instead had done something similar to the following:

• Install SharePoint
• Created my new IIS location
• Attempted to move SharePoint to new IIS location
• Angered the SharePoint gods (unknowingly)
• Successfully Browsed SharePoint site - and rejoiced!
• Discovered my custom SharePoint design is now broken
• sobbed and repented
• Un-installed SharePoint
• Re-Installed SharePoint, and took notice of the handy-dandy tab to choose my installation location
• Updated IIS to recognize the new location of my webroots
• Broke SharePoint Central Admin
• Freaked out
• Updated references on MasterPage to recognize SharePoint location
• Fixed Central Admin

As you can see, my method was not very concise.  You're welcome to try it.. but if you want to move IIS to a new location, these are the steps to run IIS and install SharePoint from a non-default drive point.

Installing SharePoint to a non-default location.

As mentioned above, this is actually, insanely simple.  During installation, choose the File Location tab when choosing Complete or Stand-alone installation types.  Choose your drive location and continue.  This will NOT install to a different IIS directory off of your "C" drive, this will just install certain application files in a separate directory.

Moving IIS to a new drive location

On your web server, create your new directory where you want to host IIS from. Then, download the MoveIIS7Root.zip file from Thomas Dehl's blog post:  http://blogs.iis.net/thomad/archive/2008/02/10/moving-the-iis7-inetpub-directory-to-a-different-drive.aspx.  Edit the bat file to indicate the location of you new IIS Root, and execute the statements.

Moving SharePoint's webroot to the new IIS location

With IIS now on it's new location, you need to ensure that the IIS references to SharePoint's webroot are accurate.  Open up the SharePoint PowerShell Admin tool and use the following commands (thank you to sbommana for the guidance on this - http://sbommana.wordpress.com/2011/05/11/move_inetpub_on_sharepoint_wfe/):
#Create a web application object
#Example: $SPSite = new-object Microsoft.SharePoint.SPSite("http://intranet.foo.com")
$SPSite = new-object Microsoft.SharePoint.SPSite("URL")
$WebApp = $SPSite.WebApplication

#Change SPUrlZone if not in default zone
$IISSettings = $WebApp.IisSettings[[Microsoft.SharePoint.Administration.SPUrlZone]::Default]

#Point to your new directory
#Example: $IISSettings.Path = "E:\inetpub\wwwroot\wss\VirtualDirectories\<SiteID>"
$IISSettings.Path = "<Your IIS Path Here>\wss\VirtualDirectories\<SiteID>"

#Update Web Application
$WebApp.Update()
$SPSite.Dispose()

Restart IIS, ensure that your SharePoint web is responding.  Then, try your SharePoint Central Admin site to ensure that it is responding.  If it isn't, check your server event logs for clues.  In most cases, you may find the item "Safe mode did not start successfully".  This issue could be because of service permissions.. but chances are in this case, because you just performed open heart surgery on SharePoint.  To resolve, unprovision and re-provision your central admin site by using the following commands using the command prompt (and dont forget to run as admin!):

psconfig.exe -cmd adminvs -unprovision
psconfig.exe -cmd adminvs -provision -port <port number>

What about the SharePoint Designer issue?

Here, my MasterPages were edited via SharePoint Designer.  Once I performed the above steps, my SharePoint site was responding, but I couldn't edit or deploy any of my custom MasterPages.  When I was viewing them within SharePoint designer, none of the controls would even load within the designer. My assumption here is that it is also related to the aforementioned open heart surgery on SharePoint.  Alas, once again the fix was relatively simple.  I created a new site in SharePoint, opened up the default MasterPage in SharePoint Designer for that new site, and copied over the "Import Namespace" and "Register Tagprefix" page directives to my currently broken MasterPage.  I saved, closed, and re-opened the page in the designer, and SharePoint designer was happy once again.