This SQL Report exports an Income Statement for the date range given.

None. This is a selection query and no data is modified in the running of it.

DECLARE @DateStart datetime;
DECLARE @DateEnd datetime;
DECLARE @ShowZero bit;
DECLARE @ShowInactive bit;
--SET @DateEnd      = ;
--SET @DateStart    = ;
--SET @ShowZero     = ;
--SET @ShowInActive = ;
SET @DateStart    = '1/1/2009';
SET @DateEnd      = '12/31/2009 23:59';
SET @ShowZero     = 0;
SET @ShowInactive = 0;
DECLARE @Spacing INT;
SET @Spacing = 4;
SELECT *
FROM
(
	SELECT
	  ROW_NUMBER() OVER(ORDER BY FormattedPath) AS ROW,
	  GLTree.FormattedName, 
	  COALESCE((SELECT SUM(Amount) FROM GL WHERE GL.GLAccountID = GLTree.NodeID AND EntryDateTime BETWEEN @DateStart AND @DateEnd), 0) AS Amount,
	  GLTree.NodeName,
	  GLTree.ExportName,
	  GLTree.ExportNumber,
	  GLTree.IsActive,
	  CASE WHEN GLTree.NodeClassTypeID = 8000 THEN 1 ELSE 0 END AS IsCategory,
	  GLTree.NodeID,
	  GLTree.NodeClassTypeID,
	  GLTree.FormattedPath,
	  GLTree.GLClassificationType
	FROM
	(
		SELECT
		  CASE WHEN IsLevel1=1 THEN SPACE(@Spacing * 0)
			ELSE CASE WHEN IsLevel2=1 THEN SPACE(@Spacing * 1)
			ELSE CASE WHEN IsLevel3=1 THEN SPACE(@Spacing * 2)
			ELSE CASE WHEN IsLevel4=1 THEN SPACE(@Spacing * 3)
			ELSE SPACE(@Spacing * 4)
			END END END END +
			COALESCE(Level5.AccountName, Level4.AccountName, Level3.AccountName, Level2.AccountName, Level1.AccountName) AS FormattedName,
		  COALESCE(Level5.AccountName, Level4.AccountName, Level3.AccountName, Level2.AccountName, Level1.AccountName) AS NodeName,
		  COALESCE(Level5.ExportAccountName, Level4.ExportAccountName, Level3.ExportAccountName, Level2.ExportAccountName, Level1.ExportAccountName, '') AS ExportName,
		  COALESCE(Level5.ExportAccountNumber, Level4.ExportAccountNumber, Level3.ExportAccountNumber, Level2.ExportAccountNumber, Level1.ExportAccountNumber, 0) AS ExportNumber,
		  COALESCE(Level5.ID, Level4.ID, Level3.ID, Level2.ID, Level1.ID) AS NodeID,
		  COALESCE(Level5.ClassTypeID, Level4.ClassTypeID, Level3.ClassTypeID, Level2.ClassTypeID, Level1.ClassTypeID) AS NodeClassTypeID,
		  COALESCE(Level5.IsActive, Level4.IsActive, Level3.IsActive, Level2.IsActive, Level1.IsActive) AS IsActive,
		  COALESCE(Level5.AccountGroupID, Level4.AccountGroupID, Level3.AccountGroupID, Level2.AccountGroupID, Level1.AccountGroupID) AS ParentID,
		  (IsLevel4 * 4 + IsLevel3 * 3 + IsLevel2 * 2 + IsLevel1 * 1) + COALESCE(Level5.ID - Level5.ID + 5, 0) AS Depth,
		  COALESCE(Level5.GLClassificationType, Level4.GLClassificationType, Level3.GLClassificationType, Level2.GLClassificationType, Level1.GLClassificationType) AS GLClassificationType,
		  Level1.AccountName AS Level1Name,
		  Level2.AccountName AS Level2Name,
		  Level3.AccountName AS Level3Name,
		  Level4.AccountName AS Level4Name,
		  Level5.AccountName AS Level5Name,
		  Level1.ID AS Level1ID,
		  Level1.AccountGroupID AS Level2ID,
		  Level2.AccountGroupID AS Level3ID,
		  Level3.AccountGroupID AS Level4ID,
		  Level4.AccountGroupID AS Level5ID,
		  IsLevel1, 
		  IsLevel2, 
		  IsLevel3, 
		  IsLevel4, 
		  CASE WHEN IsLevel1 + IsLevel2 + IsLevel3 + IsLevel4 = 0 THEN 1 ELSE 0 END AS IsLevel5, 
		  COALESCE(Level1.AccountName + N'\', '') +
			coalesce(Level2.AccountName + N'\', '') +
			coalesce(Level3.AccountName + N'\', '') +
			coalesce(Level4.AccountName + N'\', '') +
			coalesce(Level5.AccountName + N'\', '') as FormattedPath
		from GLAccount as Level1
		JOIN (SELECT 1 AS IsLevel1 UNION SELECT 0) Temp1 ON 1=1 -- create two rows for each of the above
		left join GLAccount as Level2 on Level1.ID = Level2.AccountGroupID and IsLevel1 = 0
		JOIN (SELECT 1 AS IsLevel2 UNION SELECT 0) Temp2 ON 1=1 -- create two rows for each of the above
		left join GLAccount as Level3 on Level2.ID = Level3.AccountGroupID and IsLevel1 = 0 and IsLevel2 = 0
		JOIN (SELECT 1 AS IsLevel3 UNION SELECT 0) Temp3 ON 1=1 -- create two rows for each of the above
		left join GLAccount as Level4 on Level3.ID = Level4.AccountGroupID and IsLevel1 = 0 and IsLevel2 = 0 and IsLevel3 = 0
		JOIN (SELECT 1 AS IsLevel4 UNION SELECT 0) Temp4 ON 1=1 -- create two rows for each of the above
		left join GLAccount as Level5 on Level4.ID = Level5.AccountGroupID and IsLevel1 = 0 and IsLevel2 = 0 and IsLevel3 = 0 and IsLevel4 = 0
		where (   (IsLevel1=1 and Level2.ID is NULL )
			   or (IsLevel2=1 and Level2.ID is not null and Level3.ID is NULL)
			   or (IsLevel3=1 and Level3.ID is not null and Level4.ID is NULL)
			   or (IsLevel4=1 and Level4.ID is not null and Level5.ID is NULL)
			   or (               Level5.ID is not NULL)
			  ) 
			  and (IsLevel1 + IsLevel2 + IsLevel3 + IsLevel4 = 4000))
 
ORDER By FormattedPath

• Entered : 8/25/2009, Revised 1/12/10
• Version : Control 4.4+