01-20-2021 09:47 AM
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
)
01-21-2021 12:46 PM
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.
01-22-2021 03:24 PM
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,...
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.