Hi, I have setup start date and end date parameters, however when my select gets the results from between the dates, if the dates are both set to today, no results are produced. I think its because I need to add a day to the end date parameter. How can I do this?
Thanks, Dan
Hi,
I am using a simple MDX like that :
SELECT NON EMPTY { [Measures].[NbAnnulations] } ON COLUMNS,
NON EMPTY { strtoset(@toto) } ON ROWS
FROM [TDB MENSUEL]
@toto is my parameter which contains two values :
[DIM ORG].[Zone].ALLMEMBERS (default value in the parameter)
[DIM ORG MASTER].[Zone].ALLMEMBERS
My problem is the dataset which works in these two case for the measures but not the dimensions.
To resume, first in my parameter I select the default value, and I have in my dataset something like that
Zone NbAnnulations
Paris 10
Madrid 30
NY 05
....
Then i select the second value in the parameter and i have
Zone NbAnnulations
25
22
08
So My dimension "zone" is not filled and not empty....I have an error message like : [rsMissingFieldInDataSet]
But my measures are correct in these two cases....
So I just want to used dynamically two dimensions with a parameter selection. My two dimension have the same name attribute "zone".
Do you have an idea or a solution for that ?????
Regards,
Slednet.
Hi, I have 3 parameters on my form. StartDate (datetime), EndDate (datetime) and CompanyName(string). The default values are: StartDate (Non-queried) 1-1-2005, EndDate (Non-queried) 1-1-2008, CompanyName (From query) DataSetBelow, Value field (AccountFamily):
SELECT DISTINCT AccountFamily
FROM CallDataRecords
The table on the form contains the following DataSet:
SELECT Salutation, InboundTimeMS, OutboundTimeMS, ModifiedOn, IsRightParty, AccountFamily
FROM CallDataRecords
WHERE AccountFamily = @CompanyName
AND ModifiedOn
BETWEEN @StartDate AND @EndDate
The error I get is: "Query execution failed for data set (one directly above)".
"Must declare the scalar variable "@CompanyName".
Can anybody shed light please?
Thanks, Dan
Why does this not work? <asp:SqlDataSource ID="employeeSource" runat="server" ConnectionString="<%$ ConnectionStrings:mainDB %>" DataSourceMode="DataReader" ProviderName="System.Data.SqlClient" OnSelecting="OnSourceSelecting" SelectCommand="SELECT * FROM Employees WHERE ID = @employeeID"> <SelectParameters> <asp:Parameter Name="@employeeID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>In code behind: protected void OnSourceSelecting(object sender, SqlDataSourceCommandEventArgs args) { // THIS LINE THROWS EXCEPTION: An SqlParameter with ParameterName '@campaignID' is not contained by this SqlParameterCollection. args.Command.Parameters["@employeeID"].Value = 3; // In future, will use dynamic value. } If, instead I do this: <asp:SqlDataSource ID="employeeSource" runat="server" ConnectionString="<%$ ConnectionStrings:mainDB %>" DataSourceMode="DataReader" ProviderName="System.Data.SqlClient" OnSelecting="OnSourceSelecting" SelectCommand="SELECT * FROM Employees WHERE ID = @employeeID"> <SelectParameters> <asp:QueryStringParameter Name="@campaignID" QueryStringField="id" /> </SelectParameters> </asp:SqlDataSource>(Of course I call page with EmployeePage.aspx?id=123)I get the exception: Must declare the scalar variable "@employeeID".
View 5 Replies View RelatedI have this question in one gridview: InsertCommand="INSERT INTO [Member] ([IC], [FirstName], [LastName], [Gender], [Birth], [Telephone], [Mobile], , [Address], [Postcode], [TimeOfRegistration]) VALUES (@IC, @FirstName, @LastName, @Gender, @Birth, @Telephone, @Mobile, @Email, @Address, @Postcode, @TimeOfRegistration)"If I want to insert into TimeOfRegistration with "System.DateTime.Today",can I have some expression to replace the "@TimeOfRegistration" in the SQL query? (<%# %>???Maybe?) If cannot, in the parameter control below, can I put it this way to achieve it? <asp:Parameter Name="TimeOfRegistration" Type=DateTime DefaultValue="<%# %>" />If so, how to put it? Thank you very much!
View 4 Replies View RelatedHi, I need help please.
I have two tasks.
1. SQL Execution task to output a variable which must be a datetime value "2007/12/31 00:00:00.000"
Variable name: Test
Value Type: Datetime
Value: 2007/12/31 00:00:00.000
2. Then in my OLEDB source i want use the parameter:
I have setup the parameters for QRY = 0 (Test)
QRY: WHERE WT.ApplyDtm = ?
I have two problems to this:
1. How do i know that my first task is bring back a value of datetime
"2007/12/31 00:00:00.000"
2. In my QRY i have to put the parameter within '' , I tried '?' but
it maons.
The QRY should run: WHERE WT.ApplyDtm = '2007/12/31 00:00:00.000'
Please Assist!
Regards
I'm using a sproc to insert the time (Now()) into a datetime field. Somehow the time "seconds" are not making it into the field. For example, the variable grabs the value of Now() - "9/16/2005 01:58:15 AM" to insert into the field. But when I view the records, the datetime value is "2005-09-16 13:58:00.000".What am I doing wrong?Thanks for your help!LynnetteHere is a snippet of the code and the sproc:Dim recDate As DateTime = Now()cmd.Parameters.Add(New SqlParameter("@DELastChg", SqlDbType.DateTime)).Value = recDateALTER PROC usp_AddTmpFLSAfromPT @dtWdate datetime, @whereString as varchar(255), @DEuser as varchar(30), @DELastChg datetime
AS
DECLARE @strSQL as varchar(2000)
SET @strSQL ='INSERT INTO tmpFLSAEmpInfo( Emp_Number, PT_ID, Emp_Division, Emp_Dept, --Emp_DeptInfo, Emp_Supervisor, Emp_Location, Emp_Union, Emp_SG, Emp_Shift, DEusername, DELastChg, WrkDate)SELECT Employee2.Emp_Number, Employee2.[ID], Employee2.Division, Employee2.Job_Dept_Code, Employee2.Job_Supervisor, Employee2.Loc_Name, Employee2.[Union], Employee2.Sched_Group, Employee2.Shift, ''' + @DEUser + ''', ''' + CONVERT(varchar, @DELastChg) + ''', ''' + CONVERT(varchar, @dtWdate) + '''
FROM Employee2WHERE ' + @whereString
EXEC(@strSQL)
We can use LIKE '% ...%' for character string search but how can we do the same thing to a datatime parameter like '2013-09-20 13:34:43.098'?
View 12 Replies View RelatedHi everyone
I have been reading another thread about this subject, but didn't get much useful info from it.
I have a datetime parameter on my report that is set to today() by default.
I use the User!Language to ensure that dates are formatted correctly (my end-users are Danish, Spanish, English)
Everything works fine on the preview tab, but when I access the deployed report through a link to the report server on a HTML-page the formatting of the date becomes US-style. This leads to further problems, as the report can not be processed because another format is expected (probably due to the User!Language settings on the report - in my case Danish).
I need to find a solution, that does not remove the dynamics of being able to run in different language settings (I saw in the other thread that one solution could be to hard code a statement with a specific language setting - that is not an option for me).
Hope someone can help
I have drop down Date Range(Last Month,Current Month and Custom)..
We need to set the values in our From Date Report Filter and To Date Report Filter.
We gave the Date Parameter Value in Condition to the Date Range Selected Value.
But the value always remain as on First Load of Report.
Is there any way i can set values in datetime text box..
.
Hi,
I have a report with datetime parameter which is required to be optional (there should be an option not to select this parameter which would cause in not narrowing result of this report by this parameter).
I used datetime parameter (to have the calendar control for selecting date) for which I enabled NULL value. The result is that next to this parameter there is a checkbox "NULL" which allows to send NULL as a value of this parameter. Everything is fine except for the prompt: "NULL"... Is there a way to change it? I would see there something like "Empty" or "Not selected", which would be understandable for users of the report.
Or is there any other way to send an empty date as a value of this parameter? (Setting some unusual value like 1/1/1900 for empty date is not sufficient as the empty value shouldn't appear in the parameter control).
Thank you,
Michal
DROP TABLE TestTable
CREATE TABLE TestTable (
StID tinyint NOT NULL ,
StDate datetime NULL ,
Amt float NOT NULL CONSTRAINT [DF_TestTable_Amt] DEFAULT (0),
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[StID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
/*
And After i'll create insert SP useing 'CREATE STORED PROCEDURE WIZARD'
Sp name:'insert_TestTable_1'
And Pass Some Parameter values
*/
Exec insert_TestTable_1 1,getdate(),50.25
--gives Error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
waht is that problem need help..
Hi all,
I created a stored proc that has input datetime parameters (begindate and enddate), I tried the command: exec storedproc '20060320'
in the query command part in Microsoft SQL Server Management Studio and
it works but when I try to create a report dataset using the stored
proc and execute it using value 20060320 it does not work. I even try using '20060320' and "20060320" as the value and it did not work also. I received the following error:
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
Failed to convert parameter value from a String to a DateTime.
------------------------------
ADDITIONAL INFORMATION:
Failed to convert parameter value from a String to a DateTime. (System.Data)
------------------------------
String was not recognized as a valid DateTime. (mscorlib)
------------------------------
BUTTONS:
OK
------------------------------
Anyone have any idea on how can I solve it or go about it?? Thanks in advance.
Daren
I am trying to set the default date in my datetime parameter to yesterday's date, so the user does not have to select using the datepicker calendar. I am using Visual Studio 2005.
I put the following code into the Report Parameters non-queried default values function line, but it adds the timestamp to the date.
=DateAdd(€?d€?, -(WeekDay(Now()))+1, Now())
How can I get yesterday's date to default but without the timestamp?
Thanks!
Marissa
I have datetime parameter but SSRS used mm/dd/yyyy format.
How can I change to dd/mm/yyyy format ?
in dataset I used convert(datetime,@dateparam,103) but I get error if I select for example 14/03/2008
"The value provided for the report parameter 'dateparam' is not valid for its type
Hi All,
I have a problem while trying to set "NULL" value to my datetime parameters.
I have two datetime parameters (AllowNull=True) on my report. (BeginDate and EndDate)
it works fine in preview mode in reporting design.
The problem occurs, if I try to set an emtpy value to these parameters in my asp.net application.
The code looks like;
Dim repParams(1) As ReportParameter
repParams(0) = New ReportParameter("BeginDate", String.Empty)
repParams(1) = New ReportParameter("EndDate", String.Empty)
-----
ReportParameter method requires String parameters. If user set any datetime value, I am sending "String.Empty" value to my paramaters in order to set them as NULL. But I am getting "rsReportParameterTypeMismatch Error."
How Can I set NULL value to these parameters from asp.net application?
Thans alot in advance.
I'm developig an ASP.NET application and I'm trying to pass a datetime parameter into my stored procedure. The procedure is very simple "SET @sqlMessage = @parameterPassed" I have the @sqlMessage setup as out OUTPUT parameter and I'm trying to see what I get. So far no go. The datetime value is of type string, and it was retrived from a datetime column in an SQL database. Any ideas.
Thanks.
Hi
I'm wondering about datetimes modification while debugging. I've a backup of a db debugging searching for some answeres. Problem is I have to asume it's one month back in time, so when I get the value from some date functions, if I after that modify the date value displayed for the parameters (debugging with vs pro 2005) will it be my modified values that it uses when those variables are used? It seams like it, but I'd like to be 100% sure.
Thanks
Hi All:
I create a report with a DateTime parameter, i would love to use DD/MM/YYYY this format, but it doesn't work . it works when i change to usa datetime format ,YYYY-MM-DD,
any helps are appreciated.
thanks
Nick
I have a .net 2.0 web form with a report viewer control (RS 2005) and have hard coded the report server and path. The report has two date time parameters that have a default value set up. When the user clicks the calendar icon to change the date range, the page refreshes without displaying the calendar... I don't remeber having this problem in the past. The parameters function fine in Report Manager. Any idea what could be causing this?
Hi all,
I am using stored procedure dataset. i tried filtering the dataset values with the parameter of type datetime.I am able to view and select multiple datetime value from the list. but its not filtering the values.
i changed the parameter datatype to string but it works only for the first value
(parameter. <pname>.value(0)). if i filter with the array value (parameter. <pname>.value) am getting error (Failed to evaluate Filter expression).
I guess the problem with datatype. Pls help me if anyone have idea about this.
Hi Everyone,
The SSIS is behaving very strange to me in the below scenario (Do excuse me if that€™s the behavior, I might not be knowing).
What I€™m doing is,
Step 1: Select Max(Date_Created) from Table1 -> I€™m storing the result in a parameter named Max_Date_Created.
The above one returns the value as : 2008-01-01 10:10:10.874
Step 2: Update Control_Table Set Last_Updated_Date = ? -? I€™m passing the parameter for updating the control table.
Step 3: Select Last_Updated_Date from Control_Table
To my astonishment, it returns the value as 2008-01-01 10:10:10.000. Shouldn€™t it be 2008-01-01 10:10:10.874?
The above implementation taking me for the duplication entries in the table. How to get milliseconds also? Why it€™s rounding?
Any suggestions please?
Thanks
Selva
I have a visible DateTime parameter on the Report that I would like to make optional.
When I open up the report and try to run it without specifying the date I get a validation error that parameter is required. If I set default value to null - it works, but I have to check for both '' and NULL in my query:
StartDateTime >= @StartDate OR @StartDate='' OR @StartDate IS NULL
I tried setting default parameter using expression
=''
but I get an error that
'Default Value' of parameter 'StartDate' doesn't have expected time.
So the question is can I set DateTime parameters' default value to blank?
Issue 1:
I have a report parameter StartDateTime. I set the default value to Now(). When I go to preview, the StartDateTime parameter is empty and its been locked. I am not even able to set it to different value in preview.
Can anyone help me how to set the datetime parameter to default value(Now).
Issue 2:
I have a stored procedure which takes StartDateTime parameter. Whenever the report refreshes using autorefresh interval, the startdatetime should default to Now. Right now the startdatetime defaults to whatever the value is there before i hit view report. how to do that using stored procedure.
Thanks fro your help.
Hi,
I am having a problem with the DateTime parameter option in SQL 2005 reporting services.
Note that Server, workstation and database are set to British/English (UK) date/time localisation (dd/MM/yyyy) for example 25th December 2005=25/12/2005.
I create a simple query with a parameter on a datetime column and set the report parameter to Data type: DateTime in the report parameters dialog.
On preview or after deployment the following behaviour is observed.
Selecting 1st February 2006 in the datetime picker control populates the date parameter entry field with
01/02/2006 = 1st February 2006 (the correct UK format as expected)
When View Report is clicked the datetime parameter field magically changes to
02/01/2006
So forcing the datetime values to US format MM/dd/yyyy, the report runs OK but does not return data restricted to the required date.
If we select 25th February 2006 the datetime parameter field populates with
25/02/2006 = 25th February 2006 (the correct UK format as expected)
When View Report is clicked the datetime parameter changes to 02/01/2006 and an error is displayed
An error occurred during local report processing.
The value provided for the report parameter X is not valid for its type.
Is there a solution to this?
Thanks,
Neil
Hello,
We have noticed that the calendar months in the Pop-Up Calendar option, based upon month word length, will shift in size and this will cause the left and right arrows to shift. For example, 'May' is much shorter than 'December'.
My user is complaining because she cannot quickly move from one month to another without having to reposition the mouse over the navigation arrow.
Is the Calendar feature working as designed?
Thank you,
Hi,When I pass a date time parameter the stored procedure takes about 45seconds, when I hard code the parameter it returns in 1 second. How canI rewrite my stored procedure?@createddatelower datetimeWHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,@createddatelower )AND dbo.tblCaseHistory.eventdate < dateadd(d,-6,@createddatelower ) (45seconds)vs.WHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,'11/15/05') ANDdbo.tblCaseHistory.eventdate < dateadd(d,-6,'11/15/05') (1 second)thanks for your help,Paul
View 5 Replies View RelatedI have a datetime user inputted parameter on a report. I want to display it on the report without the time part.
ie. it displays
4/8/2007 12:00:00 AM
I want just to display the date part
4/8/2007
Is this possible?
I'm creating a report with a Datetime parameter. There's no available values, so the calendar control comes out automatically when I run/preview the report. Now, when I select the date in my calendar control, say for example I select 2/5/07 (2nd of May) and run the report, it gives me data from 5th of Feb. Can anyone help me with this date format problem ???
Not sure if it's related to my problem, I did edit the language tag in the XML source code of my report to have en-AU so that the dates field is showing in the correct dd/mm/yyyy format.
Hi,
I wanted to know more about validation of SSRS parameters. I have a simple report which has a parameter called startdate of DateTime datatype. The datetime parameter in SSRS takes manual input as well. So, the user can enter any junk value. I want to ensure that the input parameter is in correct format and I want to display an error msg when the format is incorrect. My report has the following VB code for validation:
Public Function Validate( ByVal startdate As String) As Boolean
If IsDate(startdate) = True Then
Return True
Else
Return False
End If
End Function
And my report has a textbox which has the expression property set to;
=Code.Validate(Parameters!startdate.Value)
the textbox on the report has to display if the entered date is valid or not.
But, when i enter an erroneous date, SSRS doesn't render the report and throws a generic error. This happens even before the code written for validating the parameter executes.
Also couldn't find a way to disable the manual input for the datetime parameter. Even that would solve the problem.
Another alternative was to make the startdate parameter as string, but i want the calendar control button to be provided for the user.
Hi I am having this problem. I have created a stored proc that uses dynamic sql. Now this works fine when I use it in Query Analyzer.
Now when I try to use this in Reporting services it gives me the error:
'
Failed to generate fields for the query
Check the query syntax or click refresh on query toolbar.
Failed to convert parameter value from a string to a datetime
'
My sotred proc is as:
<quote>
-------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[rptDetailedAuditsIssued]
(
@param1 varchar(50) ,
@param2 varchar(50),
@param3 varchar(50),
@param4 datetime ,
@param5 datetime
)
AS
begin
declare @strSQL varchar(500)
set @strSQL = 'SELECT * FROM v_DetailedAuditsIssued WHERE '
if @param1 <> ''
Begin
set @strSQL = @strSQL + 'strRegion = ' + @param1 + ' AND '
End
if @param2 <> ''
Begin
set @strSQL = @strSQL + ' strTaxTypeCode = ' + @param2 + ' AND '
End
if @param3 <> ''
Begin
set @strSQL = @strSQL + ' strAuditType = ' + @param3 + ' AND '
End
set @strSQL = @strSQL + ' (dtmIssuedDate BETWEEN '''
+ CONVERT(nvarchar(30), @param4, 101) + ''' AND '''
+ CONVERT(nvarchar(30),@param5, 101) + ''')'
exec (@strSQ
end
--------------------------------------------------------------------------------------------------------------------------
</quote>
When I run
rptDetailedAuditsIssued '','','','1/1/2000','1/1/2001'in query editor it runs fine. But gives me that error every time I run it in SSRS. I think I got this problem but in earlier cases I used to set the param values to null in Repservices and it worked but its not working this time. This time it stops complaining but does generate any fields.
Is it possible to pass a DateTime parameter from a master report to a subreport. I have managed to pass a string, but when i pass a DateTime parameter some error regarding its type is thrown. Please point me in the correct direction. Many Thanks.
View 5 Replies View RelatedHi guys,
I have managed to pass a string parameter form the main report to the subreport with no problems, but when i try and use the same method to pass a datetime type, i am issued with the following error messase:
"[rsErrorExecutingSubreport] An error occurred while executing the subreport €˜SubReportName€™: The value provided for the report parameter 'MasterDate' is not valid for its type."
I cant see why it work for strings, but not for datetime. Any suggestions will be very much appreciated