May 3, 2011 at 9:10 am
Hi guys
I have a simple query which generates a general activity table in SSRS 2008.
In order to sort the months in the correct date order i use the dateadd function ie dateadd(m,-3,assigned_date)
I've realised when running the report this morning that this no longer works properly because i am looking at data over the past 12 months (May 10 to Apr 11) and SSRS recognises Apr 11 as month number 1 (logical) so orders it first in my tabular report rather than last.
Does anybody know of a simple solution to this problem?
May 3, 2011 at 9:14 am
Couldn't you just order it by the date instead of the results of the dateadd function?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 3, 2011 at 9:15 am
Either put a date in there or sort by year, then month.
The date is more work but it should be less work for the server to sort.
May 3, 2011 at 9:30 am
Cheers guys. I think the simplest way would be to sort by year and then by month.
Thanks for such a speedy response!
May 4, 2011 at 3:09 am
Although the year sort, month sort works in SSMS - when i pull through into SSRS it still doesn't sort in the correct way. My reporting period runs from April 2010 to April 2011 but precedence is still given to April 2011 and this is sorted first in the tabular report.
Anyone any ideas?
May 4, 2011 at 3:25 am
--First, lets build some test data, since you didn't supply any I've used a simple table
DECLARE @table AS TABLE (mydate DATETIME)
;WITH testdata AS (
SELECT CAST('2011-01-01' AS DATETIME) datevalue, 1 AS counting
UNION ALL
SELECT datevalue + Round(( ( 5 ) * Rand() + 1 ), 0), counting + 1
FROM testdata
WHERE counting + 1 < 101)
--100 rows of test data
INSERT INTO @table (mydate)
SELECT datevalue
FROM testdata
--Actual Query
SELECT Dateadd(MONTH, tbl.monthNumber, 0) AS [Month], SUM(tbl.val) AS Activity
FROM (SELECT Datediff(MONTH, 0, mydate) AS monthNumber, 1 AS val
FROM @table) tbl
GROUP BY monthNumber
ORDER BY monthNumber
--edit--
Just to make it clear, instead of ordering on the month number and the year number this query orders on the month number alone. The month number is derived from the number of months that have passed since "1900-01-01" to the particular "mydate". This allows you to ensure that different years are ordered correctly.
May 4, 2011 at 4:09 am
Cheers for this - i'll give it a crack straight away.
🙂
May 4, 2011 at 4:48 am
Works perfectly - many, many thanks again for this!
May 4, 2011 at 9:33 am
No problem. Bear in mind that there may be faster ways to accomplish your task 😉
May 4, 2011 at 3:33 pm
ByTheWestway (5/4/2011)
Although the year sort, month sort works in SSMS - when i pull through into SSRS it still doesn't sort in the correct way. My reporting period runs from April 2010 to April 2011 but precedence is still given to April 2011 and this is sorted first in the tabular report.Anyone any ideas?
It sounds like you are sorting the resultset in SQL (using ORDER BY) - AND also doing some type of sorting in the report itself.
I am guessing that you have a column in the resultset that is a formatted date string that is being used in the report for the sort. This would cause the report to sort in alpha order based upon that column.
Two things here - first, remove the order by in the SQL and rely on SSRS to sort the report data. Second, don't return a formatted date string to SSRS - return the full date as it would be returned from SQL Server. In the sorting properties for the report - use the full date column and where this date is displayed, format it how you want it to be displayed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 5, 2011 at 2:33 am
Cheers Jeffrey
So rather that using DATENAME or DATEPART functions in T-SQL bring back the 'raw' date and then build an expression in SSRS to format the date in the way i want it to appear?
I'd never thought of doing it that way as i'm accustomed to exporting my T-SQL output straight into Excel and/or trying to achieve as much in SSMS before pulling into SSRS (Reporting Services can be frustrating!)
I'll give the way you suggest a try.
May 5, 2011 at 2:02 pm
That is correct - let SSRS do the work for you. In most cases, it is just as efficient to allow SSRS to format that data - in fact, I would say it can be more efficient.
Remember, SQL Server is not very efficient at string manipulation - whereas .NET is much more efficient.
As for sorting - some people believe it to be much more efficient to sort the data in SQL Server (since you are using the server's processing power to sort). I only agree with this when the resultset is very large. For most reports, sorting in SSRS is much better because it gives you the ability to change how a report sorts without modifying the procedure/code used.
For example, if you need to have multiple reports that use the same data - but, each report would be grouped/sorted differently you would have to have multiple procedures or dynamic SQL to accomplish this in SQL. In SSRS, you just create a new report using the same procedure and set the grouping/sorting as needed for that report.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply