Subreport Duplicated Multiple Times

[Eric Cockrell]

I recently downloaded and installed the Telerik Reporting solution for a large project I am creating. I have created a report with two subreports. My datasource for the master report is a business object (WorkOrder). The datasource for the two subreports is a SqlDataSource. An abbreviated version of the business objects are shown below.

[Serializable]
public class WorkOrder
{
    public long WorkOrderId { get; set; }
    public virtual ICollection<WorkOrderItem> WorkOrderItems { get; set; }
}

[Serializable]
public class WorkOrderItem
{
    public long WorkOrderItemId { get; set; }
    public long WorkOrderId { get; set; }
    public int Type { get; set; }
}

Each subreport selects from the table WorkOrderItems. The subreport that does not repeat records has a select statement of

SELECT * FROM WorkOrderItems
WHERE (Type = 2)
ORDER BY StartDate

The subreport that repeats has a select statement of

SELECT * FROM WorkOrderItems
WHERE (Type = 1 OR Type = 3) AND (Quantity > 0)
ORDER BY Type DESC

The subreport contains a table that lists all WorkOrderItems. If there are 5 items in the query, the subreport is displayed 5 times.  If there are 2 items, the subreport is displayed 2 times. I can tell the subreport is being called multiple times because I get the complete header. Also, I get an extra record in the table that contains no data.

This is a huge project and I can post some code if needed, but it would take quite a bit of work to pare it down. And then who knows if the error will disappear or not.

This is all being used in an MVC project to show the report as a PDF to the user.  Here is the code I am using to generate the report.

WorkOrder workOrder = Repository.WorkOrderByKey(id);
ReportWorkOrder report = new ReportWorkOrder();
report.DataSource = workOrder;

ReportProcessor reportProcessor = new ReportProcessor();
RenderingResult result = reportProcessor.RenderReport("PDF", report, null);
return File(result.DocumentBytes, "application/pdf", "WorkOrder.pdf");

Any help would be greatly appreciated.

Thanks

[Hadib Ahmabi]

The subreport that repeats - it sound like you assign the data-source to it and to the table inside of it at the same time. If that is the case - normally the subReport will repeat for each row in its data-source. Remove the DataSource from the Report and leave it only to the table. 

[Eric Cockrell]

Thanks. That was the problem.  As soon as  I removed the data source from the subreport, it started showing just once.

Thanks

[Tim]

I have a report that querys 5 data tables with unions and performs counts for each listkey. I runs fine in the Report designer "Execute Query". But when I run it in the report viewer if it has 23 rows it returns the report 23 times. Not sure from the above post how the repair?  Please

SELECT
    IPTReferralFromDropDown.ListValue,
        1 AS orderKey,
    COUNT(IPTAllStatsMain.ReferralFromKey) AS ReferralFrom
FROM
    IPTReferralFromDropDown
    INNER JOIN IPTAllStatsMain
ON (IPTAllStatsMain.ReferralFromKey = IPTReferralFromDropDown.ListKey)
AND
    year(IPTAllStatsMain.CreatedOn) = year(getdate())
    --IPTAllStatsMain.CreatedOn BETWEEN @startDate AND @endDate
GROUP BY
IPTReferralFromDropDown.ListValue

UNION

--Caller Relation
SELECT
    IPTCallerRelationDropDown.ListValue,
        2 AS orderKey,
    COUNT(IPTAllStatsMain.CallerRelationKey) AS CallerRelation
FROM
    IPTCallerRelationDropDown
    INNER JOIN IPTAllStatsMain
ON (IPTAllStatsMain.CallerRelationKey = IPTCallerRelationDropDown.ListKey)
AND
    year(IPTAllStatsMain.CreatedOn) = year(getdate())
    --IPTAllStatsMain.CreatedOn BETWEEN @startDate AND @endDate
GROUP BY
    IPTCallerRelationDropDown.ListValue

UNION

--Marital Status
SELECT
    IPTMaritalStatusDropDown.ListValue,
        3 AS orderKey,
    COUNT(IPTAllStatsMain.MaritalStatusKey) AS MaritalStatus
FROM
    IPTMaritalStatusDropDown
    INNER JOIN IPTAllStatsMain
ON (IPTAllStatsMain.MaritalStatusKey = IPTMaritalStatusDropDown.ListKey)
AND
    year(IPTAllStatsMain.CreatedOn) = year(getdate())
    --IPTAllStatsMain.CreatedOn BETWEEN @startDate  AND @endDate
GROUP BY
    IPTMaritalStatusDropDown.ListValue

UNION

--Ethnicity
SELECT
    IPTEthnicityDropDown.ListValue,
        4 AS orderKey,
    COUNT(IPTAllStatsMain.EthnicityKey) AS Ethnicity
FROM
    IPTEthnicityDropDown
    INNER JOIN IPTAllStatsMain
ON (IPTAllStatsMain.EthnicityKey = IPTEthnicityDropDown.ListKey)
AND
    year(IPTAllStatsMain.CreatedOn) = year(getdate())
    --IPTAllStatsMain.CreatedOn BETWEEN @startDate  AND @endDate
GROUP BY
    IPTEthnicityDropDown.ListValue

UNION

--Annual Income
SELECT
    IPTAnnualIncomeDropDown.ListValue,
        5 AS orderKey,
    COUNT(IPTAllStatsMain.AnnualIncomeKey) AS AnnualIncome
FROM
    IPTAnnualIncomeDropDown
    INNER JOIN IPTAllStatsMain
ON (IPTAllStatsMain.AnnualIncomeKey = IPTAnnualIncomeDropDown.ListKey)
AND
    year(IPTAllStatsMain.CreatedOn) = year(getdate())
    --IPTAllStatsMain.CreatedOn BETWEEN @startDate  AND @endDate
GROUP BY
    IPTAnnualIncomeDropDown.ListValue
ORDER BY
    orderKey

[Stef]

Hello Tim,

Most probably the report's DataSource is set to the data source component used for another data item, and thus items in the report's Detail section get repeated multiple times.
For more details, please check my reply in your support ticket on the same question.

Regards,
Stef
Telerik