datetime report parameter default value

Issue 1:

I have a report parameter StartDateTime. I set the default value to Now(). When I go to preview, the StartDateTime parameter is empty and its been locked. I am not even able to set it to different value in preview.

Can anyone help me how to set the datetime parameter to default value(Now).

Issue 2:

I have a stored procedure which takes StartDateTime parameter. Whenever the report refreshes using autorefresh interval, the startdatetime should default to Now. Right now the startdatetime defaults to whatever the value is there before i hit view report. how to do that using stored procedure.

Thanks fro your help.

I don't know about Issue 2, but are you using "=Now()" to populate the StartDateTime report parameter? Also, is the report parameter type set to "datetime"? I think fixing Issue 1 should automatically solve Issue 2 since it looks like the report isn't populating with the correct default date value.|||

hey, Thanks for u'r reply. Now it started working. I mean using "=Now()" I could set the default value of StartDateTime.

Regarding Issue2, Whatever the value is there in the StartDateTime, its retaining for autorefresh. Its not taking the CurrentDateTime.

|||

Suppose the user enters a date for the startdate, and then the report refreshes. I assume that in that case you do not want the start date to change when the report .

If my assumption is correct, I think the best way for you to accomplish this would be to set the parameter to allow a null value, and use null to indicate that the user wants to run the report using the current time. This way, when the report refreshes the startdate will still be null, and the report data will adjust.

Unfortunately, there is no way to have the parameter show the current time and return null, and still allow the user to enter a date manually.

|||

Thanks for your reply. There is more clarification from the customer on the requirements. Little bit of changes to the original question I posted..

We are trying to use a SSRS report as a dashboard on a TV screen. I am facing two issues that I need advice on

Issue 1 : We are using End Date Time and Duration as user selectable parameters. We autorefresh the report once every 15 minutes using report properties refresh. Essentially the customer wants a rolling time window report the auto refreshes.

If we use Now() function for End Date Time, at the end of the 15 minutes it still uses the the time from first time instead of the time at the refresh. How do I fix this?

Issue 2: We are using a custom ASP.NET appliction in which the SSRS Report Viewer displays the report. When the report refreshes, it does not retain the window scroll position. How can I fix it?

Thanks in advance.

|||

Sorry it took so long for me to get back to you. hopefully you found a solution already, but if not here are my thoughts:

The first issue is actually two issues:

How to get a report to run using a 'rolling time period' on auto-refresh, and

how to get the parameters to change at refresh so that they match the reporting period.

The solution to the first is outlined above. Instead of using now(), use getdate() in your sql query.

The solution to the latter is more difficult, if not impossible. I would suggest not displaying the reporting period in the parameters (you can put it in the body of the report or something.)

I have had some luck in the past getting parameters to refresh if I put them in the available values instead of in the default values, but that doesn't seem to be working for me when I ran a quick test, so either I've forgotten how to do it, or I never actually got it to work the first time.

I'm not sure what the best way to approach the second problem is. Can you intercept the scroll events, or are they happening inside the ssrss viewer?

sql

datetime report parameter default value

Issue 1:

I have a report parameter StartDateTime. I set the default value to Now(). When I go to preview, the StartDateTime parameter is empty and its been locked. I am not even able to set it to different value in preview.

Can anyone help me how to set the datetime parameter to default value(Now).

Issue 2:

I have a stored procedure which takes StartDateTime parameter. Whenever the report refreshes using autorefresh interval, the startdatetime should default to Now. Right now the startdatetime defaults to whatever the value is there before i hit view report. how to do that using stored procedure.

Thanks fro your help.

I don't know about Issue 2, but are you using "=Now()" to populate the StartDateTime report parameter? Also, is the report parameter type set to "datetime"? I think fixing Issue 1 should automatically solve Issue 2 since it looks like the report isn't populating with the correct default date value.|||

hey, Thanks for u'r reply. Now it started working. I mean using "=Now()" I could set the default value of StartDateTime.

Regarding Issue2, Whatever the value is there in the StartDateTime, its retaining for autorefresh. Its not taking the CurrentDateTime.

|||

Suppose the user enters a date for the startdate, and then the report refreshes. I assume that in that case you do not want the start date to change when the report .

If my assumption is correct, I think the best way for you to accomplish this would be to set the parameter to allow a null value, and use null to indicate that the user wants to run the report using the current time. This way, when the report refreshes the startdate will still be null, and the report data will adjust.

Unfortunately, there is no way to have the parameter show the current time and return null, and still allow the user to enter a date manually.

|||

Thanks for your reply. There is more clarification from the customer on the requirements. Little bit of changes to the original question I posted..

We are trying to use a SSRS report as a dashboard on a TV screen. I am facing two issues that I need advice on

Issue 1 : We are using End Date Time and Duration as user selectable parameters. We autorefresh the report once every 15 minutes using report properties refresh. Essentially the customer wants a rolling time window report the auto refreshes.

If we use Now() function for End Date Time, at the end of the 15 minutes it still uses the the time from first time instead of the time at the refresh. How do I fix this?

Issue 2: We are using a custom ASP.NET appliction in which the SSRS Report Viewer displays the report. When the report refreshes, it does not retain the window scroll position. How can I fix it?

Thanks in advance.

|||

Sorry it took so long for me to get back to you. hopefully you found a solution already, but if not here are my thoughts:

The first issue is actually two issues:

How to get a report to run using a 'rolling time period' on auto-refresh, and

how to get the parameters to change at refresh so that they match the reporting period.

The solution to the first is outlined above. Instead of using now(), use getdate() in your sql query.

The solution to the latter is more difficult, if not impossible. I would suggest not displaying the reporting period in the parameters (you can put it in the body of the report or something.)

I have had some luck in the past getting parameters to refresh if I put them in the available values instead of in the default values, but that doesn't seem to be working for me when I ran a quick test, so either I've forgotten how to do it, or I never actually got it to work the first time.

I'm not sure what the best way to approach the second problem is. Can you intercept the scroll events, or are they happening inside the ssrss viewer?

DateTime parameter switches format

The reports I am creating in a VS2005 Business Intelligence Project
have DateTime parameters. To preview reports, after the parameters have
been entered in "Preview" tab, the "View Report" button has to be
clicked.
It looks to me that whenever the "View Report" is cliked, it causes
"datetime" parameter boxes to re-oder the dates so that "mm" and "dd"
values are interchanged.
For instance, if I enter "12/02/2006" (meaning Feb 02, 2006, in
Australian format ie. dd/mm/yyyy) and click "View Report" button, the
datatime parameter is rearranged as "02/12/2006", and consequenly
producing data for Dec 02, 2006.
Strangely, If I click "view report" again, it interchanges "dd" and
"mm" values again resulting the date I originally intended and produces
the correct report. Clicking "View Report" again causes "dd" and "mm"
to interchange, and so on it goes.
If I enter "20/02/2006" in the datetime parameter box, and click "View
Report" button, I get an error message about incorrect datetime format.
"An error occured during local report processing. The value provided
for the report parameter 'dtStartDate' is not valid for its type".
So, it appears that VS2005
* expects me to input datetime as "mm/dd/yyyy" format,
* rearranges "mm" and "dd" to "dd/mm/yyyy" format (whenever "View
Report" button is clicked) before passing the parameters to the stored
procedures.
I have set the report's "Language" property to "Australia", and WinXP's
language to "Australian English"
Any suggestions and workarounds are greatly appreciated.
Thanks
SurOne thing you can do is to change the parameter type to string, where you can
control the string format of the date... The down side to this is that you do
NOT get the Date-picker window...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"suranga.suranga@.gmail.com" wrote:
> The reports I am creating in a VS2005 Business Intelligence Project
> have DateTime parameters. To preview reports, after the parameters have
> been entered in "Preview" tab, the "View Report" button has to be
> clicked.
> It looks to me that whenever the "View Report" is cliked, it causes
> "datetime" parameter boxes to re-oder the dates so that "mm" and "dd"
> values are interchanged.
> For instance, if I enter "12/02/2006" (meaning Feb 02, 2006, in
> Australian format ie. dd/mm/yyyy) and click "View Report" button, the
> datatime parameter is rearranged as "02/12/2006", and consequenly
> producing data for Dec 02, 2006.
> Strangely, If I click "view report" again, it interchanges "dd" and
> "mm" values again resulting the date I originally intended and produces
> the correct report. Clicking "View Report" again causes "dd" and "mm"
> to interchange, and so on it goes.
> If I enter "20/02/2006" in the datetime parameter box, and click "View
> Report" button, I get an error message about incorrect datetime format.
> "An error occured during local report processing. The value provided
> for the report parameter 'dtStartDate' is not valid for its type".
> So, it appears that VS2005
> * expects me to input datetime as "mm/dd/yyyy" format,
> * rearranges "mm" and "dd" to "dd/mm/yyyy" format (whenever "View
> Report" button is clicked) before passing the parameters to the stored
> procedures.
> I have set the report's "Language" property to "Australia", and WinXP's
> language to "Australian English"
> Any suggestions and workarounds are greatly appreciated.
> Thanks
> Sur
>|||I had what sounds very like this problem as described in an earlier thread.
If it is the same problem, it should not occur when reports are deployed to
the report server. For me, it only occurs in development.
Ed Allison
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:F8583B54-267B-40F7-B105-D450934E8F53@.microsoft.com...
> One thing you can do is to change the parameter type to string, where you
> can
> control the string format of the date... The down side to this is that you
> do
> NOT get the Date-picker window...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "suranga.suranga@.gmail.com" wrote:
>> The reports I am creating in a VS2005 Business Intelligence Project
>> have DateTime parameters. To preview reports, after the parameters have
>> been entered in "Preview" tab, the "View Report" button has to be
>> clicked.
>> It looks to me that whenever the "View Report" is cliked, it causes
>> "datetime" parameter boxes to re-oder the dates so that "mm" and "dd"
>> values are interchanged.
>> For instance, if I enter "12/02/2006" (meaning Feb 02, 2006, in
>> Australian format ie. dd/mm/yyyy) and click "View Report" button, the
>> datatime parameter is rearranged as "02/12/2006", and consequenly
>> producing data for Dec 02, 2006.
>> Strangely, If I click "view report" again, it interchanges "dd" and
>> "mm" values again resulting the date I originally intended and produces
>> the correct report. Clicking "View Report" again causes "dd" and "mm"
>> to interchange, and so on it goes.
>> If I enter "20/02/2006" in the datetime parameter box, and click "View
>> Report" button, I get an error message about incorrect datetime format.
>> "An error occured during local report processing. The value provided
>> for the report parameter 'dtStartDate' is not valid for its type".
>> So, it appears that VS2005
>> * expects me to input datetime as "mm/dd/yyyy" format,
>> * rearranges "mm" and "dd" to "dd/mm/yyyy" format (whenever "View
>> Report" button is clicked) before passing the parameters to the stored
>> procedures.
>> I have set the report's "Language" property to "Australia", and WinXP's
>> language to "Australian English"
>> Any suggestions and workarounds are greatly appreciated.
>> Thanks
>> Sur
>>|||Ed and Wayne,
Thanks. I published the reports at a Reporting Server instance; it
doesn't suffer from this bug. I guess until a service pack is realised
for VS2005, I'll have to enter dates in the universal format
(yyyy-MM-dd) to overcome this bug.

datediff

Hi,
I'd like to calculate the days between 2 dates (data type:datetime).
unfortunately all I get in the preview report is #error and nothing
else. the 2 used date fields work when used seperately.
=DateDiff("day", Max(Fields!Date.Value), Min(Fields!Date.Value))
thanks!instead of using the word "day" use "d"
"florian" <florian.stammer@.gmail.com> wrote in message
news:1153840449.717417.120890@.i42g2000cwa.googlegroups.com...
> Hi,
> I'd like to calculate the days between 2 dates (data type:datetime).
> unfortunately all I get in the preview report is #error and nothing
> else. the 2 used date fields work when used seperately.
> =DateDiff("day", Max(Fields!Date.Value), Min(Fields!Date.Value))
>
> thanks!
>|||thanks it works!!!!
Ben Watts schrieb:
> instead of using the word "day" use "d"
> "florian" <florian.stammer@.gmail.com> wrote in message
> news:1153840449.717417.120890@.i42g2000cwa.googlegroups.com...
> > Hi,
> >
> > I'd like to calculate the days between 2 dates (data type:datetime).
> > unfortunately all I get in the preview report is #error and nothing
> > else. the 2 used date fields work when used seperately.
> >
> > =DateDiff("day", Max(Fields!Date.Value), Min(Fields!Date.Value))
> >
> >
> > thanks!
> >