Reporting Services :: Date Control Does Not Allow Double Click To Move By Month Or Year
Aug 21, 2015
I have a SSRS report developed in Visual Studio 2013 and using SQL server 2012. It has a date control based on parameters which works correctly in the development environment. I can select by day AND I can move from date to date by clicking on the month name to be able to move by month or double clicking to be able to move by year.
When the report is deployed to Internet Explorer 11 or Firefox I can only, click back and forward by one day at a time and I can not double click the date control to move by month or double click to move by year. I can type the dates into the parameter box and get the correct result. I have set the compatibility view settings in IE for my server.
I am trying to use one dataset rather than two and was hoping to then filter the data via a table or matrix.
This is my dataset
SELECT Practice.ibvStaffCategorisation.StaffId ,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes ,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes ,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableAmount) AS Sum_ChargeableAmount ,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableAmount) AS Sum_NonChargeableAmount ,Practice.ibvStaffTotalsCL2Y.Period
[code]....
I would like to display two rows of data for each StaffId one representing the current period and the other all periods to date so the table would look something like below.
The above shows two separate StaffId figures the first line for each shows non chargeable and chargeable for the current period and the second line a total of all periods in that year.
I have managed to get the first row to display only figures from the current period by using a filter however it also applies the same filter to the second row in the group. I have also tried to group the rows but am drawing a blank.
How to show the CurrentMonthanddateandyear in my report header in ssrs?
1.How to show the currentdateandMonthyear exmple date format like June 29 2015 on my report header.
2.How to change the report rdl name with the same name like EmpUpdatedreportJune 29 2015.rdl ,it is possible to create and change the rdl file name with the current dateandmonth.
I want to get a running total. But its a bit tricky. I have data depending on ID, YEAR, Month, Value like the following.
ID, Year, Month, Value 36002084 2014 Jan 8391 36002084 2014 Jan 0 36002084 2014 Jan 3440 36002084 2014 Jan 0 36002084 2014 Jan 548 36002084 2014 Jan 109 36002084 2014 Jan 125
I wrote the Query below
select [UEP ID],[Year],[Month],sum([Total VtM EUR]) 'Running Total' from RegData where [UEP ID]=36002084 and [Year]=2014 group by [UEP ID],[Year],[Month]
I got this output
36002084 2014 Apr 887620 36002084 2014 Aug 789440 36002084 2014 Dec 528453 36002084 2014 Feb 606627 36002084 2014 Jan 331613 36002084 2014 Jul 681314 36002084 2014 Jun 330384 36002084 2014 Mar 1044301 36002084 2014 May 671818 36002084 2014 Nov 465576 36002084 2014 Oct 590147 36002084 2014 Sep 527861
Now I need to get a output like this,
36002084 2014 Jan 331613 36002084 2014 Feb 938240
A running total depending on the ID, Year and Month. Although I showed this for two steps I need to get it from one query.
Question is - To get a running total based on ID, Year, Month. If I do the normal group by and sum query I could get it for all the months for each id. But need to then add January value to February and updated February value.
I have to color format the bars in set of 4 colors. I have used SWITCH statements for other charts and it worked. But here in this case since it is a date field, I am getting error.
I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:
And the resultant string is: dd/mm/yyyy, for example 30/08/2004. But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?
I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?
Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas?
taskname| jan|feb|march|april|......... [taskname value]|{now i have merged all cell till the end of months row.}
I have another value (start date value). I want date to be displayed below that particular month as i have merged that all cells so its is causing me some problems.
In SSRS report I have 2 parameters start date and end date, so when somebody is selecting end date more than a month(if today date is 22 then they are selecting aug 23) then it should give error message to user like "Date out of range".
Hello All, i have three textboxes in a page and i want fill those textboxes with the date, month,year respectively..... i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..? Any help.. Thanks.. Anne
I need to see if a record has been inserted into a table, and the condition is if the record has been inserted for the current year and month and day, if not, I can insert a new row.
SO I need to do:
IF NOT EXISTS (SELECT * FROM myTable WHERE Created = @Created)
But it has to be comparing the year,month and day (excluding the time part of the datetime type).
My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)
I'll appreciate your help.
YearMonthDerived Date ---------------------- 2004101-Jan-2004 2004201-Feb-2004 2004301-Mar-2004 and so on ....
Many thanks in advance. I'll appreciate your help P.S. Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them
My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)
I'll appreciate your help.
YearMonthDerived Date ---------------------- 2004101-Jan-2004 2004201-Feb-2004 2004301-Mar-2004 and so on ....
Many thanks in advance. I'll appreciate your help P.S. Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them
I have been spoiled by some report writing tools that have intrinsicfunctions like Last Year Month-to-date. I'm looking for a way to emulatethis in SQL Server now with my fields that are date/time.I'm thinking I need to develop a user defined function to accept a dateinput parameter, but I don't know where to start.Help/Examples appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***
I have a query for which in the where clause i use where Year(openDate) = Year(GETDATE()) and Month(OpenDate) = Month(GETDATE())-1 which would give me the data i needed for this year last month. However if i run this query on Jan 2015 or Jan 2016, this query would fail.
how to modify my where clause so that it runs regardless of even if its Jan ?
In my table I am using varchar to save a date like this 2-dec-2011. Now I have a requirement where i need to select employee who where present in a month so for that I need a query so that i can pull out all the employees who are present in dec like :
Code: select * from emp_attendence where date = 'dec-2011'
I would like to take the 3 values of populate one field (BIRTH_DATE) with these combined values to look like this (19851221) BIRTH_DATE is an 8 character field in datetime format.
The code
UPDATE DOB SET BIRTH_DATE = (dob_year+dob_mo+dob_day)
I am working with a SQL Server database that was set up to store the year, month, and day in separate columns, rather than use a single column for the date. I plan to change this so that we store dates and times using the datetime data type. Until then, for now, I need to write transact-SQL select statements to concatenate the year, month and day to create a date that can be displayed in the results window in the format yyyy/mm/dd.
I can't seem to find any built-in function in SQL Server that will let me do this. Of course, using Excel or Access, I can use the DATE() function to reconstruct the date into yyyy/mm/dd. Is there a way to reconstruct the date into yyyy/mm/dd in SQL Server?
Hi All, I have a sample data like this..I have added the cases for the particular worker in the table..My question is that when displaying the cases for the particular month...the date should not be displayed..instead of date 2008-04-30 I have to display...like April 2008...if the date is 2008-10-20...it should display as October 2008...can anyone please help me with this... 312 KRISTI WHITE 865400 2008-04-30 2 312 KRISTI WHITE 1000264311 2008-04-30 3 312 KRISTI WHITE 1000430815 2008-04-30 1 312 KRISTI WHITE 1000660614 2008-04-30 1 312 KRISTI WHITE 1002371318 2008-04-30 2 312 KRISTI WHITE 2003722520 2008-04-30 4
I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example, it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:
Operand type clash: date is incompatible with int
Why is this error being returned?
Here is my function:
CREATE FUNCTION dbo.zEOM_LY_D(@Input Date) RETURNS date AS BEGIN; DECLARE @Result date; SET @Result = convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101) RETURN @Result; END;
i have some classes that I want to group by month/year (note:i dont need the day of the month) how do i wirte my sql so it only gives me the dictinct groups month/year of the classes I have so that it comes out like so.. 11/2006 12/2006 1/2007 3/2007 i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value. dbo.classgiven.classdate date of the class.thank you all SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/" + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL)", conNorthwind )
