Tag Archives: sharepoint 2010

Business Intelligence, Excel, SharePoint

PowerPivot Part 4: Sharing PowerPivot Workbooks

1 Comment

So now you have a PowerPivot workbook that’s far too awesome to keep to yourself. How do you go about sharing PowerPivot workbooks?

You could just distribute the workbook wholesale, but that’s not optimal because any user who wants to take advantage of the PowerPivot features needs to have PowerPivot for Excel 2010 installed on their machine to fully run the report.

Where else can you turn?

Luckily, SharePoint 2010 introduced PowerPivot for SharePoint that is comprised of two main pieces:

  • Server software that can retrieve the data for the report
  • The PowerPivot Gallery

The SharePoint PowerPivot Gallery is a special document library that has document management and preview for PowerPivot workbooks (along with a few other document types). With the PowerPivot Gallery’s live preview, you can interact with a PowerPivot workbook, just as you would in Excel.  You can also create workbooks from published PowerPivot workbooks and schedule data refreshes for added versatility. The gallery also has several customization options for how the reports are listed.

What’s the catch?

Continue reading PowerPivot Part 4: Sharing PowerPivot Workbooks →

SharePoint

Creating a Simple Connection Consumer in SharePoint

1 Comment

There are a million articles about using seven different interfaces and fourteen .wsp deployments to make an entirely custom connection provider and consumer. However, I couldn’t find one about how to create a simple connection consumer that filters based on a SharePoint list. I also couldn’t find anything about changing the brand-new double-headed arrow icon that SharePoint replaced the radio button with. Turns out they can go in the same simple solution:

  • Setting up the Consumer – This consumer will take a single row of information from a SharePoint list. It’s implemented in “MyWebPart.cs” and not in a user control.
//Create the data row that the list information will fill
DataRowView _row = null;
//Declare your consumer. Note that "Row" can be anything - it's just the term that
// SP will use when you enable the connection
[ConnectionConsumer("Row")]
//Set up the actual connection
public void SetConnect(IWebPartRow provider)
{
    //"RecieveRow" is the method you'll create to interpret the data
     RowCallback callback = new RowCallback(ReceiveRow);
    //This is where the data comes in 
     provider.GetRowData(callback);
}

This code sets up the DataRow. Note that the “provider” parameter is passed by the SharePoint connection.

  • Capturing your data – This is where you can actually use the filter – manipulate, save, and query to your heart’s content. Below is a sample RecieveRow method:
public void ReceiveRow(object row)
{
     //Set your local _row equal to the row passed by SharePoint
     _row = (DataRowView)row;
   
     //Make sure it isn't null 
     if (_row != null)
     {
    //The next three lines of code are a great way to open a site without having to dispose of anything!
    SPWeb contextWeb = SPControl.GetContextWeb(Context);
    using (SPSite site = new SPSite(contextWeb.Url))
    {
        using (SPWeb web = site.OpenWeb())
        {
         //Convert the DataRowView into a DataRow, so you can actually use it
             DataRow dr = _row.Row;
            
             //I need to allow unsafe updates to execute my query
             web.AllowUnsafeUpdates = true;
             //Query against the row data
             SPQuery query = new SPQuery();
             query.Query = string.Format(
                 @"<Where>
                     <Eq>
                         <FieldRef Name='MyColumn' />
                         <Value Type='Lookup'>{0}</Value>
                     </Eq>
                 </Where>", dr["mycolumnvalue"].ToString() );
                    
             SPList list = web.Lists["MyList"];
             SPListItemCollection items = list.GetItems(query);
            /*********
             *In here you do all you want with that filtered item list.
             *Convert it to a data table, pass the list to a usercontrol.
             *After all, it's your filter!
             ********/
           
            //Disable unsafe updates when you're done
             web.AllowUnsafeUpdates = false;
        }
     }
     }
}

That’s all there is to it. You can fill a data structure in the RecieveRow method and pass it on to a user control the same way you would pass any other value.

  • Customization –  Here’s a little bonus – how to update the radio buttons with the filter wsp.
    • In the hive, the double-headed arrow radio buttons are the following two files:
      • RBSEL.gif
      • RBUNSEL.gif
    • If you want to replace them create a folder in your solution package with the following path:  MyWebPartProject > TEMPLATE > IMAGES
    • Rename your “on” radio button “RBSEL” and save it as a gif
    • Rename your “off” radio button “RBUNSEL” and save it as a gif
    • Place both of them in the IMAGES folder.
    • When you deploy, it will overwrite the default arrows.

This change OVERWRITES the default SharePoint images. Only do this if you want to update all of the radio buttons on the farm. Otherwise you will have to restore the double-headed arrow icons, and it won’t be fun.

 

[Image via the San Francisco Weekly]

SharePoint

Using PowerShell to Convert SharePoint 2007 List Templates (STP) for SharePoint 2010

2 Comments
This method of converting SharePoint 2007 list templates for use in SharePoint 2010 is not supported by Microsoft. Use at your own risk. Your mileage may vary.

SharePoint 2007 List Templates in SharePoint 2010

I recently needed to move a number of list templates from SharePoint 2007 to SharePoint 2010. Unfortunately, SharePoint 2010 just doesn’t support this. The only supported method to move these lists across is to upgrade the whole content database. That just wasn’t an option for me, so I needed to find another way.

Luckily, I discovered an excellent blog post by Tom (Belgium) that describes a method for getting SharePoint 2007 list templates working in SharePoint 2010.

Tom’s basic method is:

  1. Extract the contents of the STP file (it’s really just a CAB file)
  2. Edit the manifest.xml file, changing the ProductVersion element from 3 to 4
  3. Repackage the STP file

I have had success with this method for both templates for lists and document libraries. Continue reading Using PowerShell to Convert SharePoint 2007 List Templates (STP) for SharePoint 2010 →