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.
| Author |
Topic |
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
 Posted - 2009-03-03 : 14:47:08
|
| I have 6 parameters to the reporting services report, the following are the requirements of the report:1. The scheduled report should run for the previous day, it's scheduled to run monday-sunday 7AM.2. Then it also will be run on a adhoc basis anytime for single parameter at a time.I have the follow stored proc coded so far:Create Procedure proc1@p1,@p2, @p3, @p4, @p5, @p6ASSelect * from tablewhere date1 >= DATEADD(Day, DATEDIFF(Day, 0, getdate()-1), 0)and date1 < DATEADD(Day, DATEDIFF(Day, 0, getdate()), 0)and (col1 = @p1 or @p1 = 'ALL'OR col2 = @p2 or @p2 = 'ALL'OR col3 = @p3 or @p3 = 'ALL'OR col4 = @p4 or @p4 = 'ALL'OR col5 = @p5 or @p5 = 'ALL'OR col6 = @p6 or @p6 = 'ALL')I am planning to default to "ALL" for the parameter values. And the scheduled report will be run for the previous day with the default values as ALL.I am not sure, how to handle for the adhoc run when it's run for a single parameter at a time. Please advice how I can do that in stored procedure.Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-04 : 09:55:03
|
| you mean you needto automate the adhoc run? |
 |
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2009-03-04 : 17:33:52
|
quote:
Originally posted by visakh16
you mean you needto automate the adhoc run?
No, I meant user's will also run the same scheduled report on adhoc basis by passing in just a single parameter instead of all the parameters. |
|
|
|
|
|
|
|
|
reports question