Date/Time Stamp: reporting

Showing posts with label reporting. Show all posts

Sunday, March 25, 2012

Date Problem

I have a date problem within Reporting Services.

During ETL (SSIS 2005) process we converted a date e.g. purchase date from a file

which was 20070703 to 03/07/2007 00:00:00, (this is a standalone date not associated with dim_time)

and once i ran the fact table in sql query (in sql management studio 2005) the date does indeed come out as above, 03/07/2007.

So, the cube was built and i began building reports in SSRS 2005 when the above date has gone back to 2007-07-03 00:00:00.

I tried fromatting it to dd MMM yyyy but it still brings it out the wrong way.

Can anyone help me force the date back or am i missing something ?

Thanks

LakP wrote:

I tried fromatting it to dd MMM yyyy but it still brings it out the wrong way.

Be more specific, how and *where* did you try to format it? It should work fine if in your textboxes etc in the report if you do a Format(CDate(<your date>), <required format>) (use it in the expression property of the textbox).

Date Problem

I have a date problem within Reporting Services.

During ETL (SSIS 2005) process we converted a date e.g. purchase date from a file

which was 20070703 to 03/07/2007 00:00:00, (this is a standalone date not associated with dim_time)

and once i ran the fact table in sql query (in sql management studio 2005) the date does indeed come out as above, 03/07/2007.

So, the cube was built and i began building reports in SSRS 2005 when the above date has gone back to 2007-07-03 00:00:00.

I tried fromatting it to dd MMM yyyy but it still brings it out the wrong way.

Can anyone help me force the date back or am i missing something ?

Thanks

LakP wrote:

I tried fromatting it to dd MMM yyyy but it still brings it out the wrong way.

Date Picker in US Format after SP2

Updated SQL 2005 to SP2 and Reporting Services Date Picker now has dates defaulting to US format (MM/DD/YYYY) instead of our previous English-Australian (DD/MM/YYYY). Both the clients and server are set to English Australian regional settings in the O/S and the reports are composed for English Australian language. Reports have correct format in Visual Studio development and re-deploying them had no effect. Everything was fine under SP1 - not sure what is going on. Any ideas?

I have had so much trouble with date fields that I now keep them as text fields in Reporting Services and pass then through to SQL Server as VarChars(). Using three letters for the month (Jan, Feb, Mar etc) is the best way to may sure that SQL Server gets it right. I always set a default in the format "12 Apr 07 13:00" so the use sees what format is required. This means not using the date control but in my applications we time as well as date and the date control doesn't do this anyway.

The problem seems to be that there are many places to set the "country" or "language" etc.

Places that I can think of are:-

1. Control Panel/Regonal and Language Settings.

2. SQL Server using Set Language (or Set DateFormat). I believe that this affects the current session and so must be used in every report query.

3. Intenet Explorer, which is relevant when accessing Report Server.

I am not sure which SP I have but the patch level is 9.1.2153.

|||After doing some more analysis; I have found that if a datetime parameter prompt is placed after two parameters that cascade, then the correct date format (in our case English-Australian DD/MM/YYYY) is used in the Date Picker (probably due to the language settings in the client and report). Otherwise you get the US Date format (MM/DD/YYYY). This means that if you have datetime parameters on their own or even with drop downs, multi-selects etc.. then you could be in for trouble. I could be mistaken but this seems like a bug in SQL 2005 SP2.|||The problem has now been narrowed down to the Reporting Services Sharepoint web parts: rswebparts.cab (v2.0). Reports run with the correct date picker format from the report server but are incorrect if they are run from the SharePoint report viewer web part. I believe these web parts were not updated in SP2 (see http://msdn2.microsoft.com/en-us/library/ms159772.aspx)|||We get this same problem outside of sharepoint after sp2. If you go directly to the reportserver (http://yourserver/reportserver) and navigate to the report you may find the same. This problem has surfaced after the sp2 upgrade - strange thing is that is does not occur if we run the report via report manager - I expect why the sharepoint is showing the problem is because it is making a direct call to the reportserver.

Date Picker control returning date is US format

SQL 2005 SP2 + hotfix build 3152
I'm a newbie to reporting services and I am just learning and testing at the
moment. I am based in the UK.
I have set up a report with two parameters that are both dates.
When running the report in the IDE or http://ServerName/Reports dates picked
from the calendar control return as DD/MM/YYYY
When I run the report through http://Servername/ReportServer the date is
returned to the web page as MM/DD/YYYY.
I have found that if I choose a date like 02 Feb so I can run the report and
then use the date picker again without exiting, it then returns DD/MM/YYYY.
My client PC is set to UK English and I have checked my IE settings (I have
tried firefox too). The server is all set to UK english. I have checked the
settings on IIS as well.
I have tried forcing the culture to "en-GB" in web.config.
It's interesting to note that the report services log files (eg
ReportServerWebApp__03_16_2007_14_29_30.log) contains en-US.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
9.00.3152.00</Product>
<Locale>en-US</Locale>
<TimeZone>GMT Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\LogFiles\ReportServerWebApp__03_16_2007_14_29_30.log</Path>
<SystemName>myservername</SystemName>
<OSName>Microsoft Windows NT 5.2.3790 Service Pack 2</OSName>
<OSVersion>5.2.3790.131072</OSVersion>
</Header>On 16 mar, 16:05, "Paul Cahill" <a...@.anon.com> wrote:
> SQL2005 SP2 + hotfix build 3152
> I'm a newbie to reporting services and I am just learning and testing at the
> moment. I am based in the UK.
> I have set up a report with two parameters that are both dates.
> When running the report in the IDE orhttp://ServerName/Reportsdates picked
> from the calendar control return as DD/MM/YYYY
> When I run the report throughhttp://Servername/ReportServerthe date is
> returned to the web page as MM/DD/YYYY.
> I have found that if I choose a date like 02 Feb so I can run the report and
> then use thedate pickeragain without exiting, it then returns DD/MM/YYYY.
> My client PC is set to UK English and I have checked my IE settings (I have
> tried firefox too). The server is all set to UK english. I have checked the
> settings on IIS as well.
> I have tried forcing the culture to "en-GB" in web.config.
> It's interesting to note that the report services log files (eg
> ReportServerWebApp__03_16_2007_14_29_30.log) contains en-US.
> <Header>
> <Product>MicrosoftSQLServer Reporting Services Version
> 9.00.3152.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>GMT Standard Time</TimeZone>
> <Path>C:\Program Files\MicrosoftSQLServer\MSSQL.3\Reporting
> Services\LogFiles\ReportServerWebApp__03_16_2007_14_29_30.log</Path>
> <SystemName>myservername</SystemName>
> <OSName>Microsoft Windows NT 5.2.3790 Service Pack 2</OSName>
> <OSVersion>5.2.3790.131072</OSVersion>
> </Header>
Exactly the same problem here, it is caused by SP2 and his new date-
picker control. Anyone knows if MS is working to correct this?. Thanks.

Thursday, March 22, 2012

Date parameters: yesterday's date

I'm having a problem in the report designer with setting yesterday's date as
the default date parameters (Reporting From 'yesterday' To 'yesterday') for a
report. Both the From & To parameters need to default to yesterday, but the
user can enter a different date if they wish.
I've tried using =DateTime.Now().AddDays(-1) as well as =Today.AddDays(-1),
but I always get the following error when trying to preview the report in the
designer:
An unexpected error occurred while compiling expressions. Native compiler
return value: â'[BC30260] 'EH_ReportingFrom' is already declared as 'Class
EH_ReportingFrom' in this class.â'.
I'd appreciate any suggestions on how I can get this to work.
--
Jadranka Krapic
DBA
Stargate Technologies
(www.stargatetech.com.au)Do you have a dataset called "ReportingFrom" in the report? If yes, either
rename the dataset or the parameter to resolve that error. BTW: this won't
happen on RS 2005.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jadranka" <Jadranka@.discussions.microsoft.com> wrote in message
news:5E3DE809-7FD0-46A2-8CBB-3B4AA943F35B@.microsoft.com...
> I'm having a problem in the report designer with setting yesterday's date
> as
> the default date parameters (Reporting From 'yesterday' To 'yesterday')
> for a
> report. Both the From & To parameters need to default to yesterday, but
> the
> user can enter a different date if they wish.
> I've tried using =DateTime.Now().AddDays(-1) as well as
> =Today.AddDays(-1),
> but I always get the following error when trying to preview the report in
> the
> designer:
> An unexpected error occurred while compiling expressions. Native compiler
> return value: '[BC30260] 'EH_ReportingFrom' is already declared as 'Class
> EH_ReportingFrom' in this class.'.
> I'd appreciate any suggestions on how I can get this to work.
> --
> Jadranka Krapic
> DBA
> Stargate Technologies
> (www.stargatetech.com.au)|||Thanks for the quick response!
No, I don't have a ReportingFrom dataset in the report. The only dataset on
the report is called UpfrontFeesPayments, which calls a stored procedure.
Any other ideas?
"Robert Bruckner [MSFT]" wrote:
> Do you have a dataset called "ReportingFrom" in the report? If yes, either
> rename the dataset or the parameter to resolve that error. BTW: this won't
> happen on RS 2005.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jadranka" <Jadranka@.discussions.microsoft.com> wrote in message
> news:5E3DE809-7FD0-46A2-8CBB-3B4AA943F35B@.microsoft.com...
> > I'm having a problem in the report designer with setting yesterday's date
> > as
> > the default date parameters (Reporting From 'yesterday' To 'yesterday')
> > for a
> > report. Both the From & To parameters need to default to yesterday, but
> > the
> > user can enter a different date if they wish.
> >
> > I've tried using =DateTime.Now().AddDays(-1) as well as
> > =Today.AddDays(-1),
> > but I always get the following error when trying to preview the report in
> > the
> > designer:
> >
> > An unexpected error occurred while compiling expressions. Native compiler
> > return value: '[BC30260] 'EH_ReportingFrom' is already declared as 'Class
> > EH_ReportingFrom' in this class.'.
> >
> > I'd appreciate any suggestions on how I can get this to work.
> >
> > --
> > Jadranka Krapic
> > DBA
> > Stargate Technologies
> > (www.stargatetech.com.au)
>
>|||I've gone back and re-created the parameters using different parameter names.
It works now (using both =DateTime.Now().AddDays(-1) and =Today.AddDays(-1)).
Thanks for your help.
"Jadranka" wrote:
> Thanks for the quick response!
> No, I don't have a ReportingFrom dataset in the report. The only dataset on
> the report is called UpfrontFeesPayments, which calls a stored procedure.
> Any other ideas?
> "Robert Bruckner [MSFT]" wrote:
> > Do you have a dataset called "ReportingFrom" in the report? If yes, either
> > rename the dataset or the parameter to resolve that error. BTW: this won't
> > happen on RS 2005.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Jadranka" <Jadranka@.discussions.microsoft.com> wrote in message
> > news:5E3DE809-7FD0-46A2-8CBB-3B4AA943F35B@.microsoft.com...
> > > I'm having a problem in the report designer with setting yesterday's date
> > > as
> > > the default date parameters (Reporting From 'yesterday' To 'yesterday')
> > > for a
> > > report. Both the From & To parameters need to default to yesterday, but
> > > the
> > > user can enter a different date if they wish.
> > >
> > > I've tried using =DateTime.Now().AddDays(-1) as well as
> > > =Today.AddDays(-1),
> > > but I always get the following error when trying to preview the report in
> > > the
> > > designer:
> > >
> > > An unexpected error occurred while compiling expressions. Native compiler
> > > return value: '[BC30260] 'EH_ReportingFrom' is already declared as 'Class
> > > EH_ReportingFrom' in this class.'.
> > >
> > > I'd appreciate any suggestions on how I can get this to work.
> > >
> > > --
> > > Jadranka Krapic
> > > DBA
> > > Stargate Technologies
> > > (www.stargatetech.com.au)
> >
> >
> >|||I've also done this by crating another data set with a single sql query
that returns yesterday, the first of the month, and the end of the
month. That way I can put them in a drop down, or let them be
defaults.
Works like a charm.

Date parameters: yesterday

Date Parameters show as Strings when using Analysis Services as a data source

We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.

The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.

I have two quick questions:

Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes? Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.

Code Snippet

SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@.FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@.ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@.LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@.LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.

Thanks very much in advance for your help.

Regards,

Steve

Steve,

i've been there, done that, and it can be done.

For question 1, change your parameter type to a DateTime (no dataset required), and you will get the calendar control - you are getting a dropdown probably because you have got it as a string type and because you are supplying a dataset of values from the date heirarchy of your cube. The real trick to this is converting the output of the calendar control to something that resembles the members of your date heirarchy.

For question 2, you need to change the way your date is represented so that you can include it as a parameter in your MDX. See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1480488&SiteID=1 for some actual examples i posted for someone else.

|||

Thanks for your help. I'm still missing something, because I'm still throwing an error. Could you take a look and see where I went wrong?

1. Opened the parameters created by the Wizard. Changed the Data type to DateTime. There are no boxes checked under Properties. Available values was changed to Non-queried; left the Label/Value matrix blank. Default values: Null.

2. Added Format(CDate(@.parameter),"yyyy-MM-ddT00:00:00".... to the MDX query. See below:

Code Snippet

SELECTNONEMPTY { [Measures].[Lead] } ONCOLUMNS, NONEMPTY { ([Store].[Store Region Type].[Store Region Type].ALLMEMBERS * [Store].[Store Region Name].[Store Region Name].ALLMEMBERS * [Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( STRTOMEMBER(Format(CDate(@.FromLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00"), CONSTRAINED) : STRTOMEMBER(Format(CDate(@.ToLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00"), CONSTRAINED) ) ONCOLUMNSFROM [Referral Leads]) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

3. For reference, I ran the query under the Dataset that the Wizard created for the parameter FoLeadCreationDateCalendarDate. The results of that query show the ParameterValue field to be populated with values of the format [LeadCreationDate].[Calendar Date].&[2000-01-02T00:00:00]. This is why I selected "yyyy-MM-ddT00:00:00" as my format string.

4. The error I am throwing is:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

The following system error occurred: Type mismatch. . (Microsoft SQL Server 2005 Analysis Services)

BUTTONS:

After re-reading your post, I also tried:

Code Snippet

SELECTNONEMPTY { [Measures].[Lead] } ONCOLUMNS, NONEMPTY { ([Store].[Store Region Type].[Store Region Type].ALLMEMBERS * [Store].[Store Region Name].[Store Region Name].ALLMEMBERS * [Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( STRTOMEMBER("[LeadCreationDate].[Calendar Date].&[" + Format(CDate(@.FromLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) : STRTOMEMBER("[LeadCreationDate].[Calendar Date].&[" + Format(CDate(@.ToLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) ) ONCOLUMNSFROM [Referral Leads]) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Also gave the same error.

Any help on this would be tremendously appreciated.

- Steve

|||

OK - it took me a few days but I figured out how to do it:

1. Develop the report using the Wizard.

2. As sluggy mentioned, change the Data Type of the date parameters to DateTime, and set Available Values to Non-queried.

3. On the Data tab, edit the cube's Dataset (click on the ... button).

4. Click on the Parameters tab of the Dataset dialog box. Replace the Value of the date parameters with ="[Dimension].[Member].&[" + Format(CDate(Parameters!ParameterName.Value), "yyyy-MM-ddT00:00:00") + "]" where [Dimension].[Member] and ParameterName are the dimensions and parameter names of your particular cube. Depending on how your dimension is set up, you may also need to change the format of the date.

5. There is no need to change the MDX of the query.

sql

Date Parameters

Hello,
I have some Reporting Services users who want to setup subscriptions for
reports and enter the current month, trailing 7 days, this week, etc. in the
start and end date parameter fields. Can they do this with code (i.e.
currentdate(-7)) or does this have to be setup as a schedule on the server?
Any assistance is appreciated
Thank you
David Mahler
dmahler@.superwarehouse.comDear David,
I am not sure if I understand your question well, but if you need to
address last week, you may use sql like
select ...
from ...
where date between dateadd(day,-7,getdate()) and getdate().
getdate() will give you current date-time, and dateadd will shift back
7 days.
See dateadd() for more help.
Best Regards,
leecz
On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
Mah...@.discussions.microsoft.com> wrote:
> Hello,
> I have some Reporting Services users who want to setup subscriptions for
> reports and enter the current month, trailing 7 days, this week, etc. in t=he
> start and end date parameter fields. Can they do this with code (i.e.
> currentdate(-7)) or does this have to be setup as a schedule on the server=?
> Any assistance is appreciated
> Thank you
> David Mahler
> dmah...@.superwarehouse.com|||give an exampl as how you would like to see the subscription..
On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> Dear David,
> I am not sure if I understand your question well, but if you need to
> address last week, you may use sql like
> select ...
> from ...
> where date between dateadd(day,-7,getdate()) and getdate().
> getdate() will give you current date-time, and dateadd will shift back
> 7 days.
> See dateadd() for more help.
> Best Regards,
> leecz
> On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
>
> Mah...@.discussions.microsoft.com> wrote:
> > Hello,
> > I have some Reporting Services users who want to setup subscriptions for=
> > reports and enter the current month, trailing 7 days, this week, etc. in= the
> > start and end date parameter fields. Can they do this with code (i.e.
> > currentdate(-7)) or does this have to be setup as a schedule on the serv=er?
> > Any assistance is appreciated
> > Thank you
> > David Mahler
> > dmah...@.superwarehouse.com- Hide quoted text -
> - Show quoted text -|||Thanks for your replies. I apologize for not being clear in my description.
I am referrring to the front end web interface where users can setup their
own subscriptions.
The user creates a new subscription for a report. The select the delivery
method, how often the report runs, and other parrameters. When they enter
the Start Date and End Date (might be with the calendar control), could they
enter a getdate(-7) or something like that to run the report for the current
month, trailing week, etc.
My thought is that this may have to be done on the back end. I would hope
that the user can set the report to run every week and get the current week's
data on an automated basis.
Thanks for your help
David
"Sridar K" wrote:
> give an exampl as how you would like to see the subscription..
> On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> > Dear David,
> > I am not sure if I understand your question well, but if you need to
> > address last week, you may use sql like
> > select ...
> > from ...
> > where date between dateadd(day,-7,getdate()) and getdate().
> > getdate() will give you current date-time, and dateadd will shift back
> > 7 days.
> > See dateadd() for more help.
> >
> > Best Regards,
> > leecz
> >
> > On 1æ'4æ?¥, ä¸?å'1æ?¶53å', David Mahler <David
> >
> >
> >
> > Mah...@.discussions.microsoft.com> wrote:
> > > Hello,
> >
> > > I have some Reporting Services users who want to setup subscriptions for
> > > reports and enter the current month, trailing 7 days, this week, etc. in the
> > > start and end date parameter fields. Can they do this with code (i.e.
> > > currentdate(-7)) or does this have to be setup as a schedule on the server?
> >
> > > Any assistance is appreciated
> >
> > > Thank you
> >
> > > David Mahler
> > > dmah...@.superwarehouse.com- Hide quoted text -
> >
> > - Show quoted text -
>

date parameter in reporting services

Hi
I need to have the following result format into a date time parameter with
the current date
='1/07/2005 5:00:00 AM'
so when i schudule this report to run it will look at the current date at 5a
m.
Thankyou
toddTango
Can you eloborate what you expect
Regards
R.D
"Tango" wrote:

> Hi
> I need to have the following result format into a date time parameter with
> the current date
> ='1/07/2005 5:00:00 AM'
> so when i schudule this report to run it will look at the current date at
5am.
> Thankyou
> todd
>|||my expectation is that when i schedule this report to run it looks at the da
y
at 5am & reports back. All records in my database have a date of at 5am.
so the parameter output should be in datetimeformat showing d/MM/yy h:mm:ss
tt
Thanks
Todd
"R.D" wrote:
> Tango
> Can you eloborate what you expect
> Regards
> R.D
> "Tango" wrote:
>sql

Date parameter format incorrectly interpreted

Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.

My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"

If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.

If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.

Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!

Thanks again

John

|||

Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.

So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?

|||

If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.

In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format incorrectly interpreted

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks again

John

|||

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format incorrectly interpreted

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks again

John

|||

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format incorrectly interpreted

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks again

John

|||

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

sql

Date parameter format incorrectly interpreted

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks again

John

|||

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format incorrectly interpreted

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks again

John

|||

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format incorrectly interpreted

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks again

John

|||

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format incorrectly interpreted

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks again

John

|||

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date Parameter Format - help!

Hello - I am using Reporting Services 2000 / SQL 2000 and have a problem with
the way input date parameters display on the report.
I have start and end date parameters and have set the report to English(NZ)
- if i enter 1-12-2004 and 1-12-2006, when these are displayed on the report
they are always shown in US format - ie 12 Jan 2004
to display the date on the report i use this code: ="Date range between " &
Format(Parameters!startDate.Value,"d-MMM-yyyy") & " and " &
Format(Parameters!endDate.Value,"d-MMM-yyyy")
any suggestions?On the report properties, change your report language to English(NZ).
Andy Pottersql

Date Parameter ERROR

My RS has two date parameters setup as a date data type. If the user was to
key in 01102004 instead of 01/10/2004 they get this error;
Reporting Services Error
"The value provided for the report parameter 'P_FromDate' is not valid for
its type. (rsReportParameterTypeMismatch)"
How can set that parameter up so I can enter in either of the above dates
formats? I need to beable to do error handling on these two fields.Have the parameter be of string type and then base your query on a dynamic
sql (an expression). The expression can call code that parses the date. But
if it is a bad date it gets tricky to give an error message. To have more
control of the parameters you would need to have your own asp page that is
used to get the parameters and then use either URL integration or web
services to integrate with RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"doug" <doug@.discussions.microsoft.com> wrote in message
news:44D70CDD-0EF9-4BA1-82F0-ADFD748E45D8@.microsoft.com...
> My RS has two date parameters setup as a date data type. If the user was
to
> key in 01102004 instead of 01/10/2004 they get this error;
> Reporting Services Error
> "The value provided for the report parameter 'P_FromDate' is not valid for
> its type. (rsReportParameterTypeMismatch)"
> How can set that parameter up so I can enter in either of the above dates
> formats? I need to beable to do error handling on these two fields.

Date Parameter - run through date not to date

Is there a way in reporting services to define your date parameters as
including the final date? Right now if someone enters 08/22/2005 for the
start date and 08/29/2005 for end date it will run from 08/22/2005 12:00 AM
TO 08/29/2005 12:00 AM. This will not include results from the 29th - which
is not what the users expect.
I'd like for it to run through the date - so 08/29/2005 23:59 or 11:59 PM
Thanks for any help.
CoryIF THE START DATE PARAMETER IS NAMED SDATE AND THE END DATE PARAMETER IS
NAMED EDATE AND THE USER ENTERS EITHER AUG 30 2005 , 2005-08-30 OR 08/30/2005
YOU CAN CONVERT THE PARAMETER IN THE QUERY
SELECT *
FROM [TABLE]
WHERE CONVERT(VARCHAR(11),[DATE],101) >= CONVERT(VARCHAR(11),@.SDATE,101)
AND CONVERT(VARCHAR(11),[DATE],101) >= CONVERT(VARCHAR(11),@.EDATE,101)
THIS IS ONE OF MANY WAYS IT CAN BE DONE
P.S. SORRY FOR THE CAPS TO LAZY TO HIT THE CAPS LOCK : )
"Cory" wrote:
> Is there a way in reporting services to define your date parameters as
> including the final date? Right now if someone enters 08/22/2005 for the
> start date and 08/29/2005 for end date it will run from 08/22/2005 12:00 AM
> TO 08/29/2005 12:00 AM. This will not include results from the 29th - which
> is not what the users expect.
> I'd like for it to run through the date - so 08/29/2005 23:59 or 11:59 PM
> Thanks for any help.
> Cory|||OOPS THE >= ON THE @.EDATE SHOULD BE <= HEHE.. DUNNO HOW TO EDIT MY POST
"EsWallace" wrote:
> IF THE START DATE PARAMETER IS NAMED SDATE AND THE END DATE PARAMETER IS
> NAMED EDATE AND THE USER ENTERS EITHER AUG 30 2005 , 2005-08-30 OR 08/30/2005
> YOU CAN CONVERT THE PARAMETER IN THE QUERY
> SELECT *
> FROM [TABLE]
> WHERE CONVERT(VARCHAR(11),[DATE],101) >= CONVERT(VARCHAR(11),@.SDATE,101)
> AND CONVERT(VARCHAR(11),[DATE],101) <= CONVERT(VARCHAR(11),@.EDATE,101)
> THIS IS ONE OF MANY WAYS IT CAN BE DONE
> P.S. SORRY FOR THE CAPS TO LAZY TO HIT THE CAPS LOCK : )
> "Cory" wrote:
> > Is there a way in reporting services to define your date parameters as
> > including the final date? Right now if someone enters 08/22/2005 for the
> > start date and 08/29/2005 for end date it will run from 08/22/2005 12:00 AM
> > TO 08/29/2005 12:00 AM. This will not include results from the 29th - which
> > is not what the users expect.
> >
> > I'd like for it to run through the date - so 08/29/2005 23:59 or 11:59 PM
> >
> > Thanks for any help.
> >
> > Cory|||Thank you for your help! (I figured the less/greater than was backwards).
Thanks again.
--Cory
"EsWallace" wrote:
> OOPS THE >= ON THE @.EDATE SHOULD BE <= HEHE.. DUNNO HOW TO EDIT MY POST
> "EsWallace" wrote:
> > IF THE START DATE PARAMETER IS NAMED SDATE AND THE END DATE PARAMETER IS
> > NAMED EDATE AND THE USER ENTERS EITHER AUG 30 2005 , 2005-08-30 OR 08/30/2005
> > YOU CAN CONVERT THE PARAMETER IN THE QUERY
> >
> > SELECT *
> > FROM [TABLE]
> > WHERE CONVERT(VARCHAR(11),[DATE],101) >= CONVERT(VARCHAR(11),@.SDATE,101)
> > AND CONVERT(VARCHAR(11),[DATE],101) <= CONVERT(VARCHAR(11),@.EDATE,101)
> >
> > THIS IS ONE OF MANY WAYS IT CAN BE DONE
> >
> > P.S. SORRY FOR THE CAPS TO LAZY TO HIT THE CAPS LOCK : )
> > "Cory" wrote:
> >
> > > Is there a way in reporting services to define your date parameters as
> > > including the final date? Right now if someone enters 08/22/2005 for the
> > > start date and 08/29/2005 for end date it will run from 08/22/2005 12:00 AM
> > > TO 08/29/2005 12:00 AM. This will not include results from the 29th - which
> > > is not what the users expect.
> > >
> > > I'd like for it to run through the date - so 08/29/2005 23:59 or 11:59 PM
> > >
> > > Thanks for any help.
> > >
> > > Cory

Sunday, March 25, 2012

Date Problem

Date Problem

Date Picker in US Format after SP2

Date Picker control returning date is US format

Thursday, March 22, 2012

Date parameters: yesterday's date

Date parameters: yesterday

Date Parameters show as Strings when using Analysis Services as a data source

Date Parameters

date parameter in reporting services

Date parameter format incorrectly interpreted

Date parameter format incorrectly interpreted

Date parameter format incorrectly interpreted

Date parameter format incorrectly interpreted

Date parameter format incorrectly interpreted

Date parameter format incorrectly interpreted

Date parameter format incorrectly interpreted

Date parameter format incorrectly interpreted

Date Parameter Format - help!

Date Parameter ERROR

Date Parameter - run through date not to date

Date/Time Stamp

Blog Archive

About Me