CODEWITHSUNDEEP
sqlsql-serverssrs-2012sql-server-data-toolssubreport
David
David

Reputation: 593

SSRS Subreport runs multiple times, I only want it running once

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.

Upvotes: 1

Views: 737

Answers (2)

jambonick
jambonick

Reputation: 716

You can use SQL Server Profiler to check the following things.

  1. How many times and with what parameters is the subreport query has ran
  2. How many values your first query returned

Upvotes: 1

Chris Stewart
Chris Stewart

Reputation: 333

I don't think your problem is more about SSRS than it is about your T-SQL Code. I'm going to guess and say that the subreport object is in the report detail section of the report. That means that the subreport is going to render once for every row in the main queries dataset. I don't have any idea what your container report actually looks like but one option you have might be to include the subreport in the header or footer section and have it run off of a MAX(), MIN(), of a value that you know will be the same for every row.

Upvotes: 1

Related Questions