I have a report which I would like to create prompts for both VENUE and DATE RANGE, and after having these prompts bring back the incorrect data, I have deleted both and tried to start again from the beginning.
To begin with, I have a Crystal Report based upon the below SQL:
To summarise the code - it uses a common table expression broken separating the query out into revenue, space and event, finally joining this together at the end.
I had to convert the data type of the 'booking date' to DATE, because the value is stored as 2018-01-01 12:00:00:00.
WITH Revenue as
(
SELECT EV200_EVENT_MASTER.EV200_EVT_ID as [Event],
Revenue =
SUM(Case
When Orddtl.ER101_PHASE = '1' and ORDDTL.ER101_COMPL_STS = 'N'
then ORDDTL.ER101_EXT_CHRG
When ORDDTL.ER101_PHASE = '5' and ORDDTL.ER101_COMPL_STS = 'N'
then ORDDTL.ER101_EXT_CHRG
Else 0
End),
SM.EV800_SPACE_DESC,
SM.EV800_SPACE_CODE
FROM EV200_EVENT_MASTER WITH (NOLOCK)
LEFT OUTER JOIN EV870_ACCT_MASTER PrimCoord WITH (NOLOCK)
ON EV200_EVENT_MASTER.EV200_ORG_CODE = PrimCoord.EV870_ORG_CODE
AND EV200_EVENT_MASTER.EV200_COORD_1 = PrimCoord.EV870_ACCT_CODE -- Event manager
LEFT OUTER JOIN EV870_ACCT_MASTER FloorMgr WITH (NOLOCK)
ON EV200_EVENT_MASTER.EV200_ORG_CODE = FloorMgr.EV870_ORG_CODE
AND EV200_EVENT_MASTER.EV200_COORD_2 = FloorMgr.EV870_ACCT_CODE -- Floor manager
LEFT OUTER JOIN EV870_ACCT_MASTER EventAccount WITH (NOLOCK)
ON EV200_EVENT_MASTER.EV200_ORG_CODE = EventAccount.EV870_ORG_CODE
AND EV200_EVENT_MASTER.EV200_CUST_NBR = EventAccount.EV870_ACCT_CODE -- The person who made the booking
LEFT OUTER JOIN EV215_EVT_TYPE WITH (NOLOCK) -- get the event type
ON EV200_EVENT_MASTER.EV200_ORG_CODE = EV215_EVT_TYPE.EV215_ORG_CODE
AND EV200_EVENT_MASTER.EV200_EVT_TYPE = EV215_EVT_TYPE.EV215_EVT_TYPE
LEFT OUTER JOIN EV130_STATUS_MASTER WITH (NOLOCK) -- this might not be necessary. Only if we want to show status = completed, etc
ON EV200_EVENT_MASTER.EV200_EVT_STATUS = EV130_STATUS_MASTER.EV130_STATUS_CODE
LEFT OUTER JOIN EV802_SPACE_BKD SPBK
ON SPBK.EV802_ORG_CODE = EV200_ORG_CODE AND SPBK.EV802_EVT_ID = EV200_EVT_ID
LEFT OUTER JOIN ER101_ACCT_ORDER_DTL ORDDTL
ON ORDDTL.ER101_ORG_CODE = EV200_EVENT_MASTER.EV200_ORG_CODE
AND ORDDTL.ER101_EVT_ID = EV200_EVENT_MASTER.EV200_EVT_ID
LEFT OUTER JOIN EV800_SPACE_MASTER SM
ON Sm.EV800_ORG_CODE = EV200_EVENT_MASTER.EV200_ORG_CODE
AND SM.EV800_SPACE_CODE = SPBK.EV802_BKD_SPACE
WHERE EV200_EVENT_MASTER.EV200_ORG_CODE = '10'
AND EV200_EVENT_MASTER.EV200_EVT_STATUS >= 30 /* only confirmed bookings */
AND EV200_EVENT_MASTER.EV200_EVT_STATUS <= 52
AND Not(EV200_EVENT_MASTER.EV200_EVT_TYPE = 'GB') -- exclude group bookings
GROUP BY EV200_EVENT_MASTER.EV200_EVT_ID,SM.EV800_SPACE_DESC,EV800_SPACE_CODE
), eventdetails as
(
SELECT EV200_EVENT_MASTER.EV200_EVT_ID as [Event],
EV215_EVT_TYP_DESC as [Event Type],
EV200_event_master.EV200_EVT_DESC,
EV200_EVENT_MASTER.EV200_PLN_ATTEND,
--EventAccount.EV870_NAME AS [Account],
PrimCoord.EV870_FIRST_NAME + ' ' + PrimCoord.EV870_LAST_NAME AS [Event Manager],
--FloorMgr.EV870_FIRST_NAME + ' ' + FloorMgr.EV870_LAST_NAME AS [Floor Manager]
EV130_STATUS_MASTER.EV130_STATUS_DESC AS 'Status'
FROM EV200_EVENT_MASTER WITH (NOLOCK)
LEFT OUTER JOIN EV870_ACCT_MASTER PrimCoord WITH (NOLOCK)
ON EV200_EVENT_MASTER.EV200_ORG_CODE = PrimCoord.EV870_ORG_CODE
AND EV200_EVENT_MASTER.EV200_COORD_1 = PrimCoord.EV870_ACCT_CODE -- Event manager
LEFT OUTER JOIN EV870_ACCT_MASTER FloorMgr WITH (NOLOCK)
ON EV200_EVENT_MASTER.EV200_ORG_CODE = FloorMgr.EV870_ORG_CODE
AND EV200_EVENT_MASTER.EV200_COORD_2 = FloorMgr.EV870_ACCT_CODE -- Floor manager
LEFT OUTER JOIN EV870_ACCT_MASTER EventAccount WITH (NOLOCK)
ON EV200_EVENT_MASTER.EV200_ORG_CODE = EventAccount.EV870_ORG_CODE
AND EV200_EVENT_MASTER.EV200_CUST_NBR = EventAccount.EV870_ACCT_CODE -- The person who made the booking
LEFT OUTER JOIN EV215_EVT_TYPE WITH (NOLOCK) -- get the event type
ON EV200_EVENT_MASTER.EV200_ORG_CODE = EV215_EVT_TYPE.EV215_ORG_CODE
AND EV200_EVENT_MASTER.EV200_EVT_TYPE = EV215_EVT_TYPE.EV215_EVT_TYPE
LEFT OUTER JOIN EV130_STATUS_MASTER WITH (NOLOCK) -- this might not be necessary. Only if we want to show status = completed, etc
ON EV200_EVENT_MASTER.EV200_EVT_STATUS = EV130_STATUS_MASTER.EV130_STATUS_CODE
WHERE EV200_EVENT_MASTER.EV200_ORG_CODE = '10'
AND EV200_EVENT_MASTER.EV200_EVT_STATUS >= 30 /* only confirmed bookings */
AND EV200_EVENT_MASTER.EV200_EVT_STATUS <= 52
AND Not(EV200_EVENT_MASTER.EV200_EVT_TYPE = 'GB') -- exclude group bookings
), spacedetails as
(
SELECT distinct
SPDTL.EV803_BKD_SPACE,
SPDTL.EV803_EVT_ID,
SM.EV800_SPACE_DESC,
CONVERT(DATE,SPDTL.EV803_BKG_DATE) as bkg_date,
spdtl.EV803_START_TIME,
spdtl.EV803_END_TIME,
SPDTL.EV803_BKG_START_TIME,
SPDTL.EV803_BKG_END_TIME,
SPDTL.EV803_USAGE,
EV800_NOTE_1 AS [VENUE]
FROM EV803_SPACE_BKD_DTL SPDTL
INNER JOIN EV800_SPACE_MASTER SM ON SPDTL.EV803_BKD_SPACE = SM.EV800_SPACE_CODE
)
select distinct eventdetails.Event,
[Event Type],
eventdetails.EV200_EVT_DESC as 'Description',
eventdetails.EV200_PLN_ATTEND as 'PAX',
eventdetails.[Event Manager],
ss.EV800_SPACE_DESC as 'Space',
ss.bkg_date as 'Booking Date',
ss.VENUE,
eventdetails.Status,
/*spacedetails.
(CASE
WHEN spacedetails.EV803_USAGE = 'IN' THEN ''
WHEN spacedetails.EV803_USAGE = 'ET' THEN 'EVENT'
WHEN spacedetails.EV803_USAGE = 'OUT' THEN 'BUMP OUT'
END) as 'Booking Type',
*/
/*this doesnt work because it will be evaluated one row at a time, and one row will not satisfy in,et,out
CAST((CASE WHEN spacedetails.ev803_usage = 'IN' THEN spacedetails.EV803_BKG_START_TIME END) as time(0)) as 'Bump in Start Time',
CAST((CASE WHEN spacedetails.ev803_usage = 'IN' THEN spacedetails.EV803_BKG_END_TIME END) as time(0)) as 'Bump in End Time',
CAST((CASE WHEN spacedetails.ev803_usage = 'ET' THEN spacedetails.EV803_BKG_START_TIME END) as time(0)) as 'Event Start Time',
CAST((CASE WHEN spacedetails.ev803_usage = 'ET' THEN spacedetails.EV803_BKG_END_TIME END) as time(0)) as 'Event End Time',
CAST((CASE WHEN spacedetails.ev803_usage = 'OUT' THEN spacedetails.EV803_BKG_START_TIME END) as time(0)) as 'Bump Out Start Time',
CAST((CASE WHEN spacedetails.ev803_usage = 'OUT' THEN spacedetails.EV803_BKG_END_TIME END) as time(0)) as 'Bump Out End Time',
*/
(
SELECT TOP(1) CAST(s.EV803_BKG_START_TIME AS time(0))
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'IN'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Bump-in Start Time',
(
SELECT TOP(1) CAST(s.EV803_BKG_END_TIME AS time(0))
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'IN'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Bump-in End Time',
(
SELECT TOP(1) CAST(s.EV803_BKG_START_TIME AS time(0))
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'ET'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Event Start Time',
(
SELECT TOP(1) CAST(s.EV803_BKG_END_TIME AS time(0))
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'ET'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Event End Time',
(
SELECT TOP(1) CAST(s.EV803_BKG_START_TIME AS time(0))
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'OUT'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Bump-out Start Time',
(
SELECT TOP(1) CAST(s.EV803_BKG_END_TIME AS time(0))
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'OUT'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Bump-out End Time',
revenue.revenue
from eventdetails
inner join spacedetails ss on eventdetails.Event = ss.EV803_EVT_ID
inner join Revenue on ss.EV803_EVT_ID = revenue.Event and ss.EV803_BKD_SPACE = Revenue.EV800_SPACE_CODE
I created a Dynamic Parameter on the 'Booking date' setting allow for 'range values',
but when i try to run the report (it is deployed in Ungerboeck Event Management) I only get the prompt for a non range value.
