Second Stanza

October 19, 2008

Using the Report Viewer Component with .RDL Files

Filed under: .NET Examples, SQL Server — Tags: , — dfbaskin @ 9:30 pm

You can use Visual Studio to create Report Definition Language (.RDL) files that can be used within SQL Server Reporting Services. The editor for .RDL files allows you to put together the SQL Query, design the report layout, and preview the report all within Visual Studio.

Microsoft has also provided a Report Viewer control that allows you to display reports within your application. This control is not tied to SQL Server since you can use your own data sources as input to the report (though it can also display reports that originate from SQL Server Reporting Services). Reports are defined using the .RDLC format, which is the same format as .RDL files, but ignores certains aspects of the definitions (more details here).

Of course, since a data source can be defined within a Visual Studio project, you lose the ability to preview your report easily. Generally, you have to compile and run the application in order to view your report and verify that it is correct. Being able to quickly view your report output was a nice feature of working with the SQL Server Reporting Services version of the report editor.

However, since the .RDL and .RDLC formats are the same, you can use an .RDL specification as input to the Report Viewer control. All you need to do is to implement some code that runs the query embedded in the .RDL file. By doing so, you may use the Visual Studio 2005 version of the Reporting Services editor (with its handy preview mode) but still use the Report Viewer component within your application.

Here’s source code that shows this technique:



using System;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.XPath;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Reporting.WinForms;

namespace ReportView
{
    static class Program
    {
        [STAThread]
        static void Main( string[] argList )
        {
            try
            {
                if( argList.Length != 1 )
                    throw new ArgumentException( "Must specify name of .RDL file." );
                string rdlFileName = argList[0];
                if( !File.Exists( rdlFileName ) )
                    throw new FileNotFoundException( "Could not find .RDL file." );

                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault( false );
                Application.Run( new ReportViewForm( rdlFileName ) );
            }
            catch( Exception ex )
            {
                StringBuilder errMsg = new StringBuilder();
                errMsg.Append( "Application Error generated:\n" );
                for( Exception x = ex; x != null; x = x.InnerException )
                    errMsg.AppendFormat( "   {0} - {1}\n", x.GetType().FullName, x.Message );
                errMsg.AppendFormat( "Stack Trace:\n{0}", ex.StackTrace );
                MessageBox.Show( errMsg.ToString(), "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
            }
        }
    }

    class ReportViewForm : Form
    {
        private Microsoft.Reporting.WinForms.ReportViewer rptView;
        private string reportFileName;

        public ReportViewForm( string fileName )
        {
            reportFileName = fileName;
            rptView = new Microsoft.Reporting.WinForms.ReportViewer();

            SuspendLayout();

            rptView.Dock = System.Windows.Forms.DockStyle.Fill;
            rptView.Name = "rptView";
            rptView.TabIndex = 0;
            Controls.Add( rptView );

            const int border = 20;
            System.Drawing.Size winSize = Screen.PrimaryScreen.WorkingArea.Size;
            Location = new System.Drawing.Point( border, border );
            Size = new System.Drawing.Size( winSize.Width - ( 2 * border ), winSize.Height - ( 2 * border ) );
            StartPosition = FormStartPosition.Manual;
            Name = "ReportViewForm";
            Text = Path.GetFileName( reportFileName );

            ResumeLayout( false );
        }

        const string ReportDefinitionNS = "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition";
        const string ReportDesignerNS = "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";

        protected override void OnLoad( EventArgs e )
        {
            base.OnLoad( e );

            rptView.ProcessingMode = ProcessingMode.Local;
            using( Stream readStream = new FileStream( reportFileName, FileMode.Open, FileAccess.Read, FileShare.Read ) )
                rptView.LocalReport.LoadReportDefinition( readStream );

            XPathDocument xpDoc;
            using( Stream readStream = new FileStream( reportFileName, FileMode.Open, FileAccess.Read, FileShare.Read ) )
                xpDoc = new XPathDocument( readStream );

            ConnectionStringSettings connSett = ConfigurationManager.ConnectionStrings["Default"];
            using( SqlConnection sqlConn = new SqlConnection( connSett.ConnectionString ) )
            {
                sqlConn.Open();

                XPathNavigator xpNav = xpDoc.CreateNavigator();
                XPathExpression xpe = xpNav.Compile( "/r:Report/r:DataSets/r:DataSet" );
                XmlNamespaceManager nsMgr = new XmlNamespaceManager( xpNav.NameTable );
                nsMgr.AddNamespace( "r", ReportDefinitionNS );
                nsMgr.AddNamespace( "rd", ReportDesignerNS );
                xpe.SetContext( nsMgr );
                foreach( XPathNavigator xpNode in xpNav.Select( xpe ) )
                {
                    string dsName = Convert.ToString( xpNode.Evaluate( "string( @Name )", nsMgr ) );
                    string cmdTxt = Convert.ToString( xpNode.Evaluate( "string( r:Query/r:CommandText )", nsMgr ) );
                    if( String.IsNullOrEmpty( dsName ) || String.IsNullOrEmpty( cmdTxt ) )
                        throw new Exception( "Could not obtain query for report definition." );

                    SqlCommand sqlCmd = sqlConn.CreateCommand();
                    sqlCmd.CommandType = CommandType.Text;
                    sqlCmd.CommandText = cmdTxt;

                    DataTable dataTable = new DataTable();
                    SqlDataAdapter sqlAdapter = new SqlDataAdapter( sqlCmd );
                    sqlAdapter.Fill( dataTable );
                    rptView.LocalReport.DataSources.Add( new ReportDataSource( dsName, dataTable ) );
                }
            }

            rptView.RefreshReport();
        }
    }
}


8 Comments »

  1. Fantastic article! Very clear and easy to follow. Also easy to expand to be able to read the parameters of the queries.

    I was wondering if there is anyway to do this without being tied to the 2005 schema?

    I was hoping to be able to get the dataset info out of the doc WITHOUT having to be worried about whether the doc was created in 2005 or 2008?

    Comment by bushfoot — December 16, 2008 @ 8:37 am

    • Not yet. The report viewer component is implemented using the reporting engine from SQL Server 2005. From this post:

      An important thing to understand is that the ReportViewer in VS 2008 is based on the report engine from SQL Server 2005 Reporting Services, which means it uses the 2005 RDL schema and processing engine. Since SQL Server 2008 has not shipped yet, we will not be able to release a 2008 RDL version of the ReportViewer until next year.

      I assume there will be an update to Visual Studio that will include SQL Server 2008 Reporting Services functionality some time in the future.

      Comment by dfbaskin — December 27, 2008 @ 9:25 pm

  2. The above code i have copied and implemented it but what if i need to pass the parameter for my report through report viewer control and what if my report have sub report in it how can i implement it using your logic. can you please guide me

    Comment by dilipvarma — February 11, 2009 @ 8:40 am

    • dilipvarma, You would have to add some code to handle the LocalReport.SubreportProcessing Event. I believe the same technique would work, only you would be performing the SQL query and setting the data source for the sub-report within the event handling logic.

      Comment by dfbaskin — February 26, 2009 @ 9:54 pm

  3. When I call myReportViewer.LocalReport.Render what process will do the work to create the report before my custom renderer is called?
    I have a report that is big (170 000 rows 14 columns) and when I run it, my processes takes up a lot of physical memory and the sql process stays at about 20%.
    Is there some way to increase the speed ?
    Is there some way to pass it a bit of data at a time?
    Maybe instead of assigning a ado dataset to the datasources of the doc I should use something else?

    Comment by bushfoot — February 18, 2009 @ 9:27 am

  4. Hi,
    I like the artical it is clear and well defined, I stopped here because I needed to dynamically define the report’s groupby function and write it to the rdl to enable drill down ?

    Thanks

    Comment by Hazem Fessi — June 28, 2010 @ 1:09 pm

  5. Please have do the same thing with reporting 2010 ?
    thanks you

    Comment by Cyril — February 16, 2012 @ 1:47 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: