Jeff102410

Problem:

I have a view that does group by and shows the data collapsed by default.  So far, so good.  However, the users wanted to see the sum of the details in the group line.  The default summing in the view's group by/total shows an overall sum (which was meaningless in this case), but does not show the group's sums until the group is expanded.  The users wanted to see the sums for all the groups columns, and then see the details if the information showed something interesting.

Solution:

This is actually a several step solution.  First, you'll need to add the list to the page in the appropriate web part location.  Set the correct view, etc., etc., etc.  Exit edit mode

Next, you'll need to go to SharePoint Designer.  Open the page.  Anywhere on the list, right click and select the option to convert the view to a data view web part.  Before saving the page, you'll need to understand that saving the modified page will do the ghost/un-ghost thing.  It's probably not that big of a deal, but you'll need to understand what is happening when you save a page from SharePoint Designer.  That said, save the page and exit SharePoint Designer.

In the browser, go back to the web page in your SharePoint site.  Edit the web page, and select edit/Modify Shared Web Part.  Now, you'll see a couple of new buttons at the top of the Data View Properties area.  The XSL Editor is the item we are interested in.

Click on the XSL Editor button to get the the XSL that is used to display the list.  Copy the XSL and paste the XSL into the appropriate XSL editor. 

You'll need to find the section in the XSL that does the group header row displaying.  It is started with the text <xsl:template name="dvt_1.groupheader0">.  First, just a few lines down you see something like colspan="99".  You will need to change 99 to the appropriate number of columns to skip over to get to the sum columns.  In my case, I had three columns of text information that were displayed when the group was expanded before the first sum column was displayed.  I changed the 99 to 3.

Next, go to the bottom of the header display section.  Look for </xsl:template>.  Just before that you should see </TR>.  Just before the </TR> is where we want to put the sum lines.  Add the line:

    <td><xsl:value-of select="format-number(string(sum(/dsQueryResponse/Rows/Row[@<GroupByField>=$fieldvalue]/@<SumField>)), '#,##0%;-#')" /></td>

You'll need to change <GroupByField> to the name of the field in the list being grouped by.  It's may seem redundant, but it is necessary.  Also change the <SumField> to the name of the field being summed.  Also note that in this above example, I"m displaying the summed field as a percent.  Change the formatting string as you need.  Also, do the above line thing for each column that is needed.

I think that is everything, but If you have problems, or if I'm missing a step, let me know.

Problem:

Well, another problem that consumed several days of my life.  I just completed a migration of MOSS 2007 to new servers.  The old servers were Windows Server 2003/SQL Server 2005/MOSS 2007.  The new servers are Windows Server 2008/SQL Server 2008/MOSS 2007.  Got everything working except the search.  When searching for anything, I got no results.  I looked in the crawl logs (Central Admin/SharedServices1/Search Settings/Crawl Logs/<site name>) and found the following error:

Access is denied. Verify that either the Default Content Access Account has access to this repository, or add a crawl rule to crawl this repository. If the repository being crawled is a SharePoint repository, verify that the account you are using has "Full Read" permissions on the SharePoint Web Application being crawled. (The item was deleted because it was either not found or the crawler was denied access to it.)

I kept setting the default content access account to an account that I knew had access, but nothing changed.  I then went off and tried all kinds of other things, which were just a waste of time.

Solution:

I finally found this KB article: http://support.microsoft.com/kb/896861.  I'd like to give credit to the site that put me on that path, but I have done way too much searching to find it in my history…Sorry.

Anyway, the article does not talk about about this issue specifically, but the root problem is the same.  Either Windows Server 2008 or .Net Framework 3.5 SP1 update enable something called "look back check".  This seems to be the root of the problem.  The solution (for me) was to follow the Method 1 solution in the KB article.  Here are the steps:

1. Click Start, click Run, type regedit, and then click OK.
2. In Registry Editor, locate and then click the following registry key:
3. HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlLsaMSV1_0
4. Right-click MSV1_0, point to New, and then click Multi-String Value.
5. Type BackConnectionHostNames, and then press ENTER.
6. Right-click BackConnectionHostNames, and then click Modify.
7. In the Value data box, type the host name or the host names for the sites that are on the local computer, and then click OK.
8. Quit Registry Editor, and then restart the IISAdmin service.

