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 )
END CATCH
Step : 2 Create a DataSet using the DataSet Designer
Step : 3 Create a report definition
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
- 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.WebFormsto 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 ReportNow Press F5 to Run the .aspx page.
