datetime parameter optional? - SQL Server Forums

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)

datetime parameter optional?

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-23 : 11:37:32

Hi there,

On my report I have two Date fields with DateTime datatype. Is there a way to make these parameters optional or is that by design a no no as it is for Integer parms?

Thanks!

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 14:32:29

you can make it optional. just add a default value to parameter as null and then write query behind also to filter conditionally

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-10-23 : 14:58:17

Sorta like this:


SELECT * 
FROM  Your_Table a
WHERE   (a.Date >= @Date) 
     OR (@Date IS NULL)

John
It's a small world (but I wouldn't want to paint it)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-23 : 15:25:12

thanks . but in the report design in Visual Studio if your field is datatype datetime it will not accept a NULL value unless you select Allow Null and this fat checkbox comes user has to check uncheck. what kind of crazy design is that? other than that checkbox I guess there is no other way?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-10-23 : 16:32:34

Are you using the BI version of Visual Studio? I created in the tool and copy/pasted. It worked fine and did not mind NULL.

John
It's a small world (but I wouldn't want to paint it)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-23 : 17:25:10

really? yes I do have the BI version. ok, so I open up an .rdl file in VS then go to ReportParameters and add a file with DateTime Data Type. Then in Default Values choice you checked the Null checkbox and you unchecked Allow null value and it will run the report without any message to fill date field?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-10-23 : 20:24:08

unfortunately I do not have access when away from work. So this from memory...

I have RDL with a file with datetime field requirement. Go to center tab layout view. Click Report Parameters and create new if it didn't pull in from SQL design. Change type to DateTime. Click Allow Null. Default Null. When I preview the Parameter has small check box to designate default is to pass NULL. If I uncheck, I can use date picker to select a date.

Go to my profile and click send email to get me your email address. On monday I will send you a working RDL with NULL default. But above instructions should work.

John
It's a small world (but I wouldn't want to paint it)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources