none
How do I use 'DECLARE' in SSRS 2012? RRS feed

  • Question

  • Hi,

    This will be my first report to be created with 'DECLARE' in the SQL query.

    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
    Thank You.

    Monday, December 12, 2016 5:59 PM

Answers

All replies