Using ReportViewer 10 control in SharePoint Foundation 2010 webpart

Using  ReportViewer 10 control in SharePoint Foundation 2010 webpart.

I’m using new ReportViewer  control in a SharePoint for rendering reports in local mode. My reports are using  data from different SharePoint lists. ReportViewer control is then displayed  in SharePoint webpart.

I decided to upgrade my previous solution to new version of Report Viewer control that shipped with new Visual Studio 2010 and is also available as redistribualbe package http://www.microsoft.com/downloads/en/details.aspx?FamilyID=a941c6b2-64dd-4d03-9ca7-4017a0d164fd&displaylang=en

Why I decided to use new versionof reportViewer? Because it allow using reports in new format Reporting services 2008 .  I’m using new Visual Studio 2010 with build in report designer from Report Services 2008

To use ReportViewer control in SharePoint webpart, they are following requirements to meet in a SharePoint configuration:

Enabled  Session state for a web application:

1.       Open IIS 7 manager, and find your web application.

2.       Double click "Modules" in the IIS section.

3.       Click "Add Managed Module..." on the right hand pane.

4.       In the Add Managed Module dialog, enter "SessionState" or something like that for the name, and choose the following item from the dropdown:

System.Web.SessionState.SessionStateModule, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a

Enable View State:

In a web.config file change

enableSessionState="true" enableViewState="true"….

Register ReportViewer control:

   

     

…

   

     

…

Source code of my webpart that use report viewer:

    public class GenerateWebpart : System.Web.UI.WebControls.WebParts.WebPart

    {

        #region Members

        private DataSet ds;

        private ReportViewer reportViewer = null;

        private Microsoft.Reporting.WebForms.ReportDataSource reportDataSource = null;

        #region Child controls

        //UI elements

        private Button cmdGenerate;

        private DropDownList cmbSelectReport;

        #endregion Child controls

        #endregion Members

        #region Construction / Finalization

        public GenerateWebpart()

        {

            this.ChromeType = PartChromeType.None;

        }

        #endregion Construction / Finalization

        #region Overrides

        ///

        /// Called by the ASP.NET page framework to notify server controls that use composition-based implementation to create any child controls they contain in preparation for posting back or rendering.

        ///

        protected override void CreateChildControls()

        {

            try

            {

                // Create and add the controls that compose the

                // user interface of the Web Part.

                this.SetPersonalizationDirty();

                cmbSelectReport = new DropDownList();

                cmbSelectReport.Items.Add(new ListItem("Report sales", "sales"));

                cmbSelectReport.Items.Add(new ListItem("Report products", "products"));

                this.Controls.Add(cmbSelectReport);

                cmdGenerate = new System.Web.UI.WebControls.Button();

                cmdGenerate.Text = "Generate";

                cmdGenerate.Width = new Unit(75);

                cmdGenerate.Click += new EventHandler(GenerateButton_Click);

                this.Controls.Add(new LiteralControl("  "));

                this.Controls.Add(cmdGenerate);

                this.Controls.Add(new LiteralControl("

"));

                reportDataSource = new Microsoft.Reporting.WebForms.ReportDataSource();

                reportViewer = new ReportViewer();

                reportViewer.ProcessingMode = ProcessingMode.Local;

                reportViewer.ShowRefreshButton = false;

                reportViewer.EnableViewState = true;

                reportViewer.AsyncRendering = false;

                reportViewer.Width = this.Width;               

                this.Controls.Add(reportViewer);

            }

            catch (Exception ex)

            {

                Literal _ErrorMessageLiteral = new Literal();

                _ErrorMessageLiteral.Text = ex.Message + "Inner exception: " + ex.InnerException.Message;

                this.Controls.Clear();

                this.Controls.Add(_ErrorMessageLiteral);

            }

        }

        #endregion Overrides

        #region UI event handlers

        protected void GenerateButton_Click(object sender, EventArgs e)

        {

            if (cmbSelectReport != null)

            {

                GenerateReport();

            }

        }

        #endregion UI event handlers

        #region Private methods

        private void GenerateReport()

        {

            switch (cmbSelectReport.SelectedValue)

            {

                case "sales":

                    ds = new SalesData();

                    ds = Helper.PopulateDataSet(ds);

                    reportDataSource = new Microsoft.Reporting.WebForms.ReportDataSource();

                    reportDataSource.Name = "MergedFlatData";

                    reportDataSource.Value = Helper.JoinDataSetByRelation(ds).Tables["MergedFlatData"];

                    this.reportViewer.LocalReport.ReportEmbeddedResource = "Sales.rdlc";

                    break;

                case "products":

                    ds = new ProductData();

                    ds = Helper.PopulateDataSet(ds);

                    reportDataSource = new Microsoft.Reporting.WebForms.ReportDataSource();

                    reportDataSource.Name = "MergedFlatData";

                    reportDataSource.Value = Helper.JoinDataSetByRelation(ds).Tables["MergedFlatData"];

                    this.reportViewer.LocalReport.ReportEmbeddedResource = "Products.rdlc";

                    break;

            }

           

            this.reportViewer.LocalReport.DataSources.Clear();

            this.reportViewer.LocalReport.DataSources.Add(reportDataSource);

            reportViewer.LocalReport.ExecuteReportInCurrentAppDomain(System.Reflection.Assembly.GetExecutingAssembly().Evidence);

            reportViewer.LocalReport.Refresh();

        }

        #endregion Private methods

    }

To get it working I need to set reportViewer.AsyncRendering = false;

(by default is set to true)

Actually I don’t use asynchronous calls by using this control and I don’t know exactly how to use ReportViewer in asynchronous mode.

Customizing SharePoint Search - overriding search results webpart

Customizing Sharepoint Search - overriding search results webpart

Some times ago, I developed custom search solution in SharePoint 2007 enviroment.

I implemented search using Search URL Syntax

I needed special custom search webpart with multiple additional search criteria related to custom properties. additionally I implemented custom result webpart

Introduction

In this article i describe, how to create additional filter to search results by overriding

"core search result" webpart.

Searching using URL syntax query has limited functionality (searching by scope or keyword).

http://msdn.microsoft.com/en-us/library/aa637082.aspx

It is not posibile to compare dates or number, (like greather then  01.01.2008 or less then 15).

why not just use full text search engine? Because using URL syntaxt query give more flexibilty! You can easly redirect  your search results to other pages/webparts!

Using aproach described in this article, it is posible to perfom compare operation on searched columns with date or number values.The way to do this, it is filtering already returned search results in search results webpart.

Implementation

1) Override CoreResultsWebPart webpart

namespace SearchWebParts

{
[Guid("85DF3779-8E2C-49c9-8A83-828DC4FACE03")]

public class EasyAdvancedSearchResults : Microsoft.Office.Server.Search.WebControls.CoreResultsWebPart

{




2) where to modify search results???

Search results are stored in xmlResponseDoc member variable. Good place to modify it is in protected 
ModifyXsltArgumentList(Microsoft.SharePoint.WebPartPages.ArgumentClassWrapper argList) method.


#region Overrides
protected override void ModifyXsltArgumentList(Microsoft.SharePoint.WebPartPages.ArgumentClassWrapper argList)
{
base.ModifyXsltArgumentList(argList);
//here starts custom filtering of the search results
FilterSearchResultsByCreateDate(); 
} #endregion Overrides
3) Get filter parameters

In this example i need to filter searched documents with creation date. 
User can select the scope of creation date with start date and end date parameters on the custom search webpart. This parameters are then send with url query. For example:

http://testdomain/sites/test/erweitertesuche.aspx?k=search&fromdate=06.04.2009+00%3a00%3a00&todate=30.04.2009+00%3a00%3a00&s=Alle+ERGO+Dokumente&start=1

Get this values from URL in our custom result webpart

System.Globalization.CultureInfo provider = System.Globalization.CultureInfo.CurrentCulture; 
//filter when one of the date parameters is present 'fromdate' or 'todate'
if (this.Context.Request["todate"] != null || this.Context.Request["fromdate"] != null)
{
//try parse 'to date' parameter
if (this.Context.Request["todate"] != null)
{
DateTime.TryParse(this.Context.Request["todate"], provider, System.Globalization.DateTimeStyles.None, out toDate);
}
//try parse 'from date' parameter
if (this.Context.Request["fromdate"]!=null)
{
DateTime.TryParse(this.Context.Request["fromdate"], provider, System.Globalization.DateTimeStyles.None, out fromDate);
} 

4) Filter search results 

Filter results by creation date with start date and end date parameters. 
You have results in 

protected XmlDocument xmlResponseDoc;

Modfiy xml results the way you want. I do this using LINQ query, fast and easy.

//get original xml with search results XElement root = XElement.Parse(xmlResponseDoc.InnerXml);

//LINQ query, results are filtered by 'from date' and 'to date' 
IEnumerable<XElement> searchResults =
from el in root.Elements()
where DateTime.Parse((string)el.Element("write").Value, provider) > fromDate &&
DateTime.Parse((string)el.Element("write").Value, provider) < toDate
select el; 


5) Set filtered xml as a new search results value

//set filtered result as a new search result
root.ReplaceNodes(searchResults); 
xmlResponseDoc.InnerXml = root.ToString();

Below complete method listing

#region Private methods

///

/// Filter original results xml by create date.

/// Filter parameters are requested from context.

///

private void FilterSearchResultsByCreateDate()

{           

    //set initial values for parameters

    DateTime fromDate = DateTime.MinValue;

    DateTime toDate = DateTime.MaxValue; 

    System.Globalization.CultureInfo provider = System.Globalization.CultureInfo.CurrentCulture;

    //filter when one of the date parameters is present 'fromdate' or 'todate'

    if (this.Context.Request["todate"] != null || this.Context.Request["fromdate"] != null)

    {

        //try parse 'to date' parameter

        if (this.Context.Request["todate"] != null)

        {

            DateTime.TryParse(this.Context.Request["todate"], provider, System.Globalization.DateTimeStyles.None, out toDate);

        }

        //try parse 'from date' parameter

        if (this.Context.Request["fromdate"]!=null)

        {

            DateTime.TryParse(this.Context.Request["fromdate"], provider, System.Globalization.DateTimeStyles.None, out fromDate);

        }

        //get original xml with search results

        XElement root = XElement.Parse(xmlResponseDoc.InnerXml);

       

        //LINQ query, results are filtered by 'from date' and 'to date'                

        IEnumerable searchResults =

        from el in root.Elements()

        where DateTime.Parse((string)el.Element("write").Value, provider) > fromDate &&

              DateTime.Parse((string)el.Element("write").Value, provider) < toDate

        select el;                               

       

        //set filtered result as a new search result

        root.ReplaceNodes(searchResults);               

        xmlResponseDoc.InnerXml = root.ToString();

    }

}

#endregion Private methods

Working with Entity Framework 1.0 and Stored Procedures - map complex data types

Working with Entity Framework 1.0 and Stored Procedures – how to deal with complex data  types

Last months  I worked on a ASP.NET project with Entity Framework.

The key requirement for a project was to use .NET Framework 3.5 not 4.0, so I used older version of EF that was included in .NET Framework 3.5 Service Pack 1.

 I also must use SQL Server with Stored Procedures, instead of great features from the EF - LINQ.

Entity Framework is not a nice tool. But the problem is when I started to use Stored Procedures instead of generated SQL queries from LINQ.

To get started I recommend following articles:

Microsoft documentation - Quickstart (Entity Framework)

http://msdn.microsoft.com/pl-pl/library/bb399182(v=VS.90).aspx

ADO.NET Entity Framework Tutorial and Basics:

http://www.codeguru.com/csharp/csharp/cs_linq/article.php/c15489__1/ADONET-Entity-Framework-Tutorial-and-Basics.htm

ADO.NET team blog: Stored Procedure Mapping

http://blogs.msdn.com/b/adonet/archive/2008/03/26/stored-procedure-mapping.aspx

Many examples in Microsoft documentation and blog posts does not show, how to deal with complex types by using Entity Framework and Stored Procedures?

The biggest drawback is, that the references are not automatically loaded by creating instances of objects. This means that child collection and child objects references are equal to null.

(This is not a problem when you are using LINQ. You write a LINQ query, SQL query will be generated and objects hierarchy is populated as you need: See example here.)

When working with EF 1.0 with Stored Procedure, you must deal with a problem manually.

1)      Write  “Select”  Stored Procedures  for object type you need and for all object types you need to reference.

- Select SP for root object - that return one data row by ID

- Select SP for single child objects - that return one data row by a parent ID

- Select SP for child collections - that returns a list of data rows by a parent ID

Because references are empty, you need to realize it with SQL Inner Joins, and query objects by a parent ID.

Example:

When we have such object hierarchy:

Select SP for root Account type:

Create Procedure [dbo].[usp_Account_SelectRow]

      @ID bigint

As

Begin

      Select

            [ID],

            [Name],                

            [AccountTypeId],

            [Supervisor],          

            [ManagerName],

            [Created],

            [Modified]

      From Account

      Where

            [ID] = @ID

End

GO

                Select SP for child type Account Type. Here important is that you load AccountType data by Account.ID by using inner join ( 1 to N relation)

Create Procedure [dbo].[usp_AccountType_SelectRowsByAccountId]

 @AccountID bigint

As

Begin

 Select

  AccountType.ID,

  [Code],

  AccountType.Name as Name

 From AccountType inner join Account on AccountType.ID = Account.AccountTypeId

 Where

  Account.ID = @AccountID

End

GO

Select SP for child collection accountObjectives is similar, the difference is, that it return whole list of data rows (N to N relation)

CREATE Procedure [dbo].[usp_AccountObjective_SelectRowsByAccountId]

      @AccountId bigint

As

Begin

      Select

            [ID],

            [AccountId],

            [ProductId],           

            [Titel],

            [Ranking],

            [Created],

            [Modified]

      From AccountObjective

      Where

            [AccountId] = @AccountId

      Order by [Ranking]

End

SET ANSI_NULLS ON

GO

2)      Map all “Select”Stored Procedures to the Methods using “Function Import” in Visual Studio EF Model browser.

3)      Loading root object by using Imported select function. Attach needed child objects and child collections

In this example I load Account object and its referenced AccountType child object.

        public Account FindById(long id)

        {

             SelectedItem = _dataContext.FindAccountById(id).First();           

            var accountType = _dataContext.GetAccountTypeForAccount(id).ToList();

            if(accountType.Any())

            {

                SelectedItem.AccountTypeReference.Attach(accountType.First());  

            }

            if(IsAuthorizedForSelectedAccount)

            {

                return SelectedItem;   

            }

            else

            {

                throw new ApplicationException("The user does not have permission for selected Account.");

            }           

        }