下拉参数以限制不在结果集中的列上的数据集

我有一个基于数据集的 SSRS 报告。在数据集查询中，我的 where 子句看起来像这样

TM.term_grp_id in (SELECT distinct term_grp_id from ig_mrvCustomisations.dbo.vw_TerminalsByProfitCentre WHERE store_name = @STORENAME))

如果我在没有任何进一步开发的情况下运行报告，则不会提示我输入 @STORENAME 值。

相反，我手动创建了一个参数并尝试将其链接到上述值，但是由于我的结果集中没有包含 store_name 值，因此我似乎无法对其进行限制。

我根据上表中的不同值在 SSRS 中创建了一个下拉参数，但我无法将它们联系在一起。

如何创建限制 @STORENAME 的提示？

完整查询：

--DECLARE @STARTDATE DATETIME
--DECLARE @ENDDATE DATETIME
DECLARE @STORENAME NVARCHAR(800)

--SET @STARTDATE = GETDATE()-1 + '06:00:00:000'
--SET @ENDDATE = GETDATE() + '05:59:59:997'
SET @STORENAME = 'IVY'


select 
(DATENAME(dw,GETDATE()-1 + '06:00:00:000')) as 'Day',
 CONVERT(date,GETDATE()-1 + '06:00:00:000') as 'Date',
one.term_grp_name AS 'Terminal Group Name',
one.check_type_name AS 'Check Type Name',
(CASE WHEN LEFT(one.check_type_name,2) = 'EV' THEN 'Y' ELSE 'N' END) AS 'Event?',
LEFT(one.revenue_category_name,5) AS 'T3 Coding',
(CASE WHEN LEFT(one.revenue_category_name,2) = '71' THEN '99500'
    WHEN LEFT(one.revenue_category_name,2) = '72' THEN '99550'
 ELSE '99600' END) AS 'T3 Coding for comps',


LEFT(one.term_grp_name,4) AS 'Department',

(CASE WHEN LEFT(one.revenue_category_name,2) = '71' THEN 1010 
WHEN LEFT(one.revenue_category_name,2) = '72' THEN 1020
WHEN LEFT(one.revenue_category_name,5) = '77700' THEN 1070
WHEN LEFT(one.revenue_category_name,5) = '77750' THEN 6165
ELSE 1090 END) as 'Account',
(CASE WHEN one.revenue_category_name = '' THEN 'Total' ELSE one.revenue_category_name END) AS 'Lookup',

LEFT(one.term_grp_name,4) + ' ' + 
(CASE WHEN one.revenue_category_name = '' THEN 'Total' ELSE one.revenue_category_name END) AS 'Dept/Lookup',




one.revenue_category_name as 'Revenue Category',
sum(CONVERT(money,one.Tax)) + sum(CONVERT(money,one.NetRev)) +  sum(CONVERT(money,one.Grat)) AS 'Net Tender',
sum(CONVERT(money,one.NetRev)) as 'Net Revenue',
sum(CONVERT(money,one.disc)) as 'Discount',
sum(CONVERT(money,one.gross)) as 'Gross Revenue',

(CASE WHEN LEFT(check_type_name,2) = 'EV' THEN 0 ELSE sum(CONVERT(money,one.gross)) END) as 'Gross Rev (excl Events)',

(CASE WHEN LEFT(check_type_name,2) = 'EV' THEN 0 ELSE SUM(CONVERT(MONEY,one.grat)) END) AS 'Gratuity (excl Events)',

(CASE WHEN LEFT(check_type_name,2) = 'EV' THEN 0 ELSE SUM(CONVERT(MONEY,one.tax)) END) AS 'Tax (excl Events)',




sum(CONVERT(money,one.Tax)) as 'Tax',
sum(CONVERT(money,one.Grat)) as 'Gratuity',
sum(CONVERT(money,one.SC)) as 'Service Charge',
sum(CONVERT(money,one.Tip)) as 'Tip',
sum(CONVERT(money,one.Covers)) as 'Covers',
Sum(CONVERT(decimal(30,2),one.avecover)) as 'Average Cover',
sum(CONVERT(money,one.Checks)) as 'Checks',
CONVERT(decimal(30,2),(sum(one.NetRev) / nullif(sum(one.Checks),0))) as 'Average Check'


from

(select 
sum(CSD.sales_gross_amount - CSD.discount_amount) as 'NetRev',
sum(CSD.sales_gross_amount) as Gross,
sum(CSD.discount_amount) as Disc,
sum(CSD.gratuity_amount) as Grat,
sum(CSD.service_charge_amount) as SC,
sum(CSD.tip_amount) as Tip,
sum(CSD.tax_amount) as Tax,
sum(CSD.num_covers) as Covers,
(sum(CSD.sales_gross_amount - CSD.discount_amount) / nullif(sum(CSD.num_covers),0)) as 'AveCover',
0 as Checks,
'' as revenue_category_name,
TGM.term_grp_name,
CTD.check_type_name

from ig_business..Check_Sales_Detail CSD (NoLock)

join it_cfg..Terminal_Master TM (NoLock) on TM.term_id = CSD.tendered_terminal_id
join it_cfg..Term_Grp_Master TGM (NoLock) on TGM.term_grp_id = TM.term_grp_id and TGM.ent_id = 1
join ig_dimension..Check_Type_Dimension CTD (NoLock) on CTD.check_type_dim_id = CSD.check_type_dim_id

where CSD.transaction_data_id in
(select transaction_data_id
from ig_business..Check_Sales_Detail CSD (NoLock)
join it_cfg..Terminal_Master TM on TM.term_id = CSD.tendered_terminal_id
where tendered_date_time between GETDATE()-1 + '06:00:00:000' and GETDATE() + '05:59:59:997'
and TM.term_grp_id in (SELECT distinct term_grp_id from ig_mrvCustomisations.dbo.vw_TerminalsByProfitCentre WHERE store_name = @STORENAME))
and CSD.tendered_terminal_id <> 0
and CSD.void_state <> 2

group by TGM.term_grp_name, CTD.check_type_name

union all

select 
sum(CRC.gross_sales_amount_tax_included - CRC.discount_amount_tax_included) as 'NetRev',
sum(CRC.gross_sales_amount_tax_included) as Gross,
sum(CRC.discount_amount_tax_included) as Disc,
0 as Grat,
0 as SC,
0 as Tip,
0 as Tax,
0 as Covers,
0 as 'AveCover',
0 as Checks,
RCD.revenue_category_name,
TGM.term_grp_name,
CTD.check_type_name

from 
ig_business..Check_Revenue_Category_Detail CRC (NoLock) 
join ig_dimension..Revenue_Category_Dimension RCD (NoLock) on RCD.revenue_category_dim_id = CRC.revenue_category_dim_id
left join ig_business..Check_Sales_Detail CSD (NoLock) on CRC.transaction_data_id = CSD.transaction_data_id
join it_cfg..Terminal_Master TM (NoLock) on CSD.tendered_terminal_id = TM.term_id
join it_cfg..Term_Grp_Master TGM (NoLock) on TGM.term_grp_id = TM.term_grp_id and TGM.ent_id = 1
join ig_dimension..Check_Type_Dimension CTD (NoLock) on CTD.check_type_dim_id = CSD.check_type_dim_id

where CRC.transaction_data_id in (select transaction_data_id
from ig_business..Check_Sales_Detail CSD (NoLock)
join it_cfg..Terminal_Master TM on TM.term_id = CSD.tendered_terminal_id
where tendered_date_time between GETDATE()-1 + '06:00:00:000' and GETDATE() + '05:59:59:997'
and TM.term_grp_id in (SELECT distinct term_grp_id from ig_mrvCustomisations.dbo.vw_TerminalsByProfitCentre WHERE store_name = @STORENAME))

Group By
RCD.revenue_category_name,
TGM.term_grp_name,
CTD.check_type_name

union all

select 
0 as 'NetRev',
0 as Gross,
0 as Disc,
0 as Grat,
0 as SC,
0 as Tip,
0 as Tax,
0 as Covers,
0 as 'AveCover',
COUNT(distinct(CSD.check_number))as Checks,
'' as revenue_category_name,
TGM.term_grp_name,
CTD.check_type_name

from ig_business..Check_Sales_Detail CSD (NoLock)
join it_cfg..Terminal_Master TM (NoLock) on TM.term_id = CSD.tendered_terminal_id
join it_cfg..Term_Grp_Master TGM (NoLock) on TGM.term_grp_id = TM.term_grp_id and TGM.ent_id = 1
join ig_dimension..Check_Type_Dimension CTD (NoLock) on CTD.check_type_dim_id = CSD.check_type_dim_id

where transaction_data_id in (select transaction_data_id
from ig_business..Check_Sales_Detail CSD (NoLock)
join it_cfg..Terminal_Master TM on TM.term_id = CSD.tendered_terminal_id
where tendered_date_time between GETDATE()-1 + '06:00:00:000' and GETDATE() + '05:59:59:997'
and TM.term_grp_id in (SELECT distinct term_grp_id from ig_mrvCustomisations.dbo.vw_TerminalsByProfitCentre WHERE store_name = @STORENAME))
and (CSD.associated_check_number = 0 and CSD.refund_flag = 0 and void_reason_dim_id = 0)

group by TGM.term_grp_name, CTD.check_type_name
) as one


Group By one.term_grp_name,
one.revenue_category_name, 
one.check_type_name
ORDER BY [Date] asc, [Terminal Group Name] asc

更新：当我从查询的开头删除声明和设置时，我收到以下错误：