SharePoint Customization: Veni, Vidi, Vici

Using SQL Reporting Services Report Viewer Web Part to display Access Services report.

As you may or may not be aware when an Access database is published to a SharePoint site the reports in the database are converted into SQL Reporting Services reports.  Great, you think... that should mean you can host the report elsewhere in the SharePoint higherarchy simply by using the SQL Reporting Services Report Viewer Web Part.  And in fact, you can, it's just a little tricky getting it configured.  Certainly not anything you can't overcome but hopefully by posting this information it'll help save some time.

So first, we need to create the page that we're going to put our report on.  Next, you're going to want to insert a SQL Reporting Services Report Viewer Web Part.  And finally, we need to configure it, which includes setting the location of the report as well as any report aparameters.

Configuring the Web Part

The first step is to set the report that the Web Part should display.  To do that you need the URL to the rdl file that represents the Access report.  To get that, browse to the SharePoint site hosting the Access application.  Next, manually change the URL after the site name to show all site content.  You do this by appending "_layouts/viewlsts.aspx" after the site which should look something like the following where "Access Services Demo" is the name of my SharePoint site:

Now in the listing of the site's contents you'll see a document library called Report Definitions.  Browse to that library and you'll see all your Access Reports defined there.  Now, go back to the web part properties.  As shown below click on the ellipses of the Report parameter and then put in the URL of the report definition document library.

When you click the green arrow it will load a list of the reports available in that library.  Select the one you want to display and click OK.

Now, back in the web part properties expand the parameters section and click on Load Parameters.

In Access Services all reports have the following parameters:

• AccSrv_SiteId: This is the GUID of the root site collection.
• AccSrv_SiteZone: Default
• AccSrv_WebId: This is the GUID of the site hosting the Access application
• AccSrv_TimeOffset: TimeZone offset
• AccSrv_Where: A where clause for the report
• AccSrv_DataLCID: The Data Language Reference ID (1033 is the English Default)
• AccSrv_CollationLCID: The collation Language Reference ID (2070 is the English Default)

I have found that for most reports setting the AccSrv_SiteId and AccSrv_WebId are all that is necessary for the report to generate properly.  However, under certain circumstances where there was summation and grouping in the report I've had to set the DataLCID value to 1033 (the default for English).  All the other values can be left to use the report default.

Note: There are many ways to get the GUID for a site but I find the quickest way is to use a tool like SharePoint Manager 2010. 

If you have the need to adjust the Where parameter of the report keep in mind that you need to pass the entire where clause as you would type it in the filter parameter of the report if you were working in Access.  You also must include a begining "=" sign. 

For instance the following "Where" clause limits the report to those records where the value of the [Quarter] field is equal to 1.  (Even though [Quarter] is a numeric field I still have to pass it as a string).

And then here's what the report looks like when rendered:

If you want to pass the Where clause in using a Query String filter you can do that but you'll first need to modify the parameter of the report by going back to the Report Definition library, and modifying the parameters of the report so that the AccSrv_Where parameter is changed to "Prompt" instead of hidden.  The only downside is that the report shows the user the where clause when it's rendered but this can be overcome by using a little JQuery script to hide the <div> that shows in the report.

Note also in the above screen shot that one of the option is "Edit in Report Builder".  If you don't have a complex report you can modify the report definition usint this SQL Report Builder tool, which is downloaded for you automatically.  This was how I initially determined the values of some of the parameters by showing them in the header and then running the report from within the Access UI on the SharePoint site.

Setting an Application Page Title from code

I'm currently dealing with a requirement where I need to the set an application page's title from the code behind. 

By default the form has a ContentPlaceHolder control with a ContentPlaceHolderID="PlaceHolderPageTitle" which in the master page is the text that will fall in the header's title tag.  Unfortunately, <asp:Content> controls are not added to the higherarchy at runtime so I can't access it directly.

There are good ways to modify this title on the client side with Javascript but client side code doesn't really suit my needs as the title will come from some custom manipulation that's better done on the server.  It did cross my mind that I could do this minipulation and then post it back in script to have it update but it seemed kindof like taking the long way around.  Anyway, long story short I found a way to access that title in the Page_Load event but if anyone out there has any better ideas I'd love to hear them.

using System.Web.UI.HtmlControls;

protected void Page_Load(object sender, EventArgs e)
{
  if(!Page.IsPostBack){
    HtmlTitle tagTitle = new HtmlTitle();
    //Obviously this would be more complex code in practice
    tagTitle.Text = "My new title here";
    //The parent of "PlaceHolderPageTitle" is the HtmlTitle control
    Page.Header.Controls.Remove(
        Page.Header.FindControl("PlaceHolderPageTitle").Parent);
    Page.Header.Controls.Add(tagTitle);
  }
}

Maintain file version history when moving/copying files between SharePoint sites

I'm working on a requirement to copy files from one document library to another document library in a sub-site.  I figured this wouldn't be horribly difficult to do but turns out it's not as simple as you might think and for some reason, although I found many questions about how to do it, I found very few answers.  From the things I've read out there it's apparently relatively easy if you're moving documents within the same site... apparently the Move method works and I read where someone suggesting using SPExport and SPImport.  I also found a post by Ivan Sanders that said that you can use Site Actions > Manage Content and Structure > Choose the Library > Choose the docs >Use the Actions drop Down Click onMove > Choose the new Location to accomplish the move on this thread.  Unfortunately, none of these solutions fully met my needs.

Ok, so onward and upward... I ended up started from a good post by 'Dink' on Copying documents between libraries with metadata – including version history which was a great starting point but seemed like it could be optimized a bit and was posted back in 2007, so I hoped that there might be potential improvements in 2010.  As it turns out there is, in the form of additional overload methods for SPFileCollection.Add

So here's my code snippet.  I'll point out that this snippet is in a function that takes two parameters SPListItem itmSource which is the source item to move, and SPDocumentLibrary libDest which is the target document library.

SPFile fileSource = itmSource.File;
/*Here we'll get the created by and created on values from the source document.*/
SPUser userCreatedBy = fileSource.Author;
/*Note we need to convert the "TimeCreated" property to local time as it's stored in the database as GMT.*/
DateTime dateCreatedOn = fileSource.TimeCreated.ToLocalTime();
//Get the versions
int countVersions = itmSource.File.Versions.Count;
/*This is a zero based array and so normally you'd use the < not <= but we need to get the current version too which is not in the SPFileVersionCollection so we're going to count one higher to accomplish that.*/
for (int i = 0; i <= countVersions; i++)
{
     Hashtable hashSourceProp;
     Stream streamFile;
     SPUser userModifiedBy;
     DateTime dateModifiedOn;
     string strVerComment = "";
     bool bolMajorVer = false;
     if (i < countVersions)
     {
/*This section captures all the versions of the document and gathers the properties we need to add to the SPFileCollection.  Note we're getting the modified information and the comments seperately as well as checking if the version is a major version (more on that later).  I'm also getting a stream object to the file which is more efficient than getting a byte array for large files but you could obviously do that as well.  Again note I'm converting the created time to local time.*/
          SPFileVersion fileSourceVer = itmSource.File.Versions[i];
          hashSourceProp = fileSourceVer.Properties;
          userModifiedBy = (i == 0) ? userCreatedBy: fileSourceVer.CreatedBy;
          dateModifiedOn = fileSourceVer.Created.ToLocalTime();
          strVerComment = fileSourceVer.CheckInComment;
          bolMajorVer = fileSourceVer.VersionLabel.EndsWith("0") ? true : false;
          streamFile = fileSourceVer.OpenBinaryStream();
     }
     else
     {
/*Here I'm getting the information for the current version.  Unlike in SPFileVersion when I get the modified date from SPFile it's already in local time.*/
          userModifiedBy = fileSource.ModifiedBy;
          dateModifiedOn = fileSource.TimeLastModified;
          hashSourceProp = fileSource.Properties;
          strVerComment = fileSource.CheckInComment;
          bolMajorVer = fileSource.MinorVersion == 0 ? true : false;
          streamFile = fileSource.OpenBinaryStream();
     }
     string urlDestFile = libDest.RootFolder.Url + "/" + fileSource.Name;
/*Here I'm using the overloaded Add method to add the file to the SPFileCollection.  Even though this overload takes the created and modified dates for some reason they aren't visible in the SharePoint UI version history which shows the date/time the file was added instead, however if this were a Microsoft Word document and I opened it in Word 2010 and looked at the version history it would all be reflective of the values passed to this Add method.  I'm voting for defect but there could just be something I'm missing.*/
     SPFile fileDest = libDest.RootFolder.Files.Add(
                 urlDestFile,
                 streamFile,
                 hashSourceProp,
                 userCreatedBy,
                 userModifiedBy,
                 dateCreatedOn,
                 dateModifiedOn,
                 strVerComment,
                 true);
     if (bolMajorVer)
/*Here we're checking if this is a major version and calling the publish method, passing in the check-in comments.  Oddly when the publish method is called the passed created and modified dates are displayed in the SharePoint UI properly without further adjustment.*/
          fileDest.Publish(strVerComment);
     else
     {
/*Setting the created and modified dates in the SPListItem which corrects the display in the SharePoint UI version history for the draft versions.*/
          SPListItem itmNewVersion = fileDest.Item;
          itmNewVersion["Created"] = dateCreatedOn;
          itmNewVersion["Modified"] = dateModifiedOn;
          itmNewVersion.UpdateOverwriteVersion();
     }
}

