Date range parameters don't work in reportviewer?

Hi, I've got this ssrs 2005 report that works great passing a few
security related parameters from asp.net codebehind. However, there
are two date related parameters that won't be coming from my web form,
but rather from the report form itself. When I test the report's date
parameters from visual studio it work fine, but when I attempt the
same report from a reportviewer no matter what input I place on the
report's date fields or even if I select the date picker, the report
simply resets to default and reloads. And actually the date picker
from the reportviewer does not not even pop up.
Here's my aspx code:
<%@. Page Language="VB" AutoEventWireup="false"
Inherits="_ReportViewer" MasterPageFile="~/Main/MasterPage.master"
CodeFile="~/Reports/CashSales.aspx.vb" Title="Cash Sales" %>
<%@. Register Assembly="Microsoft.ReportViewer.WebForms,
Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<asp:Content ID="Content1" ContentPlaceHolderID="Content1"
runat="Server">
<rsweb:ReportViewer BackColor="Transparent" ZoomMode="PageWidth"
Width="100%" ProcessingMode="Remote" ID="ReportViewer1"
runat="server">
<ServerReport ReportPath="/Retailer/CashSales"
ReportServerUrl="http://myserver/reportserver" />
</rsweb:ReportViewer>
</asp:Content>
the code behind:
Imports system.web.security.membership
Imports system.web.security.Roles
Imports Microsoft.Reporting.Webforms
Note: I'm only passing the parameters that are not coming from the
reportviewer form.
Partial Class _ReportViewer
Inherits System.Web.UI.Page
Private Users As New Retailer.Core()
Protected Overrides Sub OnLoad(ByVal e As EventArgs)
Dim Roles() As String = GetRolesForUser(Page.User.Identity.Name.ToString)
Dim cred As New Retailer.ReportServerCredentials("myuser",
"mypassword", "mydomain")
ReportViewer1.ServerReport.ReportServerCredentials = cred
Dim param As New ReportParameter("r_user",
Page.User.Identity.Name.ToString)
Dim param2 As New ReportParameter("r_role", Roles(0))
Dim p() As ReportParameter = {param, param2}
ReportViewer1.ServerReport.SetParameters(p)
ReportViewer1.ServerReport.Refresh()
End Sub
End Class
on my reportviewer form, at the top I have parameters for startdate
and end date, they are not set to internal or hidden.
thanks for any help or information.update on this. I see I am getting a javascript error, but it then
loading the report. How can I troubleshoot this? I've got disable i.e
script debugging turned off on i.e 7.0, but where would i place this
javascript to trap the error.
debugger; // execution will break here
to see the errors in VS.NET.
my report viewer in on a contentpage.
I see others have had a similar problem.
thanks for any help or information!|||More on this..
I noticed I get a javascript error when click on the calendar select:
Line: 606
Object Required
When I attempt to debug I get a Just-In-time failed : Unspecified
error. Check the documentation index for 'Just-in-time debugging,
errors' for more information.
and if I enter anything in the date fields, it disregards them and
loads with the defaults.
Could it be that I'm not sending security information when these built
in javascript functions are called?
thanks again.

Date picker parameter with MDX...

I am sure others have ran into this issue.

But I need to have date parameters using the datetime type and I need to be able to pass the correct value to the MDX.

For those of you that are interested you merely need to format the parameter values coming into your dataset your passing the report params to.

Ex.

Code Snippet

="[COE Date].[Date].&["&Format(Parameters!FromCOEDateDate.Value,"yyyy-MM-dd")+"T00:00:00]"

The above example shows the FromCOEDateDate parameter used in the report

Date Picker on Report Parameters

Is there a way to configure a Date Picker option on a report date type
parameter. The report parameter ("From Date") is defined as a date.
Thanks
--
Mike RottmannNo date picker in RS 2000, there is a date picker in RS 2005
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike" <Mrottmann@.micorsoft.com> wrote in message
news:D6D9528D-7CB5-4CBB-8DA8-9C1ACE6EA069@.microsoft.com...
> Is there a way to configure a Date Picker option on a report date type
> parameter. The report parameter ("From Date") is defined as a date.
> Thanks
> --
> Mike Rottmann|||Thanks.
Mike Rottmann
"Bruce L-C [MVP]" wrote:
> No date picker in RS 2000, there is a date picker in RS 2005
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike" <Mrottmann@.micorsoft.com> wrote in message
> news:D6D9528D-7CB5-4CBB-8DA8-9C1ACE6EA069@.microsoft.com...
> > Is there a way to configure a Date Picker option on a report date type
> > parameter. The report parameter ("From Date") is defined as a date.
> >
> > Thanks
> > --
> > Mike Rottmann
>
>

Date picker not showing...

I have report parameters configured to use date pickers...but they don't show up when I run the report. I see the date picker controls when I preview the report in design mode, but they don't show when I open the report in a browser. Any thoughts?

It could be unrelated, but the error I get when I try to run the report with text dates is:

Collection was modified after the enumerator was instantiated.Just to clarify a little. This is for RS 2005. When I "Preview" the report or run it thru the "Report Manager" the Date Pickers show up. But if I try to use the Report Viewer control (ie. ReprotViewer1) embedded in a form the Date Pickers don't show up.

Is this a bug or am I doning something wrong?

Thanks!|||The date picker did not make it into the controls in Visual Studio Beta 2. This will be available in the final version.|||

I was wondering what the status is on this issue?

I am having this exact same problem but am running VS 2005 8.0.50727. Could it be because I have the reportviewer in a webpart?

Thanks,

Lorna

Date picker not showing in RS2005

I have report parameters configured to use date pickers...but they don't show up when I run the report. I see the date picker controls when I preview the report in design mode, but they don't show when I open the report in a browser. Any thoughts?

It could be unrelated, but the error I get when I try to run the report with text dates is:

Collection was modified after the enumerator was instantiated.Just to clarify a little. This is for RS 2005. When I "Preview" the report or run it thru the "Report Manager" the Date Pickers show up. But if I try to use the Report Viewer control (ie. ReprotViewer1) embedded in a form the Date Pickers don't show up.

Is this a bug or am I doning something wrong?

Thanks!|||The date picker did not make it into the controls in Visual Studio Beta 2. This will be available in the final version.|||

I was wondering what the status is on this issue?

I am having this exact same problem but am running VS 2005 8.0.50727. Could it be because I have the reportviewer in a webpart?

Thanks,

Lorna

sql

Date picker not showing in RS2005

I have report parameters configured to use date pickers...but they don't show up when I run the report. I see the date picker controls when I preview the report in design mode, but they don't show when I open the report in a browser. Any thoughts?

It could be unrelated, but the error I get when I try to run the report with text dates is:

Collection was modified after the enumerator was instantiated.Just to clarify a little. This is for RS 2005. When I "Preview" the report or run it thru the "Report Manager" the Date Pickers show up. But if I try to use the Report Viewer control (ie. ReprotViewer1) embedded in a form the Date Pickers don't show up.

Is this a bug or am I doning something wrong?

Thanks!|||The date picker did not make it into the controls in Visual Studio Beta 2. This will be available in the final version.|||

I was wondering what the status is on this issue?

I am having this exact same problem but am running VS 2005 8.0.50727. Could it be because I have the reportviewer in a webpart?

Thanks,

Lorna

date parmeter converts into drop down

I am working in SSRS 2005. I have three parameters on the reports

Parmeter 1 is Date filter which is drop down and values are MTD,QTD,YTD...

2nd and 3rd parameters are fromdate and todate which are datetime parameters.

When user select let's say MTD from parameter1 then i have Stored procedure which populate the fromdate paramter with 1st date of the month and, todate populate the todays's date. The problem i am facing is the moment these date parameters get populated it converts into drop down. i want these date parameters should be still datetime so user can select the date. The value i am passing to these parameters are datetime type( Now()) .Still the date parameters controls are showing as dropdown. I don't know how to handle it. Please help me if you have faced this kind of problem.

Thanks,

Ranjay

Dropdown appears because parameter's "available values" option is set. In this case, it is not possible to enter value. Try to set default value instead of available values.|||Lev,thanks for your reply. I know that it will work for default but i want to populate the date parameters based on some other parameters selcted in report. Default value just shows one time. it doesn't change the value next time when i change the other parameter.|||Default value can be query based.|||

That's true but problem with default value is it's not getting refreshed when my other parameter change. Default value is set only the first time when it initialize. I don't know if i was able to explain the problem properly

|||

Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097549&SiteID=1

date parmeter converts into drop down

I am working in SSRS 2005. I have three parameters on the reports

Parmeter 1 is Date filter which is drop down and values are MTD,QTD,YTD...

2nd and 3rd parameters are fromdate and todate which are datetime parameters.

When user select let's say MTD from parameter1 then i have Stored procedure which populate the fromdate paramter with 1st date of the month and, todate populate the todays's date. The problem i am facing is the moment these date parameters get populated it converts into drop down. i want these date parameters should be still datetime so user can select the date. The value i am passing to these parameters are datetime type( Now()) .Still the date parameters controls are showing as dropdown. I don't know how to handle it. Please help me if you have faced this kind of problem.

Thanks,

Ranjay

Dropdown appears because parameter's "available values" option is set. In this case, it is not possible to enter value. Try to set default value instead of available values.|||Lev,thanks for your reply. I know that it will work for default but i want to populate the date parameters based on some other parameters selcted in report. Default value just shows one time. it doesn't change the value next time when i change the other parameter.|||Default value can be query based.|||

That's true but problem with default value is it's not getting refreshed when my other parameter change. Default value is set only the first time when it initialize. I don't know if i was able to explain the problem properly

|||

Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097549&SiteID=1

sql

Date paramter default value

Hello all,
I have written a report that I would like to be sent to a person or
persons at the beginning of the week. There are two parameters in the
report that the users would normally type the dates. I know I can to the
previous week thing by using a stored procedure, but I would like to
know how I can, at report design time, give those parameters a default
date. I would like to try and use a non-queried value expression like:
=CDATE(dateadd("dd",-7,NOW())) for the start date and
=CDATE(dateadd("dd",-2,NOW())) for the end date
Would this be a valid approach?
Thanks,
Jeff JonesJeffrey Jones wrote:
> Hello all,
> I have written a report that I would like to be sent to a person or
> persons at the beginning of the week. There are two parameters in the
> report that the users would normally type the dates. I know I can to the
> previous week thing by using a stored procedure, but I would like to
> know how I can, at report design time, give those parameters a default
> date. I would like to try and use a non-queried value expression like:
> =CDATE(dateadd("dd",-7,NOW())) for the start date and
> =CDATE(dateadd("dd",-2,NOW())) for the end date
> Would this be a valid approach?
> Thanks,
> Jeff Jones
Okay...I was able to get the default thing to work. I used
=CDATE(MONTH(NOW()) & "/" & DatePart("d",DateAdd("d",-1,NOW())) & "/" &
YEAR(NOW())). Now I seem to be having a bit of a time problem on my
reporting server. My time seems to be set to UTC and I need it set to
US-Central time. Can anyone tell me how to do this? The server machine
time is correct, it is just the reporting server's output that is off.
Thanks,
Jeff Jones|||Take a look at the function ToLocalTime to convert from UTC. If the regional
settings (timezone) set on your reportserver differs from desired output
format you may have to specify timezone params in code.
=MyUTCDateTime.ToLocalTime()
"Jeffrey Jones" wrote:
> Jeffrey Jones wrote:
> > Hello all,
> > I have written a report that I would like to be sent to a person or
> > persons at the beginning of the week. There are two parameters in the
> > report that the users would normally type the dates. I know I can to the
> > previous week thing by using a stored procedure, but I would like to
> > know how I can, at report design time, give those parameters a default
> > date. I would like to try and use a non-queried value expression like:
> > =CDATE(dateadd("dd",-7,NOW())) for the start date and
> > =CDATE(dateadd("dd",-2,NOW())) for the end date
> > Would this be a valid approach?
> > Thanks,
> > Jeff Jones
> Okay...I was able to get the default thing to work. I used
> =CDATE(MONTH(NOW()) & "/" & DatePart("d",DateAdd("d",-1,NOW())) & "/" &
> YEAR(NOW())). Now I seem to be having a bit of a time problem on my
> reporting server. My time seems to be set to UTC and I need it set to
> US-Central time. Can anyone tell me how to do this? The server machine
> time is correct, it is just the reporting server's output that is off.
> Thanks,
> Jeff Jones
>|||I'm new in Reporting Services. Could you please tell me how to use the
ToLocalTime Method within Reporting Services.
Thanks
"Frederik" wrote:
> Take a look at the function ToLocalTime to convert from UTC. If the regional
> settings (timezone) set on your reportserver differs from desired output
> format you may have to specify timezone params in code.
> =MyUTCDateTime.ToLocalTime()
> "Jeffrey Jones" wrote:
> > Jeffrey Jones wrote:
> > > Hello all,
> > > I have written a report that I would like to be sent to a person or
> > > persons at the beginning of the week. There are two parameters in the
> > > report that the users would normally type the dates. I know I can to the
> > > previous week thing by using a stored procedure, but I would like to
> > > know how I can, at report design time, give those parameters a default
> > > date. I would like to try and use a non-queried value expression like:
> > > =CDATE(dateadd("dd",-7,NOW())) for the start date and
> > > =CDATE(dateadd("dd",-2,NOW())) for the end date
> > > Would this be a valid approach?
> > > Thanks,
> > > Jeff Jones
> > Okay...I was able to get the default thing to work. I used
> > =CDATE(MONTH(NOW()) & "/" & DatePart("d",DateAdd("d",-1,NOW())) & "/" &
> > YEAR(NOW())). Now I seem to be having a bit of a time problem on my
> > reporting server. My time seems to be set to UTC and I need it set to
> > US-Central time. Can anyone tell me how to do this? The server machine
> > time is correct, it is just the reporting server's output that is off.
> > Thanks,
> > Jeff Jones
> >

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

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)Please check my response on your other thread with the same title. Thanks.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jadranka" <Jadranka@.discussions.microsoft.com> wrote in message
news:46164A41-C729-402B-B121-8FF9DFB9FBF4@.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)

Date parameters with annoying and unwanted Timestamp.

I have two date parameters which will be used as data range in my
report.
But when I enter same date in both date parameter text boxes it appends
the timestamp (12:00:00 AM).
Example: When I enter 07/24/2004 and 07/24/2004 it converts that into
07/24/2004 12:00:00 AM and 07/24/2004 12:00:00 AM.
If I'm generating a report with those parameters I shouldn't get any
data back if I can't take care of that datetime in my stored proc. But
for how many end users do I need to explain and ask them for not to
worry about it when it shows in the front end?
Isn't it really annoying?
Is there anyway to get rid of 12:00:00 AM?The only way is to change the report parameter from date to string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RK" <am_i_dotnet@.hotmail.com> wrote in message
news:1122317815.803912.144810@.o13g2000cwo.googlegroups.com...
>I have two date parameters which will be used as data range in my
> report.
> But when I enter same date in both date parameter text boxes it appends
> the timestamp (12:00:00 AM).
> Example: When I enter 07/24/2004 and 07/24/2004 it converts that into
> 07/24/2004 12:00:00 AM and 07/24/2004 12:00:00 AM.
> If I'm generating a report with those parameters I shouldn't get any
> data back if I can't take care of that datetime in my stored proc. But
> for how many end users do I need to explain and ask them for not to
> worry about it when it shows in the front end?
> Isn't it really annoying?
> Is there anyway to get rid of 12:00:00 AM?
>

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:

OK

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 in subscriptions

I have a standard on-demand report that now also needs to be e-mailed to a group of clerks. No problem there as I have several subscriptions in place already, however, they want this subscription to report from the system date to three days in the past. I could clone the report and change the date range to @.Startdate= GETDATE()-3 and @.Enddate = GETDATE() but then I would have to manage multiple reports that return the same thing. Is there a method in the date parameter field in subscriptions that can achieve the same thing?

Any ideas?

|||

There are 2 ways to do this:

1) change the parameter values for the report similar to what you were doing

2) use a data driven subscription with a 'dummy' query such as:

select pseudoDate1 as Start, pseudoDate2 as End

Then map the dates that are returned by the query to the report parameters.

-Lukasz

You'll have to write the query against your preferred data source.

Date parameters in subscriptions

I have a standard on-demand report that now also needs to be e-mailed to a group of clerks. No problem there as I have several subscriptions in place already, however, they want this subscription to report from the system date to three days in the past. I could clone the report and change the date range to @.Startdate= GETDATE()-3 and @.Enddate = GETDATE() but then I would have to manage multiple reports that return the same thing. Is there a method in the date parameter field in subscriptions that can achieve the same thing?

Any ideas?

|||

There are 2 ways to do this:

1) change the parameter values for the report similar to what you were doing

2) use a data driven subscription with a 'dummy' query such as:

select pseudoDate1 as Start, pseudoDate2 as End

Then map the dates that are returned by the query to the report parameters.

-Lukasz

You'll have to write the query against your preferred data source.

Date parameters in ="select.......where creationdate="&...."

Hi there,
I'm looking for the correct syntax for using datetime parameters in the SQL
statement.
The parameter is called ReceiveDate and defaults to =Today()
="select ...... where creationdate = "&.....
Thanks
LudoWhere you are writing the statemnt? In report or in stored procedure ?
If it is in report itself you can do as given belo:
If you declare ReceiveDate as DateTime Type. you can write the sql statement
directly .
Select ...... Where creationdate = @.ReceiveDate
If you Declare it as String then you need to use date conversion function
Select ...... Where creationdate = CDate(@.ReceiveDate)|||I think the issue here is that you are using an expression which is really a
mistake unless there is no other way. If you use the generic query designer
(two pane) then just do this:
select ... where creationdate = @.ReceiveDate
You don't have to do anything special. RS takes care of everything. If you
use an expression then you have to do a lot more (for instance, embed single
quotes).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm looking for the correct syntax for using datetime parameters in the
> SQL statement.
> The parameter is called ReceiveDate and defaults to =Today()
> ="select ...... where creationdate = "&.....
> Thanks
> Ludo
>|||Thanks,
it is in the report (RS 2005). It works when I put the parameter to string
and use query:
select part_number,serial_number from UNIT_STATUS_V where creation_time
between @.StartDate and @.EndDate
creation_time is a DateTime field.
But I'd rather use the parameter as a DateTime as well since then I get a
calender pick control.
I tried using CDate but with no result so far...
Ludo
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>I think the issue here is that you are using an expression which is really
>a mistake unless there is no other way. If you use the generic query
>designer (two pane) then just do this:
> select ... where creationdate = @.ReceiveDate
> You don't have to do anything special. RS takes care of everything. If you
> use an expression then you have to do a lot more (for instance, embed
> single quotes).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>|||Thanks,
it is in the report (RS 2005). It works when I put the parameter to string
and use query:
select part_number,serial_number from UNIT_STATUS_V where creation_time
between @.StartDate and @.EndDate
creation_time is a DateTime field.
But I'd rather use the parameter as a DateTime as well since then I get a
calender pick control.
I tried using CDate but with no result so far...
Ludo
"Rama Prasad" <RamaPrasad@.discussions.microsoft.com> wrote in message
news:827DCC76-9E60-40EF-9065-EBB1D9B4A335@.microsoft.com...
> Where you are writing the statemnt? In report or in stored procedure ?
> If it is in report itself you can do as given belo:
> If you declare ReceiveDate as DateTime Type. you can write the sql
> statement
> directly .
> Select ...... Where creationdate = @.ReceiveDate
> If you Declare it as String then you need to use date conversion function
> Select ...... Where creationdate = CDate(@.ReceiveDate)
>|||This is very odd. RS should be handling this. There is no reason for you to
have to use CDate or anything like that. Try changing your SQL to be like
this:
select part_number,serial_number from UNIT_STATUS_V where creation_time >=@.StartDate and creation_time <= @.EndDate
You definitely should be able to have the parameter be a datetime.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
news:ONBhiqI1FHA.3780@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> it is in the report (RS 2005). It works when I put the parameter to string
> and use query:
> select part_number,serial_number from UNIT_STATUS_V where creation_time
> between @.StartDate and @.EndDate
> creation_time is a DateTime field.
> But I'd rather use the parameter as a DateTime as well since then I get a
> calender pick control.
> I tried using CDate but with no result so far...
> Ludo
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>>I think the issue here is that you are using an expression which is really
>>a mistake unless there is no other way. If you use the generic query
>>designer (two pane) then just do this:
>> select ... where creationdate = @.ReceiveDate
>> You don't have to do anything special. RS takes care of everything. If
>> you use an expression then you have to do a lot more (for instance, embed
>> single quotes).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
>> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>>
>|||Hi again....
thanks for your help so far, I found the problem and I'll try to explain.
My date settings are set to british "dd/MM/yyyy". When I do the preview of
the report the EndDate parameter defaults to =DateAdd("d",-1,Today()) and
the StartDate to =Today(). The readable fields read 19/10/2005 and
20/10/2005 which look fine. When I click on the preview tab the report runs
but when I click on view report the system returns an erro on the date
parameter. Now...when I select a date in the calender picker the readable
fields shows the date correctly like dd/MM/yyyy but the report returns the
same error. But when I select fi 08/08/2005 till 09/08/2005 and I click
view report the report runs and the 2 visible fields previously filled by
the calender picker change to 08/08/2005 till 08/09/2005 so the reports runs
from august 8th till september 8th ...I don't know the reason but when I
deploy the report it seems to run correctly, so the problem only occurs in
the preview....
Could this be a bug in the RS 2005 or do you know some settings I need to
check ?
Anyway many thanks for your help.
Ludo Van Dun
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eMgscQL1FHA.2540@.TK2MSFTNGP09.phx.gbl...
> This is very odd. RS should be handling this. There is no reason for you
> to have to use CDate or anything like that. Try changing your SQL to be
> like this:
> select part_number,serial_number from UNIT_STATUS_V where creation_time >=> @.StartDate and creation_time <= @.EndDate
> You definitely should be able to have the parameter be a datetime.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
> news:ONBhiqI1FHA.3780@.TK2MSFTNGP12.phx.gbl...
>> Thanks,
>> it is in the report (RS 2005). It works when I put the parameter to
>> string and use query:
>> select part_number,serial_number from UNIT_STATUS_V where creation_time
>> between @.StartDate and @.EndDate
>> creation_time is a DateTime field.
>> But I'd rather use the parameter as a DateTime as well since then I get a
>> calender pick control.
>> I tried using CDate but with no result so far...
>> Ludo
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>>I think the issue here is that you are using an expression which is
>>really a mistake unless there is no other way. If you use the generic
>>query designer (two pane) then just do this:
>> select ... where creationdate = @.ReceiveDate
>> You don't have to do anything special. RS takes care of everything. If
>> you use an expression then you have to do a lot more (for instance,
>> embed single quotes).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
>> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>>
>>
>

Date Parameters dynamically defined

After searching and searching through this forum I have seen several similar
questions but no answers for my question so here it goes.
We have added front end screens for all of our reports. We also allow our
users to create subsciptions using these screens. For dates, our users would
like to enter "First day of the Current Fiscal Month". To compute this, it
requires a data base lookup.
In a perfect world I would be able to manipulate the input field (a date
that is defined as a string) convert it to the proper date, pass that in to
the SP and I would have my problem solved. However, I see no way to
manipulate an input parm.
I could code my SPs in such a way to handle this but I am using SPs that
already exist and accept dates as input parms. ADITTIONALLY, this doesn't
work for filters.
Is there a way to manipulate an input parm before it is used in the SP call?Yes there is. You can set your dataset source to an expression and the
expression can do any massaging you need it to do prior to calling the SP.
The downside of doing this is the field list not being populated so first
get the field list populated and then change it to an expression. In the
generic query designer do this:
= "mystoredproc " & code.yourcustomcode(parameters!parametername.value) &
etc
When I create custom code what I do is have a vb project with a textbox
(which will act as input to the code) and I develop the code in their so I
get all the nice debugging and development tools. Then I copy and paste into
RS.
HTH,
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ron Knott" <RonKnott@.discussions.microsoft.com> wrote in message
news:06347FC4-3E78-4C5B-A74D-7C458902F34B@.microsoft.com...
> After searching and searching through this forum I have seen several
> similar
> questions but no answers for my question so here it goes.
> We have added front end screens for all of our reports. We also allow our
> users to create subsciptions using these screens. For dates, our users
> would
> like to enter "First day of the Current Fiscal Month". To compute this,
> it
> requires a data base lookup.
> In a perfect world I would be able to manipulate the input field (a date
> that is defined as a string) convert it to the proper date, pass that in
> to
> the SP and I would have my problem solved. However, I see no way to
> manipulate an input parm.
> I could code my SPs in such a way to handle this but I am using SPs that
> already exist and accept dates as input parms. ADITTIONALLY, this doesn't
> work for filters.
> Is there a way to manipulate an input parm before it is used in the SP
> call?

date parameters

I need a parameter with the following options:
current week
last week
current month
current quarter
I have a dataset with today, monday of current week, current quarter number
and current month number, but i'm at a loss as to how to tie this in with my
report and how to put it into a parameter...any suggestions or referrals
would be most appreciated.Hi,
It is possible to ty values to a labels for a parameter through a query. All
you have to do is to create the dataset with two columns. One with you're
desired options and the other column with the corresponding number. In this
way it is possible to show the user the list of the four option, but use in
your report your own variables.
Jan Pieter Posthuma
"jmann" wrote:
> I need a parameter with the following options:
> current week
> last week
> current month
> current quarter
> I have a dataset with today, monday of current week, current quarter number
> and current month number, but i'm at a loss as to how to tie this in with my
> report and how to put it into a parameter...any suggestions or referrals
> would be most appreciated.|||This logic probably isn't right for your data but the basic idea should be
the same. For simplicity, I'm figuring that current week/month/quarter means
one week/month/quarter ago through today. You'll likely need to figure out
when each period begins using your data and make adjustments.
SELECT * FROM YourTables
WHERE OrderDate BETWEEN
CASE
WHEN @.Period = 'Current Week' THEN DATEADD(Week, -1, GETDATE())
WHEN @.Period = 'Last Week' THEN DATEADD(Week, -2, GETDATE())
WHEN @.Period = 'Current Month' THEN CAST(CAST(MONTH(GETDATE()) AS
VarChar(2)) + '-1-' + CAST(YEAR(GETDATE()) AS VarChar(4)) AS DateTime)
WHEN @.Period = 'Current Quarter' THEN DATEADD(Quarter, -1, GETDATE())
END
AND
CASE
WHEN @.Period = 'Last Week' THEN DATEADD(Week, -1, GETDATE())
ELSE GETDATE()
END
Paul Turley
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:E474B451-652E-405F-B698-D0D1642D907D@.microsoft.com...
>I need a parameter with the following options:
> current week
> last week
> current month
> current quarter
> I have a dataset with today, monday of current week, current quarter
> number
> and current month number, but i'm at a loss as to how to tie this in with
> my
> report and how to put it into a parameter...any suggestions or referrals
> would be most appreciated.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 without time

In the Report Parameters dialog I define my Parameter and set
Data Type to Datetime.
When I run report I get 22/2/2005 12:00:00 AM in parameter box on
top of report (parameters section of report), which is correct.
But I want to format this to appear and make input like 22/2/2005
ie.(dd/MM/yyyy) and without time part.
Thanks in advanceI use a string for all of my dates and it works fine. In my dataset or
SP that is called, I simply format the date and add 00:00:00 to the
start date, and 23:59:59 to the end date.|||Or you could use a custom code to return a short date string for that
field.
call Code.FormatDate(Fields!data.Value)
Function FormatDate(ByVal date as Object) as String
If IsDate(data) Then
Return CDate(date).ToShortDateString()
Else
Return String.Empty
End If
End Funtion