SSRS子报表运行多次，我只希望它运行一次(SSRS Subreport runs multiple times, I only want it running once)

我有一个报表，它有一个钻取子报表，当它与多个与子报表无关的项目有多个关系时，会多次运行。

主报告查询

SELECT DISTINCT cat.CategoryName AS 'Category Name', sub.SubCategoryName AS 'SubCategory Name', cur.Status, cur.PastConsiderationFlag, cur.Model, cur.Version, cur.Vendor, cur.AvailableDate AS 'Available Date', cur.EndOfProduction AS 'End of Production', cur.EndOfSupport AS 'End of Support', dep.DepartmentName AS 'Department Name', emp.FirstName + ' ' + emp.LastName AS 'Tech Owner', emp2.FirstName + ' ' + emp2.LastName AS 'Tech Contact', cur.NumOfDevices AS '# of Devices', cur.UpgradeDuration AS 'Upgrade Duration', cur.FiscalConsideration AS 'Fiscal Consideration', cur.Description, cur.SupportingComments, cur.CurrencyId, STUFF ((SELECT ', ' + pl.PlatformName AS Expr1 FROM Platform AS pl LEFT OUTER JOIN Currency_Platform AS cp ON cur.CurrencyId = cp.CurrencyId WHERE (pl.PlatformId = cp.PlatformId) FOR XML PATH('')), 1, 1, '') AS 'Platforms', ISNULL(STUFF ((SELECT ', ' + cu2.Model AS Expr1 FROM Currency AS cu2 RIGHT OUTER JOIN Currency_Dependency AS cd ON cur.CurrencyId = cd.CurrencyId WHERE (cu2.CurrencyId = cd.DependencyId) FOR XML PATH('')), 1, 1, ''), 'N/A') AS 'Dependencies', ISNULL(STUFF ((SELECT ', ' + cu2.Model AS Expr1 FROM Currency AS cu2 RIGHT OUTER JOIN Currency_Affected AS ca ON cur.CurrencyId = ca.CurrencyId WHERE (cu2.CurrencyId = ca.AffectedId) FOR XML PATH('')), 1, 1, ''), 'N/A') AS 'Affected Apps', Currency_Platform.PlatformId FROM Currency AS cur INNER JOIN SubCategory AS sub ON cur.SubCategoryId = sub.SubCategoryId INNER JOIN Category AS cat ON sub.CategoryId = cat.CategoryId LEFT OUTER JOIN Employee AS emp ON cur.OwnerId = emp.EmployeeId LEFT OUTER JOIN Employee AS emp2 ON cur.ContactId = emp2.EmployeeId LEFT OUTER JOIN Department AS dep ON cur.PortfolioOwnerId = dep.DepartmentId LEFT OUTER JOIN Currency_Platform ON cur.CurrencyId = Currency_Platform.CurrencyId

即使它是一个独特的选择，子报表也将运行等于它所属的平台数量。我将在此处包含子报表的查询。

;with cte as ( -- anchor elements: where curr.Status = 1 and not a dependent select CurrencyId , Model , Version , ParentId = null , ParentModel = convert(varchar(128),'') , Root = curr.Model , [Level] = convert(int,0) , [ParentPath] = convert(varchar(512),Model + Version) from dbo.Currency as curr where curr.Status = 1 /* anchor's do not depend on any other currency */ and not exists ( select 1 from dbo.Currency_Dependency i where curr.CurrencyId = i.DependencyId ) -- recursion begins here union all select CurrencyId = c.CurrencyId , Model = c.Model , Version = c.Version , ParentId = p.CurrencyId , ParentModel = convert(varchar(128),p.Model + p.Version) , Root = p.Root , [Level] = p.[Level] + 1 , [ParentPath] = convert(varchar(512),p.[ParentPath] + ' > ' + c.Model + ' ' + c.Version) from dbo.Currency as c inner join dbo.Currency_Dependency as dep on c.CurrencyId = dep.DependencyId inner join cte as p on dep.CurrencyId = p.CurrencyId ) select CurrencyId, ParentPath, Model + ' ' + Version AS 'Model' from cte WHERE CurrencyId = @CurrencyId

当我单独运行子报表时，一切都很好。当我通过主报表打开子报表时，将CurrencyId作为参数传递，它的次数与它所属的平台数量相同。

有没有办法可以通过改进查询来纠正这个问题，或者正如我所希望的那样，迫使子报表只运行一次，无论如何？

非常感谢你看一看。

I have a report that has a drillthrough subreport that runs multiple times when it has more than one relationship to a many to many item that has nothing to do with the subreport.

Main report query

SELECT DISTINCT cat.CategoryName AS 'Category Name', sub.SubCategoryName AS 'SubCategory Name', cur.Status, cur.PastConsiderationFlag, cur.Model, cur.Version, cur.Vendor, cur.AvailableDate AS 'Available Date', cur.EndOfProduction AS 'End of Production', cur.EndOfSupport AS 'End of Support', dep.DepartmentName AS 'Department Name', emp.FirstName + ' ' + emp.LastName AS 'Tech Owner', emp2.FirstName + ' ' + emp2.LastName AS 'Tech Contact', cur.NumOfDevices AS '# of Devices', cur.UpgradeDuration AS 'Upgrade Duration', cur.FiscalConsideration AS 'Fiscal Consideration', cur.Description, cur.SupportingComments, cur.CurrencyId, STUFF ((SELECT ', ' + pl.PlatformName AS Expr1 FROM Platform AS pl LEFT OUTER JOIN Currency_Platform AS cp ON cur.CurrencyId = cp.CurrencyId WHERE (pl.PlatformId = cp.PlatformId) FOR XML PATH('')), 1, 1, '') AS 'Platforms', ISNULL(STUFF ((SELECT ', ' + cu2.Model AS Expr1 FROM Currency AS cu2 RIGHT OUTER JOIN Currency_Dependency AS cd ON cur.CurrencyId = cd.CurrencyId WHERE (cu2.CurrencyId = cd.DependencyId) FOR XML PATH('')), 1, 1, ''), 'N/A') AS 'Dependencies', ISNULL(STUFF ((SELECT ', ' + cu2.Model AS Expr1 FROM Currency AS cu2 RIGHT OUTER JOIN Currency_Affected AS ca ON cur.CurrencyId = ca.CurrencyId WHERE (cu2.CurrencyId = ca.AffectedId) FOR XML PATH('')), 1, 1, ''), 'N/A') AS 'Affected Apps', Currency_Platform.PlatformId FROM Currency AS cur INNER JOIN SubCategory AS sub ON cur.SubCategoryId = sub.SubCategoryId INNER JOIN Category AS cat ON sub.CategoryId = cat.CategoryId LEFT OUTER JOIN Employee AS emp ON cur.OwnerId = emp.EmployeeId LEFT OUTER JOIN Employee AS emp2 ON cur.ContactId = emp2.EmployeeId LEFT OUTER JOIN Department AS dep ON cur.PortfolioOwnerId = dep.DepartmentId LEFT OUTER JOIN Currency_Platform ON cur.CurrencyId = Currency_Platform.CurrencyId

Even though it's a distinct select, the subreport will run equal to the amount of Platforms it belongs to. I'll include the Query for the subreport here.

;with cte as ( -- anchor elements: where curr.Status = 1 and not a dependent select CurrencyId , Model , Version , ParentId = null , ParentModel = convert(varchar(128),'') , Root = curr.Model , [Level] = convert(int,0) , [ParentPath] = convert(varchar(512),Model + Version) from dbo.Currency as curr where curr.Status = 1 /* anchor's do not depend on any other currency */ and not exists ( select 1 from dbo.Currency_Dependency i where curr.CurrencyId = i.DependencyId ) -- recursion begins here union all select CurrencyId = c.CurrencyId , Model = c.Model , Version = c.Version , ParentId = p.CurrencyId , ParentModel = convert(varchar(128),p.Model + p.Version) , Root = p.Root , [Level] = p.[Level] + 1 , [ParentPath] = convert(varchar(512),p.[ParentPath] + ' > ' + c.Model + ' ' + c.Version) from dbo.Currency as c inner join dbo.Currency_Dependency as dep on c.CurrencyId = dep.DependencyId inner join cte as p on dep.CurrencyId = p.CurrencyId ) select CurrencyId, ParentPath, Model + ' ' + Version AS 'Model' from cte WHERE CurrencyId = @CurrencyId

When I run the subreport individually, everything is fine. When I open the subreport through the main report passing the CurrencyId as a parameter, it does so as many times as the amount of platforms it belongs to.

Is there a way I can correct this either by improving the queries, or as I would prefer, force the subreport to only run once no matter what?

Thanks so much for having a look.