title: SQL - SSRS Reports
body: <p>I am trying to report on staff anniversaries (either this month or the next month) by using a stored procedure and SSRS report builder.</p>
  
  <p>The problem is if I select the parameter of the month or next month with the organization unit as 'All' it works, but if I select an individual organization unit, the report displays people who are not having a work anniversary (lists everyone on the database for that organization unit etc).</p>
  
  <p>ON SSRS Report builder my Parameters are @month and @SelectList (in other words Organisation unit)</p>
  
  <p>My Datasets is the main report stored procedure and SelectList.</p>
  
  <p>The Selectlist basically breaks it down by the division then the region then section then cost group etc.</p>
  
  <p>So am wondering how I can get the correct data displayed if I select a particular option from the select list?</p>
  
  <p>I would greatly appreciate all assistance in this matter :)</p>
  
  <p>My stored procedure code is below.</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[rpt_AnniversaryNextMonthTest](
  @SelectList nvarchar(2000)
  , @Month nvarchar(10)
  
  )
  
  AS
  BEGIN
  SET NOCOUNT ON;
  
  
  SELECT 
      DET_NUMBERA             as 'Emp ID'
      , DET_SURNAMEA          as 'Surname'    
      , DET_PREF_NMA          as 'Preferred Name'
      , DET_DATE_JNDD
      , CONVERT(VARCHAR, DET_DATE_JNDD, 103)          as 'Starting Date'
      , (CASE WHEN @Month= 'Next Month' AND Month(getdate())=12
              THEN DATEDIFF (year, (DET_DATE_JNDD), (getdate())) +1
              ELSE DATEDIFF (year, (DET_DATE_JNDD), (getdate()))
              END)    as 'Years of Service'
  
      , Division
      , Section
      , Pay_Location          as 'Location'
      , ReportsToName
  
  FROM Current_Staff
  WHERE POS_STATUSA NOT IN ('AA','AB','AC')
  AND ((@Month= 'This Month' AND Month(DET_DATE_JNDD) = Month(Getdate()))
  OR (@Month= 'Next Month' AND (Month(DET_DATE_JNDD) = Month(Getdate())+1 OR Month(Getdate())=12 AND Month(DET_DATE_JNDD)=1)))
  AND (left(@SelectList,1)='0')
  OR (left(@SelectList,1)='1' AND dbo.Current_Staff.Division=substring(@SelectList,2,50))
  OR (left(@SelectList,1)='2' AND dbo.Current_Staff.Region=substring(@SelectList,2,50))
  OR (left(@SelectList,1)='3' AND dbo.Current_Staff.Section=substring(@SelectList,2,50))
  OR (left(@SelectList,1)='4' AND dbo.Current_Staff.CostGroup=substring(@SelectList,2,50))
  
  END
  
  GO
  </code></pre>