SharePoint 2007 Ate My Breadcrumbs

For a recent project my team created a process to programmatically generate new sites inside SharePoint 2007.  As it turns out this is pretty simple and can be accomplished with the method: SPWeb.Webs.Add.  You can find a good write-up of using this method here.

However, if you create a site in this manner you will notice the breadcrumbs do not show up.  This can be a major problem if you counted on breadcrumbs for site navigation.  Thankfully, this problem is easily fixed by adding the following line of code.

newSPWeb.UseShared = true;

Don’t worry if you have already created lots of sites, this setting can be applied after they have been created.  Just iterate through the sites and set this property.

Here is the full code for creating the site and ensuring that your breadcrumbs appear:

void WebsAddNoLeak(string strWebUrl)
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb web = siteCollection.OpenWeb())
        {
            using (SPWeb addedWeb = web.Webs.Add(strWebUrl))
            {
                addedWeb.UseShared = true;
            }

        } // SPWeb object web.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called.
}

Redirect User After Submission of New or EDIT List Form in SharePoint

I recently had the requirement to redirected uses back to a MOSS sites main page after creating a new list item or editing an existing one.  The default behavior in MOSS is to you are redirect back to the list.  Thankfully MOSS embeds the URL that will be used after clicking the OK button in the URL of the new or edit form.  This URL parameter is called Source.  Knowing this it was possible to write a JavaScript function that could be added to the ASPX page to either update or insert this value associated with the parameter.

Here is the function originally written by another RDA Consultant Viktor Dolezel and modified by myself to work in additional cases. 

<script type="text/javascript">
 
_spBodyOnLoadFunctionNames.push("redirectAfterSave");
 
function redirectAfterSave() {
   var newUrl = "%2Fsite%2Fdefault%2Easpx";

   //parse the form action query string into a map 
   var qs = document.forms[0].action.substring(1, document.forms[0].action.length);
   var args = qs.split("&");
   var vals = new Object();
   for (var i=0; i < args.length; i++) {
     var nameVal = args[i].split("=");
     vals[nameVal[0].toLowerCase()] = nameVal[1].toLowerCase();
   }  
   
   //if source doesn't exists in the original form action string, add it, 
   //otherwise replace it
   if (!vals["source"]) {
    
      if(args.length > 1) {
         document.forms[0].action+= "&";   
      } else {
         document.forms[0].action+= "?";
      }
      document.forms[0].action += "Source=" + newUrl;
 
   } else {
      document.forms[0].action = document.forms[0].action.toLowerCase().replace(
         "source=" + vals["source"], 
         "Source=" + newUrl );
   }
}
</script>

I manually replaced the characters / and . in my URL with %2F and %2E respectively.  Also I did not have to provide the full URL just the name of the site and MOSS filled in the remainder of the URL

On word of caution, it is against best practices to directly edit the ASPX pages for out of the box list.  So use this post at your own risk in those cases.  In my case we had created custom list definitions so this did not apply to us.

SSAS and Dynamic Excel Reports

Excel can be a very powerful tool for allowing power users to analyze data in cubes.  However it is also possible to create dynamic reports using Excel.  This can be accomplished using Excel Cube functions.  Cube functions allow you to create the same type of functionality achieved in a pivot table but in a individual cell.  Then utilizing other Excel functions it is possible to parameterize the report making it dynamic.

For this example, I will use one of the Adventures Works Cubes.  The first step is to create a pivot table that contains the information we want.  For this example I created a comparison of Internet Gross Profit sales for current month, prior month and the same month a year ago.  This data is further divided by primary sales territory.

Personally I found that using the Cube Functions in the formula editor to be complicated.  Thankfully there is a menu option in Excel 2007 that can help.  It is called ‘Convert to Formulas’ and is found in the PivotTable Tools –> OLAP Tools menu.

Once this table has been converted we can analyze a few key cells to understand what is going on. The first is A3 where the measure is located. (All cell references relate to the image of the pivot table above.) This cell now contains the formula:

