August 5, 2014 at 4:47 am
Hello
I have developed report with parameter having multi value option. The report was developed in Microsoft Visual Studio 2010 and having SQL 2012.
Acceptance environment report works on SSRS 2012 and a database engine sql 2012
Production environment report does not works on SSRS 2008 r2 and a database engine sql 2012
I do not have direct acces to clients machine, can any one help me to understand what could be the issue
Thanks in Advanced
Dinesh
August 5, 2014 at 5:10 am
Dinesh R. Nandwalkar (8/5/2014)
HelloI have developed report with parameter having multi value option. The report was developed in Microsoft Visual Studio 2010 and having SQL 2012.
Acceptance environment report works on SSRS 2012 and a database engine sql 2012
Production environment report does not works on SSRS 2008 r2 and a database engine sql 2012
I do not have direct acces to clients machine, can any one help me to understand what could be the issue
Thanks in Advanced
Dinesh
How does it not work? Are there any errors?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 5:43 am
I have attached error in document
August 5, 2014 at 5:51 am
Could you post the query here, in plain text?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 5:57 am
Parameter query where am using multivalue
select distinct hr_agreement_contract.atct_contract_status
from hr_agreement_contract
where isnull(hr_agreement_contract.atct_contract_status,'') not in ('A','L','P')
Union
select 'A'
Union
select 'L'
Union
select 'P'
August 5, 2014 at 5:59 am
Dinesh R. Nandwalkar (8/5/2014)
Parameter query where am using multivalueselect distinct hr_agreement_contract.atct_contract_status
from hr_agreement_contract
where isnull(hr_agreement_contract.atct_contract_status,'') not in ('A','L','P')
Union
select 'A'
Union
select 'L'
Union
select 'P'
This is the query that fetches the value for the parameter, right?
What about the query where you actually use the parameter?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 6:01 am
yes correct , Main query is
SELECT
max(drpb.drpb_bsn) as BSN,
atct.atct_global_id as Corp_ID,
replace(ltrim(replace(atct.atct_global_id, '0', ' ')), ' ', '0') as Corp_ID_match,
atct.atct_local_id as Local_ID,
replace(ltrim(replace(atct.atct_local_id, '0', ' ')), ' ', '0') as Local_ID_match,
emdr.emdr_first_name as First_Name,
emdr.emdr_middle_name as Middle_Name,
emdr.emdr_last_name as Last_Name,
emdr.emdr_known_as as Known_As,
format(atct.atct_birth_date,'dd-MM-yyyy') as Birth_Date,
case when emdr.emdr_business_email is null or ltrim(emdr.emdr_business_email) = ''
then emdr.emdr_preferred_email
else emdr.emdr_business_email
end as Email,
lower(emdr.emdr_country_name) as Countr_Name,
emdr.emdr_office_country_name as Office_Country,
atct.atct_contract_status as Employee_Status,
case emdr. emdr_employee_internal
when 'E' then 'Employee'
when 'N' then 'Non Employee'
end as Employee_Indicator,
format(atct.atct_hire_date,'dd-MM-yyyy') as Hire_Date,
format(atct.atct_termination_date,'dd-MM-yyyy') as Termination_Date,
atct.atct_functional_title_code as Job_Code,
emdr.emdr_hr_job_title as HR_JobTitle,
emdr.emdr_job_title as Job_Title,
emdr.emdr_local_cost_cd as Local_Cost_Cd,
emdr.emdr_local_dept_name as Local_Dept_Name,
emdr.emdr_sbu_name as Business_Unit,
emdr.emdr_bu_name as Business_Area,
bote.bote_structure_cd as Structure_Code,
sum(distinct
(case when atct.atct_row_excluded is NULL
then 1
else 0
end)
*
(case atct.atct_contract_seq_nbr
when 1
then (case atct.atct_contract_status
when 'A' then (case atct.atct_contract_role
when 'B' then (case atct.atct_fte
when 0 then 0
else atct.atct_headcount end)
when 'E' then atct.atct_headcount
when 'M' then atct.atct_headcount
when 'R' then atct.atct_headcount
when 'T' then atct.atct_headcount
when 'U' then atct.atct_headcount
else 0 end
)
when 'S' then (case atct.atct_contract_role
when 'B' then (case atct.atct_fte
when 0 then 0
else atct.atct_headcount end)
when 'E' then atct.atct_headcount
when 'M' then atct.atct_headcount
when 'R' then atct.atct_headcount
when 'T' then atct.atct_headcount
when 'U' then atct.atct_headcount
else 0 end
)
when 'P' then(case atct.atct_contract_role
when 'B' then (case atct.atct_fte
when 0 then 0
else atct.atct_headcount end)
when 'E' then atct.atct_headcount
when 'M' then atct.atct_headcount
when 'R' then atct.atct_headcount
when 'T' then atct.atct_headcount
when 'U' then atct.atct_headcount
else 0 end
)
else 0 end)
else 0 end)) as Permanent_Staff_Headcount,
case when emdr.emdr_middle_name is null or emdr.emdr_middle_name =''
then emdr.emdr_last_name
else emdr.emdr_middle_name+' '+isnull(emdr.emdr_last_name,'')
end as CoCName
FROM ih_employee_directory emdr
join ih_directory_phonebook drpb on lower(emdr.emdr_country_name)='netherlands' and emdr.emdr_employee_internal in (@Employee_type) and drpb.drpb_country_cd=emdr.emdr_country_cd and drpb.drpb_ed_global_id=emdr.emdr_ed_global_id
left join hr_bo_table bote on emdr.emdr_local_cost_cd=bote.bote_bo_nr
left join hr_agreement_contract atct on atct.atct_contract_status in (@Employee_Status) and emdr.emdr_country_cd=atct.atct_country_cd and emdr.emdr_check_global_id=atct.atct_global_id
WHERE atct.atct_reporting_period=@Reporting_period
and (emdr.emdr_hr_date_inactive IS NULL or emdr.emdr_hr_date_inactive>getdate())
and (emdr.emdr_dd_date_inactive IS NULL OR emdr.emdr_dd_date_inactive>getdate())
and emdr.emdr_employee_internal in (@Employee_type)
and atct.atct_contract_status in (@Employee_Status)
and lower(emdr.emdr_country_name) = 'netherlands'
GROUP BY
atct.atct_global_id,
replace(ltrim(replace(atct.atct_global_id, '0', ' ')), ' ', '0'),
atct.atct_local_id,
replace(ltrim(replace(atct.atct_local_id, '0', ' ')), ' ', '0'),
emdr.emdr_first_name,
emdr.emdr_middle_name,
emdr.emdr_last_name,
emdr.emdr_known_as,
atct.atct_birth_date,
case when emdr.emdr_business_email is null or ltrim(emdr.emdr_business_email) = ''
then emdr.emdr_preferred_email
else emdr.emdr_business_email
end,
lower(emdr.emdr_country_name),
emdr.emdr_office_country_name,
atct.atct_contract_status,
case emdr. emdr_employee_internal
when 'E' then 'Employee'
when 'N' then 'Non Employee'
end,
atct.atct_hire_date,
atct.atct_termination_date,
atct.atct_functional_title_code,
emdr.emdr_hr_job_title,
emdr.emdr_job_title,
emdr.emdr_local_cost_cd,
emdr.emdr_local_dept_name,
emdr.emdr_sbu_name,
emdr.emdr_bu_name,
bote.bote_structure_cd,
case when emdr.emdr_middle_name is null or emdr.emdr_middle_name =''
then emdr.emdr_last_name
else emdr.emdr_middle_name+' '+isnull(emdr.emdr_last_name,'')
end
order by atct.atct_global_id,emdr.emdr_last_name
August 5, 2014 at 6:11 am
So if I understand it correctly, SSRS passes ('A','L','P') to SQL Server in acceptance, but ('A,L,P') in production?
Without any changes to the report or queries?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 6:14 am
Correct
August 5, 2014 at 6:16 am
Dinesh R. Nandwalkar (8/5/2014)
Correct
No use of stored procedures?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 6:18 am
no i am directly using query
August 5, 2014 at 6:19 am
No I am directly using query
August 5, 2014 at 6:29 am
Well it is weird. SSRS 2008R2 can work with multi-valued parameters. A bit strange you develop on SQL Server 2012, only to deploy to a lower version.
What if you create the report in BIDS 2008 and deploy that to the production server?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 6:34 am
I have developed report in Microsoft Visual Studio 2008 only
August 5, 2014 at 6:39 am
Dinesh R. Nandwalkar (8/5/2014)
I have developed report in Microsoft Visual Studio 2008 only
Your initial question states otherwise:
I have developed report with parameter having multi value option. The report was developed in Microsoft Visual Studio 2010 and having SQL 2012.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply