Formatting RDLC

To format matrix report select desired row or column then goto its properties and set background color, forecolor, font, textalign etc as per your requirement.

To limit value upto two decimal places write down below code to your desired column.

 To show serial no  row wise write down below code to 1^st column.

After Implementation of these formatting now report will look like this :

Passing Parameters to RDLC

To pass parameters to rdlc write down below code to the code behind

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


ReportParameter p1 = new ReportParameter("Showdt", "Date : " + DateTime.Now.ToShortDateString());
ReportParameter p2 = new ReportParameter("FromDt", "From : Year  " + Session["FromYear"].ToString() + "      Week No. " + Session["FromWeek"].ToString());
ReportParameter p3 = new ReportParameter("ToDt", "To : Year  " + Session["ToYear"].ToString() + "  Week No. " + Session["ToWeek"].ToString());
ReportParameter p4 = new ReportParameter("Zone", "Zone : " + Session["Zone"].ToString());
ReportParameter p5 = new ReportParameter("Unit", "Unit Name : " + Session["Unit"].ToString());


this.ReportViewer1.LocalReport.SetParameters(new ReportParameter[]{p1,p2,p3,p4,p5});


ReportViewer1.LocalReport.DataSources.Add(rds);
ReportViewer1.LocalReport.Refresh();
Now add parameters to rdlc.Open RDLC --> Click Report Menu
    

In VS-2008 You ll get Report Parameters option from here.

 But in VS-10 u wont get it here.U have to open ReportData tab --> Select Parameter --> Click New

A window ll popup, here u add parameters one by one which is defined in code behind.

Now DragNDrop a textbox from ReportItems --> Right Click --> Click Expression

Write down this code snippet in blank area after "=" and click ok.

Now browse and see the results.

You can Download Sample Code from Here

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

Difference Between Varchar and nVarchar

VARCHAR is an abbreviation for variable-length character string where as The abbreviation of NVarchar is uNicode Variable Length character String.VARCHAR is a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR  to 8,000 bytes.

Like  consider a scenario where we want to save data in other languages then we have to use Unicode format.To save any text in its unicode format we will use nVarchar.

Insert Data From One Table to Another Table

Before Inserting Data into table we have to consider if table is created or not.If Table is created then we use INSERT INTO SELECT method and in another case SELECT INTO TABLE.

INSERT INTO SELECT  :

INSERT INTO [dbo].[Rpt_WeeklySales]
([Description],[Value],[Week_No],[Year],[fkPartyId])
SELECT B.Description, A.Order_Val, A.Week_No,A.Year,A.fkParty_Id
FROM dbo.Trans_Order as A INNER JOIN dbo.SA_TRANS_ORDER_DETAIL as B 

ON A.Order_Id = B.Order_Id

SELECT INTO TABLE :

SELECT B.Description, A.Order_Val as Value, A.Week_No,A.Year,A.fkParty_Id
INTO TestTable
FROM dbo.Trans_Order as A INNER JOIN dbo.SA_TRANS_ORDER_DETAIL as B 

ON A.Order_Id = B.Order_Id

Create, Insert, Search, Delete in DataTable

Create DataTable and Define its Fields (Cloumns)
DataTable dt = new DataTable();
dt.Columns.Add("ItemId",typeof(int));
dt.Columns.Add("Item",typeof(string));
dt.Columns.Add("Qty",typeof(float));
dt.Columns.Add("Category_Name", typeof(string));

Fetch Data from DataBase and Insert into DataTable

DataSet dstmp = new DataSet();
query = "SELECT  *  FROM  vw_ItemDet";

SqlDataAdapter adp = new SqlDataAdapter(query, con);
adp.Fill(dstmp);
dt = dstmp.Tables[0];

Insert Data from WebForm -:

 DataTable dt = (DataTable)ViewState["Dt"];
 DataRow dtdr = dt.NewRow();
 dtdr["ItemId"]=ddlOtherItem.SelectedValue;
 dtdr["Item"]=ddlOtherItem.SelectedItem.Text;
 dtdr["Qty"] = txtQty.Text;
 dt.Rows.Add(dtdr);

Store values of DataTable in  ViewState for further use.Basically its helpful if you have to use  this table value later.

ViewState["dtable"] = dt;

Retrieve value from ViewState

DataTable VWTempDTable=new DataTable();
VWTempDTable = (DataTable)ViewState["dtable"];

Insert Values from one DataTable to Another based on some condition..-:

string cond = "Category_Name='" + ViewState["sad"] + "'";

DataRow[] foundRows;
foundRows = VWTempDTable.Select(cond);
foreach (DataRow row in foundRows)
{
            VWTempSecond.ImportRow(row); ///VWTempSecond is another DataTable
}

Delete Rows from DataTable -:

VWTempSecond.Rows[Convert.ToInt32(s)].Delete();