Reporting services - default value of today

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Reporting services - default value of today

Author

Topic

dwill
Starting Member

4 Posts

Posted - 2009-08-10 : 04:20:45

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

392 Posts

Posted - 2009-08-10 : 09:29:22

How about this?

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

<Value>=today()</Value>

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-08-10 : 09:31:50

Sorry - forgot to state that you must use upper case MM because lower case mm refers to milliseconds

dwill
Starting Member

4 Posts

Posted - 2009-08-10 : 09:57:57

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>

cranky franky

12 Posts

Posted - 2009-08-12 : 13:14:20

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

dwill
Starting Member

4 Posts

Posted - 2009-08-13 : 04:12:33

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources