Dynamic builds a select statement on top of whatever is the data source -- treating it just as any other table or view, and appending clauses for where, grouping and ordering as needed according to report design, drill-down path and runtime context. You would get the same errors if you tried to do the same query on top of your CTE in SSMS.

Just make it a back-end view -- or if passed in parameters are required, then make it a table-valued function. Then it should work in DAI just like any other "table".

Bob