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.

Reporting Services - very helpful blog

This is very helpful blog about Reporting Services

http://blogs.msdn.com/chrishays/

Reporting Service 2005 - top values? - limit the number of displayed data rows

Sometimes I need to show only top 5 values from defined data source in the data table, without setting the number of rows in SQL query (top statement).

In data table set visibility expresion for a table row to:

=iif(RunningValue(Fields!Id.Value, CountDistinct, nothing) > 5, true, false)

Reporting services: show value with line breaks between words

I need to display value as a list separated with coma and line break.


I found great tip, how to realize line breaks in reporting services here:
http://www.kodyaz.com/articles/reporting-services-add-line-break-between-words-custom-code.aspx

very helpful custom code function:

Public Function AddLineBreakBetweenWords(words As String) As String
Dim rsRegEx as System.Text.RegularExpressions.Regex = new System.Text.RegularExpressions.Regex("\s+")
words = rsRegEx.Replace(words, " ")
return words.Replace(" ", vbCrLf).Trim()
End Function
 
 
 

Generate PDF from Reporting Services - cache problem

I develop reports with Reporting Services server and WCF as data sources
Reports are generated in PDF format.

I could not see changes in my reports after updating data.
I found the solution on Peter's Gekko blog.

I added  following  to my report url:

&rs:ClearSession=true

Here is my code:

var reportUrl = ConfigurationManager.AppSettings["ReportURL"];


var reportParameters = @"&rs:Command=Render&rc:toolbar=false&rs:Format=PDf&rc:OmitFormulas=true&rs:ClearSession=true&id=";


System.Windows.Browser.HtmlPage.Window.Navigate(new Uri(string.Concat(reportUrl, reportParameters, myId)), "_blank");

Reporting Services ReportViewer control in local mode - permissions problem

I implemented solution for generating reports on the server for my sharepoint application. I use reporting Services ReportViewer control.

My first POC worked fine as a windows console application. I generated reports and write results to the file as a HTML

The same logic executed from Sharepoint WebPart caused exception during rendering a report:

byte[] htmlBytes = reportViewer1.LocalReport.Render("HTML4.0", null, out mimeType, out encoding, out fileNameExtension, out streamids, out warnings);

InnerException: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
Message="Failed to load expression host assembly. Details: Could not load file or assembly 'expression_host_380e9163b7f3428c8dab16a7f4e9022d, Version=10.8.21022.8, Culture=neutral, PublicKeyToken=null' or one of its dependencies. Failed to grant permission to execute. (Exception from HRESULT: 0x80131418)"


The solution for this problem is adding following line before rendering

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