hi and thanx in advance
in a report, i am getting data from a stored proc which take to date parameter fromdate and todate which are passed from report.
user select dates in datepicker control
but problem is that it not returning any record
Can problem be in date format difference in report and sql server ?
Hi,
I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
Thanks !
JF
I am having report parameter end date I am showing the default value "5/21/2007 11:59:59 PM" in the end date paramter. And also I want to show date time format "(MM/DD/YYYY HH:MMS)" in the right hand side of the parameter. How to do this?
Thank You
Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.
I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?
Hi,
I tried to create parameters in my report. I need one date picker. So i select data type as datetime. in default values i selected as non-queried and in date time functions i selected =Today. But am unable to set current date. Please help me to solve it.
Regards
Nataraj.C
Hello,
I am using the calender parameter and I need to convert my data date format to the one that matched that is returned on selecting a date from this calender. Can you show me what this format is.
how can I convert my existing date format to this format. The existing date format is 2007-07-26 21:27:13.000
thank you
Kiran
I am working into a t-sql where i need to expand the date within a date parameter. For example:
Start Date = '1/1/2014'
End Date = '12/31/2014'
I want to expand this as:
01/2014
02/2014
03/2014
04/2014
05/2014
06/2014
up toÂ
12/2014
store procedure
@End_Date datetime
SELECT *　FROM table1 WHERE PROCESS_DATE <= @End_Date
@End_Date format： 1/1/2008
PROCESS_DATE format: 1/1/2008 12:45:01 PM
I can not get the date after running the procedure using the value/format above. I think the problem is that SQL convert @End_Date value to 1/1/2008 00:00:00:AM, which makes 1/1/2008 12:45:01 PM > 1/1/2008
Since I only need to compare the date (no need time), how can I resolve the problem from the C# code or SQL level? Is that possbile to make 1/1/2008 default to the max datetime like 1/1/2008 11:59:59:PM?
Hi,
My boss would like to see on our report a code that would pull the date 01-01-2008 to the present. They want to be able to run this report whenever they wanted to. What is the best way to create this code.
Could I just use my field, which is "clm_dout" to say Clm_dout >= or is there another type of code I should use?
Thanks
Hello ........
I have a DateTime parameter in my report.
I want the last date of the current month selected as default in the date parameter(datetime picker ).
Can anybody help me ?
Thanks
One of the fields in the table is of type smalldatetime.
When I select a date from the report parameter (a calender), the report produces an error: The value provided for the report parameter 'ValueDate' is not valid for its type.
Please note that if I use a report parameter of type string and enter 21 may 2007 the report works. But if a date is selected from the calender control i.e. 21/05/2007 the report produces the above error.
How is it possible to use the calender parameter without the report giving the error pls?
Thanks
Hello guys I hope everyone is doing well I have a question. I have a database thats an ADP. on one of the forms I have a command button that has a stored procedure in it that will allow them to search by TM# or name or whatever, (I have the code below) I would like them to be able to do the samething but by date rather then TM# or Name, how would I go about doing that with a datetime datatype??
thank you
ALTER PROCEDURE dbo.Search_ActiveConditionals
(@Enter_TM# int)
AS SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, INVESTIGATOR_COND, REASONFORCOND
FROM dbo.ACTIVE_CONDITIONALS
WHERE (TM# = @Enter_TM#)
Hi,
I have a situation where i need to have two parameters (startDate, endDate) and use them as filters for my query. But i don't want to have a stored procedure with these parameters. So how can i have the two date parameters as calendars to choose dates from?
Hi,I'm trying to run a select statement that takes includes an if/elseclause. I need to select the 'tran_date' between....if the current month is greater than 10 i.e. after OCT then thetran_date will be between '01-Oct' - plus current year or thetran_date is '01-Oct' plus previous year.and the current dateHere is my script so far:SELECT a.resource_code ASSOCIATE, a.tran_date START_DATE,b.description PROJECT_CODE, sysdateFROM actrans a, acactivity b, dual cWHERE a.resource_type = 'E'AND a.acct_category = 'TIME'and a.activity like '9-WW-357852%'and b.activity = a.activityand tran_date betweenif to_char(sysdate, 'MM') > 10)begina.tran_date = '01-Oct' & to_char(sysdate, 'YY')endelsebegina.tran_date = '01-Oct' & to_char(sysdate, 'YY'- 1))EndORDER BY tran_dateCan anyone help me out with this? I need to run it as a job, but notsure whether I should be using a stored procedure etc..Thanks in advance.George
View 1 Replies View RelatedHello Im having a date problem
I have a parameter called @Param_Daterange with the following values
Last 30 days =Today.AddDays(-30)
Last 60 days =Today.AddDays(-60)
My SQL has a chart which uses the following SQL
SELECT Sum(dbo.tbl_OMDQ_Aggregates.Value) AS SumOfValue, dbo.tbl_OMDQ_Aggregates.status_desc, dbo.tbl_OMDQ_Aggregates.DateRun
FROM dbo.tbl_OMDQ_Aggregates
WHERE (((dbo.tbl_OMDQ_Aggregates.SaleMonth) IN (@Param_SaleMonth))and (status_desc IN (@Param_ChurnType))) and dbo.tbl_OMDQ_Aggregates.DateRun > @Param_DateRange
GROUP BY dbo.tbl_OMDQ_Aggregates.status_desc, dbo.tbl_OMDQ_Aggregates.DateRun;
In visual studio the chart is working fine, however in IE the data getting returned is only a small subset off the entire data, ...my IE language setting is at English Aus, I also created a textbox so i can see what value the daterange parameter is passing and it seems to be in the correct format...the graph is also in the right day/time format of dd/MM/yyyy. However i have no idea which date is getting passed incorrectly, somewhere the date is getting switched to American format however what is outputting on the screen is in the right format...just not the correct records are returning
can anyone please help
Hi all,
I have a report that have 2 date parameters (start and end date). How would I check that end date parameter move be equal or greater than Start date?
thanks
Hi,
How to control the Datetime Parameter's format, such as I need the user input Date and Time, but default, the user can only select date, but can not input the time.
How to solve it?
Thank you.
I am sure others have ran into this issue.
But I need to have date parameters using the datetime type and I need to be able to pass the correct value to the MDX.
hi all
We are a multi site / multi language company so do not want to hardcode the date format - these reports will be on intranet
Using MS Sql - Reporting Services 2005
Using no formatting on fields = yyyy-mm-dd - is how it is displayed.
I added a @startdate and an @enddate parameter
I read up and saw this information:
Report properties set
'Language' setting to '=User!Language' to detect user
culture automatically
My regional settings are set to English (New Zealand)
but whatever I do I receive no data - (data is definately there eg when run without parameters) so it is obviously the dates incorrect.
Can someone give me some advice on what else to check.
Hi There,
I've only just starting working with RSS2005. I've started designing basic reports that pulls info from our HEAT DB (incident/change logging system).
One of the report requirements is to be able to pull reports for calls that were logged for specific day/date, using the calllog.recvddate field in the HEAT DB.
What i would like to know is how do i do this ? i.e. how do i setup the date query parameter so that the user can select a date from the calender picker that returns all calls logged for the selected date ? How do i link the date parameter to the calllog.recdvddate field ?
Thanks,
RSS2005 NOOB
Hello,
I have a report parameter StartDate. Properties are
DataTypeataTime
Prompt: StartDate
Default Values:
Non Queried : =NOW()
I set the default value to Now(). When I go to preview, the StartDate parameter is blank and its been locked & grayed out. I also tried
Today() and Globals!SystemTime but that does not work either. Is there any other solution to make this work?
Thanks
Raj
The date format in the 'weekending' textbox is 5/5/2007Here's what I've got:
With cmd.Parameters.Add(New SqlParameter("@SQLweekending", SqlDbType.DateTime, weekending.Text))the error message is:"Conversion from string "4/8/2007" to type 'Integer' is not valid."
Then, I tried:.Add(New SqlParameter("@SQLweekending", SqlDbType.DateTime, CDate(weekending.Text)))but, before I even tried it, it gave me a blue squiggly line, with an error message:"Error 2 Value of type 'Date' cannot be converted to 'Integer'"
How can I get this to work?
(This is prob. a really dumb question but it's driving me mad!!...)
I am using the Debugger in SQL Query Analyzer & want to set the value of a datetime parameter prior to executing the stored proc. The "Debug procedure" window allows me to specify the parameter values - but I can't get it to accept a datetime. The language is us_english & I've tried most ways if specifying the date - 01/02/2004, with/out quotes, 02 Jan 2004, as a full datetime, swapping day/month values etc etc. The procedure always fails immediately with: Invalid character value for cast specification.
Thanks.
I am trying to pull MTD and YTD sales for this year and last year.
I have this line to pull all invoices between two dates:
WHERE (invc_hdr.ivh_dt Between '08/01/2013' And '08/31/13')
But I want to be able to put the dates in without having to change the code each time. I want a window to pop up so I can input the date range. Is there a way to do this.
I am collecting a year parameter from my user and want to use that year as part of a complete date condition.
So I collect the year say '2003' from my user.
A condition must be applied so that the date must be after 01-APR-...and here is where i want to utilize the parameter @Year. So I can apply the same date condition depending on what year we are looking at.
I am currently using the following logic:
(DATEPART(yy, CANCELLATION_DATE) = @Year)
AND
(DATEPART(mm, CANCELLATION_DATE) >= 04)
AND
(DATEPART(dd, CANCELLATION_DATE) >= 02)
This causes problems as the date can be only Jan, Feb, Mar, Apr 01 or 02. What I need is to gather all dates prior to Apr 02, with the year paramter to follow.
We originally developed this querty using Oracle so idealy there would exist a pl-sql to t-sql conversion tool out there! Anybody know of one?
Here is how the pl-sql is structured:
CANCELLATION_DATE >= TO_DATE('02-APR-'||:"Year",'DD-MON-YYYY') ) ) )
Thanks in advance,
mpaskevi
I am having trouble adding a smalldatetime parameter @ScrubbedDate to my procedure and getting it to work with my report.
I get the cannot convert string to smalldatetime error.
Would like to restrict the dates users can put as parameters. For example, I have a report showing all orders in year 2007. I have exposed Start Date and End Date parameters. When users click the 'View Report' button, the report will filter for orders where attribute EnteredDate is >= Start Date and <= End Date.
I want to restrict the users from entering a start date greater than today's date. Would also like to restrict them from entering an end date that is less than the start date parameter. How can I do this?
TIA.
ElSalsero
How do I assign the current date to a DTS parameter using a script - the following fails:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.VisualBasic
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dts.Variables("RunDate").Value = Now.Date
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Error message:
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
Thanks.
Dan
I have a stored procedure that grabs the Region key from the user, and the date. But i want to set up 5 reports, for day of the week. So i want to make the second parameter a defualt parameter set by me in each report. which is better then having to create 5 stored procedures with the same info just different dates.
when i try to enter the same data i have in my stored procedure to get Mondays date, i get an error.
Here is what i entered in the report parameter default expression box for the date :
=DATEADD(dd, - 6, DATEADD(dd, 1 - DATEPART(w, GETDATE()), CONVERT(varchar, GETDATE(),
101))))
but i keep getting an error that says:
The Value expression for the report parameter €˜Daily_activity_statistics_datetime€™ contains an error: [BC30451] Name 'dd' is not declared.
Build complete -- 1 errors, 0 warnings
whats wrong with it? This works fine in the stored procedure for the where clause. Please help! this would save me alot of time.
Hi All,
I have to filter my report with the date parameter. Custom Dates are comming from the database.
I have to filter with the date just previous Date which user selected as a parameter.
As an example if parameter contains 2/04/2008 , 10/04/2008, 14/04/2008, 16/04/2008 and user is selecting 14/04/2008 then I want to filter the data from just previous parameter i.e. 10/04/2008
Ritesh Kumar
I have a series of "Payroll Periods", called S1 thru S26 for the year. I want to create a parameter call "Pay Period" and have the user select a single pay period, i.e. S13. Elsewhere I know the beginning and ending date for each pay period, but it is in another dataset. So if the user selects S13 and I know the 13th pay period is April 1st thru April 15th, how do I use those dates as my beginning and end dates for my SQL query statement to return only the data from the selected pay period. The data itself has dates and not pay period references.
View 1 Replies View RelatedHi,
I have some difficulties to format a Date value in my reporting service.
As you know you can use this kind of expression:
=DateTime.Parse(Parameters!ReportingDate.Value).ToString("dd/MM/yyyy")
But it does not work, and when I just want to use the format property of the textbox content such as "dd/MM/yyyy", it displays "dd/MM/yyyy", not the real formatted data, this property works for "=Today()" but not for parameters and fields. What is the diffrence?
Have you an idea to format a Parameter/Field of Date type such as dd/MM/yyyy?
Many thanks!
I have a report that runs transcripts for people. I have a date parameter setup along with last and first name. The date parameter field is used to specify what year (ex. 1/1/2006 - 12/31/2006) to pull the information for.
Now..I have a text box at the bottom (Footer) of my report that says something to the affect of "Official transcripts for 2006". There will be times when I have to run the report using a different year other then "2006" in the date parameter field. How do I have the text at the bottom change to reflect the year I'm reporting on? If I use dates say in 2004 I need the text at the bottom to reflect that year and not 2006. Can this be accomplished?
Thx,
Bill