Thursday, January 26, 2012

Matrix Report via RDLC

Step1: Define a new DataSet Class.

For that Right Click on App_Code Folder-->Click on Add New Item-->Select Dataset From Templates--->Click On Add.

Step 2 :  Before proceeding to Step 2 You have to create your stored procedure which is used to bind the Dataset.Now on the blank area of created xsd file Right Click --> Click Add --> Click Table Adapter


Step 3 : Choose Your Connection String and Click Next

Step 4 : Choose Use Existing Procedure and click Next

Step 5 : In Select DropDown Choose your Stored Procedure--->Finally Click On  Finish.
And Don't forget to save the changes you make.

Step 6 : Now its time to create the rdlc file. For this click on New Item--> Select Report From Templates-->Click OK



Step 7 : Now from ToolBox drag n drop Matrix

 I will write down the steps to design the given report format.

Step 8: Drag n Drop Particular Field from WebSite Data Sources to Row of Matrix.
Then Drag n Drop Year Field to Columns of Matrix and then Right Click and then Click On Insert Group.

Now in General Tab Select the Field which You want to display under Year.Then in Parent group Select Year.


Now Right Click on quarter column and insert a new group for BDShip whose Parent Group will be Quarter.
Then Drag n Drop ParametersValue to data region.
For ColumnWise Total right click on Year column then select SubTotal and for RowWise Total right click on Particular column and click SubTotal.
Now your report looks like this .

I ll cover passing parameters and formatting in my next article.Right now concentrate on basic report.But dont forget  to click save button for rdlc.Now create an aspx where just drag n drop ReportViewer to your page. You ll find ReportViewer in Reporting tab of ToolBox.In the top notch of ReportViewer select rdlc.

Now its time to write some code.Move to the .cs page (code behind of ur aspx). In cs at first add this namespace " using Microsoft.Reporting.WebForms;" and then add below function and call it from proper place.



 public void BindReport()
    {
        SqlCommand cmd = new SqlCommand("sp_RptUnitPerformance", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@FromWeek", Session["FromWeek"].ToString());
        cmd.Parameters.AddWithValue("@ToWeek", Session["ToWeek"].ToString());
        cmd.Parameters.AddWithValue("@FromYear", Session["FromYear"].ToString());
        cmd.Parameters.AddWithValue("@ToYear", Session["ToYear"].ToString());
        cmd.Parameters.AddWithValue("@BDId", Session["BDId"].ToString());
        cmd.Parameters.AddWithValue("@Zone", Session["Zone"].ToString());
        cmd.Parameters.AddWithValue("@UnitName", Session["Unit"].ToString());
        if (con.State == ConnectionState.Closed)
            con.Open();


        DataSet ds = new DataSet();
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        adp.Fill(ds);


        ReportDataSource rds = new ReportDataSource("UnitPerformanceDataSet_sp_RptUnitPerformance",              ds.Tables[0]);
        ReportViewer1.LocalReport.DataSources.Clear();
       
        ReportViewer1.LocalReport.DataSources.Add(rds);
        ReportViewer1.LocalReport.Refresh();
    }


UnitPerformanceDataSet_sp_RptUnitPerformance  -: To get this datasource name goto the source of your aspx  in datasource tag of reportViewer 

 <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource1" 
                        Name="UnitPerformanceDataSet_sp_RptUnitPerformance" />
 </DataSources>


Your page will be lokk like this -:
Now browse ur page and see the results.For further query feel free to contact me.To know how to pass parameter and format output read other articles.


You can download Sample Code from Here

No comments:

Post a Comment