SSRS User should Recieve only Output

• waseemshaikh345

  Mr or Mrs. 500

  Points: 580

  More actions

  June 16, 2014 at 8:21 am

  #308255

  Hello So basically everything I have in my query is fine. What I am trying to do is make the user select their company and type in a @begin_date and @End_date. So the date they choose has to be within the year 2014 ONLY. Also the user can only see the passed months from the present month. So if we are in the month of June we should only see any month up until January which is the 1st month of 2014. We cant see June because the month has not been over yet. So when we are in July we can see the month for June. So for each month its suppose to display how many funded contracts are within that year. The query I made works fine except I only want the MONTHS to be show ONLY what the user INPUTS. Not the other months which is what my problem is.

  So I Don't want this if the user inputs @begin_Date '01/22/2014' @End_date 02/04/2014. The user should only get the output for Janurary and feburary. The other Months should not displayed until the dates enter match the month.

  

  I would like to post a picture of exactly what I am talking about but I don't know how. If someone can tell me I would be glad to be more clear since I think of how I explained might be confusing.

  Alter Proc spGetAdminTotalYTD

  (@Begin_Date DATETIME,

  @End_Date DATETIME,

  @program int=null) As

  Declare @year int

  Set @year = 2014

  Begin

  SELECT d.name, a.dealer_code, b.last_name, b.city, b.state, b.phone

  , COUNT(CASE WHEN MONTH(c.Funded_date) = 1 THEN 1 ELSE NULL END) January

  , COUNT(CASE WHEN MONTH(c.Funded_date) = 2 THEN 1 ELSE NULL END) Feburary

  , COUNT(CASE WHEN MONTH(c.Funded_date) = 3 THEN 1 ELSE NULL END) March

  , COUNT(CASE WHEN MONTH(c.Funded_date) = 4 THEN 1 ELSE NULL END) April

  , COUNT(CASE WHEN MONTH(c.Funded_date) = 5 THEN 1 ELSE NULL END) May

  , COUNT(CASE WHEN MONTH(c.Funded_date) = 6 THEN 1 ELSE NULL END) June

  , COUNT(CASE WHEN MONTH(c.Funded_date) = 7 THEN 1 ELSE NULL END) July

  , COUNT(CASE WHEN MONTH(c.Funded_date) = 8 THEN 1 ELSE NULL END) August

  , COUNT(CASE WHEN MONTH(c.Funded_date) = 9 THEN 1 ELSE NULL END) September

  , COUNT(CASE WHEN MONTH(c.Funded_date) = 10 THEN 1 ELSE NULL END) October

  , COUNT(CASE WHEN MONTH(c.Funded_date) = 11 THEN 1 ELSE NULL END) November

  , COUNT(CASE WHEN MONTH(c.Funded_date) = 12 THEN 1 ELSE NULL END) December,

  COUNT(1) AS YTD

  FROM tdealer a JOIN tContact b ON a.contact_id = b.contact_id JOIN tContract c ON a.dealer_id = c.dealer_id JOIN tCompany d ON c.company_id = d.company_id

  WHERE d.company_id = @program AND c.Funded_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-5, 0) And YEAR(c.Funded_date) = @Year

  And c.Funded_date < DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, 0) And (c.funded_date) between @Begin_Date And @End_Date

  GROUP BY

  d.name,

  a.dealer_code,

  b.last_name,

  b.city,

  b.state,

  b.phone

  end

  exec spGetAdminTotalYTD '01/04/2014', '05/30/2014', '58'