Below is the SQl I am using - How do i use restrictions in the main and sub query
SELECT
OMG_Outage_Report_APP.DataCenter,
OMG_Outage_Report_APP.AppName,
Format(OMG_Outage_Report_APP.OutageDate,'yyyy') as [Year],
Month(OMG_Outage_Report_APP.OutageDate)as [Month],
OMG_Outage_Report_APP.TestDomain,
SUM(Distinct OMG_Outage_Report_APP.OutageDurationInMins) AS [Sum_OutageDurationInMins]
FROM
OMG_Outage_Report_APP with (nolock)
WHERE
OMG_Outage_Report_APP.DataCenter = 'Staging'
and OMG_Outage_Report_APP.AppName = 'EN'
and Convert(VARCHAR(10),OMG_Outage_Report_APP.OutageDate,111) not in (Select CONVERT(VARCHAR(10), StartTime , 111) from OMG_DCMaintenance_Data where DataCenter = 'staging')
and Convert(VARCHAR(10),OMG_Outage_Report_APP.OutageDate,111) not in (Select CONVERT(VARCHAR(10), EndTime , 111) from OMG_DCMaintenance_Data where DataCenter = 'staging')
GROUP BY
OMG_Outage_Report_APP.DataCenter,
OMG_Outage_Report_APP.AppName,
Format(OMG_Outage_Report_APP.OutageDate,'yyyy'),
Month(OMG_Outage_Report_APP.OutageDate),
OMG_Outage_Report_APP.TestDomain

Please help

Thank you.
I tried the report input values and tasks and the output was not quiet right

So, I have actually tried by dropping restrictions in the Restrictions Text as explained in the documentation and substituting those restrictions in my SQL statement.
This works, but somehow the restriction used in sub query does not seem to honor the value as I see that the output numbers are not right.
but if I substitute the actual value, it gets it right

My restriction names are:
Data Center and App Name
Both are enumerated lists

Then in my SQL i have substituted the restriction names as follows

The highlighted restrictions in the Where clause works, but the highlighted and underlined restrictions in Subquery does nto seem to work - Any suggestions??

SELECT
OMG_Outage_Report_APP.DataCenter,
OMG_Outage_Report_APP.AppName,
Format(OMG_Outage_Report_APP.OutageDate,'yyyy') as [Year],
Month(OMG_Outage_Report_APP.OutageDate)as [Month],
OMG_Outage_Report_APP.TestDomain,
SUM(Distinct OMG_Outage_Report_APP.OutageDurationInMins) AS [Sum_OutageDurationInMins]
FROM
OMG_Outage_Report_APP with (nolock)
WHERE
OMG_Outage_Report_APP.DataCenter = [DataCenter]
and OMG_Outage_Report_APP.AppName = [AppName]
and Convert(VARCHAR(10),OMG_Outage_Report_APP.OutageDate,111) not in (Select CONVERT(VARCHAR(10), StartTime , 111) from OMG_DCMaintenance_Data with(nolock) where OMG_DCMaintenance_Data.DataCenter = [DataCenter])
and Convert(VARCHAR(10),OMG_Outage_Report_APP.OutageDate,111) not in (Select CONVERT(VARCHAR(10), EndTime , 111) from OMG_DCMaintenance_Data with(nolock) where OMG_DCMaintenance_Data.DataCenter = [DataCenter])
GROUP BY
OMG_Outage_Report_APP.DataCenter,
OMG_Outage_Report_APP.AppName,
Format(OMG_Outage_Report_APP.OutageDate,'yyyy'),
Month(OMG_Outage_Report_APP.OutageDate),
OMG_Outage_Report_APP.TestDomain

Finally figured it out ..
I removed the restriction from subquery and replaced with
"where OMG_DCMaintenance_Data.DataCenter = OMG_Outage_Report_APP.DataCenter"


So
"where OMG_DCMaintenance_Data.DataCenter = [DataCenter])" replaced with
"where OMG_DCMaintenance_Data.DataCenter = OMG_Outage_Report_APP.DataCenter"

This gives the correct results.


Thank you!

Edited by user Friday, October 25, 2019 4:40:29 PM(UTC)  | Reason: Not specified