Thursday, December 13, 2012

Rotating Quotes on my Intranet via ASP.NET

I had a few hours to perform some cleanup activities, and I wanted to mix in something a little fun for my Intranet.  Where I work, some business\leadership writers are often cited and\or celebrated, to the point that our conference rooms have been named the "Godin Room", "Buckingham Room" or "Collins Room", and in each room a few samples of their work.  I thought it would be fitting to take quotes from some of these leaders, place them on an image, and rotate an image periodically on my homepage.  This ended up being so stupidly simple, I wanted to post some code to do it as well.

Step 1 - Create your images

As indicated above, create the images you want to rotate.  For me, I found a plethora of quotes, and placed a quote on an image that was a picture of a room, hallway, or somewhere else in our building*.  In the end, I wound up with 27 different images, looking similar to the following.


The key item here is that you probably want every image to be the same size.  In the end, I had a 300x200 jpg, and then added some whitespace below it to add a quote, so when all was said and done, each image is 300x283. 

Finally, you also want to name your images consistently.  So name the images 01.jpg, 02.jpg... 24.jpg.

Step 2 - Write some code

In principle, all I wanted was to show an image.  I didn't it to randomly load each time, because some of my users are mobile users, and I didnt want to eat up useless bandwidth.  Plus, I didn't want to randomly load an image periodically.. if I made 27 of these things, I wanted them to cycle through appropriately. In the end, all I did was:
  • Create a text file that stores the value of the last image that was shown
  • Store an image file name in server cache that expires
  • Set a parameter that indicates what the "last" image is in our rotation
Again, this is stupidly simple.  In an ASP.NET project, create an images directory off of your root, and store all your images in there.  Next, create a new page.. or use default.aspx.  You don't need ANYTHING on the aspx page, so go right to the code behind.

In the code behind, I used the following code in the page_load

As stated above, all this code is doing is reading the current value of an http cached item.  If that value isn't null, then the item hasnt expired yet.  If it is null, We read a line in a text file to determine what the "last" image was that was displayed.  We then increment that number by 1, add that value to our cache, update our text file to reflect the current files image name, and write the image out to the page.  The last line here is of the most importance though.. we also set cache of the response object to none... this way, the users web browser doesn't cache the result.  If it does so, then the image would never update on their PC, unless they performed a hard refresh or cleared out their browsers temporary items.

Thirdly, in your web root, create a text file called "lastimg.txt".  In that file, enter the term "01", save and close the file.  Now, when you run the project, the application thinks that the last image displayed to a user via cache was 01.
Now, you might be thinking.. do I specify a cache timeout?  Or what about a jpeg extension? Or how do I know when I got to my last image!?  Have no fear, the web.config is here.  Add these items to your web.config:

    <add key="maxFile" value="27"/>
    <add key="fileType" value="jpg"/>
    <add key="expTimeMinutes" value="60"/>

These items are already included in the logic from the code displayed above.  MaxFile is the last file that you have in your rotation (so if you only have 12 images, this value would be 12.. and so on).  FileType is your image file type, all mine were jpg, so I stored this value as such.  expTimeMinutes is the amount of time an image will be cached for, before rotating to the next item.

Step 3 - Host It!

Go ahead and run\debug your application.  When you see it working, go ahead and deploy this piece of modern artistry.  Just make sure that the user the application is running as has write access to the lastimg.txt file, so it can update it as it runs.

Once deployed, in SharePoint, create a new Image Web Part.  In the properties, for the Image link, just link to your application's <whatever_you_called_it>.aspx page, and click OK.  Now, your images will display in your Intranet site!

*Someone in our building took hi-res photo's of our boardroom, front lobby, etc.  I took these images, popped up in PhotoShop, and used one\two of the filters to give the pictures a unique effect, such as an oil painting feel, or a colored pencil drawing.

The permissions granted to user are insufficient for performing this operation. (rsAccessDenied)

With my SharePoint 2010 launch looming, I rolled my site out to some "select" users to give the site some gas, revv up the engines, and hopefully not have it come to a screeching halt.  Almost immediately, my users encountered an unusual error when attempting to execute an SSRS report we converted - "The permissions granted to user <user> are insufficient for performing this operation. (rsAccessDenied)"

When I hit the site, everything worked fine for me.. then again... Im an "owner" throughout my site... so that doesn't prove much.  I also saw that I'm inheriting permissions from the parent site, where all domain users have read access.. so the user can see the report, they just can't execute it.  I spent a few hours looking at database permissions, SSRS report properties, etc.  In the end, I mistakenly uncovered the solution.

My SharePoint site has publishing features enabled, which is kind of nice.. but some things are treated as "private" until their approved.  With reports, that means data connections.  In my site, I wound up in my Data Connections Library (which should be visible after clicking Site Actions -> View All Site Content).  Within my data connection library, the approval status on my data connection read as "Pending"

Once I edited the data connection (hover over the name, expand the drop down that appears, and click Approve/Reject), and set the connection to Approved, all was right with the world once again.  Odd issue.. but happy in the end it was such a simple solution

Tuesday, December 11, 2012

Recurring Error After Adding an ASP.NET 4.0 application to a subdirectory on SharePoint 2010

So a few weeks ago I was proud as a peacock after I got a 4.0 web application hosted as a SharePoint subdirectory, mentioned here:

I'm trying to update the authentication mode of my SharePoint install to use Kerberos, and had to flip some settings on and off in Central Admin in the process.  Low and behold, my sub application broke, and started giving me yellow screen errors.  I found in my Windows Event Logs that the "Target Framework Attribute" was unrecognized, and before I knew what hit me, I was back to square one with hosting my sub application!

What Happened? 

Well, when messing around with the authenticaiton settings, the SharePoint configuration files were updated, wiping out my custom changes in the process.  Fortunately, reverting to our previous changes was simple. 
  1. My sub application switched it's application pool back to the Default - 80 SharePoint pool, rather than my .NET 4.0 framework pool I had created specifically for this application.
  2. With the appropriate framework applied to my application again, I was now receiving "there is a duplicate ... section defined" error again. My SharePoint web.config was reverted (as mentioned above), removing our customizations!

    Thanks to Microsoft, they made our rollback easy, but it's good to know that this stuff can occur. When the config update was made, a previous version was stored in the directory ABOVE our SharePoint implementation, with the date and time appended to the filename. As such, I have the following:

All I had to do here was take my most recent config from this directory and move it into the "80" subdirectory, where my SharePoint intranet is running, and I was back to where I was before I tinkered with Kerberos and other settings. 

Thursday, December 6, 2012

Career Tips For the Future IT Professional

Last night was the last classroom meeting for the course I have been teaching, CIS 108 - Intro to Technology.  The class met once a week for 3 hours per meeting where I usually offered homework for each week as well containing a quiz on the chapter discussed in class, and a video to watch\discuss in a discussion board.  Most weeks I had chosen videos from that discussed a range of topics from Who Are Hackers to capturing video at fentometer speed to what physically makes up the Internet to data transmission via light.  All in all, my students seemed to enjoy the video topics.

Since we hit the end of the semester, I had compiled a list of items I thought would be helpful to a future IT professional.  The educational model in America has shifted enormously over the past 20 years or so.  A college degree used to be a ticket to a new job\career, where now employment is more of a buyer's market.  Everyone has a degree, you all look the same on paper, and often the question asked is:

How do I get a job if I have no experience, yet I cant get experience because no-one will hire me!?

First of all, I phrased that question in such a way because of how negative it is.  If you find yourself asking this question, in a same negative tone, re-frame your perspective on your job search, don't be a defeatist.  Secondly, you need other means to grow and prove your technical aptitude outside the workplace.  This isn't a bad thing, because all experienced, quality and successful technologists develop and hone their skills both inside AND outside their workplace as well.  Do you want to be successful?  Well.. why not do what other successful people do?  Novel concept, eh?

So, the items below were part of my final presentation to my class.  Some students just started their college career's, others were taking IT classes part time, while remaining in the workforce in other industries.  I wanted to offer suggestions for my students to help them develop a professional resume, while not yet being a true professional.  I'm sure I will twist and tweak these items over time, but wanted to offer them nonetheless.

Career Tips

1. As soon as you can, create a professional profile.. and start building it out. These things can grow over time, but make an excellent resume builder.

  • Go buy a domain name (theyre really cheap), I use
  • Go to, create a new blog
  • Associate your domain name with – online help file helps you. This way, your blog now powers, looking very professional to future employers
  • This whole process takes 10 minutes

2. With your blog setup, publish!

Publish your thoughts, results of your school work, "interesting" tech articles from cnet, slashdot.. whatever.. this becomes a great resource to show your technical aptitude when you otherwise have little to no experience in the workplace.  
  • Create a PROFESSIONAL twitter account - not the one where you tweet how drunk you were last night, or how you ate too much pizza, but one where you can discuss things your mom wouldn't be offended by reading.  Use this account to post\link similar ideas and concepts discussed in your blog above. 
  • Create a LinkedIn account - add EVERYONE you know, and keep in touch, jobs happen because of people more often that a resume submission
  • Associate your domain with your linkedin, resume, wherever you can post it

3. Clean up your FaceBook profile

  • Remember, you can be "tagged" in pics, so anything of you doing anything you shouldn’t be doing, you should get rid of.  After all, if issues like this haven't taught us by now.. nothing ever will

4. Scour Monster, CareerBuilder and Dice for jobs you might be interested in

  • Get a feel for job needs based on the technology
  • Get a feel for salary in the area your researching to live in
  • Get a feel for relevant skills, what types of jobs are out there, but what are these positions looking for? Windows? Linux? Particular networking equipment? Certifications? Programming Languages?

5. And realize that sometimes the best jobs aren’t landed through Monster and CareerBuilder

  • Its also who you know, have that friend of a friend of a friend get you in touch with their boss.. who happens to be looking for an IT pro.  Check out The Power of Who to learn how landing your next job isn't because you filled out 1,000 resume's!
  • It's through Internships - paid\free internships give you great in-field knowledge, and build a resume, and potentially lead to a full time job
  • It's through “free” work, such as charities - much like internships, "free" work lets you build your technical skillset, and look fantastic on a resume.  Reach out to local charities and ask if they need any help from a technical perspective.  Chances are, if they don't need help, they may know someone who does..

6. Books - You should ALWAYS read.. always, always, always. 

Especially in the IT world, concepts are always changing.  If you're not much of a reader, change that habit, challenge yourself and do it.  I hardly ever read Fiction, some of my personal favorites I've read (in no particular order):
  • Drive - Daniel Pink - Misconceptions and solutions to what people's real motivations are in live
  • Total Money Makeover - Dave Ramsey - Solutions for personal finance to help you relinquish debt and build income, I almost feel this should be recommended reading for any student BEFORE they take out school loans!
  • Outliers - Malcolm Gladwell - Discusses topics like why Bill Gates was so great, how Korean airlines went from the worst to among the best airlines because of a cultural issue, or why most professional hockey players are born Jan-March. All in all, great perspective on what can make you great in your career
  • Einstein His Life and Universe - Walter Isaacson - Think of someone influential and learn about them, provides amazing perspective. For me, this was Einstein
  • Linchpin - Seth Godin - Learn how to be a key resource in your workplace, and why you can be almost indespensible
Oh, and you can get all of these used for under 10 bucks each. Remember that you are the same person every year, except for the people you meet and the books you read

