This blog is moved to
http://amalhashim.wordpress.com

Saturday, October 31, 2009

Using Sharepoint List Webservice

Sharepoint exposes some really useful webservices. One among the most useful is the List Webservices.

We can take the webreference from the following location

http://mossserver/_vti_bin/lists.asmx

Once we have added the webservice, we can use following generic method to call webservice

XmlNode CallWebService(bool isRecursive, ListsService listService,
string listName, string queryObj, string viewID, string viewFieldsInnerXml)
{
try
{
NetworkCredential credential = new NetworkCredential();
credential.UserName = "UserName";
credential.Password = "Password";
credential.Domain = "Domain";
listService.Credentials = credential;

// Instantiate an XmlDocument object
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();

string viewName = string.Empty;
if (string.Empty != viewID)
{
viewName = viewID;
}
string rowLimit = "150";

System.Xml.XmlElement query = xmlDoc.CreateElement("Query");
System.Xml.XmlElement viewFields = xmlDoc.CreateElement("ViewFields");
System.Xml.XmlElement queryOptions = xmlDoc.CreateElement("QueryOptions");

if (isRecursive)
queryOptions.InnerXml = "<ViewAttributes Scope=\"Recursive\" />";
else
queryOptions.InnerXml = "";

if (string.Empty == queryObj)
{
query.InnerXml = "<Where><Gt><FieldRef Name=\"ID\" />" +
"<Value Type=\"Counter\">0</Value></Gt></Where>";
}
else
{
query.InnerXml = queryObj;
}

// Assign View fields
if (string.Empty != viewFieldsInnerXml)
{
viewFields.InnerXml = viewFieldsInnerXml;
}
else
{
viewFields.InnerXml = "<FieldRef Name=\"Title\" />";
}
System.Xml.XmlNode nodeListItems =
listService.GetListItems(listName, viewName, query,
viewFields, rowLimit, queryOptions, null);

return nodeListItems;
}
catch (Exception ex)
{
throw ex;
}
}

You can use this generic method for getting the list items.

Pass true to “IsRecursive” if you want to get items inside folder.

Example:
string fields = "<FieldRef Name=\"FieldName1\" />
<FieldRef Name=\"FieldName2\" />";
XmlNode nodeListItems = CallWebService(true, l, "Answer Choices",
string.Empty, string.Empty, fields);
foreach (System.Xml.XmlNode listItem in nodeListItems)
{
if (listItem.ChildNodes.Count > 0)
{
foreach (XmlNode node in listItem.ChildNodes)
{
string choice = node.Attributes["ows_Title"]
.Value;
}
}
}

Thursday, October 29, 2009

SSRS report and Webservice as datasource

One good feature i found in SSRS report is that, we can provide webservice as the datasource. We can create dataset using webservice methods.

For creating a dataset using webservice, do the following steps.
  • Create a new Datasource
  1. From solution explorer, right click "Shared Data Sources".
  2. Click on "Add new Datasource".
  3. In the new window, provide a name for the new datasource.
  4. Select type as "Xml".
  5. In connection string, provide the path of wsdl. Ex: http://localhost/Service1.asmx?wsdl
  6. Hit Ok.
  • Create a new Dataset
  1. Select the Data tab.
  2. From the Dataset dropdown, select "New Dataset".
  3. In the Dataset window, Select the dataset we have created.
  4. Select Text as command.
  5. Use the following query
<Query>
<Method Namespace="http://tempuri.org/" Name="MethodName">
<Parameters>
<Parameter Name="parameter1">
<DefaultValue>1 </DefaultValue>
</Parameter>
</Parameters>
</Method>
<SoapAction>http://tempuri.org/MethodName </SoapAction>
</Query>

MethodName is the method you want to invoke in the webservice.
parameter1 is the name of the parameter the webservice method is expecting. You need to create a Report Parameter. And in the "Create New Datset" window, open the Parameter tab and add that parameter.

Now in the query window, execute the query by clicking the "Red Exclamation Icon".

Done!!!

For queries and suggestions, please feel free to ping me.

Report Viewer control and Local Reports

There will be cases in which we need to load a local report on ReportViewer control. The below code will help to do the same.

//Declare ReportViewer object

public Microsoft.Reporting.WebForms.ReportViewer reportViewer1;

reportViewer1.ProcessingMode = ProcessingMode.Local;
reportViewer1.LocalReport.ReportPath = path;
reportViewer1.LocalReport.
ExecuteReportInCurrentAppDomain(
System.Reflection.Assembly.GetExecutingAssembly().
Evidence);

// The application is responsible for collecting
// parameters
ReportParameterInfoCollection parameterInfo =
reportViewer1.LocalReport.GetParameters();
if (parameterInfo.Count > 0)
{
List<ReportParameter> param = new List<ReportParameter>();
ReportParameter yearParam = new ReportParameter("Year", “2009”);
param.Add(yearParam);
// Add report data sources

ReportDataSource rds = new ReportDataSource("ds", dataSourceObject);
reportViewer1.LocalReport.DataSources.Add(rds);
// Add report parameters
reportViewer1.LocalReport.SetParameters(param);
}

reportViewer1.LocalReport.Refresh();


Hope this helps :)


If you have any query, please feel free to ping me.

Wednesday, October 28, 2009

Sharepoint Calculated Field - Week Number

The following formula will help us to find the week number for a date value.

=INT(([Date]-DATE(YEAR([Date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Date]),1,1)),"d")))/7)+1

Hope this helps :)

Saturday, October 24, 2009

Integrating SSRS + Sharepoint + Report Viewer

Scenario:

We need to display a report based on the data spread across multiple list. In such a case we won't be able to use either List View or Data View. One easier way to solve this problem is to create a custom webservice which will basically call the List Web Services provided by Sharepoint, create the dataset and return it back to the SSRS report.

One problem which we will face is while the deployment in production server. What we can do is in the report viewer control, lets take the rdl file from local instead of server. We can keep the rdl file in sharepoint list only. Thus the rdl file will be secure and can be replaced only by administrators. One drawback of the approach is that, the rdl file will not be available in the Sql Reporting Server. But compared to the ease of deployment, I think we can live with this :)

Wednesday, October 14, 2009

Sharepoint List View - Calculated Columns

In some scenarios, while creating views we might need to filter based on complex queries. In List View, filters are available but they are not powerful. For example, if we want to group the condition based on And and or, that feature is not available.

One workaround for this problem is to add a calculated column in the list and the result of the calculation will say whether that row should be placed in the view or not.

Example query will be as follows:

IF(AND(OR([Col1]="Some Value", [Col2]="Some Value"), AND([Col3]="Some Value", [Col4]="Some Value")), "Show Row", "Dont Show")

Friday, October 9, 2009

SSRS - Creating reports with specific rows per page

In layout tab, select the table and open the properties windows by right clicking and selecting properties. From the properties windows move to the Groups tab. Click Add and enter the following expression

=Ceiling(RowNumber(Nothing)/20)

Replace 20 with the required number of rows per page. Select "Page Break at Start" and uncheck "Page break at Start". If you want the header and footer to be appear on each page then select those options.

Hope this helps :-)