On step 8, I just went ahead and restarted the server.  By the time I got remote access back into the server, got Central Administration up, and looked at search settings, I could see that the Index Status was crawling. 

Search is back.

Problem:

I have two servers (WebServer and DatabaseServer).  Both are Windows Server 2008.  I have MOSS 2007 installed on WebServer and SQLServer 2008 installed on DatabaseServer.  I could not get MOSS to use a content database from the DatabaseServer.

Solution:

It turns out that I had to do several things. 

First, I had to go into SQL Server Configuration Manager and enable Named Pips.  TCP/IP was enabled already, but I believe that both have to be enabled to get things to work.

Second, I had to go into Windows Firewall and specify a couple of exceptions.  One I called "SQL Server" on port 1433 and the second called "SQL Browser" on port 1434.

Third, I had to go into SQL Server Management Studio and set up a user with the WebServer machine name.  This was a bit tricky.  I had to try to connect to the content database on DatabaseServer.  Next, I had to go to "C:Program FilesCommon Filesmicrosoft sharedWeb Server Extensions12LOGS" and look at the newest file.  Searching for the name of the DatabaseServer, I found that I was getting a permissions error and it gave me the name that MOSS was using when attempting to connect to the database server.  It was actually in the form of <DOMAIN>/<MACHINE$>.  I then added the <DOMAIN>/<MACHINE$> as a SQL User under Security/Logins.  I tried agian, but got another error.  Checking the log again, I found that the user did not have create database permissions on the database master.  DOOOOH.  I went back to  <DOMAIN>/<MACHINE$> and gave permissions.  In my case, because I'm working on an Intranet and have good network security, I went ahead and gave the <DOMAIN>/<MACHINE$> login admin permissions.  This is probably overkill, and I'll get back to this and scale permissions back at a later time.  Right now, I just need to get the thing working.

This is a bit different from the Windows Server 2003/MOSS 2007/SQL Server 2005 configuration that I last performed this configuration on…

Problem:

I needed to create a view that only showed rows of data from a list that had a field that was not blank.  Basically, I have an issues column that is normally blank or has no data.  However, sometimes an issue is noted.  I needed a view that quickly showed the list of issues.  The problem was that the issues column was a multiple lines of text column, and the only way to filter the column was with either a contains or a begins with filter.  The other filters like equals or not equal to are not allowed with a multiple lines of text column.

Solution:

The first thing was to get the multiple lines of text field to contain the correct information.  By default, multiple lines of text columns gets assigned the "rich text" type of "text to allow".  This will always put in <div></div> HTML tags.  Even thought the exit box for this field shows nothing, the HTML tags are still there.  The problem this causes in a minute.

Change the type of text to allow to plain text.  This will not put in any hidden HTML.

Next, the view.  Create the view with whatever columns are needed.  On the filter for the view, add in five "or" filters.  Basically, the filters need to read "show information when the issues column contains an 'a' or an 'e' or an 'i' or an 'o' or a 'u'".  Any English readable text in the column will have to contain one of these letters (vowels). 

The problem with the "rich text" type of "text to allow" is that the filter looks at the information in the field, not just what is shown.  The HTML tags have an "i" and the rows always show up.  Eliminating the hidden HTML tags get the filters working as desired.

Extra:

Also, a final thing I also have a status column.  I applied a 6th column filter that was "and" and said to only show rows where the status column was not "Completed".  This way I got the active issues.

Business Need

I'm needing to download an InfoPath XML document and read the contents into an application for further processing.

Problem

When downloading the <InfoPathFile>.xml form the SharePoint library, I get the generated HTML from InfoPath Services instead of the actual XML.  Having just spent 7 calendar days looking for the solution and trying everything I could think of, all I could get was the InfoPath Services generated HTML.

Solution

Well, I finally ran across a post that someone talked about doing this same thing (actual one of many posts).  However, at the end of the post, he mentioned that you need to include the "?NoRedirect=true" parameter in the URL.  I immediately search MSDN and found exactly one (1) post on this topic.

