How to add row numbering in crystal report C#

Question

I have started creating reports using crystal reports. I am able to show everything using dataset and sql except for the auto-row numbering.

Here's my code:

SqlConnection cnn;
string connectionString = null;
string sql = null;
connectionString = "data source=Kim; initial catalog=DBO;user id=sa; password=passw0rd";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "select Name as DataColumn1, Age as DataColumn2,  from tbl1";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
cnn.Close();

DataSet2 ds = new DataSet2();
dscmd.Fill(ds, "DataTable2");

CrystalReport1 objRpt = new CrystalReport1 ();
objRpt.SetDataSource(ds.Tables[0]);

CrystalReportViewer1.ReportSource = objRpt;
CrystalReportViewer1.RefreshReport();

The Result of the report is like this:

No  Name    Age
    Kim     22
    Ian     29
    Aris    27

Need to show the report like this:

No  Name    Age
1   Kim     22
2   Ian     29
3   Aris    27

Can you give me idea on how to add row number.

Answer 1

Automatic sequential numbering in crystal report

1. Open Crystal report

2. Field Explorer, right-click "Running Total Fieids" - New

3. Configuration.

   3.1 Select Field to set (Choose a field to summarize)

   3.2 Click to select Custom Field values

   3.3 Select "Type of summary" to set ( 'Type of summary' to 'distinct count')

   3.4 Set "Evaluate" (Set ‘for each record’)

   3.5 Set "Reset"(set ‘never’)

   3.6 Click OK.

4. Then add the field to report

   

Answer 2

You can change the select query to

select ROW_NUMBER() OVER(ORDER BY Age) as No, Name, Age from tbl1

which will produce expected result.

Answer 3

1. Create a new 'Running Total Field'
2. Give a name for the field Like 'RowNo'
3. Choose a field to summarize
4. Set the 'Type of summary' to 'distinct count'
5. In 'Evaluate' choose 'For each record'

6. In 'Reset' choose 'Never'

   Click OK Add the field to the report.

Answer 4

Creating a 'RecordNumber' special field would be the most simple and easy way to achieve it. Record Number Field is used to number each record printed in the Details section of your report.

Answer 5

In crystal report their is option to add auto increment field,no need to fetch rownumber from database

Answer 6

Use datatable and take DS.tables[0].rows.count and take a count inside and which increments with every loop iteration.

    DataTable tab_lvl = new DataTable();
       tab_lvl.Columns.Add(new DataColumn("NO", typeof(string)));
       tab_lvl.Columns.Add(new DataColumn("Name", typeof(string)));
       tab_lvl.Columns.Add(new DataColumn("AGE", typeof(string)));
       tab_lvl.Columns.Add(newDataColumn("Allot_Asset_Code",typeof(string)));
int count;
        for(int i=0;i<ds.Tables[0].Rows.Count;i++)
    {
        count++;
        DataRow dr = tab_lvl.NewRow();
        dr["NO"] = count;
        dr["NAME"] = ds.Tables[0].Rows[i]["COLname/number"];
        dr["AGE"] = ds.Tables[0].Rows[i]["COLname/number"];
    }

//bind the datatable to the report
objRpt.SetDataSource(tab_lvl);