sql2005/BIDS: getting error 102 with CTE in dynamic query expression

[emccormick]

Hi,

Using BIDS / Report Designer with SSRS / SQL2005(SP2) and for one dataset, I'm trying to make a CTE into a 'dynamic' query by wrapping the accessor query in an Iif() function. Here it is:

= "with fpy_insp1 as " &
"(  " &
"select count(distinct(alias)) as fmacount " &
"from e_cs_mfdunit_history " &
"where (product_id like '103%' or product_id like '106%' or product_id like '108%') " &
"and updated_date >= @start_date and updated_date < DateAdd("Day",1,@end_date) " &
"and (current_step in ( 'ARCVAULT.2U.600.PREINSPECTION.PREP','ARCVAULT.2U.600.INTERNAL.INSPECTION') " &
"or current_step in ('ARCVAULT.2U.EXTERNAL.INSPECTION'))  " &
"and substring(alias,1,2) = '2B' " &
"and (exception_step like 'FMA.REWORK' or exception_step like 'FMA.PROCESS') " &
"), " &

"fpy_insp2 as ( " &
"select count(distinct(alias)) as allcount " &
"from e_cs_mfdunit_history " &
"where (product_id like '103%' or product_id like '106%' or product_id like '108%') " &
"and updated_date >= @start_date and updated_date < DateAdd("Day",1,@end_date) " &
"and (current_step in ( 'ARCVAULT.2U.600.PREINSPECTION.PREP','ARCVAULT.2U.600.INTERNAL.INSPECTION') " &
"or current_step in ('ARCVAULT.2U.EXTERNAL.INSPECTION'))  " &
"and substring(alias,1,2) = '2B' " &
"and exception_step is null " &
") "  &

Iif(allcount = 0, "select 0", "select (1 - (select cast(fmacount as decimal) from fpy_insp1) " &  "/ (select cast(allcount as decimal) from fpy_insp2)) * 100  as fpy_insp)"

Here are the error details:

Incorrect syntax near '='. (Microsoft Report Designer)
Incorrect syntax near '='. (.Net SqlClient Data Provider)

Server Name: (local)
Error Number: 102
Severity: 15
State: 1
Line Number: 1

Never had trouble doing this on a simple select query before. Anyone know if this is specific to using CTEs?

Thanks!
e