Does Report Services Support Common Table Expression queries?

[Joe_Pineda]

The query below works just fine in MSSQL Studio. But I don't know how to make it "fit" in the Report Services Configuration tool. The tool asks first for  FROM and WHERE clauses, followed by SQL Parameters (i.e., "@TicketDate for a date range) then the Display Columns (the SELECT clause).  Where would I put the cte code into that RS config tool? The problem is that the RS tool doesn't seem to "understand" the ;WITH cte AS... part of the code.

What I am trying to achieve is to  get a report of Type A Tickets showing their Ticket # and Ticket Date when: a) the Type B Ticket with the same Ticket # DOES NOT exist or b) the Type B ticket exists with the same Ticket # but a different Ticket Date than the one found on the Type A ticket.  THE RS config tool doesn't "understand" the ";WITH cte AS (SELECT ...blah blah" portion of the code.

;WITH cte AS (
SELECT
ticketNumber.keyvaluechar AS TicketNum,
ticketDate.keyvaluedate AS TicketDate,
customerNumber.keyvaluechar as CustomerNumber,
itemdata.itemtypenum,
itemdata.itemnum
FROM
itemdata
INNER JOIN
hsi.keyitem110 ticketNumber on ( itemdata.itemnum = ticketNumber.itemnum )
INNER JOIN
hsi.keyitem101 ticketDate on ( itemdata.itemnum = ticketDate.itemnum )
INNER JOIN
hsi.keyitem102 customerNumber on ( hsi.itemdata.itemnum = customerNumber.itemnum )

)
SELECT DISTINCT
TicketNum, TicketDate
FROM
cte
WHERE
ticketDate BETWEEN '01-01-2021' AND '01-31-2021'
AND
(
cte.itemtypenum = 178
) -- this is Doc Type A
AND NOT EXISTS (SELECT cte2.TicketNum
FROM
cte cte2
WHERE cte2.TicketNum = cte.TicketNum --Same Ticket #
AND cte2.TicketDate = cte.TicketDate -- Same dates
AND cte2.itemtypenum = 183 -- this is DOC Type B
)

[Joe_Pineda]

FYI: I ended up configuring a sql stored procedure, and running it from the report services client. It works! But I couldn't get the actual report configurator to work.

[Thomas_Reu]

Joe, I don't know about "with cte as", but I've got this to work doing:

Select *

from

(

 inner sql

) as Derived_tbl

Note: you can do Derived_tbl.* however, it is just the one, so you don't need it.  You would, however, need it if you were doing multiple derived tables.

As for setting up the parameters, change your sql into a view, then from report services do select * from view

Then for setting up parameters check out the canned history report that comes with RS.  It has a bunch of parameter examples.  That said, I have only been successful with 1 where parameter.  To make more parameters work, I have to create a fake inner or left join.  Then put that parameter in with the join.  

Don't forget that to use a view (or possibly any new non-onbase objects), you have to grant select to the new view for the viewer account...  This could be why RS doesn't like your stored procedure...?

Additional safety point: for non-onbase objects, I use a separate db.  This keeps the onbase db pristine and inviolate.  Anything custom, stays outside of onbase...

Regards,...