=CUBEMEMBER("AdventureWorksDW",
"[Measures].[Internet Gross Profit]")

The function CUBEMEMBER allows you to select the measure.  If you edit the text you can select other measures, just delete all the text inside quote after this ‘[Measures].[’ and Excel will offer other options.

The second cell we want to inspect is B8 (or any other value cell.)

=CUBEVALUE("AdventureWorksDW",$A$3,$A8,B$7)

You can see that this cell just contains the name of the data source and references to other cells.  One thing to note is that not all cells that were in the pivot table are used by the cube functions so they can be deleted.  Here is my cleaned up table.

The next step is to parameterize the dates reference in my converted table so that we can make the report dynamic.  Evaluating the cell D4 will help us do that.

=CUBEMEMBER("AdventureWorksDW","[Date].[Calendar].[Month].&[2004]&[5]")

You can see that the year and month number are used, however this will vary depending on how the time dimension in your cube is setup.  The first step is to place the date to drive the report into cell B1.  Then change the formulas in B4, C4, and D4 to use this date.

I replaced the formulas in cells B4 and D4 with the ones below.

B4: =CUBEMEMBER("AdventureWorksDW", CONCATENATE("[Date].[Calendar].[Month].&[",YEAR(B1)-1,"]&[",MONTH(B1),"]"))

D4: =CUBEMEMBER("AdventureWorksDW",
CONCATENATE("[Date].[Calendar].[Month].&[",YEAR(B1),"]&[",MONTH(B1),"]"))

The only difference is we subtract 1 year from cell B4.  Cell C4 is a little more difficult because when the current month is January the prior month is December of the prior year.  If it is January the we need to add an if statement to see if we need to subtract 1 form the the year and set the month to 12.  If not we can just subtract 1 from the month number.

C4: =CUBEMEMBER("AdventureWorksDW", CONCATENATE("[Date].[Calendar].[Month].&[",IF(MONTH(B1)=1,YEAR(B1)-1,YEAR(B1)),"]&[",IF(MONTH(B1)=1,12,MONTH(B1)-1),"]"))

The final spreadsheet now looks like the image below but now the data in the table, which is pulled from the cube can be updated by changing the date in cell B2.

This is a straightforward example but could easily be expanded to handle more complex cases.  Graph data can also be updated using the same methodology.  So it is possible to see that this now gives you a complete report authoring environment that you can give to Excel power users and allow them to build their own reports from the cubes your organization has built.

Replace Your Network Shared Drives with SharePoint

One of the interesting things I realized recently is that I had not used a shared drive since I started at RDA. This got me to thinking about network shares and how outdated they were.  So I put together a quick 2 page paper on the topic.  If your interested you can find it here:

How to Increase Your Employees' Productivity By Allowing Them to Find Information Quickly

MOSS Document Upload on Windows Server 2008 Fails

I came across an interesting problem at a customer site recently.  When they tried to upload large documents that were more than 30mb into the MOSS document libraries they would either receive an error or the document would just fail to appear.

After a little research I came across this blog entry that describes the problem:

http://blog.henryong.com/2008/08/04/cant-upload-large-files-to-wss-v3-on-windows-server-2008/

It has a link to a KB article for a workaround:

http://support.microsoft.com/kb/944981/en-us

This is a good reminder that MOSS works a little differently with Windows 2008 and IIS 7.0.

Setting up Multiple MOSS SSL Sites in IIS 7

I just published a two part blog article about setting up multiple MOSS sites each with their own unique SSL certificate.  Hope you find this or something else on the blog helpful.

Part 1 Part 2

Finding and Removing Data Validation

However, each time I uploaded the file I got an error that the spreadsheet contained data validation. After some research, I found a way to locate cells that used data validation within the workbook. Using a special search feature in Excel 2007, Home -> Find & Select -> Data Validation, I was able to locate the cells that used data validation. I could then use Data -> Data Validation… -> Clear All to remove it from that cell. Once all the data validation had been removed I was able to upload the file using excel services and display it in my portal.