Add BCS Secondary Fields to a custom list definition

When working with BCS data and creating an external data column in a list or library it is often desirable to show some or all of the secondary fields. In the SharePoint UI this is a simple task, completed by checking off the fields to capture for viewing, searching, filtering, and sorting later.

However, when you're building your list or library using a custom list definition schema.xml file this is not as simple. After struggling a bit trying to code the XML myself, it dawned on me that I could mock up the field using the UI and then extract the properly formated field tags by getting the schemaxml proerty of the list.

There are various ways to do this but I've found the simplest way it to use a tool like SharePoint Manager

1. Browse to the list/library definition using SharePoint Manager
2. Copy and paste the SchemaXML property into a text file
3. Save the text file with an XML extention
4. Open the XML file in Visual Studio (or some other XML editor). If using Visual Studio, select "Format Document" from the Edit/Advanced menu (Ctrl+E, D)
5. Scroll down to find the external data field you created through the UI. You're looking for a <Field> tag with a the Type attribute equal to "BusinessData" The next <Field> tag with Type="Note" will be the hidden field that stores the key value for the selected value. All the additional <Field> tags that follow will be the secondary fields you selected in the UI.

If you copy and paste those Field tags into your list definition schema file and remove the "SourceID" attribute, the list definition will generate a list with the appropriately defined external data column including the secondary fields.

To dig a little deeper note that you could potentially do this by hand. Creating the field tags for the hidden primary key field and the additional secondary fields isn't all that difficult.

Where this all get's tricky is trying to figure out how to format the SecondaryFieldBdcNames, SecondaryFieldWssNames, SecondaryFieldsWssStaticNames attributes of the primary "BusinessData" field tag. Below is our example field tag for the primary business data field.

<Field Type="BusinessData" DisplayName="Order" Required="FALSE" ID="{5a261e1a-e157-436c-83a2-fda125d72266}" StaticName="Order0" BaseRenderingType="Text" Name="Order0" ColName="nvarchar3" RowOrdinal="0" Version="6" Group="" SystemInstance="MSSExternal" EntityNamespace= http://sharepoint/bidemo EntityName="Order" BdcField="OrderNumber" Profile="/_layouts/ActionRedirect.aspx?EntityNamespace=http%3A%2F%2Fjturner%2Dsrv08r2%2Fbidemo&amp;EntityName=Order&amp;LOBSystemInstanceName=MSSExternal&amp;ItemID=" HasActions="True" SecondaryFieldBdcNames="15%2014%2015%208%20CustomerRegion%20CustomerState%20CustomerTarget%20Product%2011" RelatedField="Order_ID" SecondaryFieldWssNames="33%2033%2033%2027%20Order%5Fx003a%5F%5Fx0020%5FCustomerRegio%20Order%5Fx003a%5F%5Fx0020%5FCustomerState%20Order%5Fx003a%5F%5Fx0020%5FCustomerTarge%20Order%5Fx003a%5F%5Fx0020%5FProduct%2012" RelatedFieldBDCField="" RelatedFieldWssStaticName="Order_ID" SecondaryFieldsWssStaticNames="33%2033%2033%2027%20Order%5Fx003a%5F%5Fx0020%5FCustomerRegio%20Order%5Fx003a%5F%5Fx0020%5FCustomerState%20Order%5Fx003a%5F%5Fx0020%5FCustomerTarge%20Order%5Fx003a%5F%5Fx0020%5FProduct%2012" AddFieldOption="AddToDefaultContentType, AddFieldToDefaultView"/>

If you take the value of SecondaryFieldBdcNames attribute for instance and decode it you get:

15 14 15 8 CustomerRegion CustomerState CustomerTarget Product 11

The text CustomerRegion, CustomerState, etc all make sense, they are the root BDC field names. However the numbers that proceed and follow the field names allude me completely.

Regardless, I hope this workaround helps anyone out there who too was struggling to define secondary fields in a custom list definition.