SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Reporting services - default value of today
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dwill
Starting Member

4 Posts

Posted - 08/10/2009 :  04:20:45  Show Profile  Reply with Quote
First of all apologies as I know this is a SQL 2000 related question - but there isn't a Reporting Services board in the 2000 forum, and I am hoping that this question is relevant to RS2005... so bear with me!

The problem is infuriating me! - and hopefully is simlpe enough for those 'in the know'.

I am using SQL 2000 Reporting Services - and manually editing the RDL (no IDE/GUI to desing in). "All" I want to do is to set a default display value of "today's" date in a parameter box - but I can so far only get the default to display the date WITH a time stamp.

The RDL snippet is:

<ReportParameter Name="DateTo">
<DataType>DateTime</DataType>
<AllowBlank>false</AllowBlank>
<Prompt>Date To (DD/MM/YYYY):</Prompt>
<DefaultValue>
<Values>
<Value>=Now</Value>
</Values>
</DefaultValue>
</ReportParameter>

the above "now" produces the value "06/08/2009 13:26:32" - but I don't want the time part to show.

Similarly "today" produces "06/08/2009 00:00:00" again - the timestamp is there.

I've managed to display the correct date by changing the DataType to String and hacking off the final characters, but that seems fundamentally wrong as I want the parameter to be a valid date.


Hopefully this is possible (it must be!) and hopefully the above makes sense.

Thanks in advance!

JCirocco
Constraint Violating Yak Guru

USA
392 Posts

Posted - 08/10/2009 :  09:29:22  Show Profile  Visit JCirocco's Homepage  Click to see JCirocco's MSN Messenger address  Reply with Quote

How about this?

<Style>
<Format>MM/dd/yyyy</Format>
</Style>

<Value>=today()</Value>
Go to Top of Page

JCirocco
Constraint Violating Yak Guru

USA
392 Posts

Posted - 08/10/2009 :  09:31:50  Show Profile  Visit JCirocco's Homepage  Click to see JCirocco's MSN Messenger address  Reply with Quote
Sorry - forgot to state that you must use upper case MM because lower case mm refers to milliseconds
Go to Top of Page

dwill
Starting Member

4 Posts

Posted - 08/10/2009 :  09:57:57  Show Profile  Reply with Quote
First of all thanks for the reply - it's been a quiet topic!

I don't know if it's because it's RS2000 I am using, but that doesn't work I'm afraid. As far as i can tell the Style element cannot be a child of either ReportParameter or DefaultValue (and I get an error to confirm this). Unless I am using it in the wrong place?

I have tried both:


<ReportParameter Name="DateTo">
<DataType>DateTime</DataType>
<AllowBlank>false</AllowBlank>
<Prompt>Date To (DD/MM/YYYY):</Prompt>
<Style>
<Format>MM/dd/yyyy</Format>
</Style>
<DefaultValue>
<Values>
<Value>=Today</Value>
</Values>
</DefaultValue>
</ReportParameter>


and


<ReportParameter Name="DateTo">
<DataType>DateTime</DataType>
<AllowBlank>false</AllowBlank>
<Prompt>Date To (DD/MM/YYYY):</Prompt>
<DefaultValue>
<Values>
<Value>=Today</Value>
<Style>
<Format>MM/dd/yyyy</Format>
</Style>
</Values>
</DefaultValue>
</ReportParameter>
Go to Top of Page

cranky franky
Starting Member

USA
12 Posts

Posted - 08/12/2009 :  13:14:20  Show Profile  Reply with Quote
I'm a newbie but have been working with date stuff recently. Not sure if this will help as you appear quite knowledgeable already. I've used
CONVERT(varchar(12),GETDATE(),107)AS Today
to get today's date without the time. The 107 is a MS Server datetime style code. Specifically 107 is "mon dd, yyyy"
Hence, 08-12-2009 11:05:50AM => Aug 12, 2009

I've been told it's not the most elegant solution but it works for a simple report. However, I added this to an already created stored procedure so not sure how to tweak that to make it fit into your parameter code.
Here is the URL to an MS page for a list of these styles

http://msdn.microsoft.com/en-us/library/ms187928.aspx

cranky franky
Go to Top of Page

dwill
Starting Member

4 Posts

Posted - 08/13/2009 :  04:12:33  Show Profile  Reply with Quote
Thanks for the input - however that code is SQL side in the proc and it is the display side in the RDL that I am trying to get right.

My solution that 'does the job' is to change the DataType to a String in the RDL (i.e. <DataType>String</DataType> ) and then handle the date validation and convertions in the SQL (using similar CONVERT code).

What is annoying is that in the RDL using a String instead of a DateTime means that any inbuilt validation is lost from RS - and it is simply 'wrong'. But hey - it's an old version of Reporting Services and it' the only solution I've got!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000