December 12, 2016 at 1:29 pm
Hi,
This will be my first report to be created with 'DECLARE' in the SQL query and try creating reports.
How do I use or convert this in SSRS? Should I convert this to Stored Procedure? If so, do I need to create SP in SSRS or SSMS?
If anyone can provide a sample of the converted SP from below query and show how to use that in SSRS, would be of great help.
Here is the SQL Query -
Declare @Current_College_year Varchar(20)
,@Prior_College_year Varchar(20)
Set @Current_College_year = '2015-16'
set @Prior_College_year = '2014-15'
select
CYR.College_YEAR
,CYR.SPE_ALT as CYR_EStatus
,CYR.College_DIS_CODE
,DIST.DIS_NAME
,PYR.College_YEAR
,PYR.SPE_ALT as PYR_EStatus
,PYR.College_DIS_CODE
,ISNULL(CYR_COUNT,0) as CYR_COUNT
,ISNULL(PYR_COUNT,0) as PYR_COUNT
,(ISNULL(CYR_COUNT,0)-ISNULL(PYR_COUNT,0)) as Count_Diff
,case when ISNULL(PYR_COUNT,0) = 0 then 0 else cast((CYR_COUNT-PYR_COUNT)/cast(PYR_COUNT as numeric(8,2)) as numeric(8,2)) end *100 AS Percent_Diff
from
(select
ml.SPE_ALT,ml.SPE_SORT
,College_DIS_CODE
,College_YEAR
,count(distinct s_key) AS CYR_COUNT
from
TELECOLL_R.ML_ATTEND as ml WITH (NOLOCK)
where College_year = @Current_College_year
and enrolled_fs_id = 'Yes'
group by ml.SPE_ALT,ml.SPE_SORT,College_DIS_CODE,College_YEAR
)CYR
left join
(
select
ml.SPE_ALT,ml.SPE_SORT
,College_DIS_CODE
,College_YEAR
,count(distinct s_key) AS PYR_COUNT
from
TELECOLL_R.ML_ATTEND as ml WITH (NOLOCK)
where 1=1
and College_year = @Prior_College_year
and enrolled_fs_id = 'Yes'
group by ml.SPE_ALT,ml.SPE_SORT,College_DIS_CODE,College_YEAR
)PYR
on CYR.College_DIS_CODE = PYR.College_DIS_CODE
and CYR.SPE_ALT = PYR.SPE_ALT
left join [TELECOLL_USRDATA].[XL_DIS] as DIS WITH (NOLOCK)
on CYR.College_DIS_CODE = dist.DIS_ID
order by Percent_Diff
I am not getting pass the First step which is when I use the above query it throws me "The Declare SQL construct or statement is not supported." and even if I just ignore this step, it throws me the next error message -
"Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct" - An item with the same key has already been added.
In SQL Query - If I try removing 'Declare' it throws me syntax error.
Should I first work on modifying the sql query? (this query is owned by someone else and i do not have rights to do that)
December 12, 2016 at 2:48 pm
Yes - this logic should be put into a stored proc. What the stored proc looks like will depend on what you are doing with @Current_College_year & @Prior_College_year.
If these are user-supplied params then your stored proc would look like this:
CREATE PROC dbo.usp_SSRS_xxx
(
@Current_College_year Varchar(20),
@Prior_College_year Varchar(20)
)
AS
BEGIN
Set @Current_College_year = '2015-16';
set @Prior_College_year = '2014-15';
... The rest of your logic here...
END
If those are NOT user-supplied and you want to hard code them, then the SQL would look like this:
CREATE PROC dbo.usp_SSRS_xxx
AS
BEGIN
DECLARE
@Current_College_year Varchar(20),
@Prior_College_year Varchar(20)
set @Current_College_year = '2015-16';
set @Prior_College_year = '2014-15';
... The rest of your logic here...
END
Based on my experience - you may need to delete and re-create the data set that is using/calling this code.
-- Itzik Ben-Gan 2001
December 12, 2016 at 2:49 pm
Thanks Alan. I appreciate your explanation and I agree with you.
It worked when I removed the DECLARE and Set from the query and I am trying to set the parameters manually. (as explained here)Associate a Query Parameter with a Report Parameter (Report Builder and SSRS)
Is this a good approach to work to create a report or to create SP?
December 13, 2016 at 8:41 am
NewSSAS (12/12/2016)
Thanks Alan. I appreciate your explanation and I agree with you.It worked when I removed the DECLARE and Set from the query and I am trying to set the parameters manually. (as explained here)Associate a Query Parameter with a Report Parameter (Report Builder and SSRS)
Is this a good approach to work to create a report or to create SP?
Yes.
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply