Total Pageviews

10/29/2011

How to use RDLC with Asp.net?

How to use RDLC with Asp.net?
Sql server reporting service without instilling SSRS

I just passed thru a situation where my client does require 2 reports to be developed in SSRS ( SQL Server Reporting Service ). It was not good advise to spend extra money behind SSRS on shared hosting as his requirement was very small. I show some example of RDLC (Report Definition Language Client-side) to my client and he was very excited about that.

I thought it’s nice to share this with all my blog readers and my friends.

Introduction
Step : 1 Create a Parameterized Store Procedure.
(I have used table called ProjectDetail for this tutorial )

    CREATE PROCEDURE [dbo].[ReportProjectDetail]
    (
      @ProjectID  INT=NULL ,
      @FromDate   DATE=NULL,
      @ToDate     DATE=NULL
    )
    AS
    BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
           SELECT      ProjectID,
                       ProjectName,
                       ClientName,
                       ProjectReqDate,
                       ProjectValue,
                       PartnerID,
                       PartnerType,
                       PartnerName,
                       PartnerEmail,
                       FundingID,
                       FundingStatus,
                       AmountRequested,
                       AmountRecieved,
                       SentFundTo,
          FROM   ProjectDetail                            
          WHERE (@ProjectID =NULL or ProjectID=@ProjectID)
          AND   (@FromDate IS NULL OR CreatedOn BETWEEN @FromDate AND                        @ToDate)

     END TRY                                              
      BEGIN CATCH                                    
          PRINT Error_Message()              
          DECLARE @msg VARCHAR(MAX)
          SELECT @msg = ERROR_MESSAGE()
          EXECUTE ERR_LogError
          RAISERROR('Error in %s: %s', 16, 1, 'ReportProjectDetail',@msg)
      END CATCH                                            
     END





Step : 2 Create a DataSet using the DataSet Designer
  • Start by running Visual Studio and select New Website from the Start page.
  • Add ASP.Net folder App_Code
  • In Solution Explorer Right Click on App_Code > Add new Item > DataSet
  • Now select DataSet & Give name. e.g. ds_ProjectDetail.xsd and Save.
  • Now Right Click anywhere in DataSet sceen and select Add from the context menu.
  • Select Table Adapter In to Wizard. Now create data table.
  • Now choose Existing Store procedures.
  



Step : 3 Create a report  definition


  •  In Soultion Explorer right click on & select > Add New Item > Reporting > Report 
  • Now Given report name e.g. rptProjectDetail.rdlc Click Add to your project.
  • Now Drag a table from the report designer screen.
  • Now Dataset Properties has to be come in pop up now give your dataset name next choose your dataset next select available reports & last click OK.
  • The table has in three Bands, first header, second detail, & last footer bands. 
  • After created your dataset, it will appear on your left panel in Website Data Source window.
  • Expand it and drag the file to the report designer page . For example, drag and place it in the Table Fields.
  • After you have assigned the attribute, now  report creation is done.
     
     

    Step : 4 Add Report Viewer control into your ASPX page

      Step:-

    • In Solution Explore Right click > Add new item >Web > Web Form  now give name e.g. rdlcDemo.aspx and click on Add.
    • Now Open your Left Side Toolbox and drag the Report Viewer control to your ASPX page (In Design mode).
    • Once your have Drop n drag Control on aspx Webpage , select it and click on the arrow on the top right corner to choose your rdlcReport.rdlc report in dropdown list.
    • Don’t Forget to add namespace Microsoft.Reporting.WebForms to be in your code-behind file.


    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.Common;
    using System.Web.UI;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
    using Microsoft.Reporting.WebForms;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web.UI.WebControls;
    using System.Data;
    using DataAccessLayer;

    protected void GenerateReportButton_Click(object sender, EventArgs e)
        {
            ReportViewer1.Visible = true;
            string connectionString = ConfigurationManager.ConnectionStrings
    ["ConnectionStringName"].ConnectionString;

            Database db = new SqlDatabase(connectionString);
            DbCommand command = db.GetStoredProcCommand("ReportProjectDetail");
            db.AddInParameter(command, "@ProjectID", DbType.String, ddlProject.SelectedValue);
            if (calFromDate.Text.ToString() != "")
            {
                db.AddInParameter(command, "@FromDate", DbType.String, calFromDate.Text.ToString());
            }
            if (calToDate.Text.ToString() != "")
            {
                db.AddInParameter(command, "@ToDate", DbType.String, calToDate.Text.ToString());
            }
            DataSet dataset = db.ExecuteDataSet(command);
            ReportDataSource datasource = new ReportDataSource("DataSet1", dataset.Tables[0]);

            ReportViewer1.LocalReport.DataSources.Clear();
            ReportViewer1.LocalReport.DataSources.Add(datasource);

            ReportViewer1.LocalReport.Refresh();

        }
    }

    Bind and Run The Report

    Now  Press F5 to Run the .aspx page.
     
    Thanks,
    Bhavik 

     

9 comments:

Jayesh L said...

thanks

Anonymous said...

I am getting this error The type or namespace name 'database' could not be found . I am using microsoft visual studio 2008.

Thanks for your help in advance

Anonymous said...

Database db = new SqlDatabase(connectionString);
DbCommand command = db.GetStoredProcCommand

Getting error in this line and i can't use following using statements
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

Anonymous said...

Thanks

Anonymous said...

how to drag dataset from xsd to report?

sibghat ullah Buzdar said...

displayed on the page after Running the Project:

A data source instance has not been supplied for the data source 'DataSet1'.

Anonymous said...

my report has limited width when export to pdf.
the data which doesn't shows up in page(because of width)
It comes in next page

Rajendra Pathi said...

Could you please give the code for loading server RDLC report with server authentication

Anonymous said...

ok

Post a Comment

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Design by Free WordPress Themes | Bloggerized by Lasantha - Premium Blogger Themes | Affiliate Network Reviews