Sum(Case When) Question
I think you do not need the last column in your GROUP BY clause:

SELECT     Household.Name, FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode, 
           SUM(Asset.Value) AS HHValue,
           SUM(CASE WHEN financialinst.name = 'National Financial Services' THEN Asset.Value ELSE 0 END) AS NationalValue, 
           SUM(CASE WHEN financialinst.name &lt;&gt; 'National Financial Services' THEN Asset.Value ELSE 0 END) AS DirectValue
FROM       PlanFirmSpecCode INNER JOIN
           FinPlanSpecCode ON PlanFirmSpecCode.PlanFirmSpecCodeID = FinPlanSpecCode.PlanFirmSpecCodeID RIGHT OUTER JOIN
           Household INNER JOIN
           Account ON Household.HouseholdID = Account.HouseholdID INNER JOIN
           Asset ON Account.AccountID = Asset.AccountID INNER JOIN
           FinancialPlanner ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID LEFT OUTER JOIN
           FinancialInst ON Account.FinancialInstID = FinancialInst.FinancialInstID ON FinPlanSpecCode.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
WHERE      (Account.Closed &lt;&gt; 'Y') AND (Asset.Value &gt; '0') and (Account.Managed &lt;&gt; 'N')
GROUP BY   Household.Name, FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode
Order by   household.Name