Thank you guys so much and especially to Jeff for the analysis.

The one with function was accepted by the Dundas and the other one with DECLARE was rejected as DECLARE is not allowed in building Virtual Table in Dundas Dashboard.

And, HUGE THANKS TO YOUR FINDING OF THE TARGETCLOSUREDATE AND FISCAL YEAR

Good thing is the original sp is not in production yet.

Yes I know CTE although I don't use it often, in Dundas I get this error for script:

WITH test (ID, Ref, Title )

AS

-- Define the CTE query.

(

SELECT ID, Ref, Title

FROM Document

WHERE DocStatus = 'Active' and DocType = 1

)

-- Define the outer query referencing the CTE name.

SELECT *

FROM test

Incorrect syntax near the keyword 'WITH'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near ')'.