7. Take Free Classes, now, or whenever you graduate

  • - Harvard\MIT classes lead and designed by Harvard\MIT professors.. for FREE!
  • iTunes U - recorded presentations from leading universities
  • Never stop learning...
Over time, I try to think of concepts that either helped me land my first job, or things that would have helped me grow my career over time.  In the end, none of these items above are quick wins or silver bullets, they're really just several individual concepts that can help you round out your career and professionalism over time, but the sum total is worth more than it's individual parts.  Hope this helps!

Monday, December 3, 2012

Adding an ASP.NET 4.0 application to a subdirectory on SharePoint 2010

My SharePoint 2010 site is running under SSL, and I have some web applications I want to keep as separate applications (aka, not publish them to SharePoint), and just have a web part page with a web page viewer.  This way, the page is harnessed within SharePoint, so it still has my SharePoint header\navigation, but its only showing a web application from

For setting this up, most of this was straightforward in that we had to create an "application" in IIS (7.5) underneath my SharePoint site, but I quickly hit a snag.  SharePoint 2010 runs under an ASP.NET 2.0 application pool.  Since 2.0 is old, I wrote my application in 4.0, so my parent (SharePoint) site is 2.0, but the child application is 4.0.

The issue here is that some settings\configurations are loaded because of the 2.0 framework, and launching the 4.0 framework application is also trying to inject similar settings, causing all hell to break loose (OK, just an error page.. but a sternly written one I must say).  To resolve, I did quite a bit of googling, and a little bit of loose translating from here:

In short, if you have to add a .NET 4.0 application as a sub-application to your SharePoint 2010 implementation, start with the usual steps, which are: 
  • Create a new application pool targeting the 4.0 Framework. 
    • If possible, give this the same identity to run under that your SharePoint site is running as. 
  • Then, create your directory on the server that will host your 4.0 application, and link to it within IIS (right-click the SharePoint web and click Add Application). 
  • Go ahead and deploy your application, and try to browse to it. 
If your in a similar spot than I, then you should have an error message similar to a 500.19, barking about a configuration error and that there is a duplicate <somethingOrOther> section defined.  To resolve...

  1. In your sharepoint web.config, we need to prevent inheritance of parent (sharepoint) settings, I surrounded system.web with:
    <location inheritInChildApplications="false" path=".">
  2. Also within our SharePoint config, I commented out the system.web.extensions section group (and everything within it), and added it to the .NET framework's 2.0 web.config, found at c:\windows\\framework64\v2.0.50727\config\web.config
  3. I then added the following to the modules section to my application's config file, to prevent these modules from inheriting
    <remove name="SPRequestModule"/>
    <remove name="PublishingHttpModule"/>
    <remove name="RSRedirectModule"/>
    <remove name="StateServiceModule"/>
    <remove name="SharePoint14Module"/>
At this point, I was still getting 500 error's, but the error message wouldn't even display for me.  Much like the aforementioned article, I had to enable Failed Request Tracking in IIS.  Once I did so, I found that the following error was occurring:
"Handler PageHandlerFactory-Integrated has a bad module ManagedPipelineHandler in its module list. 

Some more googling led me down the path that the 4.0 Framework wasn't installed properly.  That didn't make much sense to me, but it was easy enough to open a command line and execute: %windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i
Once I did so, my application started working.  Hope this helps!

Wednesday, October 24, 2012

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

Wednesday, October 17, 2012

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:

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:

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!?
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 - - 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.

Monday, October 15, 2012

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!

Friday, October 12, 2012

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.

Thursday, October 11, 2012

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

Monday, October 8, 2012

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

Friday, October 5, 2012

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 -

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

Thursday, October 4, 2012

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:
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 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 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

Tuesday, October 2, 2012

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 file from Thomas Dehl's blog post:  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 -
#Create a web application object
#Example: $SPSite = new-object Microsoft.SharePoint.SPSite("")
$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

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.