sql-server-2008reporting-servicesssrs-2012
Drop down parameter to restrict dataset on a column not in resultset
I have an SSRS report based upon a dataset. In the datasets query my where clause looks something like this
TM.term_grp_id in (SELECT distinct term_grp_id from ig_mrvCustomisations.dbo.vw_TerminalsByProfitCentre WHERE store_name = @STORENAME))
If I run the report without any further development, I won't be prompted for the @STORENAME value.
Instead, I manually created a parameter and tried to link it to the above value, but because I do not include the store_name value in my resultset, it seems i was unable to restrict on it.
I have created a drop down parameter in SSRS based upon the distinct values in the table above, but I am unable to tie them together.
How do I create a prompt that will restrict on @STORENAME ?
Full Query:
--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
Update: When I remove the Declare and set from the beginning of the query I get the following error:
Solution
You have declared and specified the @STREENAME variable in your code so SSRS has not need to set it, it will always be 'IVY'
Comment out the following two lines
DECLARE @STORENAME NVARCHAR(800)
SET @STORENAME = 'IVY'
It should not work.
NOTE: Parameter names are case sensistive so the query parameter name @STORENAME should match the SSRS parmatername EXACTLY excluding the @ symbol.
- How can I find out what FOREIGN KEY constraint references a table in SQL Server?
- Why does my query fail to convert a varchar value to int?
- Verify and/or Recommend Re Database Design for an Internal Admin Chat System
- Tricky SQL update
- How to select the last record of a table in SQL?
- Search multiple occurrences of a word in a string
- Login failed for user 'NT AUTHORITY\NETWORK SERVICE'
- How do I perform a simple string mapping as part of a t-sql select?
- Joining Two CTE's with Pivots
- SQL Server query to find all permissions/access for all users in a database
- SQL - Continue running all SQL statements even after error
- Best alternative for the Top Clause in Sql Server 2008?
- How to remove a specific character from a string, only when it is the first or last character in the string.
- Cannot open backup device. Operating System error 5
- How to grant database user read/write access roles the "new way"?
- Procedure is not returning any data when run from VB6
- TSQL rollup -return not null
- SQL Stored Procedures not finishing when called from Python
- How to remove or alter the dbo prefix from SQL Server 2012 tables?
- Date format in dd/MM/yyyy hh:mm:ss
- "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine" Error in importing process of xlsx to a sql server
- How to split a comma-separated value to columns
- Where value in column containing comma delimited values
- How do I find a disabled index on SQL server 2008
- Attach multiple databases using T-SQL
- "č" converted to "c" when querying through SQL Server 2008
- How to find circular dependent table in sql server
- Running Sql Server stored proc in context of caller
- SQL Server misses that column doesn't exist
- Convert varchar list to int in Sql Server