Do I really need subreport links for independent subreports?

Do I really need subreport links for independent subreports?

• Jmp

  I don't think you absolutely have to link the subreports. In my experience, subreports tend to slow things down tremendously. If possible, why not use one stored procedure and then you don't have to set up subreports. Use temp tables and then link those together in your stored procedure. But either way, good luck!
  

• Scott Steigerwald

  Thanks for your reply melody45,

  That's exactly my issue - these 4 subreports are slowing this report down no end, so if linking helps even just a little, I'm all for it!

  Unfortunantly I can't use your workaround as I have multiple one-to-many relationships that need to be separated by blocks of text, thus the necessity for subreports.

  Any other suggestions or help would be appreciated!

  
  

• Coyote13

  You do not need subreport links. I doubt you'd see an appreciable difference either way in terms of performance, but simplest is always best (i.e. don't bother with linking).
  
  
  

• Filiberto Selvas MSFT

  You could set up a stored procedure with temp tables. This would allow you to perform various links and then create an "end result" dataset. Then you could group on various fields in your report. To make it easier, create a View for your first table, then a another View for your second table, etc. It makes it easier to be able to see it rather than to just start typing. Here is an example of using temp tables. It looks nasty but once you start using them it gets a lot easier. Good luck!!

  CREATE PROCEDURE [usp_rptEscrowProjects]
  @OfficeId int
  AS
  SET NOCOUNT ON

  --1st Table
  SELECT SUM(CASE WHEN ft.TransactionTypeID = 1 OR
  ft.TransactionTypeID = 3 THEN ft.Amount WHEN ft.TransactionTypeID = 2 OR ft.TransactionTypeID = 20 THEN - ft.Amount ELSE 0 END) AS CashTtl, COUNT(DISTINCT u.UnitID) AS UnitCountCash, u.ProjectID
  INTO #UnitCash
  FROM dbo.FinancialTransaction ft INNER JOIN
  dbo.Unit u ON ft.UnitID = u.UnitID INNER JOIN
  dbo.ListTransactionType ltt ON ft.TransactionTypeID = ltt.TransactionTypeID INNER JOIN
  dbo.FinancialAccount fa ON ft.FinancialAccountID = fa.FinancialAccountID
  WHERE (ltt.AccountTypeID = 1) AND (fa.AccountStatusID = 1) AND (fa.ParentID IS NULL)
  GROUP BY u.ProjectID
  HAVING (SUM(CASE WHEN ft.TransactionTypeID = 1 OR
  ft.TransactionTypeID = 3 THEN ft.Amount WHEN ft.TransactionTypeID = 2 OR
  ft.TransactionTypeID = 20 THEN - ft.Amount ELSE 0 END) <> 0)

  --2nd Table

  SELECT SUM(CASE WHEN ft.TransactionTypeID = 5 THEN ft.Amount WHEN ft.TransactionTypeID = 6 OR
  ft.TransactionTypeID = 7 OR
  ft.TransactionTypeID = 8 THEN - ft.Amount ELSE 0 END) AS SecTtl, COUNT(DISTINCT u.UnitID) AS UnitCountSec, fa.ProjectID, fa.FinancialAccountID,
  fa.SECTypeID, fa.AccountNumber, fa.StatementName, fa.ParentID
  INTO #UnitSec
  FROM dbo.FinancialTransaction ft INNER JOIN
  dbo.Unit u ON ft.UnitID = u.UnitID INNER JOIN
  dbo.ListTransactionType ltt ON ft.TransactionTypeID = ltt.TransactionTypeID INNER JOIN
  dbo.FinancialAccount fa ON ft.FinancialAccountID = fa.FinancialAccountID
  WHERE (ltt.AccountTypeID = 2) AND (fa.AccountStatusID = 1)

  GROUP BY u.ProjectID, fa.ProjectID, fa.FinancialAccountID, fa.SECTypeID, fa.AccountNumber, fa.StatementName, fa.ParentID
  HAVING (SUM(CASE WHEN ft.TransactionTypeID = 5 THEN ft.Amount WHEN ft.TransactionTypeID = 6 OR
  ft.TransactionTypeID = 7 OR
  ft.TransactionTypeID = 8 THEN - ft.Amount ELSE 0 END) <> 0) AND (fa.SECTypeID = 4)

  --final table

  SELECT d.DeveloperDescription, p.ProjectID,p.ProjectDescription, p.FileNumber, p.PricePerUnit, p.Agent, uc.UnitCountCash, us.UnitCountSec, c.Address1, c.Address2,
  c.City, c.State, c.Zip, dbo.[User].DisplayName, us.SECTypeID, us.FinancialAccountID, us.AccountNumber, us.StatementName, p.NumberOfUnits,
  CASE WHEN ProjectStatusID = 1 THEN 'A' WHEN ProjectStatusId = 2 THEN 'I' ELSE 'C' END AS AIC, us.ParentID,d.SystemFlag
  FROM dbo.Developer d INNER JOIN
  dbo.Project p ON d.DeveloperID = p.DeveloperID INNER JOIN
  dbo.ListCostCenter lcc ON p.CostCenterID = lcc.CostCenterID INNER JOIN
  dbo.XrefOfficeCostCenter x ON lcc.CostCenterID = x.CostCenterId LEFT OUTER JOIN
  dbo.[User] ON p.EmployeeUserID = dbo.[User].UserID LEFT OUTER JOIN
  dbo.Contact c ON p.LocationContactID = c.ContactID LEFT OUTER JOIN
  #UnitSec us ON p.ProjectID = us.ProjectID LEFT OUTER JOIN
  #UnitCash uc ON p.ProjectID = uc.ProjectID
  WHERE (us.SECTypeID = 4) AND (us.ParentID IS NULL) AND x.OfficeID=@OfficeID
  GO
  

  
  
• Do I really need subreport links for independent subreports?