June 24, 2014 at 8:09 am
Hey guys, so I have this stored procedure. The query works fine but the problem is the output.
I wanted the user to enter a BEGIN DATE and END DATE. And the output be only those months between the user has chosen.
I don't want other Columns of the months to be shown which is the problem I am having.
I am using a table in Report builder 3.0 and can't use matrix.
This is my Stored procedure:
Alter Proc spGetAdminTotalYTDDD
(@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
, month(c.Funded_date) [Month]
, datename(month, dateadd(month, month(c.funded_date), - 1)) [MonthName],
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,
Month(c.funded_date)
end
June 24, 2014 at 8:47 am
What do you mean by "other Columns of the months "?
What is the problem exactly?
June 24, 2014 at 9:02 am
Okay lets say the user inputs @begin_date 04/02/2014 and @End_Date 06/30/2014. That user is only suppose to see
April and May's contracts.
The problem is it shows ALL the months of the year. It shows the funded contracts for the correct months but the header of all the months still show which I don't want.
To better explain I'll give you a visual.
so User inputs 02/04/2014 and End Date 05/30/2014.
I want this as the results
Feb Mar Apr YTD
1 0 2 3
0 1 1 2
1 0 1 2
--------------------
2 1 4 7 <-- Total
I don't want this
Jan Feb Mar Apr May Jun Jul Aug Sep Nov Dec YTD
0 1 0 0 2 0 0 0 0 0 0 3
0 0 1 0 1 0 0 0 0 0 0 2
0 1 0 0 1 0 0 0 0 0 0 2
---------------------------------------------------------
2 1 4 7 <-- Total
June 24, 2014 at 9:17 am
Why are you posting this again when the solution was posted in another topic?
You cannot pass a variable number of columns to reporting services.
You have to use a matrix (which you refuse to do) or output all columns and suppress them in the report as already said in the other topic.
Far away is close at hand in the images of elsewhere.
Anon.
June 24, 2014 at 9:21 am
You compare Funded_date to the following:
- Greater than or equal to - DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-5, 0) = 2014-01-01
- Less than - DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, 0) = 2014-06-01
- between @BeginDate and @End_Date = Since this is between the previous two statements it doesn't matter
- Year = @Year = ? (2014?)
So I can see why you would get all months between Jan. and Jun, but not sure why you would get back all months. Try to compare Funded_date to the incoming parameters only (clean them up prior if you need to strip off days or time)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply