I am trying to pass value to the parameter field which i have created in crystal report 8. the parameter field is of type date. but i get error. the code is as follows.
.Reset
.ReportFileName = App.Path & "\reports\vaccine_flow_ledger.rpt"
.Connect = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=APACCESS"
.DiscardSavedData = True
.ParameterFields(0) = "SDT;" & #1/7/2005#
.ParameterFields(1) = "EDT;" & #1/7/2005#
.Action = 1
End With
Please help me to resolve this problem.Try this
.ParameterFields(0) = "SDT;"# & 1/7/2005 & #"
.ParameterFields(1) = "EDT;"# & #1/7/2005& #"|||I tried that one,but i didn't work.
but
.parameterfields(0) = "sdt;" & "date(2005,1,1);"
works finesql
Showing posts with label date. Show all posts
Showing posts with label date. Show all posts
Friday, March 30, 2012
passing parameter from stored proc to crystal
Im using crystal 11.5.
Ive written a stored proc in my sybase DB which has 2 parameters, one for start date & one for end date. When I execute the proc on the DB I am prompted to enter values for my parameters.
Using an ODBC connection I am able to find my stored proc through crystal, however when I choose my proc, crystal is not prompting me to enter values for the parameters.
In the past Ive used crystal (versions 8 & 9) & when I select the stored proc, it always prompted me to enter values for the parameters.
What am I missing here? Any help would be greatly appreciated.check the sql statement in CR, also, are you using a Command ?,
if not, that may be the way you want to go. Lastly, you could just
add the parameter in CR and have CR prompt you for the date range.|||According to the crystal help, once you choose your stored proc from the list & add it to your report, crystal should prompt you to enter parameter values.
How do I get crystal to prompt me when I add the stored proc to the report?
Ive written a stored proc in my sybase DB which has 2 parameters, one for start date & one for end date. When I execute the proc on the DB I am prompted to enter values for my parameters.
Using an ODBC connection I am able to find my stored proc through crystal, however when I choose my proc, crystal is not prompting me to enter values for the parameters.
In the past Ive used crystal (versions 8 & 9) & when I select the stored proc, it always prompted me to enter values for the parameters.
What am I missing here? Any help would be greatly appreciated.check the sql statement in CR, also, are you using a Command ?,
if not, that may be the way you want to go. Lastly, you could just
add the parameter in CR and have CR prompt you for the date range.|||According to the crystal help, once you choose your stored proc from the list & add it to your report, crystal should prompt you to enter parameter values.
How do I get crystal to prompt me when I add the stored proc to the report?
passing null date value to database
how can i pass null value to database? date is not required field in my database. i can pass default date but i think default date is not good in my case as it is DOB of a customer.Hi there,
This is the function where I have passed the dates but i can't assign the System.Data.SqlTypes.Null to date variable.|||OK, I believe I have misled you. You should be using System.DBNull. Let us know if this works better for you.
Check out the DBNull class. :)|||DBNull.Value should do the trick ...
But don't forget when reading back from the table to check for the NULL value|||well, i tried that it says can't convert System.DBNull to datetime. i also used Convert.DBNull but it shows error at run time if the value isn't provided. well, it seems like i have to pass DBNull.Value directly but in my case i have to assign it to variable coz i am using stored procedure and using SqlHelper class.
what about integers though? is it the same thing?|||I think you should be able to use System.Data.SqlTypes.SqlDateTime.Null.
Terri|||hi terri,
it still says "value of type System.Data.SqlTypes.SqlDateTime.Null cannot be converted to 'Date'"|||Can you please post the relevant code?
Terri|||
Dim commDate As DateTimeIf tbCommDate.Text = "" Then
'commDate = New DateTime(1900, 1, 1)'can't use this statement
commDate = System.Data.SqlTypes.SqlDateTime.Null
Else
commDate = CType(tbCommDate.Text, DateTime)
End If
I pass this "commDate" to the stored procedure. i am using SQLHelper class.|||We need to also see the code where you are telling the SQLHelper class the data type of the commDate parameter.
Terri|||
Public Shared Function ProspectiveInsert(ByVal agentId As Integer, ByVal tripId As Integer, ByVal institution As String, ByVal nationality As String, ByVal title As String, ByVal dob As DateTime, ByVal sex As String, ByVal elicos As String, ByVal course1 As String, ByVal course2 As String, ByVal fname As String, ByVal mname As String, ByVal lname As String, ByVal osStud As Boolean, ByVal osAdd As String, ByVal osPhone As String, ByVal osMobile As String, ByVal osFax As String, ByVal osSuburb As String, ByVal osPostcode As String, ByVal osCity As String, ByVal osCountry As String, ByVal add As String, ByVal phone As String, ByVal mobile As String, ByVal fax As String, ByVal suburb As String, ByVal postcode As String, ByVal city As String, ByVal country As String, ByVal email As String, ByVal ugQual As String, ByVal ugIns As String, ByVal ugYear As String, ByVal seconQual As String, ByVal seconYear As Integer, ByVal seconIns As String, ByVal pgQual As String, ByVal pgIns As String, ByVal pgYear As Integer, ByVal appStatus As String, ByVal appNotes As String, ByVal appDate As String, ByVal ielts As Double, ByVal engRemarks As String, ByVal coeDate As DateTime, ByVal coeRemarks As String, ByVal offerDate As DateTime, ByVal offerRemarks As String, ByVal recAddDate As DateTime, ByVal prefCommDate As DateTime, ByVal notes As String, ByVal modBy As String) As IntegerTry
SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings("connectionString"), "MCS_ProspectiveStudentInsert", agentId, tripId, institution, nationality, title, dob, sex, elicos, course1, course2, fname, mname, lname, osStud, osAdd, osPhone, osMobile, osFax, osSuburb, osPostcode, osCity, osCountry, add, phone, mobile, fax, suburb, postcode, city, country, email, ugQual, ugIns, ugYear, seconQual, seconIns, seconYear, pgQual, pgIns, pgYear, appStatus, appNotes, appDate, ielts, engRemarks, coeDate, coeRemarks, offerDate, offerRemarks, recAddDate, prefCommDate, notes, modBy)
Catch ex As SqlException
Return ex.Number
End TryReturn 0
End Function
This is the function where I have passed the dates but i can't assign the System.Data.SqlTypes.Null to date variable.|||OK, I believe I have misled you. You should be using System.DBNull. Let us know if this works better for you.
Terri|||it says "DBNull is a type in system and can't be used as an expression."
at the moment i am using default value but i think that's not a good idea is it?|||thanx a lot for your time Terri.
i appreciate your willingness to help me.|||THe problem has NOTHING to do with SQL. See:
[code]
Dim commDate As DateTime
If tbCommDate.Text = "" Then
'commDate = New DateTime(1900, 1, 1)
'can't use this statement
commDate = System.Data.SqlTypes.SqlDateTime.Null
[/code]
NATURALLY blows. You can not assign SqlDateTime.Null to a DateTime.
You have to make the assignment "lower" - i.e. you pass the SqlDateTime directly into the relevant SQL parameter. You can not store it in another non-compatible time in the meantime.
For example, the EntityBroker - my O/R-Mapper - makes the switch statement directly when putting the value into the PARAMETER (or encoding the value for the SQL string). THEN it gets accepted (DBNull.Value, btw.). The way you do it earlier just results in an invalid cast.|||but i am using SqlHelper class. so i think i can't use it then. right?
Monday, March 26, 2012
Passing in Date Parameters
I have a stored procedure that accepts a date value.
Create Procedure spTest as
@.DateFrom DateTime,
@.DateTo DateTime
/* Procedure Logic Not Shown */
GO
I'm trying to pass in a date generated from Functions.
EXECUTE spTest Month(GetDate()) + '/1/2003', '2/2/2003'
When I run the Stored Procedure I get an "error near Month" but when I hard code in a date things work fine. i.e. '1/1/2003'. Any ideas?From the code taht you presented to us, I see two problems:
1)
Create Procedure spTest as
@.DateFrom DateTime,
@.DateTo DateTime
/* Procedure Logic Not Shown */
GO
should be like this:
Create Procedure spTest @.DateFrom DateTime,
@.DateTo DateTime as
/* Procedure Logic Not Shown */
GO
but this one I think it's just a type error when you posted your question, because you said that the sp worked when you passed the parameters hardcoded. The sp would never work (and never compile) in the form mentioned in your post
2)
Instead of:
EXECUTE spTest Month(GetDate()) + '/1/2003', '2/2/2003'
I would use:
EXECUTE spTest '' & Month(GetDate()) & '/1/2003', '2/2/2003'
Good luck!
ionut calin|||EXECUTE spTest '' & Month(GetDate()) & '/1/2003', '2/2/2003'
I paired up the quotes and the above line doesn't seem to work. Also tried:
EXECUTE spTest '' & Month(GetDate()) & '/1/2003''', '2/2/2003'
EXECUTE spTest Month(GetDate()) & '/1/2003', '2/2/2003'
EXECUTE spTest Month(GetDate()) + '/1/2003', '2/2/2003'
Just in case it was a data conversion issue I tried the following as well.
EXECUTE spTest CONVERT(VARCHAR(2), Month(GetDate)) + '/1/2003', '9/1/2003'
Create Procedure spTest as
@.DateFrom DateTime,
@.DateTo DateTime
/* Procedure Logic Not Shown */
GO
I'm trying to pass in a date generated from Functions.
EXECUTE spTest Month(GetDate()) + '/1/2003', '2/2/2003'
When I run the Stored Procedure I get an "error near Month" but when I hard code in a date things work fine. i.e. '1/1/2003'. Any ideas?From the code taht you presented to us, I see two problems:
1)
Create Procedure spTest as
@.DateFrom DateTime,
@.DateTo DateTime
/* Procedure Logic Not Shown */
GO
should be like this:
Create Procedure spTest @.DateFrom DateTime,
@.DateTo DateTime as
/* Procedure Logic Not Shown */
GO
but this one I think it's just a type error when you posted your question, because you said that the sp worked when you passed the parameters hardcoded. The sp would never work (and never compile) in the form mentioned in your post
2)
Instead of:
EXECUTE spTest Month(GetDate()) + '/1/2003', '2/2/2003'
I would use:
EXECUTE spTest '' & Month(GetDate()) & '/1/2003', '2/2/2003'
Good luck!
ionut calin|||EXECUTE spTest '' & Month(GetDate()) & '/1/2003', '2/2/2003'
I paired up the quotes and the above line doesn't seem to work. Also tried:
EXECUTE spTest '' & Month(GetDate()) & '/1/2003''', '2/2/2003'
EXECUTE spTest Month(GetDate()) & '/1/2003', '2/2/2003'
EXECUTE spTest Month(GetDate()) + '/1/2003', '2/2/2003'
Just in case it was a data conversion issue I tried the following as well.
EXECUTE spTest CONVERT(VARCHAR(2), Month(GetDate)) + '/1/2003', '9/1/2003'
Passing in a default parameter date via a URL
Hi Guys,
What is the syntax for passing in a default parameter of current date via a URL.
cheers
If you don't want to have to deal with IFormatProvider and DateTimeStyles of DateTime.Parse() method, then you can simply use the "MM/dd/yyyy" format. For example:string dateToConvert = "12/25/2006";
DateTime today = DateTime.Parse(dateToConvert);
So, in your url, simply do: myPage.aspx?date=12/25/2006
Then retrieve the query string (Request.QueryString["date"]), check to make sure it's not null and not empty, and then finally convert it using the above mentioned DateTime.Parse(...).|||
Thanks jcasp.
This isn't quite what I meant. I want the currentdate to be passed in which I do not know of beforehand. Usually in VB I use Now or Date what is the equivalent.
Cheers
|||Hi,
you can use DateTime.Now in .NET.
Grz, Kris.
Friday, March 23, 2012
Passing datetime parameter to stored procedure
I want to pass a date parameter to stored procedure which is expecting
smalldatetime. I need to subtract a number of days from the current time and
pass it to the stored procedure. I tried DateAdd("d", -1, Now()). I can
display the the result in a text field on the report but when passing it to
the stored procedure to retrieve data I get and error that it is in bad
format.
Thanks,
AntoninOn May 22, 11:03 pm, "Antonin" <Antonin.Koude...@.fmc.sa.gov.au> wrote:
> I want to pass a date parameter to stored procedure which is expecting
> smalldatetime. I need to subtract a number of days from the current time and
> pass it to the stored procedure. I tried DateAdd("d", -1, Now()). I can
> display the the result in a text field on the report but when passing it to
> the stored procedure to retrieve data I get and error that it is in bad
> format.
> Thanks,
> Antonin
You could do one of a few different things:
- Use the CDate() function to pass it to a stored procedure.
- Redefine the smalldatetime input parameter in the stored procedure
as datetime and manipulate it to be a smalldatetime somewhere else in
the stored procedure
- Try to use Datepart to split the report field and then concatenate
it with '/' and then convert it to a datetime (the stored procedure
might implicitly accept it as a smalldatetime, though I don't recall).
- A combination of the above.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique. Would you know the solution for my other problem?
When I try to deploy a report I get this error:
The underlying connection was closed: Could not establish trust
relationship for the SSL/TLS secure channel.
The same error I get when I try to open
http://localhost/Reports/Pages/Folder.aspx
Antonin
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1179923004.037985.311190@.q69g2000hsb.googlegroups.com...
> On May 22, 11:03 pm, "Antonin" <Antonin.Koude...@.fmc.sa.gov.au> wrote:
>> I want to pass a date parameter to stored procedure which is expecting
>> smalldatetime. I need to subtract a number of days from the current time
>> and
>> pass it to the stored procedure. I tried DateAdd("d", -1, Now()). I can
>> display the the result in a text field on the report but when passing it
>> to
>> the stored procedure to retrieve data I get and error that it is in bad
>> format.
>> Thanks,
>> Antonin
> You could do one of a few different things:
> - Use the CDate() function to pass it to a stored procedure.
> - Redefine the smalldatetime input parameter in the stored procedure
> as datetime and manipulate it to be a smalldatetime somewhere else in
> the stored procedure
> - Try to use Datepart to split the report field and then concatenate
> it with '/' and then convert it to a datetime (the stored procedure
> might implicitly accept it as a smalldatetime, though I don't recall).
> - A combination of the above.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>sql
smalldatetime. I need to subtract a number of days from the current time and
pass it to the stored procedure. I tried DateAdd("d", -1, Now()). I can
display the the result in a text field on the report but when passing it to
the stored procedure to retrieve data I get and error that it is in bad
format.
Thanks,
AntoninOn May 22, 11:03 pm, "Antonin" <Antonin.Koude...@.fmc.sa.gov.au> wrote:
> I want to pass a date parameter to stored procedure which is expecting
> smalldatetime. I need to subtract a number of days from the current time and
> pass it to the stored procedure. I tried DateAdd("d", -1, Now()). I can
> display the the result in a text field on the report but when passing it to
> the stored procedure to retrieve data I get and error that it is in bad
> format.
> Thanks,
> Antonin
You could do one of a few different things:
- Use the CDate() function to pass it to a stored procedure.
- Redefine the smalldatetime input parameter in the stored procedure
as datetime and manipulate it to be a smalldatetime somewhere else in
the stored procedure
- Try to use Datepart to split the report field and then concatenate
it with '/' and then convert it to a datetime (the stored procedure
might implicitly accept it as a smalldatetime, though I don't recall).
- A combination of the above.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique. Would you know the solution for my other problem?
When I try to deploy a report I get this error:
The underlying connection was closed: Could not establish trust
relationship for the SSL/TLS secure channel.
The same error I get when I try to open
http://localhost/Reports/Pages/Folder.aspx
Antonin
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1179923004.037985.311190@.q69g2000hsb.googlegroups.com...
> On May 22, 11:03 pm, "Antonin" <Antonin.Koude...@.fmc.sa.gov.au> wrote:
>> I want to pass a date parameter to stored procedure which is expecting
>> smalldatetime. I need to subtract a number of days from the current time
>> and
>> pass it to the stored procedure. I tried DateAdd("d", -1, Now()). I can
>> display the the result in a text field on the report but when passing it
>> to
>> the stored procedure to retrieve data I get and error that it is in bad
>> format.
>> Thanks,
>> Antonin
> You could do one of a few different things:
> - Use the CDate() function to pass it to a stored procedure.
> - Redefine the smalldatetime input parameter in the stored procedure
> as datetime and manipulate it to be a smalldatetime somewhere else in
> the stored procedure
> - Try to use Datepart to split the report field and then concatenate
> it with '/' and then convert it to a datetime (the stored procedure
> might implicitly accept it as a smalldatetime, though I don't recall).
> - A combination of the above.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>sql
Passing DateTime Parameter to MDX Query
HI All,
Looking for a little help with this issue: I currently have an MDX
query which is accepting 2 parameters for a date range. The query is
as follows:
SELECT NON EMPTY { [Measures].[Line Item Sales Amount] } ON COLUMNS
FROM ( SELECT ( STRTOMEMBER(@.FromDateDateForReporting, CONSTRAINED) :
STRTOMEMBER(@.ToDateDateForReporting, CONSTRAINED) ) ON COLUMNS FROM
[KIT Retail Sales]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The parameters pull their respective values from a dataset populated
with this query:
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Date For
Reporting].CURRENTMEMBER.MEMBER_CAPTION' MEMBER
[Measures].[ParameterValue] AS '[Date].[Date For
Reporting].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel]
AS '[Date].[Date For Reporting].CURRENTMEMBER.LEVEL.ORDINAL' SELECT
{[Measures].[ParameterCaption], [Measures].[ParameterValue],
[Measures].[ParameterLevel]} ON COLUMNS , [Date].[Date For
Reporting].ALLMEMBERS ON ROWS FROM [KIT Retail Sales]
In the Report Parameters window, I would like these parameters to be
DateTime datatypes so my users would get the handy Calendar control.
The only way I can get them to work now is by setting them to string
datatype. This works, but the dropdown list is populated with
thousands of date strings (in the form 01/01/2005, 01/02/2005, etc...)
which makes it difficult for users.
Whenver I attempt to make them DateTime datatypes, I get the error:
"The Property 'ValidValues' of the report parameter
'FromDateDateForReporting' doesn't have the expected type" when I run
the report.
My question is: How can I do the conversion to get the datetime back
to a string to use in the first MDX query above?Quick followup: In the report parameters window, if I choose to make
the available values "Non-queried", I can see the calendar control on
the preview page. However when choosing dates for the range, and then
clicking "view report", I get the following error:
The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
function have been violated.|||Guys-
This appears to work fine. However, now go add a column to your main query
(the one with the parameter) and now your report parameter is trashed because
SSRS decided it needed to refresh it. Anybody know of a way to tell it to
leave that parameter alone?
"Bret Updegraff" wrote:
> This is the method from my class file. It is in C# so if you are using
> VB.net or if you are putting this in the Code tab under Report-->
> Properties you will need to convert to VB.NET
> public string ParseDateToMDX( DateTime sDate )
> {
> string year = sDate.Year.ToString();
> string month = sDate.Month.ToString();
> string day = sDate.Day.ToString();
> StringBuilder MDXValue = new StringBuilder();
> if (month.Length == 1)
> {
> month = "0" + month; //pad month
> }
> if (day.Length == 1)
> {
> day = "0" + day; //pad day
> }
> MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> MDXValue.Append(month.ToString()).Append("-");
> MDXValue.Append(day.ToString()).Append("T00:00:00]");
> return MDXValue.ToString();
> }
> Hope this helps
> "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> > Quick followup: In the report parameters window, if I choose to make
> > the available values "Non-queried", I can see the calendar control on
> > the preview page. However when choosing dates for the range, and then
> > clicking "view report", I get the following error:
> > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > function have been violated.
>
> --
> Bret Updegraff, MCAD,MCSD,MCDBA
> Microsoft MVP - SQL Server
> Crowe Chizek and Company LLC
> President - Indianapolis Professional Association for SQL Server
> Join our SQL Server Community http;//www.IndyPASS.org
> 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
>|||I hear this may be fixed in SP1:
http://prologika.com/CS/forums/thread/1069.aspx
"FurmanGG" wrote:
> Guys-
> This appears to work fine. However, now go add a column to your main query
> (the one with the parameter) and now your report parameter is trashed because
> SSRS decided it needed to refresh it. Anybody know of a way to tell it to
> leave that parameter alone?
> "Bret Updegraff" wrote:
> > This is the method from my class file. It is in C# so if you are using
> > VB.net or if you are putting this in the Code tab under Report-->
> > Properties you will need to convert to VB.NET
> >
> > public string ParseDateToMDX( DateTime sDate )
> > {
> > string year = sDate.Year.ToString();
> > string month = sDate.Month.ToString();
> > string day = sDate.Day.ToString();
> > StringBuilder MDXValue = new StringBuilder();
> >
> > if (month.Length == 1)
> > {
> > month = "0" + month; //pad month
> > }
> > if (day.Length == 1)
> > {
> > day = "0" + day; //pad day
> > }
> >
> > MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> > MDXValue.Append(month.ToString()).Append("-");
> > MDXValue.Append(day.ToString()).Append("T00:00:00]");
> >
> > return MDXValue.ToString();
> > }
> >
> > Hope this helps
> >
> > "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> > news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> >
> > > Quick followup: In the report parameters window, if I choose to make
> > > the available values "Non-queried", I can see the calendar control on
> > > the preview page. However when choosing dates for the range, and then
> > > clicking "view report", I get the following error:
> > > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > > function have been violated.
> >
> >
> > --
> > Bret Updegraff, MCAD,MCSD,MCDBA
> > Microsoft MVP - SQL Server
> > Crowe Chizek and Company LLC
> > President - Indianapolis Professional Association for SQL Server
> > Join our SQL Server Community http;//www.IndyPASS.org
> > 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
> >
> >
Looking for a little help with this issue: I currently have an MDX
query which is accepting 2 parameters for a date range. The query is
as follows:
SELECT NON EMPTY { [Measures].[Line Item Sales Amount] } ON COLUMNS
FROM ( SELECT ( STRTOMEMBER(@.FromDateDateForReporting, CONSTRAINED) :
STRTOMEMBER(@.ToDateDateForReporting, CONSTRAINED) ) ON COLUMNS FROM
[KIT Retail Sales]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The parameters pull their respective values from a dataset populated
with this query:
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Date For
Reporting].CURRENTMEMBER.MEMBER_CAPTION' MEMBER
[Measures].[ParameterValue] AS '[Date].[Date For
Reporting].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel]
AS '[Date].[Date For Reporting].CURRENTMEMBER.LEVEL.ORDINAL' SELECT
{[Measures].[ParameterCaption], [Measures].[ParameterValue],
[Measures].[ParameterLevel]} ON COLUMNS , [Date].[Date For
Reporting].ALLMEMBERS ON ROWS FROM [KIT Retail Sales]
In the Report Parameters window, I would like these parameters to be
DateTime datatypes so my users would get the handy Calendar control.
The only way I can get them to work now is by setting them to string
datatype. This works, but the dropdown list is populated with
thousands of date strings (in the form 01/01/2005, 01/02/2005, etc...)
which makes it difficult for users.
Whenver I attempt to make them DateTime datatypes, I get the error:
"The Property 'ValidValues' of the report parameter
'FromDateDateForReporting' doesn't have the expected type" when I run
the report.
My question is: How can I do the conversion to get the datetime back
to a string to use in the first MDX query above?Quick followup: In the report parameters window, if I choose to make
the available values "Non-queried", I can see the calendar control on
the preview page. However when choosing dates for the range, and then
clicking "view report", I get the following error:
The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
function have been violated.|||Guys-
This appears to work fine. However, now go add a column to your main query
(the one with the parameter) and now your report parameter is trashed because
SSRS decided it needed to refresh it. Anybody know of a way to tell it to
leave that parameter alone?
"Bret Updegraff" wrote:
> This is the method from my class file. It is in C# so if you are using
> VB.net or if you are putting this in the Code tab under Report-->
> Properties you will need to convert to VB.NET
> public string ParseDateToMDX( DateTime sDate )
> {
> string year = sDate.Year.ToString();
> string month = sDate.Month.ToString();
> string day = sDate.Day.ToString();
> StringBuilder MDXValue = new StringBuilder();
> if (month.Length == 1)
> {
> month = "0" + month; //pad month
> }
> if (day.Length == 1)
> {
> day = "0" + day; //pad day
> }
> MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> MDXValue.Append(month.ToString()).Append("-");
> MDXValue.Append(day.ToString()).Append("T00:00:00]");
> return MDXValue.ToString();
> }
> Hope this helps
> "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> > Quick followup: In the report parameters window, if I choose to make
> > the available values "Non-queried", I can see the calendar control on
> > the preview page. However when choosing dates for the range, and then
> > clicking "view report", I get the following error:
> > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > function have been violated.
>
> --
> Bret Updegraff, MCAD,MCSD,MCDBA
> Microsoft MVP - SQL Server
> Crowe Chizek and Company LLC
> President - Indianapolis Professional Association for SQL Server
> Join our SQL Server Community http;//www.IndyPASS.org
> 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
>|||I hear this may be fixed in SP1:
http://prologika.com/CS/forums/thread/1069.aspx
"FurmanGG" wrote:
> Guys-
> This appears to work fine. However, now go add a column to your main query
> (the one with the parameter) and now your report parameter is trashed because
> SSRS decided it needed to refresh it. Anybody know of a way to tell it to
> leave that parameter alone?
> "Bret Updegraff" wrote:
> > This is the method from my class file. It is in C# so if you are using
> > VB.net or if you are putting this in the Code tab under Report-->
> > Properties you will need to convert to VB.NET
> >
> > public string ParseDateToMDX( DateTime sDate )
> > {
> > string year = sDate.Year.ToString();
> > string month = sDate.Month.ToString();
> > string day = sDate.Day.ToString();
> > StringBuilder MDXValue = new StringBuilder();
> >
> > if (month.Length == 1)
> > {
> > month = "0" + month; //pad month
> > }
> > if (day.Length == 1)
> > {
> > day = "0" + day; //pad day
> > }
> >
> > MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> > MDXValue.Append(month.ToString()).Append("-");
> > MDXValue.Append(day.ToString()).Append("T00:00:00]");
> >
> > return MDXValue.ToString();
> > }
> >
> > Hope this helps
> >
> > "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> > news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> >
> > > Quick followup: In the report parameters window, if I choose to make
> > > the available values "Non-queried", I can see the calendar control on
> > > the preview page. However when choosing dates for the range, and then
> > > clicking "view report", I get the following error:
> > > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > > function have been violated.
> >
> >
> > --
> > Bret Updegraff, MCAD,MCSD,MCDBA
> > Microsoft MVP - SQL Server
> > Crowe Chizek and Company LLC
> > President - Indianapolis Professional Association for SQL Server
> > Join our SQL Server Community http;//www.IndyPASS.org
> > 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
> >
> >
Passing dates to ServerFilter from Access
Hello.
I have a solution written in Access. In order to filter a
report a date is passed the the serverfilter property.
e.g. MyFilterDate='01/28/2004'
This works fine on one machine but on another machine I
get an overflow error due to the fact that date is passed
in American Format.
Both machines appear configured with the same regional
settings, the VBA references are the same and anything I
have thought of have been the same.
They are both connected to the same SQL server.
Can anyone help?
If you pass in the date as 'yyyymmdd' it will never get confused.
Andrew J. Kelly SQL MVP
"Scamps" <anonymous@.discussions.microsoft.com> wrote in message
news:2dc5801c46a8a$2c5537f0$a501280a@.phx.gbl...
> Hello.
> I have a solution written in Access. In order to filter a
> report a date is passed the the serverfilter property.
> e.g. MyFilterDate='01/28/2004'
> This works fine on one machine but on another machine I
> get an overflow error due to the fact that date is passed
> in American Format.
> Both machines appear configured with the same regional
> settings, the VBA references are the same and anything I
> have thought of have been the same.
> They are both connected to the same SQL server.
> Can anyone help?
|||Thank you very much.
Problem appears solved immediately.
>--Original Message--
>If you pass in the date as 'yyyymmdd' it will never get
confused.
>--
>Andrew J. Kelly SQL MVP
>
>"Scamps" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2dc5801c46a8a$2c5537f0$a501280a@.phx.gbl...
filter a[vbcol=seagreen]
passed[vbcol=seagreen]
I
>
>.
>
I have a solution written in Access. In order to filter a
report a date is passed the the serverfilter property.
e.g. MyFilterDate='01/28/2004'
This works fine on one machine but on another machine I
get an overflow error due to the fact that date is passed
in American Format.
Both machines appear configured with the same regional
settings, the VBA references are the same and anything I
have thought of have been the same.
They are both connected to the same SQL server.
Can anyone help?
If you pass in the date as 'yyyymmdd' it will never get confused.
Andrew J. Kelly SQL MVP
"Scamps" <anonymous@.discussions.microsoft.com> wrote in message
news:2dc5801c46a8a$2c5537f0$a501280a@.phx.gbl...
> Hello.
> I have a solution written in Access. In order to filter a
> report a date is passed the the serverfilter property.
> e.g. MyFilterDate='01/28/2004'
> This works fine on one machine but on another machine I
> get an overflow error due to the fact that date is passed
> in American Format.
> Both machines appear configured with the same regional
> settings, the VBA references are the same and anything I
> have thought of have been the same.
> They are both connected to the same SQL server.
> Can anyone help?
|||Thank you very much.
Problem appears solved immediately.
>--Original Message--
>If you pass in the date as 'yyyymmdd' it will never get
confused.
>--
>Andrew J. Kelly SQL MVP
>
>"Scamps" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2dc5801c46a8a$2c5537f0$a501280a@.phx.gbl...
filter a[vbcol=seagreen]
passed[vbcol=seagreen]
I
>
>.
>
Passing Date Variables
I am a newbie and need help. I am creating an intranet site for my company
and my SQL2000 database has a column named transdate. I want to provide the
user with the ability to run a canned report depending on a date range
entered in a web form (Start Date / End Date).
How would I pass these form entries to my SQL select statement? I am using
ASP VBScript
I'm thinking...
Select *
from DB where transdate >= ' and transdate <= '
Your help is greatly appreciated!!!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1> How would I pass these form entries to my SQL select statement? I am using
> ASP VBScript
You can use a parameterized SQL statement, specifying '?' as parameter
markers:
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = myConnection
command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
transdate <= ?"
Set fromDateParameter = command.CreateParameter( _
"@.fromDate", adDate, adParamInput)
command.Parameters.Append fromDateParameter
fromDateParameter.Value = "2006-05-01"
Set toDateParameter = command.CreateParameter( _
"@.toDateParameter", adDate, adParamInput)
command.Parameters.Append toDateParameter
toDateParameter.Value = "2006-05-31"
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:615867ef9c88a@.uwe...
>I am a newbie and need help. I am creating an intranet site for my company
> and my SQL2000 database has a column named transdate. I want to provide
> the
> user with the ability to run a canned report depending on a date range
> entered in a web form (Start Date / End Date).
> How would I pass these form entries to my SQL select statement? I am using
> ASP VBScript
> I'm thinking...
> Select *
> from DB where transdate >= ' and transdate <= '
> Your help is greatly appreciated!!!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Thanks Dan, I'll give it a shot
Dan Guzman wrote:
>You can use a parameterized SQL statement, specifying '?' as parameter
>markers:
>Set command = CreateObject("ADODB.Command")
>command.ActiveConnection = myConnection
>command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
>transdate <= ?"
>Set fromDateParameter = command.CreateParameter( _
> "@.fromDate", adDate, adParamInput)
>command.Parameters.Append fromDateParameter
>fromDateParameter.Value = "2006-05-01"
>Set toDateParameter = command.CreateParameter( _
> "@.toDateParameter", adDate, adParamInput)
>command.Parameters.Append toDateParameter
>toDateParameter.Value = "2006-05-31"
>
>[quoted text clipped - 11 lines]
Message posted via http://www.webservertalk.com|||Obviously I am not advanced enough to get this? I need to pass the dates
from my Web form to the embedded SQL statement in Dreamweaver. I am using
multiple recordsets that require these same date ranges. In ACCESS it is eas
y
because you can create the form and reference it. Is there anything like thi
s
in SQL?
Dan Guzman wrote:
>You can use a parameterized SQL statement, specifying '?' as parameter
>markers:
>Set command = CreateObject("ADODB.Command")
>command.ActiveConnection = myConnection
>command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
>transdate <= ?"
>Set fromDateParameter = command.CreateParameter( _
> "@.fromDate", adDate, adParamInput)
>command.Parameters.Append fromDateParameter
>fromDateParameter.Value = "2006-05-01"
>Set toDateParameter = command.CreateParameter( _
> "@.toDateParameter", adDate, adParamInput)
>command.Parameters.Append toDateParameter
>toDateParameter.Value = "2006-05-31"
>
>[quoted text clipped - 11 lines]
Message posted via http://www.webservertalk.com|||> Obviously I am not advanced enough to get this? I need to pass the dates
> from my Web form to the embedded SQL statement in Dreamweaver. I am using
> multiple recordsets that require these same date ranges. In ACCESS it is
> easy
> because you can create the form and reference it. Is there anything like
> this
> in SQL?
SQL Server is basically just the back-end database engine. Unlike SQL
Server, Access also includes an IDE so that you can develop a 'rich client'
GUI for your users. The Access database engine isn't a client/server DBMS
because Access runs in the client process and, in the case of a multi-user
application, the Access database file is shared among multiple Access
instances. With SQL Server, it is the database engine is shared and only
that SQL Server instance accesses the database files.
I know next to nothing about Dreamweaver so I can't provide detailed help.
I don't know what an 'embedded SQL statement in Dreamweaver' is. I assume
this part of server-side code (ASP or ASP.NET) that is generated by the
Dreamweaver IDE. I would expect that the IDE would provide some method to
parameterize the SQL statement, map to your form variables and associate
with a SQL Server database connection.
don't know if this will help but below is an ASP VBScript snippet that can
execute a SQL statement based on the date range. I would expect Dreamweaver
would generate something similar.
<!-- include ADO constants -->
<!-- METADATA
TYPE="typelib"
UUID="00000200-0000-0010-8000-00AA006D2EA4"
-->
<%
Set connection = CreateObject("ADODB.Connection")
connection,Open "Provider=SQLOLEDB;Data Source=MyDbServer;Integrated
Security=SSPI"
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = myConnection
command.CommandText = "SELECT * FROM MyTable " & _
"WHERE transdate >= ? AND transdate <= ?"
Set fromDateParameter = command.CreateParameter( _
"@.fromDate", adDate, adParamInput)
command.Parameters.Append fromDateParameter
fromDateParameter.Value = Request("fromDate")
Set toDateParameter = command.CreateParameter( _
"@.toDateParameter", adDate, adParamInput)
command.Parameters.Append toDateParameter
toDateParameter.Value = Request("toDate")
Set results = command.Execute
While results.EOF = False
'process row here
results.MoveNext
Loop
results.Close
connection,Close
&>
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:61bd9dcc71568@.uwe...
> Obviously I am not advanced enough to get this? I need to pass the dates
> from my Web form to the embedded SQL statement in Dreamweaver. I am using
> multiple recordsets that require these same date ranges. In ACCESS it is
> easy
> because you can create the form and reference it. Is there anything like
> this
> in SQL?
> Dan Guzman wrote:
> --
> Message posted via http://www.webservertalk.comsql
and my SQL2000 database has a column named transdate. I want to provide the
user with the ability to run a canned report depending on a date range
entered in a web form (Start Date / End Date).
How would I pass these form entries to my SQL select statement? I am using
ASP VBScript
I'm thinking...
Select *
from DB where transdate >= ' and transdate <= '
Your help is greatly appreciated!!!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1> How would I pass these form entries to my SQL select statement? I am using
> ASP VBScript
You can use a parameterized SQL statement, specifying '?' as parameter
markers:
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = myConnection
command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
transdate <= ?"
Set fromDateParameter = command.CreateParameter( _
"@.fromDate", adDate, adParamInput)
command.Parameters.Append fromDateParameter
fromDateParameter.Value = "2006-05-01"
Set toDateParameter = command.CreateParameter( _
"@.toDateParameter", adDate, adParamInput)
command.Parameters.Append toDateParameter
toDateParameter.Value = "2006-05-31"
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:615867ef9c88a@.uwe...
>I am a newbie and need help. I am creating an intranet site for my company
> and my SQL2000 database has a column named transdate. I want to provide
> the
> user with the ability to run a canned report depending on a date range
> entered in a web form (Start Date / End Date).
> How would I pass these form entries to my SQL select statement? I am using
> ASP VBScript
> I'm thinking...
> Select *
> from DB where transdate >= ' and transdate <= '
> Your help is greatly appreciated!!!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Thanks Dan, I'll give it a shot
Dan Guzman wrote:
>You can use a parameterized SQL statement, specifying '?' as parameter
>markers:
>Set command = CreateObject("ADODB.Command")
>command.ActiveConnection = myConnection
>command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
>transdate <= ?"
>Set fromDateParameter = command.CreateParameter( _
> "@.fromDate", adDate, adParamInput)
>command.Parameters.Append fromDateParameter
>fromDateParameter.Value = "2006-05-01"
>Set toDateParameter = command.CreateParameter( _
> "@.toDateParameter", adDate, adParamInput)
>command.Parameters.Append toDateParameter
>toDateParameter.Value = "2006-05-31"
>
>[quoted text clipped - 11 lines]
Message posted via http://www.webservertalk.com|||Obviously I am not advanced enough to get this? I need to pass the dates
from my Web form to the embedded SQL statement in Dreamweaver. I am using
multiple recordsets that require these same date ranges. In ACCESS it is eas
y
because you can create the form and reference it. Is there anything like thi
s
in SQL?
Dan Guzman wrote:
>You can use a parameterized SQL statement, specifying '?' as parameter
>markers:
>Set command = CreateObject("ADODB.Command")
>command.ActiveConnection = myConnection
>command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
>transdate <= ?"
>Set fromDateParameter = command.CreateParameter( _
> "@.fromDate", adDate, adParamInput)
>command.Parameters.Append fromDateParameter
>fromDateParameter.Value = "2006-05-01"
>Set toDateParameter = command.CreateParameter( _
> "@.toDateParameter", adDate, adParamInput)
>command.Parameters.Append toDateParameter
>toDateParameter.Value = "2006-05-31"
>
>[quoted text clipped - 11 lines]
Message posted via http://www.webservertalk.com|||> Obviously I am not advanced enough to get this? I need to pass the dates
> from my Web form to the embedded SQL statement in Dreamweaver. I am using
> multiple recordsets that require these same date ranges. In ACCESS it is
> easy
> because you can create the form and reference it. Is there anything like
> this
> in SQL?
SQL Server is basically just the back-end database engine. Unlike SQL
Server, Access also includes an IDE so that you can develop a 'rich client'
GUI for your users. The Access database engine isn't a client/server DBMS
because Access runs in the client process and, in the case of a multi-user
application, the Access database file is shared among multiple Access
instances. With SQL Server, it is the database engine is shared and only
that SQL Server instance accesses the database files.
I know next to nothing about Dreamweaver so I can't provide detailed help.
I don't know what an 'embedded SQL statement in Dreamweaver' is. I assume
this part of server-side code (ASP or ASP.NET) that is generated by the
Dreamweaver IDE. I would expect that the IDE would provide some method to
parameterize the SQL statement, map to your form variables and associate
with a SQL Server database connection.
don't know if this will help but below is an ASP VBScript snippet that can
execute a SQL statement based on the date range. I would expect Dreamweaver
would generate something similar.
<!-- include ADO constants -->
<!-- METADATA
TYPE="typelib"
UUID="00000200-0000-0010-8000-00AA006D2EA4"
-->
<%
Set connection = CreateObject("ADODB.Connection")
connection,Open "Provider=SQLOLEDB;Data Source=MyDbServer;Integrated
Security=SSPI"
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = myConnection
command.CommandText = "SELECT * FROM MyTable " & _
"WHERE transdate >= ? AND transdate <= ?"
Set fromDateParameter = command.CreateParameter( _
"@.fromDate", adDate, adParamInput)
command.Parameters.Append fromDateParameter
fromDateParameter.Value = Request("fromDate")
Set toDateParameter = command.CreateParameter( _
"@.toDateParameter", adDate, adParamInput)
command.Parameters.Append toDateParameter
toDateParameter.Value = Request("toDate")
Set results = command.Execute
While results.EOF = False
'process row here
results.MoveNext
Loop
results.Close
connection,Close
&>
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:61bd9dcc71568@.uwe...
> Obviously I am not advanced enough to get this? I need to pass the dates
> from my Web form to the embedded SQL statement in Dreamweaver. I am using
> multiple recordsets that require these same date ranges. In ACCESS it is
> easy
> because you can create the form and reference it. Is there anything like
> this
> in SQL?
> Dan Guzman wrote:
> --
> Message posted via http://www.webservertalk.comsql
Passing date Value
Hi,
I have a problem while passing a date value to datefunction.My query is like that ..
declare @.todate varchar(20),@.FromDate varchar(20)
set @.todate='18/10/2005'
set @.FromDate = '11/10/2005'
print DATEADD(yy, DATEDIFF(yy,0,convert(datetime,@.todate)), 0)
But i getting the error like this..
Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Can any body help me ..
Thanks and Regards
AJOSH
I have a problem while passing a date value to datefunction.My query is like that ..
declare @.todate varchar(20),@.FromDate varchar(20)
set @.todate='18/10/2005'
set @.FromDate = '11/10/2005'
print DATEADD(yy, DATEDIFF(yy,0,convert(datetime,@.todate)), 0)
But i getting the error like this..
Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Can any body help me ..
Thanks and Regards
AJOSH
See my post
http://forums.asp.net/1083329/ShowPost.aspx
Passing Date to Stored procedure as variable
Thanks in advance for your help!!!!!
I have created a stored procedure that uses a date range for paramenters. As long as I hardcode the dates in (3/21/03, 3/25/03) I get no errors. As soon as I replace the dates with the variable name and try to run the sp, I get an error message that it can't convert string to date.
It is my plan to call this sp from a web page and pass the date paramenters where they look like this 3/21/03
Here is what I have
@.BegDate Smalldatetime,
@.EndDate Smalldatetime
....
....
Where EntryDate BETWEEN '@.BegDate' AND '@.EndDate'
Could someone please help me understand what I am missing?
Thanks,
Leeyou are searching where EntryDate is between the string '@.BegDate' and the string '@.EndDate' not the values contained in @.BegDate and @.EndDate.
use Where EntryDate BETWEEN @.BegDate AND @.EndDate|||Thanks so much!!!!!! ' '
I have created a stored procedure that uses a date range for paramenters. As long as I hardcode the dates in (3/21/03, 3/25/03) I get no errors. As soon as I replace the dates with the variable name and try to run the sp, I get an error message that it can't convert string to date.
It is my plan to call this sp from a web page and pass the date paramenters where they look like this 3/21/03
Here is what I have
@.BegDate Smalldatetime,
@.EndDate Smalldatetime
....
....
Where EntryDate BETWEEN '@.BegDate' AND '@.EndDate'
Could someone please help me understand what I am missing?
Thanks,
Leeyou are searching where EntryDate is between the string '@.BegDate' and the string '@.EndDate' not the values contained in @.BegDate and @.EndDate.
use Where EntryDate BETWEEN @.BegDate AND @.EndDate|||Thanks so much!!!!!! ' '
passing date parameters to report
How do you pass date parameters to a report? I've been trying to pass my date parameters as strings but I keep getting errors.
Try setting the data type to DateTime, like in this article:
http://msdn.microsoft.com/msdnmag/issues/06/06/DataPoints/
passing date parameters between reports changing from dd/mm/yyyy to mm/dd/yyyy
I have a report which is based on a start and end date that are passed
as parameters at run time. This works fine. However, in the report is
a link to another report, which needs to run based on the same date
parameters. However, the following happens...
Report 1 runs fine with date parameters and report is generated.
When report 2 is selected the same dates are passed, but the date
format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
report 2 will either run with the wrong dates, or it will crash and not
run at all if swapping month and day gives an invalid date.
I have my report languages set to default so it should pick up my
regional setting ok. But when passing these dates between reports the
settings seem to be lost somehow.
Can anyone help'Check the data settings on the machine, and the report server configuration.
That may be the culprit.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
<gearoid_healy@.yahoo.com> wrote in message
news:1102350682.082943.295010@.c13g2000cwb.googlegroups.com...
>I have a report which is based on a start and end date that are passed
> as parameters at run time. This works fine. However, in the report is
> a link to another report, which needs to run based on the same date
> parameters. However, the following happens...
> Report 1 runs fine with date parameters and report is generated.
> When report 2 is selected the same dates are passed, but the date
> format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
> report 2 will either run with the wrong dates, or it will crash and not
> run at all if swapping month and day gives an invalid date.
> I have my report languages set to default so it should pick up my
> regional setting ok. But when passing these dates between reports the
> settings seem to be lost somehow.
> Can anyone help'
>|||Oops. Not "data settings" but "date settings", as in Control Panel.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:er8cQd82EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Check the data settings on the machine, and the report server
> configuration. That may be the culprit.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> <gearoid_healy@.yahoo.com> wrote in message
> news:1102350682.082943.295010@.c13g2000cwb.googlegroups.com...
>>I have a report which is based on a start and end date that are passed
>> as parameters at run time. This works fine. However, in the report is
>> a link to another report, which needs to run based on the same date
>> parameters. However, the following happens...
>> Report 1 runs fine with date parameters and report is generated.
>> When report 2 is selected the same dates are passed, but the date
>> format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
>> report 2 will either run with the wrong dates, or it will crash and not
>> run at all if swapping month and day gives an invalid date.
>> I have my report languages set to default so it should pick up my
>> regional setting ok. But when passing these dates between reports the
>> settings seem to be lost somehow.
>> Can anyone help'
>|||The date settings on my pc are correct. How do I check the report
server configuration? I've taken a look at RSReportServer.config and
the other .config files but can't see anything there that look like
regional settings, apart from
<Render>
<Extension Name=...>
<Configuration>
<OWCConfiguration>
<OWCDownloadLocation
language="en">http://office.microsoft.com/downloads/2002/owc10.aspx</OWCDownloadLocation>
but I don't think this has anything to do with it? Like I said, it
renders fine the first time, but when the dates are passed as part of
the url in a query string to the second report the date format changes,
so it must be some kind of report server configuration setting alright.
I just don't know where to go to check or fix this...|||ok, finally found a solution to this. Apparently it's a known issue
that was addressed in SP1 -
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
...which states "The rs:ParameterLanguage URL access parameter
alleviates a problem in which culture-sensitive report parameters, such
as dates, times, currency, and numbers, are interpreted using the
browser language"
Basically you have to add
...&rs:ParameterLanguage=<regionalSetting>
where regional setting is en-ie or en-us or whatever...|||Great, thanks for posting your solution back to the newsgroup.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:1102422016.683757.198040@.z14g2000cwz.googlegroups.com...
> ok, finally found a solution to this. Apparently it's a known issue
> that was addressed in SP1 -
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
> ...which states "The rs:ParameterLanguage URL access parameter
> alleviates a problem in which culture-sensitive report parameters, such
> as dates, times, currency, and numbers, are interpreted using the
> browser language"
> Basically you have to add
> ...&rs:ParameterLanguage=<regionalSetting>
> where regional setting is en-ie or en-us or whatever...
>|||I posested my solution to this before I actually tested it. This still
doesn't solve the problem I was having...
I call all my reports through a custom .net application, and display
them in my own application, building up my own URLs to display them.
However, when I link from one report (by clicking on an an image) it
calls the second report. But this is done within the report manager
and as such I don't get the option to append my own parameters to the
query string - namely the &rs:ParameterLanguage=en-ie part that I need.
Can anyone tell me how I might overcome this? I tried adding it as a
parameter in the Image Properties - Navigation - Hyperlink action -
Parameters but got the following error
...Parameter names must be CLS-compliant identifiers.
I'd appreciate a response from someone from Microsoft on this as it's a
known issue. How do I get around this bug in Reporting Services'|||It sounds like you are using Jump to Report. Use Jump to URL. You can put an
expression in Jump to URL that can be anything you want so you should have
no problem appending this onto it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:1102509563.514657.75340@.z14g2000cwz.googlegroups.com...
> I posested my solution to this before I actually tested it. This still
> doesn't solve the problem I was having...
> I call all my reports through a custom .net application, and display
> them in my own application, building up my own URLs to display them.
> However, when I link from one report (by clicking on an an image) it
> calls the second report. But this is done within the report manager
> and as such I don't get the option to append my own parameters to the
> query string - namely the &rs:ParameterLanguage=en-ie part that I need.
> Can anyone tell me how I might overcome this? I tried adding it as a
> parameter in the Image Properties - Navigation - Hyperlink action -
> Parameters but got the following error
> ...Parameter names must be CLS-compliant identifiers.
> I'd appreciate a response from someone from Microsoft on this as it's a
> known issue. How do I get around this bug in Reporting Services'
>|||Thanks Bruce!
Exactly what I was looking for. One slight problem... As I said, I'm
running these reports from a .net app. Is there anyway of reading the
URl of the web server from my web.config for my .net app'|||Use the global variable for this. Here is an example of a working jump to
url expression of mine:
=Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
First(Fields!manifstdocno.Value, "LoadID") &"&WasteIDNum=" &
First(Fields!wasteidnum.Value, "LoadID")
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:1102529644.706986.86770@.z14g2000cwz.googlegroups.com...
> Thanks Bruce!
> Exactly what I was looking for. One slight problem... As I said, I'm
> running these reports from a .net app. Is there anyway of reading the
> URl of the web server from my web.config for my .net app'
>|||brilliant - thanks a million for that Bruce!! Problem sorted.|||I have something to share with everyone for this problem.
If you are using Jump to Report method then to solve this problem simply
format the date you are passing to linked report as "dd-MMM-yyyy" in
paraemeters window.
This worked for me.
Cheers,
Harry
"gearoid_healy@.yahoo.com" wrote:
> I have a report which is based on a start and end date that are passed
> as parameters at run time. This works fine. However, in the report is
> a link to another report, which needs to run based on the same date
> parameters. However, the following happens...
> Report 1 runs fine with date parameters and report is generated.
> When report 2 is selected the same dates are passed, but the date
> format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
> report 2 will either run with the wrong dates, or it will crash and not
> run at all if swapping month and day gives an invalid date.
> I have my report languages set to default so it should pick up my
> regional setting ok. But when passing these dates between reports the
> settings seem to be lost somehow.
> Can anyone help'
>
as parameters at run time. This works fine. However, in the report is
a link to another report, which needs to run based on the same date
parameters. However, the following happens...
Report 1 runs fine with date parameters and report is generated.
When report 2 is selected the same dates are passed, but the date
format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
report 2 will either run with the wrong dates, or it will crash and not
run at all if swapping month and day gives an invalid date.
I have my report languages set to default so it should pick up my
regional setting ok. But when passing these dates between reports the
settings seem to be lost somehow.
Can anyone help'Check the data settings on the machine, and the report server configuration.
That may be the culprit.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
<gearoid_healy@.yahoo.com> wrote in message
news:1102350682.082943.295010@.c13g2000cwb.googlegroups.com...
>I have a report which is based on a start and end date that are passed
> as parameters at run time. This works fine. However, in the report is
> a link to another report, which needs to run based on the same date
> parameters. However, the following happens...
> Report 1 runs fine with date parameters and report is generated.
> When report 2 is selected the same dates are passed, but the date
> format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
> report 2 will either run with the wrong dates, or it will crash and not
> run at all if swapping month and day gives an invalid date.
> I have my report languages set to default so it should pick up my
> regional setting ok. But when passing these dates between reports the
> settings seem to be lost somehow.
> Can anyone help'
>|||Oops. Not "data settings" but "date settings", as in Control Panel.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:er8cQd82EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Check the data settings on the machine, and the report server
> configuration. That may be the culprit.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> <gearoid_healy@.yahoo.com> wrote in message
> news:1102350682.082943.295010@.c13g2000cwb.googlegroups.com...
>>I have a report which is based on a start and end date that are passed
>> as parameters at run time. This works fine. However, in the report is
>> a link to another report, which needs to run based on the same date
>> parameters. However, the following happens...
>> Report 1 runs fine with date parameters and report is generated.
>> When report 2 is selected the same dates are passed, but the date
>> format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
>> report 2 will either run with the wrong dates, or it will crash and not
>> run at all if swapping month and day gives an invalid date.
>> I have my report languages set to default so it should pick up my
>> regional setting ok. But when passing these dates between reports the
>> settings seem to be lost somehow.
>> Can anyone help'
>|||The date settings on my pc are correct. How do I check the report
server configuration? I've taken a look at RSReportServer.config and
the other .config files but can't see anything there that look like
regional settings, apart from
<Render>
<Extension Name=...>
<Configuration>
<OWCConfiguration>
<OWCDownloadLocation
language="en">http://office.microsoft.com/downloads/2002/owc10.aspx</OWCDownloadLocation>
but I don't think this has anything to do with it? Like I said, it
renders fine the first time, but when the dates are passed as part of
the url in a query string to the second report the date format changes,
so it must be some kind of report server configuration setting alright.
I just don't know where to go to check or fix this...|||ok, finally found a solution to this. Apparently it's a known issue
that was addressed in SP1 -
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
...which states "The rs:ParameterLanguage URL access parameter
alleviates a problem in which culture-sensitive report parameters, such
as dates, times, currency, and numbers, are interpreted using the
browser language"
Basically you have to add
...&rs:ParameterLanguage=<regionalSetting>
where regional setting is en-ie or en-us or whatever...|||Great, thanks for posting your solution back to the newsgroup.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:1102422016.683757.198040@.z14g2000cwz.googlegroups.com...
> ok, finally found a solution to this. Apparently it's a known issue
> that was addressed in SP1 -
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
> ...which states "The rs:ParameterLanguage URL access parameter
> alleviates a problem in which culture-sensitive report parameters, such
> as dates, times, currency, and numbers, are interpreted using the
> browser language"
> Basically you have to add
> ...&rs:ParameterLanguage=<regionalSetting>
> where regional setting is en-ie or en-us or whatever...
>|||I posested my solution to this before I actually tested it. This still
doesn't solve the problem I was having...
I call all my reports through a custom .net application, and display
them in my own application, building up my own URLs to display them.
However, when I link from one report (by clicking on an an image) it
calls the second report. But this is done within the report manager
and as such I don't get the option to append my own parameters to the
query string - namely the &rs:ParameterLanguage=en-ie part that I need.
Can anyone tell me how I might overcome this? I tried adding it as a
parameter in the Image Properties - Navigation - Hyperlink action -
Parameters but got the following error
...Parameter names must be CLS-compliant identifiers.
I'd appreciate a response from someone from Microsoft on this as it's a
known issue. How do I get around this bug in Reporting Services'|||It sounds like you are using Jump to Report. Use Jump to URL. You can put an
expression in Jump to URL that can be anything you want so you should have
no problem appending this onto it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:1102509563.514657.75340@.z14g2000cwz.googlegroups.com...
> I posested my solution to this before I actually tested it. This still
> doesn't solve the problem I was having...
> I call all my reports through a custom .net application, and display
> them in my own application, building up my own URLs to display them.
> However, when I link from one report (by clicking on an an image) it
> calls the second report. But this is done within the report manager
> and as such I don't get the option to append my own parameters to the
> query string - namely the &rs:ParameterLanguage=en-ie part that I need.
> Can anyone tell me how I might overcome this? I tried adding it as a
> parameter in the Image Properties - Navigation - Hyperlink action -
> Parameters but got the following error
> ...Parameter names must be CLS-compliant identifiers.
> I'd appreciate a response from someone from Microsoft on this as it's a
> known issue. How do I get around this bug in Reporting Services'
>|||Thanks Bruce!
Exactly what I was looking for. One slight problem... As I said, I'm
running these reports from a .net app. Is there anyway of reading the
URl of the web server from my web.config for my .net app'|||Use the global variable for this. Here is an example of a working jump to
url expression of mine:
=Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
First(Fields!manifstdocno.Value, "LoadID") &"&WasteIDNum=" &
First(Fields!wasteidnum.Value, "LoadID")
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:1102529644.706986.86770@.z14g2000cwz.googlegroups.com...
> Thanks Bruce!
> Exactly what I was looking for. One slight problem... As I said, I'm
> running these reports from a .net app. Is there anyway of reading the
> URl of the web server from my web.config for my .net app'
>|||brilliant - thanks a million for that Bruce!! Problem sorted.|||I have something to share with everyone for this problem.
If you are using Jump to Report method then to solve this problem simply
format the date you are passing to linked report as "dd-MMM-yyyy" in
paraemeters window.
This worked for me.
Cheers,
Harry
"gearoid_healy@.yahoo.com" wrote:
> I have a report which is based on a start and end date that are passed
> as parameters at run time. This works fine. However, in the report is
> a link to another report, which needs to run based on the same date
> parameters. However, the following happens...
> Report 1 runs fine with date parameters and report is generated.
> When report 2 is selected the same dates are passed, but the date
> format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
> report 2 will either run with the wrong dates, or it will crash and not
> run at all if swapping month and day gives an invalid date.
> I have my report languages set to default so it should pick up my
> regional setting ok. But when passing these dates between reports the
> settings seem to be lost somehow.
> Can anyone help'
>
Passing Date Parameter to Oracle Query
ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is
WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')
When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.
But when I run the report from Preview, I get no results at all no matter what format I use in the function.
Any chance any of you have seen this and know how to work with it?
The question is what is the data type of the report parameter specified as? Try it as a string instead of a date.|||If I type it as string, then the user has to key in the date rather than use the calendar pop-up. I don't want to force that.|||Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.
I think what I need to know is this: If I set a report parameter's data type to DateTime, what is the format of the returned value? As mentioned in the first entry of this thread, what I need to do is:
WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')
'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?
|||Hi,Did you ever find a solution to this problem?
Cheers
Phil|||No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.|||
Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')
|||Use this syntax to omit time portion:
WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')
sqlPassing Date Parameter to Oracle Query
ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is
WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')
When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.
But when I run the report from Preview, I get no results at all no matter what format I use in the function.
Any chance any of you have seen this and know how to work with it?
The question is what is the data type of the report parameter specified as? Try it as a string instead of a date.|||If I type it as string, then the user has to key in the date rather than use the calendar pop-up. I don't want to force that.|||Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.
I think what I need to know is this: If I set a report parameter's data type to DateTime, what is the format of the returned value? As mentioned in the first entry of this thread, what I need to do is:
WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')
'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?
|||Hi,Did you ever find a solution to this problem?
Cheers
Phil|||No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.|||
Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')
|||Use this syntax to omit time portion:
WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')
Passing Date Parameter to Oracle Query
ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is
WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')
When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.
But when I run the report from Preview, I get no results at all no matter what format I use in the function.
Any chance any of you have seen this and know how to work with it?
The question is what is the data type of the report parameter specified as? Try it as a string instead of a date.|||If I type it as string, then the user has to key in the date rather than use the calendar pop-up. I don't want to force that.|||Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.
I think what I need to know is this: If I set a report parameter's data type to DateTime, what is the format of the returned value? As mentioned in the first entry of this thread, what I need to do is:
WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')
'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?
|||Hi,Did you ever find a solution to this problem?
Cheers
Phil
|||No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.|||
Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')
|||Use this syntax to omit time portion:
WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')
Passing Date Parameter to Oracle Query
ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is
WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')
When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.
But when I run the report from Preview, I get no results at all no matter what format I use in the function.
Any chance any of you have seen this and know how to work with it?
The question is what is the data type of the report parameter specified as? Try it as a string instead of a date.|||If I type it as string, then the user has to key in the date rather than use the calendar pop-up. I don't want to force that.|||Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.
I think what I need to know is this: If I set a report parameter's data type to DateTime, what is the format of the returned value? As mentioned in the first entry of this thread, what I need to do is:
WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')
'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?
|||Hi,Did you ever find a solution to this problem?
Cheers
Phil
|||No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.|||
Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')
|||Use this syntax to omit time portion:
WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')
Passing 'date' parameter to Jet 4.0 linked server - collation problem?
Hello,
I've attached Access MDB as a linked server to SQL 2000 server. Now I
want to run distributed queries. I set 'collation compatible' option,
so when I reference an Access MDB table in WHERE clause, the parameter
is passed to Access data provider. But I can't do the same for
Datetime columns! :-((
Unfortunately I have a large legacy application which uses dynamic SQL
creation, so it is VERY painful for me to rewrite&optimize all SQL
statements in order to use OPENQUERY statements :-((
Example:
Select * From MyMDB...Orders Where OrderDate Between '1/1/5' and
GETDATE()
This statement results in scanning all of the Orders table by SQL
server :-(
Please help me!> Select * From MyMDB...Orders Where OrderDate Between '1/1/5'
What the heck kind of date is that? Try '20050101', assuming that is the
date you meant. The fact that *I* don't know what date you are passing
should be some kind of clue as to why the software doesn't understand it.
I've attached Access MDB as a linked server to SQL 2000 server. Now I
want to run distributed queries. I set 'collation compatible' option,
so when I reference an Access MDB table in WHERE clause, the parameter
is passed to Access data provider. But I can't do the same for
Datetime columns! :-((
Unfortunately I have a large legacy application which uses dynamic SQL
creation, so it is VERY painful for me to rewrite&optimize all SQL
statements in order to use OPENQUERY statements :-((
Example:
Select * From MyMDB...Orders Where OrderDate Between '1/1/5' and
GETDATE()
This statement results in scanning all of the Orders table by SQL
server :-(
Please help me!> Select * From MyMDB...Orders Where OrderDate Between '1/1/5'
What the heck kind of date is that? Try '20050101', assuming that is the
date you meant. The fact that *I* don't know what date you are passing
should be some kind of clue as to why the software doesn't understand it.
Passing Date as String
I have a report that returns date in the format 'YYYY-MM-DD'. This report
has no parameters, but I am drilling to another report that accepts date as a
parameter. So, I select the textbox I want to drill from and select "Jump to
Report". I select the report to be drilled to and define the parameters that
are to be passed.
Parameter Parameter Value
date =Fields!date.Value
Next, I go to the report that is to be drilled to and add date as a
parameter. Parameter name "date", parameter data type "string". Now, when
the drill report passes the date to the drilled report, the date parameter is
filled in as:
MM/DD/YYYY 00:00:00
Why is RS converting my strings and what is the best way to either make it
stop or work around this problem.
Thanks for any help!Sprinkle liberally with:
=Format(Convert.ToDateTime(Fields!date.Value), "yyyy-MM-dd")
Cheers,
"asnewbie+rs=argh" wrote:
> I have a report that returns date in the format 'YYYY-MM-DD'. This report
> has no parameters, but I am drilling to another report that accepts date as a
> parameter. So, I select the textbox I want to drill from and select "Jump to
> Report". I select the report to be drilled to and define the parameters that
> are to be passed.
> Parameter Parameter Value
> date =Fields!date.Value
> Next, I go to the report that is to be drilled to and add date as a
> parameter. Parameter name "date", parameter data type "string". Now, when
> the drill report passes the date to the drilled report, the date parameter is
> filled in as:
> MM/DD/YYYY 00:00:00
> Why is RS converting my strings and what is the best way to either make it
> stop or work around this problem.
> Thanks for any help!
>
has no parameters, but I am drilling to another report that accepts date as a
parameter. So, I select the textbox I want to drill from and select "Jump to
Report". I select the report to be drilled to and define the parameters that
are to be passed.
Parameter Parameter Value
date =Fields!date.Value
Next, I go to the report that is to be drilled to and add date as a
parameter. Parameter name "date", parameter data type "string". Now, when
the drill report passes the date to the drilled report, the date parameter is
filled in as:
MM/DD/YYYY 00:00:00
Why is RS converting my strings and what is the best way to either make it
stop or work around this problem.
Thanks for any help!Sprinkle liberally with:
=Format(Convert.ToDateTime(Fields!date.Value), "yyyy-MM-dd")
Cheers,
"asnewbie+rs=argh" wrote:
> I have a report that returns date in the format 'YYYY-MM-DD'. This report
> has no parameters, but I am drilling to another report that accepts date as a
> parameter. So, I select the textbox I want to drill from and select "Jump to
> Report". I select the report to be drilled to and define the parameters that
> are to be passed.
> Parameter Parameter Value
> date =Fields!date.Value
> Next, I go to the report that is to be drilled to and add date as a
> parameter. Parameter name "date", parameter data type "string". Now, when
> the drill report passes the date to the drilled report, the date parameter is
> filled in as:
> MM/DD/YYYY 00:00:00
> Why is RS converting my strings and what is the best way to either make it
> stop or work around this problem.
> Thanks for any help!
>
Passing Date as aparameter to linked report from Parent report and then Dates format is Au
Hi
I need one urgent help.
When Passing Date as aparameter to linked report from Parent report,
the format of date is Automatically changing from dd/mm/yy to mm/dd/
yy. How to restrict it?
What is the solution for it in SP or rdl?
TIA.
PuneetPuneet,
Maybe you can also pass a parameter with the desired format, like "dd/mm/yy"
(this can be kept as a parameter in your application or database, so you can
change it dynamically).
Then, in the subreport, use formatting to display the date using the desired
format.
Andrei.
"appu" <ajmera.puneet@.gmail.com> wrote in message
news:1192120804.765315.73850@.o80g2000hse.googlegroups.com...
> Hi
> I need one urgent help.
> When Passing Date as aparameter to linked report from Parent report,
> the format of date is Automatically changing from dd/mm/yy to mm/dd/
> yy. How to restrict it?
> What is the solution for it in SP or rdl?
> TIA.
> Puneet
>
I need one urgent help.
When Passing Date as aparameter to linked report from Parent report,
the format of date is Automatically changing from dd/mm/yy to mm/dd/
yy. How to restrict it?
What is the solution for it in SP or rdl?
TIA.
PuneetPuneet,
Maybe you can also pass a parameter with the desired format, like "dd/mm/yy"
(this can be kept as a parameter in your application or database, so you can
change it dynamically).
Then, in the subreport, use formatting to display the date using the desired
format.
Andrei.
"appu" <ajmera.puneet@.gmail.com> wrote in message
news:1192120804.765315.73850@.o80g2000hse.googlegroups.com...
> Hi
> I need one urgent help.
> When Passing Date as aparameter to linked report from Parent report,
> the format of date is Automatically changing from dd/mm/yy to mm/dd/
> yy. How to restrict it?
> What is the solution for it in SP or rdl?
> TIA.
> Puneet
>
Subscribe to:
Posts (Atom)