Subscription parameters question -- sql server reporting services

eric_rs1
12/1/2004 12:59:03 PM

Joe, if you find anything on this I would be interested in hearing about it.
I have the same problem.

Eric

[quoted text, click to view]

Joe Scalise
12/1/2004 3:15:38 PM

A report that I have run every monday requires 2 parameters. These param's
are StartDate and EndDate. Is there a way to generate these fields for the
week automatically. For example, if i schedule it to run on Monday, I would
like the start date to be Monday 11/29/04 and the EndDate to be Friday
12/3/04. Otherwise I will have to setup new subscriptions for the reports
once a week.

I tried using =((Globals!ExecutionTime) - 5) as the default value in the
report but that doesn't work.

Thanks for the help!

Charles Kangai
12/1/2004 6:19:02 PM

If your data source is SQL Server, then you can do the following:
Create a table in SQL Server to hold the two parameter values and other
information as needed by a data-driven subscription (see documentation for
more details on data-driven subscription).
Create a SQL Server Agent job to update the parameter values once a week.
Now you can create your report and get the parameter values to be read from
the SQL Server tables. you will have have in your dataset query something like
SELECT ... WHERE MyDate BETWEEN @startDate and @endDate
In Report Manager set a data-driven subscription to read the parameter
values from the SQL SErver table.

HTH

Charles Kangai, MCT, MCDBA

[quoted text, click to view]

Robert Bruckner [MSFT]
12/1/2004 8:03:37 PM

Delete the existing report and re-publish the report with expression-based
default values. The default value could be e.g. =Today.AddDays(-7) or
=Globals!ExecutionTime.AddDays(-7)
Note: ExecutionTime and Today return DateTime objects where you can't just
subtract arbitrary integer values. See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp

--
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

JHoward
1/19/2005 2:43:04 PM

I simply used Select GetDate() as EndDate, DateAdd(day, -7, GetDate()) as
StartDate.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

sql server reporting services : Subscription parameters question