CODEWITHSUNDEEP
c#.netwinformsreportviewerrdlc
CAD
CAD

Reputation: 4292

Report only shows one record while the DataTable having many records

I'm using ReportViewer in my C# project. I have a problem when displaying a report which shows Employee Turnover Ratios monthly basis.

The problem is even my DataTable populates several records, the report shows only one record.

This is the query behind the report...

//To get all months in the given period (from @startDate to @endDate)
    WITH x AS 
    (
    SELECT CAST(@startDate AS DATE) AS Months
    UNION ALL
    SELECT DATEADD(m, 1, Months) AS Months 
    FROM x 
    WHERE (Months < @endDate)
    )
    , 

//Here I use GETNOOFEMPS() function to get the no of active employees which were in the month
    y AS                        
    (
    SELECT Months, CAST(dbo.GETNOOFEMPS(Months) AS DECIMAL(9, 2)) AS NoEmpsAtBegining, CAST(dbo.ETOR(DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, Months) + 1, 0))) 
    AS DECIMAL(9, 2)) AS NoEmpsAtEnd
    FROM x 
    )

//Calculate the ratio. This returns [Month|Emps at Begining | Emps at End | resigned | Ratio]

SELECT CONVERT(DATE, Months) AS [Month], NoEmpsAtBegining, NoEmpsAtEnd, dbo.GetResignEmployees(Months) AS NoEmpsResigned, 
CAST(dbo.GetResignEmployees(Months) / ((NoEmpsAtBegining + NoEmpsAtEnd) / 2) * 100 AS DECIMAL(9, 2)) AS EmployeeTurnOverRatio
FROM y

Even when I run this in DataAdapters query builder its runs giving the desired out put. But after I use it in RDLC and then viewing the report, it just shows only one record!

How to solve this problem please?

This is how I view the report...

    private void btnShowReport_Click(object sender, EventArgs e)
    {
        var startMonth = dtpStartMonth.Value.Month +"/" + "01"  + "/" + dtpStartMonth.Value.Year;
        var endMonth = dtpEndMonth.Value.Month + "/" + "01" + "/" + dtpEndMonth.Value.Year;
        // TODO: This line of code loads data into the 'DataSetEmployeeTurnover.DataTable1' table. You can move, or remove it, as needed.
        this.DataTable1TableAdapter.Fill(this.DataSetEmployeeTurnover.DataTable1,startMonth,endMonth);

        this.reportViewer1.RefreshReport();
    }

Upvotes: 2

Views: 5820

Answers (3)

Basem S H Saabneh
Basem S H Saabneh

Reputation: 93

you can use Table or List to solve this problem

Upvotes: 0

aseman arabsorkhi
aseman arabsorkhi

Reputation: 362

I had the same problem and it`s solved in this way: in design form on the report.rdlc using report viewer select 'Expression' on the text box and remove the First function

=First(Fields!coln_date.Value) ==>  =Fields!coln_date.Value

Upvotes: 0

Abhishek Priyadarshy
Abhishek Priyadarshy

Reputation: 11

If you want to populate all the dataset record in list then you have to use tablix Control in rdlc report.

Code to bind the RDLC report:

ReportViewer1.ProcessingMode = ProcessingMode.Local 
Dim RDS As New ReportDataSource("DataSet1", atasetName.Tables(0))   
ReportViewer1.LocalReport.DataSources.Clear() 
ReportViewer1.LocalReport.DataSources.Add(RDS)
Me.ReportViewer1.RefreshReport()

Upvotes: 1

Related Questions