Need To Return Month/YYYY And Sort By It In Report
Aug 20, 2007
I have a report that is grouped by Month and Year.
So I need it to display:
Jan, 2007
Feb, 2007
Mar, 2007
Apr, 2007
Etc…
AND it needs to be sorted with Jan as the first month and Dec as the last (as if it was 1-12 for months).
Now I can get it to display by taking the month name and concatenating it to the year, but then of course it won’t sort the way I want it to.
Any suggestions on this?
Perhaps I could/should return two values, the first being what I display on the report, and the second being what I sort on as a hidden field? So I could return 200701, 200702, 200703, and so on, to sort on that?
I've a field with month & year. I'm trying to sort this column. Something like this is my column values.
April 2001 August 2001 December 2001 February 2001 January 2001 July 2001 June 2001 March 2001 May 2001 November 2001 October 2001 September 2001 April 2002 August 2002 December 2002 February 2002 January 2002
But I want to sort this like below.
January 2001 February 2001 March 2001 April 2001 May 2001 June 2001 July 2001 August 2001 September 2001 October 2001 November 2001 December 2001 January 2002 February 2002 March 2002 April 2002 May 2002
I'm not finding any way to do this. If I sort this list by month, it'll be sorted alphabetically. Any help on this would be much appreciated!
SELECT TOP (100) PERCENT Calendar_Key, Calendar_Date, Calendar_Year, Calendar_Month, Calendar_Quarter, Calendar_week, Calendar_DayOfMonth, Calendar_DayOfYear, Calendar_Datename, Calendar_EndOfMonth, Calendar_EndOfQuarter, Calendar_EndOfYear, CAST(CASE WHEN Calendar_Month = 1 THEN 'January' WHEN Calendar_Month = 2 THEN 'February' WHEN Calendar_Month = 3 THEN 'March' WHEN Calendar_Month = 4 THEN 'April' WHEN Calendar_Month = 5 THEN 'May' WHEN Calendar_Month = 6 THEN 'June' WHEN Calendar_Month = 7 THEN 'July' WHEN Calendar_Month = 8 THEN 'August' WHEN Calendar_Month = 9 THEN 'September' WHEN Calendar_Month = 10 THEN 'October' WHEN Calendar_Month = 11 THEN 'November' WHEN Calendar_Month = 12 THEN 'December' END AS char(9)) AS Month FROM EDW.Calendar_Dim WHERE (Calendar_Year IS NOT NULL) ORDER BY Calendar_Year, Calendar_Month
I have two vlaue prompts.. which show year & month, when we select year, it shows in order. when we select month, since its character, it showing from 'April, August....
I have a table report that lists the prior 12 months of sales data. I'd like the report to display in reverse order from current month back. When I sort decedning by month it puts the months in reverse alphabetic order. How can I sort by numeric order?
I have a table where hours are being loaded in a weekly basis. The YearWeek is populated when the data is loaded. The value format of the Year Week is 2015-39, 2015-41, etc. I need to calculate the total hours per Fiscal Year.For example, week '2015-39' will be return FY15 and week '2015-41' will return FY16, and so on. By extracting the year, I can do a group by and have total hours for each year.
Currently, I have it working by splitting the value into year and week and then looping through each year and week, so I can assign the totals to the corresponding FY.select sum(hours) as total, yearweek from tablename group by yearweek...Then I loop through using C#.I can return the FY using an actual date,how to do it for year-week format for any given year.
select CASE WHEN CAST(GETDATE() AS DATE) > SMALLDATETIMEFROMPARTS(DATEPART(YEAR,GETDATE()),09,30,00,000) THEN DATEPART(YEAR,GETDATE()) + 1 ELSE DATEPART(YEAR,GETDATE()) END AS FY
Anyone please suggest me that , I have a text box where I am entering th date in dd/MM/yyyy format. But the default field in my SqlServer2005 is MM/dd/yyyy. How can i insert it into the database. or how can i change the MM/dd/yyyy of database to dd/MM/yyyy
Please, its urgent. Thanks in advance Regards Tapan
We are having problems with a new PC, we have installed oracle 10g client, Toad, and SQLServer2005 BITools PC regional settings set to en-NZ( we need date format to be dd/mm/yyyy) BITools/tools/options/international settings language = Same as Microsoft Windows
Ran a simple query(against oracle db) using between with 2 date parameters one parameter is datatype string the other datetime. When previewing report string is entered 01/03/2008 and datetime param is entered by choosing from datepicker, when view report button is clicked the datetime param is transposed whereas the string param remains unchanged.
The problem seems to be the datepicker not picking up on regional date format.
This is a new PC with xp-sp3 and the above software installed.
I'm storing records that contain a date/time data type. I am needing two links on a reports page (asp), the first should return all records for the current month and the second link should return all records for the last three months (including current month). I have no idea how to just sort by month.
I'm also not sure what to include here in this post to help you answer my question. On the form that is submitted initially the text field is named "txtSubmitDate" and in the database it's stored in a field called "submitdate" and is 8 characters in length.
I've tried: 'SELECT TODAY'S MONTH SqlJunk = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE())-1"
'SELECT TODAY'S MONTH and the last 2 months SqlJunk2 = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE()) OR MONTH(submitdate) = MONTH(GETDATE())-1 OR MONTH(submitdate) = MONTH(GETDATE())-2 ORDER BY submitdate ASC"
These are not working because it can't handle the change in year (going from january 2005 back to december 2004, etc).
I€™m getting a datetime format problem(mm-dd-yyyy for dd-MM-yyyy), when I install SQL Server 2005 Express. {The exception is: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.} My windows Regional and Language options €“ English (United Kingdom), Sort date format is dd-MM-yyyy. When converting the date time in Sql server is using the mm-dd-yyyy format. But I€™m supplying the dd-mm-yyyy format date time.
I tried number of things none of them worked for me
1. Tried changing the default language and get the date time format - exec sp_configure 'default language', 2057 reconfigure - did not work EXEC sp_defaultlanguage 'my user name', 'British' - did not work (Ref: http://www.cactushop.com/support/UKUS-date-format-issues-with-MS-SQL---conversion-errors-or-blank-pages__592__.htm)
2. Tried a registry hack by opening regedit, and get the following 3 language keys and change it to decimal 2057: [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90ToolsClientSetupCurrentVersion] [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90ToolsSetup [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1Setup] (Ref: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891) - did not work
3. Every thing in the Regional and Language options to UK and British with the date time format input language keyboard and every think else I could think of, which could link to US English or US date format --------- Did not work
4. even went into the extend of modifying the date format on a Windows machine for new users account by editing the HKEY_USERS registry key and creating a new user - Did not work (Ref: http://www.windowsitpro.com/Article/ArticleID/39407/39407.html )
5. Uninstall and reinstall SQL server express several time and did the steps 1 €“ 4 where applicable €“ did not work€¦.
If anyone has any idea of what I have to do to change the date time format in the SQL Server 2005 to use the dd-mm-yyyy format for dates.... Please help me or point me in the direction in which I have to look for an answer. Thank you very much€¦.
Hi to ALL Here I am using .net 2.0 and MS SQL 2K. In our database table DateTime saved as in the format of 2007-01-31 8:33:19.000(yyyy-mm-dd) to access to this records by searching based on Date, when we are searching based on Date that Date format would be (dd/mm/yyyy). How we can convert the date format.
Data Base Date Format: 2007-01-31 (yyyy-mm-dd) Search Criteria Date Format: 31/01/2007 (dd/mm/yyyy)
I have written following Code: Pls suggest me whether its correct r not ---------------------------------------------------------------------------- Select distinct tbl_adminuser.adminUserName,tbl_adminCategory.Name, COUNT(dbo.tbl_outbox.msgUserID) As
TotalCount
FROM dbo.tbl_adminuser,dbo.tbl_AdminCategory, dbo.tbl_outbox
where tbl_adminuser.adminUserID = dbo.tbl_AdminCategory.CatID and tbl_AdminCategory.CatID =
dbo.tbl_outbox.msgUserID
and tbl_outbox.msgUserID <> 0 and Convert(varchar,tbl_outbox.msgDate,103)>=@fromdate and convert(varchar,tbl_outbox.msgDate,103)<=@todate group by tbl_adminuser.adminUserName, dbo.tbl_AdminCategory.Name --------------------------------------------------------------------------------------
I have three types of specific reports that i have to create with the input parameters (range) either
1: By date (rdl 1)
2.By Month (rdl 2)
3.By Year (rdl 3)
Is it possible ( or how do I ) to create just one report template ( one rdl) with the three sets of parameters ( hiding/invisible which ever two sets base on user selection) and the output of the report will display the desired type( either by year, month or date).
I ask this because its possible to create a drill down report from year down to date etc in report designer (vs 2005). Not sure if I can create one instead of three rdls and with the 'logic' built within that template.
how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory]( [username] [varchar](50) NULL, [current_tenure] [int] NULL, [move_in_date] [smalldatetime] NULL, [Cnt_Lead_id] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
12/1/07 What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:
If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4). If my date parameter is 7/10/07, the charges would be 18 (7+6+5)
I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:
Code Block
SELECT a.DayCount, a.Sales, SUM(b.Sales) FROM Sales a CROSS JOIN Sales b WHERE (b.DayCount <= a.DayCount) AS RunningTotal GROUP BY a.DayCount,a.Sales ORDER BY a.DayCount,a.Sales Here is the result set i'm looking for:
Charges
Date
1
1/1/07
2
2/1/07
6
3/1/07
9
4/1/07
12
5/1/07
15
6/1/07
18
7/1/07
21
8/1/07
24
9/1/07
27
10/1/07
30
11/1/07
33
12/1/07 Each date's charges are a culmination of 3 months worth of charges.
Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!
I have a table that has a DATE field named. AccountingDate that is in the format YYYY-MM-DD. It's not a VARCHAR field. I simply want to convert this date field into the format MM/DD/YYYY and call it New_Accounting_Date.
I've played with various combinations of CAST & CONVERT but haven't been able to get it to work.
Below is my latest effort which returns the error:
Incorrect syntax near the keyword 'as'
What code would work to return a MM/DD/YYYY value for New_Accounting_Date?
Select GLBATCH.AccountingDate, convert(GLBATCH.AccountingDate as date),101) AS New_Accounting_Date from GLBATCH
I am creating a report using report builder and I am using several fields from various entities. Is there a way to sort the data in the report using a field that is not being displayed in the report? The "sort and group" option lists only fields that are selected for output in the report... but is there a way to do sorting using a field that is not displayed in the report?
OK I have a report that needs an interactive sort on a calculated field. I get the message: "Report items cannot be used in sort expressions"
That's the whole reason we purchased SS*S and are putting up a Data Warehouse, so we can rank and analyze our data. Surely there is a way to do this?? Thanks for any advice!
I have a date and time field in the following format dd/mm/yyyy hh:mm:ss:mmmm...I only want to retrieve the date in the following format dd/mm/yyyy When I use the following though it retreives it in yyyy-mm-dd format.
I have a requirement as follows: in my DB, i have a column in which i loaded the date data as dd.mm.yyyy. the column data type is string. now i want to change this value to YYYY-MM-DD format. Please help how to chaieve this.
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up ArtNo March <=March 1210 20,500 50,900 1220 21,200 64,000 1230 15,400 40,300 ... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
I've created an SSRS report in Report builder and I'm displaying it in SharePoint 2010. What I would need to do is to sort according to my totals -column. My report structure is as follows:
The first and column second columns are the ID and the name of a customer. There are over 35.000 consolidated customers in the database.
The third column is the column which I would like my report to be sorted upon. It gives the sum (Amount_EUR) of all different productlines sold to the customer.
The fourth column in this report design model is the product groups sold to that customer. There over twenty of product groups visible when Running the report.
This is what my report looks like when finished (you'd have to scroll a lot to the right if I took the whole of the report so I cut only a part of it to be visible):
I tried to add a sorting option from the reports Tablix properties and adding this code as the sorting function:
=SUM(Fields!Amount_EUR.Value)
But I'm getting an error: Â "A sort expression for the tablix 'Tablix1' includes an aggregate function. Â Aggregate functions cannot be used in data row sort expressions."
Ok, so no aggregate functions aren't allowed to the sort.
If I just put:
=Fields!Amount_EUR.Value and sort by that from Z to A(biggest to largest) it doesn't have any effect. The report is still sorted alphabetically by the ParentID.
I've also tried some other code bits, but they all seem to be saying that I couldn't put an aggregate function into the sort expression....but my Total -column is already calculated with a Sum -function....
Is it possible sort my report based on that Total -column? If yes, how? I'm using SSRS 2008
I have a drilldown report includes three groups. I add the last group for pagination. But the details in each page doesn't accord to I specified and the detail record number is different in different page.
Another issue is the interactive sort always sort in the first page scope. I set the data region or grouping to the table, and evaluate expression scrope to Detail scope.
