List of dates for parameter and default value

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

Development Tools

Reporting Services Development

List of dates for parameter and default value

Author

Topic

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2006-11-10 : 13:00:09

Using RS2000 and Visual Studio

I want to make a list of dates the user can select when running from Report Manager.

I also want to make a subscription for the report which will limit the results to dates within the next 30 days.

I've tried a couple of things however I could not get it to work. For example, I defined the dataset for my parameter as follows

SELECT 1 AS SortOrder, GETDATE()+30 AS ShipDateValue, '30 Days Out' AS ShipDateText
UNION
SELECT DISTINCT 2, [Line Ship Date], CONVERT(char(10),[Line Ship Date],1)
FROM v_SOLinesToShip

Then I defined my report parameter as from a query using this dataset, ShipDateValue for the Value field and ShipDateText for the Label field.

However, when I go to preview, select '30 Days Out' from the drop down, and click View Report, the parameter box goes blank and nothing happens.

Again, the goal is to make a subscription that only outputs data with dates 30 days out but allow the selection of specific dates when run from Report Manager.

Maybe I just need to make two reports?

Thanks in advance,

Kevin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 14:19:51

[code]SELECT 1 AS SortOrder,
DATEADD(day, DATEDIFF(day, 0, GETDATE()), 30) AS ShipDateValue,
'30 Days Out' AS ShipDateText
UNION
SELECT DISTINCT 2,
DATEADD(day, DATEDIFF(day, 0, [Line Ship Date]), 0),
CONVERT(varchar(10), [Line Ship Date], 101)
FROM v_SOLinesToShip[/code]

Peter Larsson
Helsingborg, Sweden

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2006-11-10 : 16:42:32

Thanks Peter! The list of dates for the parameter works. Now how would I go about setting a default for a subscription?

Also, I'm having trouble figuring out what functions are available for expressions. For example, I found that I can use

=Format(Parameters!ToShipDate.Value, "MM/dd/yy")

in a text box. Do you have any suggestions for where I might look? Visual Studio help maybe?

Kevin

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2006-11-10 : 17:09:55

Peter,

I think I found where to go for information. In Visual Studio help, I searched for expressions and found Common Expressions for Reporting Services.

It says

quote:
You can write expressions that use functions from the Microsoft.VisualBasic, System.Convert, and System.Math namespaces, or you can add references to other assemblies or custom code.

I think I'll make a new post for my other question.

Kevin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources