Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Chart - Partial Months


Chart - Partial Months

Author
Message
TJT
TJT
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 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
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 2218
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 Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 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
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 2218
Is the chart grouping both Novembers together and giving you 93?
TJT
TJT
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

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

Why is this?
Spiff
Spiff
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 2218
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 Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

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

Thanks.

For others, I had to remove Sorting in the Category Groups.
Spiff
Spiff
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 2218
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