With these type of query you can sometimes get a lot of success from breaking it up into several queries inserting the data into temporary tables, adding indexes to the temporary tables and then querying the temporary tables to get the results. You could start off by inserting the results of he function into a temporary table:
IF OBJECT_ID('temdb..#ReportDealCore','U') IS NOT NULL
DROP TABLE #ReportDealCoreSELECT FDC.DealID,
FDC.StageID,
FDC.PlatformID
INTO #ReportDealCore
FROM report.fnReportDealCore(@StartDate, @EndDate, @Type) AS FDC
Then query the able #ReportDealCore instead of calling the function in the query.
Then just select several tables from the query join them together and insert the results into another temporary table, then build up the whole query from joins to the temporary tables,
Preach it, Brother! 😉 A whole lot of people don't understand that "set based" doesn't actually mean "all in one query" especially when a DISTICT has to be used to overcome the accidental many-to-many joins formed by such monsters.