Default value working in preview however not in RM

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

Transact-SQL (2005)

Default value working in preview however not in RM

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-07 : 01:49:33

Default value working in preview however not in Report Manager?
I have a report parameter called @DateRange (type: string). A query returns the available values for this report parameter and the default values is set to non-queried and I have Last Month in the entry box. When I run the report in VS2005 preview it works fine however after it has been deployed to the server and viewed in Report Manager this parameter is available and all options are present however Last Month is not automatically selected as the default, it has <select value> as the option, I can then manually select an option and the report runs? I have another parameter on the same report that has a simple query for available and default values and this works. So... it seems to be because I am doing this?

CREATE TABLE #DateRangeSelection(DateRange varchar(30)); INSERT INTO #DateRangeSelection (DateRange)
SELECT 'Current Hour'
UNION ALL
SELECT 'Last Hour'
UNION ALL
SELECT 'Last 12 Hours'
UNION ALL
SELECT 'Last 24 Hours'
UNION ALL
SELECT 'Today'
UNION ALL
SELECT 'Yesterday'
UNION ALL
SELECT 'Last Weekend'
UNION ALL
SELECT 'Current Week'
UNION ALL
SELECT 'Last 7 Days'
UNION ALL
SELECT 'Last 5 Week Days'
UNION ALL
SELECT 'Last Week'
UNION ALL
SELECT 'Last 2 Weeks'
UNION ALL
SELECT 'Current Month'
UNION ALL
SELECT 'Last 30 days'
UNION ALL
SELECT 'Last 90 days'
UNION ALL
SELECT 'Last Month'
UNION ALL
SELECT 'Last 3 Months'
UNION ALL
SELECT 'Current Quarter'
UNION ALL
SELECT 'Last Quarter'
UNION ALL
SELECT 'Current Year'
UNION ALL
SELECT 'Last Year'
SELECT * FROM #DateRangeSelection;
IF OBJECT_ID(N'tempdb..#DateRangeSelection', N'U') IS NOT NULL
DROP TABLE #DateRangeSelection;

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-07 : 01:55:59

Actually the problem seems to be because I have default values set to non-queried with a value of Last Month. I have come to this conclusion because if I change my other parameter that worked from queried to non-queried and enter a string value it also now fails? Am I using the correct syntax?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-07 : 02:22:19

Do you have deployed your Report first time without default value?
In Report Manager you can have a look to the Report Properties --> Parameters.
Maybe there you can change the wanted properties.
Also you can delete the Report on Server before deploying it.

Webfred

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-07 : 02:26:20

Sometimes the parameters get a little confused if there are changes & re-deployments. Try editing the parameter on the report server. To do this, go to your report on the server, then click the "Properties" tab, then click the "Parameters" link & then type in the default you want.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-07 : 02:27:14

It seems to be chnaging on the server as I modify it. Also I should say if I chnage the default vaules option to queried and use the same query as the one that returns the avaliable options for the drop down list it works however it selects the first option in the list. I was selecting non-queried as I need the default to be option 13 in the list Last Month. Also in other time/date paramters if I use something like =Today() this works OK. So.. it seems to be something to do with the fact that I am typing Last Month into the field? Does it need an expression to set the string value to Last Month?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-07 : 02:29:39

This may be completly unreleated to your issue, but there is really no reason to create a tmp table on your report, I wouldn't be shocked if it is causing this sparatic behavior. Just use the below query for your values.


SELECT 'Current Hour' as DateRange
UNION ALL
SELECT 'Last Hour'
UNION ALL
SELECT 'Last 12 Hours'
UNION ALL
SELECT 'Last 24 Hours'
UNION ALL
SELECT 'Today'
UNION ALL
SELECT 'Yesterday'
UNION ALL
SELECT 'Last Weekend'
UNION ALL
SELECT 'Current Week'
UNION ALL
SELECT 'Last 7 Days'
UNION ALL
SELECT 'Last 5 Week Days'
UNION ALL
SELECT 'Last Week'
UNION ALL
SELECT 'Last 2 Weeks'
UNION ALL
SELECT 'Current Month'
UNION ALL
SELECT 'Last 30 days'
UNION ALL
SELECT 'Last 90 days'
UNION ALL
SELECT 'Last Month'
UNION ALL
SELECT 'Last 3 Months'
UNION ALL
SELECT 'Current Quarter'
UNION ALL
SELECT 'Last Quarter'
UNION ALL
SELECT 'Current Year'
UNION ALL
SELECT 'Last Year'

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-07 : 02:32:01

Well that seemed to be the problem, confused server, what a worry. Deleted the reports from the server and then re-deployed them. Now it works:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 02:32:43

Vinnie you have a good point there. I have spoted in some cases it throws an error message when you run query in data tab that contains temporary tables.So it can be an issue.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-07 : 02:34:50

..and thanks for that Vinnie881, I am just learning so any help is good help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources