SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Chart - Partial Months


Chart - Partial Months

Author
Message
TJT
TJT
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 254
I am hoping someone has some advice on this.

I have the query below that shows totals for each month. Everything works fine except one thing: it doesn't show the current month. For example the graph shows last November to this October - not last October to this November. Not a big deal, but I would prefer this.

When I run the query, it shows the numbers from last October to this November.

Any thoughts?


SELECT COUNT(NumberOfCars) AS TotalCars, Times
FROM MyCars
WHERE (Times > DATEADD(year, - 1, GETDATE()))
GROUP BY Years
Spiff
Spiff
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 2230
Have you verified that the chart figures you see for last November are not infact for this November? I'm wondering that if you are not grouping on Years & Months (two levels in the Category fields in the chart) it might not be sorting them correctly. The query is run against the server by SSRS so there is no reason you would get a different result.

Looking at your query, I dont think it works. Are you not getting an "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." error?
TJT
TJT
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 254
The results for the months look fine.

The query returns

28 November 2012-11
40 December 2012-12
84 January 2013-01
64 February 2013-02
73 March 2013-03
119 April 2013-04
99 May 2013-05
70 June 2013-06
95 July 2013-07
113 August 2013-08
100 September 2013-09
187 October 2013-10
65 November 2013-11
NULL NULL NULL
Spiff
Spiff
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 2230
Is the chart grouping both Novembers together and giving you 93?
TJT
TJT
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 254
Yes I think you found it. :-)

Why is this?
Spiff
Spiff
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 2230
I assume you are grouping on something like just the numerical month (i.e. 11, 10, 9 ... 12, 11) in the chart? If there is nothing to make it distinct from the other value it will be grouped together. You need to add another level like Year in.

You should try to make you query something like below:


SELECT
Year(Times) AS [Year],
Month(Times) AS [Month],
SUM(NumberOfCars) AS TotalCars
FROM
MyCars
WHERE (Times > DATEADD(year, - 1, GETDATE()))
GROUP BY Year(Times), Month(Times)



Then in SSRS chart Category Groups have Year AND Month in there. This way it'll know November 2012 is distinct from November 2013.
TJT
TJT
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 254
Works great!

Thanks.

For others, I had to remove Sorting in the Category Groups.
Spiff
Spiff
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 2230
Cool. Forgot to mention, just be aware if your data is at day level using DATEADD(YEAR,-1,GETDATE()) will be giving you data in a window of 08/11/2012 - 07/11/2013 if you ran it today. If you wanted the whole of November 2012 you'll need to work around that.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search