Anyway, just for anyone else who want the code I used to do this, here it is:

————————————————————– 

Dim request As HttpWebRequest

Dim response As HttpWebResponse = Nothing

Try

     'This is the part that caused me the problem.
     'The NoRedirect=true keeps the InfoPath Services from getting involved

     request = WebRequest.Create(lsURL & "?NoRedirect=true")

     request.AllowWriteStreamBuffering = False

     Dim s As Stream = response.GetResponseStream()

     'Write to disk
     'IFile is set in previous not included code.  It is the complete drive/path/filename
     'CreateDirectory will use what it needs from the drive/path/filename
     'CreateDirectory will also not pass an error if the drive/path/filename stuff exists

     Dim fs As New FileStream(lsFileName, FileMode.Create)

     Dim read() As Byte

     ReDim read(256)

     Dim count As Integer = s.Read(read, 0, read.Length)     Do While count > 0

     Loop

     'Close everything

     Catch ex As Exception

          MsgBox(ex.Message & vbCrLf & vbCrLf & "Please contact technical support", MsgBoxStyle.Critical, "Download Error")

          Me.Cursor = System.Windows.Forms.Cursors.Default

          Exit Sub

     End Try

———————————————-

Preface.

In the part one post on this topic, I explained why I thought the SharePoint Object Model was not letting me develop on one machine and access SharePoint information on another server.  So I'll not spend any time on that here.  Instead, I'll just show what I did to get the list of sub-sites from my root SharePoint site.

Quick Overview.

To work with the SharePoint web services, you need to know that there are actually 16 SharePoint web services.  It's older technology and a bit more cumbersome.but that's for another post.  Here are the 16 services:

1. http://<server>:<port>/_vti_adm/Admin.asmx – Administrative methods such as creating and deleting sites
2. http://<server>/_vti_bin/Alerts.asmx – Methods for working with alerts
3. http://<server>/_vti_bin/DspSts.asmx – Methods for retrieving schemas and data
4. http://<server>/_vti_bin/DWS.asmx – Methods for working with Document Workspaces
5. http://<server>/_vti_bin/Forms.asmx – Methods for working with user interface forms
6. http://<server>/_vti_bin/Imaging.asmx – Methods for working with picture libraries
7. http://<server>/_vti_bin/Lists.asmx – Methods for working with lists
8. http://<server>/_vti_bin/Meetings.asmx – Methods for working with Meeting Workspaces
9. http://<server>/_vti_bin/Permissions.asmx – Methods for working with SharePoint Services security
10. http://<server>/_vti_bin/SiteData.asmx – Methods used by Windows SharePoint Portal Server
11. http://<server>/_vti_bin/Sites.asmx – Contains a single method to retrieve site templates
12. http://<server>/_vti_bin/UserGroup.asmx – Methods for working with users and groups
13. http://<server>/_vti_bin/versions.asmx – Methods for working with file versions
14. http://<server>/_vti_bin/Views.asmx – Methods for working with views of lists
15. http://<server>/_vti_bin/WebPartPages.asmx – Methods for working with Web Parts
16. http://<server>/_vti_bin/Webs.asmx – Methods for working with sites and sub-sites

(Replace <server> with the name of the hosting server and <port> with the port used by Central Administration. )

There is a lot more information about the web methods available in the SharePoint Products and Technologies 2003 SDK.

Nuts and Bolts.

Next, I'll build a simple client app using Visual Basic .Net 2005.  Sorry, but all the Visual C# fans will just have to make the necessary code adjustments.

To start with, I created a basic Windows form project.  Created a form within the project and put a dataviewgrid on the form.  The only change was to name the dataviewgrid “dgLists”.

Web References.

Next, I added a reference.specifically, a web reference.  From the main Visual Studio menu, go to Project/Add Web Reference.

From the Add Web Reference dialog box, in the URL: area, put in http://<server>/_vti_bin/Webs.asmx (replacing <server> with your server name).  Then click the Go button next to the URL: area.  If everything goes correctly, you should se the “Web services found at this URL:” area say something like “1 Service Found” and – “Webs”. 

Once you see this information, put something meaningful in the “Web reference name:” area.  Something like wr<Server>Webs.  (wr for web reference, replace <Server> with the server name, and Webs for what information is coming back.) 

It is important to name this something meaningful.  The name here is what will be used later in the code to get to the web reference. 

Once all that is done, click the “Add Reference” button.  This will add the web reference to your project.  You can additionally add the other web references mentioned above, but for this example, we will just use the one.

The VB code.

Your form load code should be something like this:

Imports System.XmlImports System.NetImports System.Net.NetworkCredential Public Class Form1    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load         Dim lMyWebs As New RevEWebs.Webs         ' Set Network Credentials to be able to log into         ' the SharePoint site        lMyWebs.Credentials = New NetworkCredential("<UserName>", "<Password>", "<Domain>")         ' Even though the Web Reference has this,        ' the _vti_bin stuff is needed here again.        lMyWebs.Url = "http://<server>/_vti_bin/webs.asmx"         ' Actually get the list of sites from the         ' <server> listed above        Dim lMyWebsXMLNode = lMyWebs.GetWebCollection         ' Set up an XML data document to read         ' the returned list of webs from above        Dim lMyXMLDataDocument As XmlDataDocument = New XmlDataDocument         ' Set up a data set to load the grid        ' on the main form         Dim lMyDataSet As DataSet = lMyXMLDataDocument.DataSet         ' Load the xsd file that has the layout        ' of the XML        lMyDataSet.ReadXmlSchema("Webs.xsd")         ' Add an XML declaration to make the         ' XmlNode a valid XML document        lMyXMLDataDocument.LoadXml("<?xml version='1.0' ?>" & lMyWebsXMLNode.OuterXml)         ' Display the result on the DataGrid        dgLists.DataSource = lMyDataSet        dgLists.DataMember = "Web" 

    End Sub
End Class

The XSD stuff.

The list of sub-webs from the web specified by <server> is in XML format.  (On a side note, you can specify <server>/<sub-site> for <server> and it will bring back the list of sub-sites for <server>/<sub-site>.)

One additional bit of code you will need is an .xsd file with the layout of the XML.  This is a requirement of the dataset, and not related to any SharePoint requirements. 

Also, just a thing to know.to get a view of the XML, I set a breakpoint after the GetWebCollection line, and from the watch box, copied the OuterXML and pasted it into and XML editor.  From there, I created the following .XSD file:

<?xml version="1.0" standalone="yes"?><xs:schema id="Lists" targetNamespace="http://schemas.microsoft.com/sharepoint/soap/" xmlns:mstns="http://schemas.microsoft.com/sharepoint/soap/" xmlns="http://schemas.microsoft.com/sharepoint/soap/" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">      <xs:element name="Webs" msdata:IsDataSet="true"      msdata:EnforceConstraints="false">            <xs:complexType>                  <xs:choice maxOccurs="unbounded">                        <xs:element name="Web">                              <xs:complexType>                                    <xs:attribute name="Title" form="unqualified" type="xs:string" />                                    <xs:attribute name="Url" form="unqualified" type="xs:string" />                              </xs:complexType>                        </xs:element>                  </xs:choice>            </xs:complexType>      </xs:element></xs:schema>

You will need to save this in the same directory as the executable, i.e. <Project>inDebug<filename>.xsd.  (In my case D:Development<Project>inDebugWebs.xsd.)

Run the project and you should see a form with a datagridview with the list of sub-sites and their associated URL's.

The Wrap Up.

The key to writing code to work with SharePoint sites, and have the code run on machines not hosting the SharePoint box seems to be Web Services.  Again, if someone has the SharePoint Object Model doing this, PLEASE let me know.

While I have not investigated this too deeply, it seems that most everything that is possible to do in the SharePoint Object model is possible using the SharePoint Web Services.  As I am forced to use the SharePoint Web Services to complete some projects that I have coming up, I'll post additional information on functionality.  Again, if you have some insight on this, PLEASE let me know.

As always, the information and code provided here is for training purposes, provided with no guarantees, and use at your own risk.  Additionally, feel free to use this stuff to get started with whatever you need.