SPDataSource and the CrossList DataSourceMode

I was recently searching for how to construct an SPDataSource using a Crosslist or Union of two or more SharePoint lists. Not finding the answer in one place, I was able to piece together the answer. Because we're doing a union of lists, the column names and type in each list must match.


<SharePointWebControls:SPDataSource
runat="server"
ID="spDataSource1"
DataSourceMode="CrossList"
SelectCommand="<Webs Scope='SiteCollection'></Webs>
  <Lists>
    <List ID='XXXXXXXX-XXXX-4499-91F3-359A463EB89F'></List>
    <List ID='XXXXXXXX-XXXX-4EBE-9801-8751A076F069'></List>
  </Lists>
  <View>
    <ViewFields><FieldRef Name='Title' /><FieldRef Name='EventDate' /><FieldRef Name='EndDate' /></ViewFields>
    <Query>
      <Where><Geq><FieldRef Name='EndDate'/><Value  Type='DateTime'><Today/></Value></Geq>
      </Where>
      <OrderBy><FieldRef Name='EventDate' Ascending='True'/></OrderBy>
    </Query>
    <RowLimit>5</RowLimit>
  </View>"
> 
</SharePointWebControls:SPDataSource>

Getting List Items from a SharePoint List using SOAP

Use the steps below to get list items from a SharePoint list using SOAP.

First construct your xml SOAP message:


StringBuilder sbEnvelope = new StringBuilder();
sbEnvelope.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
sbEnvelope.Append("<soap:Envelope xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\">");

sbEnvelope.Append("<soap:Body><GetListItems xmlns=\"http://schemas.microsoft.com/sharepoint/soap/\">
<listName>" + listname + "</listName><viewName>" + viewName + "</viewName>" +
 "<query></query><viewFields><ViewFields><FieldRef Name='Column1' /><FieldRef Name='Column2' /><FieldRef Name='Column3' /></ViewFields></viewFields></GetListItems></soap:Body></soap:Envelope>");

Next, construct your HttpWebRequest, get your response and place it in an XmlDocument for parsing.


HttpWebRequest req = (HttpWebRequest)WebRequest.Create("http://<site>/_vti_bin/lists.asmx");
req.Method = "POST";
req.ContentType = "text/xml; charset=\"utf-8\"";
req.Accept = "text/xml";
req.Headers.Add("SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/GetListItems");
req.UseDefaultCredentials = true;

using (Stream stream = req.GetRequestStream()) 
{
      using (StreamWriter writer1 = new StreamWriter(stream)) 
     {
            writer1.Write(sbEnvelope.ToString());
      }
}

WebResponse response = req.GetResponse();
Stream responseStream = response.GetResponseStream();

XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(responseStream);

Finally, create your namespaces and parse the xml.


XmlNamespaceManager manager = new XmlNamespaceManager(xmlDocument.NameTable);
manager.AddNamespace("sp", "http://schemas.microsoft.com/sharepoint/soap/");
manager.AddNamespace("rs", "urn:schemas-microsoft-com:rowset");
manager.AddNamespace("z", "#RowsetSchema");
manager.AddNamespace("y", "http://schemas.microsoft.com/sharepoint/soap/ois");
manager.AddNamespace("w", "http://schemas.microsoft.com/WebPart/v2");
manager.AddNamespace("d", "http://schemas.microsoft.com/sharepoint/soap/directory");

XmlNodeList NodeList = xmlDocument.SelectNodes("//sp:listitems/rs:data", manager);

String t = "";
foreach (XmlNode ListItem in NodeList)
{
     foreach (XmlNode node in ListItem.ChildNodes)
    {
         t = node.Attributes["ows_Title"].Value;
     }
}

All (%) in Crystal Reports Formula Editor

When constructing a parameter in Crystal Reports that includes "all" items (%), construct a formula that returns true or false for your report's selection criteria.

In the formula editor, add:


//Selects all academic orgs or specific ones based on parameter
if {?Academic Orgs}="%" then  true
else
     if {?Academic Orgs} = {RDS_CLASS_TBL.CLASS_ACAD_ORG} then true
else
     false

Then for your record selection include your formula:

... and{@Selection Criteria for Academic Orgs} ...

Show attachment from Remedy in .NET web app

We have a BMC Remedy form that contains classroom information along with jpg or gif attachments. To pull these images from the Remedy form into a .NET web application, use the following code:


            byte[] content = null;
            server.GetEntryBLOB("AssetRooms", id, 536880917, out content);
           
            string base64String = Convert.ToBase64String(content, 0, content.Length);

            if (base64String.Equals(""))
            {
                Label1.Text = "<br/><br/> Map not available.";
                Image1.Visible = false;
            }
            else
            {
                Image1.ImageUrl = "data:image/png;base64," + base64String;
                Image1.Visible = true;
            }

Image1 is an Image control:

<asp:Image ID="Image1" runat="server" Height = "380"/>

XSL for SharePoint List webparts

For out-of-the-box SharePoint List webparts, the 'XSL Link' property allows editors to customize the display of their list data.

Below is an example of an xsl stylesheet for list webparts.

<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:msxsl="urn:schemas-microsoft-com:xslt"
  exclude-result-prefixes="msxsl"
  xmlns:ddwrt2="urn:frontpage:internal">
<xsl:output method="html" indent="yes"/>

<xsl:template match="dsQueryResponse" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime">
  <div class="resourcesList" >
      <xsl:apply-templates select="/dsQueryResponse/Rows/Row"/>
  </div> 
</xsl:template>

<xsl:template match="Row">
    <xsl:if test="position()=1">
    <h2><xsl:value-of select="@Category" disable-output-escaping="yes" /></h2>
    </xsl:if>
    <a href="{substring-before(substring-after(@URL,'&gt;'),'&lt;')}">
     <xsl:value-of select="@Title" disable-output-escaping="yes" />
    </a> <br/>
</xsl:template>

</xsl:stylesheet>

Using XSLT gives a much cleaner and customized look to your list webparts.

Launch your web browser as a different user

I often need to sign into SharePoint or another local server as a different user. An easy way to do this without logging out of your computer is to use the 'runas.exe' command. open a command prompt and enter the following command:

runas.exe /u:domain\username "C:\Program Files\Internet Explorer\iexplore.exe"

substitute domain\username with your account you want to login in with, I use a test account made specifically for these situations.

RANK function to create primary key for a Remedy View Form

To create Remedy View Form, you will need a database table or view that contains a numeric primary key. Since your table or view might not have a column that fits this requirement, you may need to dynamicall y create one in the building of the view.

Use the RANK function to create an incrementing value and then cast it as a an integer:

CAST(RANK() OVER(ORDER BY TERM,CAMPUS,BUILDING,ROOM,CRS_PREFIX,CRS_NUMBER,CRS_SUFFIX) AS int) AS Record