June 18, 2014 at 10:36 am
--I Am Using SQL Server 2008 R2 And Report Builder 3.0--
So what I am trying to do is, I have a report that generates of how many Funded contracts are funded within the year 2014 of each month and in the end of the year its suppose to total up the funded contracts of all the months a column called "Year to Date"
My problem is not that bad, So when the user enters the dates of their choosing it shows how many funded contracts within the month they chose. So if they input @Begin_date 01/02/2014 and @End_date 03/02/2014.
*THEY SHOULD ONLY SEE THOSE 3 MONTHS IN RANGE*
So my query does give the user the funded contracts within their months EXCEPT it shows all the months even though my query doesn't show the funded contracts in other months they haven't chosen.
In simpler terms I don't want this output when User enters their credentials:
@begin_Date 01/01/2014 @End_date 02/01/2014
Jan Feb Mar May Jun Jul Aug Sep Oct Nov Dec YTD
1 1 0 0 0 0 0 0 0 0 0 2
0 1 0 0 0 0 0 0 0 0 0 1
1 0 0 0 0 0 0 0 0 0 0 1
I want this for the user output when they enter their credentials
@begin_Date 01/01/2014 @End_date 02/01/2014
Jan Feb YTD
1 1 2
0 1 1
1 0 1
This is my Query
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,
c.Funded_date, MONTH(c.Funded_Date), YEAR(c.Funded_Date)
, 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,
c.funded_date
end
exec spGetAdminTotalYTD '01/04/2014', '05/30/2014', '47'
June 18, 2014 at 10:43 am
It sounds like you have a table created instead of a matrix.
Try setting it up with a matrix where the column group will be done on the months.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 18, 2014 at 10:46 am
Yes, it works with a matrix but unfortunately the client I'm working for wants it as a table that's the problem. I was thinking about Using a PIVOT function but I don't know really how to use it correctly. That's the only option I can think of now.
June 18, 2014 at 10:50 am
Why would having it as a table or matrix make any difference to the output?
If you want to persist with a table then hide columns that do not correspond to the date parameters.
Far away is close at hand in the images of elsewhere.
Anon.
June 18, 2014 at 10:52 am
If they insist on a table and tying your hands like that, then you can hide columns in the result set. Just set an expression for each columns visibility based on the results.
But in the end, a matrix is just a dynamic table.
Maybe it needs to be re-verified with the client to ensure they want empty results to not be visible.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 18, 2014 at 10:55 am
That would have worked but the way my query is set up is, The user should only see the passed months from the present month within the year 2014 ONLY. so for example we are in June, the user should only see any month up until Janurary because that's the start year of 2014. The user should not be able to see June July etc. Now lets say we passed June and we are in July now. We can see June because that month has passed. But the problem is not this. The problem is I don't want all the months to be displayed when the user inputs a specific date range. Only the date months the user inputs with the data should be displayed. That is my problem
June 18, 2014 at 10:58 am
As David and I said, an expression can hide display a column based on the results and/or parameters used.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 18, 2014 at 10:58 am
Thanks SQLRUNNER, I know I showed them the report using a Matrix and it worked great but unfortunately they want it as a table. So I will take yours and Davids suggestions. Any idea how the expression might be for January I just want an idea of how I will do this. I can handle the other months from that point on. Thanks guys for the help.
June 19, 2014 at 2:14 am
Set the expression for the Hidden property for the January column to
=Switch(1<Month(Parameters!StartDate.Value),True,1>=Month(Parameters!EndDate.Value),True,1>=Month(Today),True,True,False)
February to
=Switch(2<Month(Parameters!StartDate.Value),True,2>=Month(Parameters!EndDate.Value),True,2>=Month(Today),True,True,False)
etc
Note that based on your requirements no data will be shown when the report is run in January
Far away is close at hand in the images of elsewhere.
Anon.
June 19, 2014 at 11:26 am
Thank you all of you for the help. My report is finally done. Much appreciated to all that helped out on this thread! Thanks again!
June 19, 2014 at 1:07 pm
Hey David, for the YTD, which totals the months funded contracts is there a different type of expression for it? or do I need to filter it?
June 20, 2014 at 1:45 am
waseemshaikh345 (6/19/2014)
Hey David, for the YTD, which totals the months funded contracts is there a different type of expression for it? or do I need to filter it?
You will have to change the query to only count when months are within the parameters
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply