Chart - Partial Months

Question

Chart - Partial Months

TJT

SSCommitted

Points: 1926
More actions
November 7, 2013 at 5:49 am

#279741

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Spiff SSCertifiable Points: 7826 More actions · Answer 1

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 SSCommitted Points: 1926 More actions · Answer 2

The results for the months look fine.

The query returns

28November2012-11

40December2012-12

84January2013-01

64February2013-02

73March2013-03

119April2013-04

99May2013-05

70June2013-06

95July2013-07

113August2013-08

100September2013-09

187October2013-10

65November2013-11

NULLNULLNULL

Spiff SSCertifiable Points: 7826 More actions · Answer 3

Is the chart grouping both Novembers together and giving you 93?

TJT SSCommitted Points: 1926 More actions · Answer 4

TJT

SSCommitted

Points: 1926

November 7, 2013 at 8:26 am

#1664806

Yes I think you found it. 🙂

Why is this?

Spiff SSCertifiable Points: 7826 More actions · Answer 5

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 SSCommitted Points: 1926 More actions · Answer 6

Works great!

Thanks.

For others, I had to remove Sorting in the Category Groups.

Spiff SSCertifiable Points: 7826 More actions · Answer 7

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.