Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Chart - Partial Months Expand / Collapse
Author
Message
Posted Thursday, November 7, 2013 5:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:04 AM
Points: 37, Visits: 103
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
Post #1512218
Posted Thursday, November 7, 2013 8:04 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:49 AM
Points: 83, Visits: 690
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?
Post #1512289
Posted Thursday, November 7, 2013 8:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:04 AM
Points: 37, Visits: 103
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
Post #1512298
Posted Thursday, November 7, 2013 8:24 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:49 AM
Points: 83, Visits: 690
Is the chart grouping both Novembers together and giving you 93?


Post #1512308
Posted Thursday, November 7, 2013 8:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:04 AM
Points: 37, Visits: 103
Yes I think you found it.

Why is this?
Post #1512310
Posted Thursday, November 7, 2013 8:32 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:49 AM
Points: 83, Visits: 690
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.
Post #1512314
Posted Thursday, November 7, 2013 10:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:04 AM
Points: 37, Visits: 103
Works great!

Thanks.

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

Post #1512376
Posted Thursday, November 7, 2013 10:52 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:49 AM
Points: 83, Visits: 690
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.



Post #1512378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse