| Groups | Blog | Home |
sql server reporting services : Subscription StartDate and EndDate parameters
How do i set the startDate and EndDate parameters in the subscription page to the previous date? The default for this is the last 30 days but some users want to have it send to them everyday and the report collects data the previous day: StartDate = 2/3/2005 12:00:00 AM and EndDate = 2/3/2005 11:59:59 PM, provided that the @ExecutionTime is today. Please advice. Thanks
I typed in StartDate box:
Now.AddDays(-1).AddHours(-Hour(Now)).AddHours(23).AddMinutes(-Minute(Now)) and EndDate box: Now.AddDays(-1).AddHours(-Hour(Now)).AddHours(23).AddMinutes(-Minute(Now)).AddMinutes(59).AddSeconds(-Second(Now)).AddSeconds(59) but it didn't work. Please let me know what expression it accepts there. Thanks [quoted text, click to view] "VNN" <VNN@hotmail.com> wrote in message news:ea1YB3xCFHA.3908@TK2MSFTNGP12.phx.gbl... > Hi, > > How do i set the startDate and EndDate parameters in the subscription page > to the previous date? The default for this is the last 30 days but some > users want to have it send to them everyday and the report collects data > the previous day: StartDate = 2/3/2005 12:00:00 AM and EndDate = 2/3/2005 > 11:59:59 PM, provided that the @ExecutionTime is today. Please advice. > Thanks >
Anyone cares to help? Thanks
[quoted text, click to view] "VNN" <VNN@hotmail.com> wrote in message news:epVyDd7CFHA.1264@TK2MSFTNGP12.phx.gbl... >I typed in StartDate box: > > Now.AddDays(-1).AddHours(-Hour(Now)).AddHours(23).AddMinutes(-Minute(Now)) > > and EndDate box: > > Now.AddDays(-1).AddHours(-Hour(Now)).AddHours(23).AddMinutes(-Minute(Now)).AddMinutes(59).AddSeconds(-Second(Now)).AddSeconds(59) > > but it didn't work. Please let me know what expression it accepts there. > Thanks > > "VNN" <VNN@hotmail.com> wrote in message > news:ea1YB3xCFHA.3908@TK2MSFTNGP12.phx.gbl... >> Hi, >> >> How do i set the startDate and EndDate parameters in the subscription >> page to the previous date? The default for this is the last 30 days but >> some users want to have it send to them everyday and the report collects >> data the previous day: StartDate = 2/3/2005 12:00:00 AM and EndDate = >> 2/3/2005 11:59:59 PM, provided that the @ExecutionTime is today. Please >> advice. Thanks >> > >
Let me take a stab at this one...but, first, I need some info;
When you refer to the StartDate and EndDate parameters are you talking about the Subscription Schedule or the Report Parameters? If you are referring to the Report Parameters, then you simply move the date manipulation into the Stored Procedure or SQL Query used by the report instead of trying to pass-in the dates. Alternatively, you can dynamically calculate the dates within the RDL and pass them in to the SQL Query or SP. However, it sounds like you are referring to the Subscription Schedule settings. in this case, I would recommend multiple subscriptions. Setup 1 for daily execution, and another for the every 30-days subscription. If I have misunderstood your question, please clarify and I'll try again. Thank you, Lance Hunt http://weblogs.asp.net/lhunt/
This is where I have to type it in the subscription page. If I use default
for both, it will use the last 30 days (this is the default in the Report Manager.) If I type them in, the only thing I can use is the actual date, expression doesn't work. So my question is, in the subscription page where I have to type in the StartDate and EndDate parameters, can I use any expression at all? Or I have to use static date (like 1/1/2005)? Thanks [quoted text, click to view] "Lance" <lancehunt@gmail.com> wrote in message news:1107708613.823968.157500@c13g2000cwb.googlegroups.com... > Let me take a stab at this one...but, first, I need some info; > > When you refer to the StartDate and EndDate parameters are you talking > about the Subscription Schedule or the Report Parameters? > > If you are referring to the Report Parameters, then you simply move the > date > manipulation into the Stored Procedure or SQL Query used by the report > instead of trying to pass-in the dates. Alternatively, you can > dynamically calculate the dates within the RDL and pass them in to the > SQL Query or SP. > > However, it sounds like you are referring to the Subscription Schedule > settings. in this case, I would recommend multiple subscriptions. > Setup 1 for daily execution, and another for the every 30-days > subscription. > > If I have misunderstood your question, please clarify and I'll try > again. > > Thank you, > > Lance Hunt > http://weblogs.asp.net/lhunt/ >
One more thing to make it clear. This is not when I want the scheduler send
out report. These parameters are what the report needs to have in order to run it. I would like to have the schedule to run daily and the report will use previous day data (if the report runs today at 2:00 AM - 2/6/2005 02:00 AM, the StartDate would be 2/5/2005 00:00:00 and the EndDate would be 2/5/2005 23:59:59). Thanks [quoted text, click to view] "VNN" <VNN@hotmail.com> wrote in message news:OQ32EMIDFHA.936@TK2MSFTNGP12.phx.gbl... > This is where I have to type it in the subscription page. If I use default > for both, it will use the last 30 days (this is the default in the Report > Manager.) If I type them in, the only thing I can use is the actual date, > expression doesn't work. So my question is, in the subscription page where > I have to type in the StartDate and EndDate parameters, can I use any > expression at all? Or I have to use static date (like 1/1/2005)? > > Thanks > > > "Lance" <lancehunt@gmail.com> wrote in message > news:1107708613.823968.157500@c13g2000cwb.googlegroups.com... >> Let me take a stab at this one...but, first, I need some info; >> >> When you refer to the StartDate and EndDate parameters are you talking >> about the Subscription Schedule or the Report Parameters? >> >> If you are referring to the Report Parameters, then you simply move the >> date >> manipulation into the Stored Procedure or SQL Query used by the report >> instead of trying to pass-in the dates. Alternatively, you can >> dynamically calculate the dates within the RDL and pass them in to the >> SQL Query or SP. >> >> However, it sounds like you are referring to the Subscription Schedule >> settings. in this case, I would recommend multiple subscriptions. >> Setup 1 for daily execution, and another for the every 30-days >> subscription. >> >> If I have misunderstood your question, please clarify and I'll try >> again. >> >> Thank you, >> >> Lance Hunt >> http://weblogs.asp.net/lhunt/ >> > >
No, you cannot use expressions at the subscription page.
You could achieve this through defining expression-based default values in the report and publishing it. Alternatively you could have a list of valid values ("Yesterday", "Last 30 days", etc.) and would then e.g. use an expression-based query commandtext to translate the user-selection of the parameter value into date values behind the scenes. -- This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "VNN" <VNN@hotmail.com> wrote in message news:OQ32EMIDFHA.936@TK2MSFTNGP12.phx.gbl... > This is where I have to type it in the subscription page. If I use default > for both, it will use the last 30 days (this is the default in the Report > Manager.) If I type them in, the only thing I can use is the actual date, > expression doesn't work. So my question is, in the subscription page where > I have to type in the StartDate and EndDate parameters, can I use any > expression at all? Or I have to use static date (like 1/1/2005)? > > Thanks > > > "Lance" <lancehunt@gmail.com> wrote in message > news:1107708613.823968.157500@c13g2000cwb.googlegroups.com... >> Let me take a stab at this one...but, first, I need some info; >> >> When you refer to the StartDate and EndDate parameters are you talking >> about the Subscription Schedule or the Report Parameters? >> >> If you are referring to the Report Parameters, then you simply move the >> date >> manipulation into the Stored Procedure or SQL Query used by the report >> instead of trying to pass-in the dates. Alternatively, you can >> dynamically calculate the dates within the RDL and pass them in to the >> SQL Query or SP. >> >> However, it sounds like you are referring to the Subscription Schedule >> settings. in this case, I would recommend multiple subscriptions. >> Setup 1 for daily execution, and another for the every 30-days >> subscription. >> >> If I have misunderstood your question, please clarify and I'll try >> again. >> >> Thank you, >> >> Lance Hunt >> http://weblogs.asp.net/lhunt/ >> > >
Is there a plan to have these fields accept expressions in future version
like sp2 or 2005? Anyone knows? [quoted text, click to view] "Robert Bruckner [MSFT]" <robruc@online.microsoft.com> wrote in message news:OsibVIJDFHA.4004@tk2msftngp13.phx.gbl... > No, you cannot use expressions at the subscription page. > You could achieve this through defining expression-based default values in > the report and publishing it. Alternatively you could have a list of valid > values ("Yesterday", "Last 30 days", etc.) and would then e.g. use an > expression-based query commandtext to translate the user-selection of the > parameter value into date values behind the scenes. > > -- > This posting is provided "AS IS" with no warranties, and confers no > rights. > > "VNN" <VNN@hotmail.com> wrote in message > news:OQ32EMIDFHA.936@TK2MSFTNGP12.phx.gbl... >> This is where I have to type it in the subscription page. If I use >> default for both, it will use the last 30 days (this is the default in >> the Report Manager.) If I type them in, the only thing I can use is the >> actual date, expression doesn't work. So my question is, in the >> subscription page where I have to type in the StartDate and EndDate >> parameters, can I use any expression at all? Or I have to use static date >> (like 1/1/2005)? >> >> Thanks >> >> >> "Lance" <lancehunt@gmail.com> wrote in message >> news:1107708613.823968.157500@c13g2000cwb.googlegroups.com... >>> Let me take a stab at this one...but, first, I need some info; >>> >>> When you refer to the StartDate and EndDate parameters are you talking >>> about the Subscription Schedule or the Report Parameters? >>> >>> If you are referring to the Report Parameters, then you simply move the >>> date >>> manipulation into the Stored Procedure or SQL Query used by the report >>> instead of trying to pass-in the dates. Alternatively, you can >>> dynamically calculate the dates within the RDL and pass them in to the >>> SQL Query or SP. >>> >>> However, it sounds like you are referring to the Subscription Schedule >>> settings. in this case, I would recommend multiple subscriptions. >>> Setup 1 for daily execution, and another for the every 30-days >>> subscription. >>> >>> If I have misunderstood your question, please clarify and I'll try >>> again. >>> >>> Thank you, >>> >>> Lance Hunt >>> http://weblogs.asp.net/lhunt/ >>> >> >> > >
Not in SP2 and not in 2005. But it is under consideration for a future
release. For now, you have to look into other ways to achieve this (like the ones suggested in my previous posting). -- This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "VNN" <VNN@hotmail.com> wrote in message news:OL4NoHKDFHA.3908@TK2MSFTNGP12.phx.gbl... > Is there a plan to have these fields accept expressions in future version > like sp2 or 2005? Anyone knows? > > "Robert Bruckner [MSFT]" <robruc@online.microsoft.com> wrote in message > news:OsibVIJDFHA.4004@tk2msftngp13.phx.gbl... >> No, you cannot use expressions at the subscription page. >> You could achieve this through defining expression-based default values >> in the report and publishing it. Alternatively you could have a list of >> valid values ("Yesterday", "Last 30 days", etc.) and would then e.g. use >> an expression-based query commandtext to translate the user-selection of >> the parameter value into date values behind the scenes. >> >> -- >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> >> "VNN" <VNN@hotmail.com> wrote in message >> news:OQ32EMIDFHA.936@TK2MSFTNGP12.phx.gbl... >>> This is where I have to type it in the subscription page. If I use >>> default for both, it will use the last 30 days (this is the default in >>> the Report Manager.) If I type them in, the only thing I can use is the >>> actual date, expression doesn't work. So my question is, in the >>> subscription page where I have to type in the StartDate and EndDate >>> parameters, can I use any expression at all? Or I have to use static >>> date (like 1/1/2005)? >>> >>> Thanks >>> >>> >>> "Lance" <lancehunt@gmail.com> wrote in message >>> news:1107708613.823968.157500@c13g2000cwb.googlegroups.com... >>>> Let me take a stab at this one...but, first, I need some info; >>>> >>>> When you refer to the StartDate and EndDate parameters are you talking >>>> about the Subscription Schedule or the Report Parameters? >>>> >>>> If you are referring to the Report Parameters, then you simply move the >>>> date >>>> manipulation into the Stored Procedure or SQL Query used by the report >>>> instead of trying to pass-in the dates. Alternatively, you can >>>> dynamically calculate the dates within the RDL and pass them in to the >>>> SQL Query or SP. >>>> >>>> However, it sounds like you are referring to the Subscription Schedule >>>> settings. in this case, I would recommend multiple subscriptions. >>>> Setup 1 for daily execution, and another for the every 30-days >>>> subscription. >>>> >>>> If I have misunderstood your question, please clarify and I'll try >>>> again. >>>> >>>> Thank you, >>>> >>>> Lance Hunt >>>> http://weblogs.asp.net/lhunt/ >>>> >>> >>> >> >> > >
I have 2 paramters: StartDate and EndDate. I plan to add a third parameter
Interval to set the StartDate and EndDate like you suggested above. Since I want to hide StartDate and EndDate when user select Yesterday or Last 30 days but will appear when user select Custom (one of options in the dropdown for Interval). Is there a way to do this or I have to make StartDate and EndDate visible all the time? Thanks, [quoted text, click to view] "Robert Bruckner [MSFT]" <robruc@online.microsoft.com> wrote in message news:upepLfKDFHA.3540@TK2MSFTNGP14.phx.gbl... > Not in SP2 and not in 2005. But it is under consideration for a future > release. For now, you have to look into other ways to achieve this (like > the ones suggested in my previous posting). > > -- > This posting is provided "AS IS" with no warranties, and confers no > rights. > > "VNN" <VNN@hotmail.com> wrote in message > news:OL4NoHKDFHA.3908@TK2MSFTNGP12.phx.gbl... >> Is there a plan to have these fields accept expressions in future version >> like sp2 or 2005? Anyone knows? >> >> "Robert Bruckner [MSFT]" <robruc@online.microsoft.com> wrote in message >> news:OsibVIJDFHA.4004@tk2msftngp13.phx.gbl... >>> No, you cannot use expressions at the subscription page. >>> You could achieve this through defining expression-based default values >>> in the report and publishing it. Alternatively you could have a list of >>> valid values ("Yesterday", "Last 30 days", etc.) and would then e.g. use >>> an expression-based query commandtext to translate the user-selection of >>> the parameter value into date values behind the scenes. >>> >>> -- >>> This posting is provided "AS IS" with no warranties, and confers no >>> rights. >>> >>> "VNN" <VNN@hotmail.com> wrote in message >>> news:OQ32EMIDFHA.936@TK2MSFTNGP12.phx.gbl... >>>> This is where I have to type it in the subscription page. If I use >>>> default for both, it will use the last 30 days (this is the default in >>>> the Report Manager.) If I type them in, the only thing I can use is the >>>> actual date, expression doesn't work. So my question is, in the >>>> subscription page where I have to type in the StartDate and EndDate >>>> parameters, can I use any expression at all? Or I have to use static >>>> date (like 1/1/2005)? >>>> >>>> Thanks >>>> >>>> >>>> "Lance" <lancehunt@gmail.com> wrote in message >>>> news:1107708613.823968.157500@c13g2000cwb.googlegroups.com... >>>>> Let me take a stab at this one...but, first, I need some info; >>>>> >>>>> When you refer to the StartDate and EndDate parameters are you talking >>>>> about the Subscription Schedule or the Report Parameters? >>>>> >>>>> If you are referring to the Report Parameters, then you simply move >>>>> the >>>>> date >>>>> manipulation into the Stored Procedure or SQL Query used by the report >>>>> instead of trying to pass-in the dates. Alternatively, you can >>>>> dynamically calculate the dates within the RDL and pass them in to the >>>>> SQL Query or SP. >>>>> >>>>> However, it sounds like you are referring to the Subscription Schedule >>>>> settings. in this case, I would recommend multiple subscriptions. >>>>> Setup 1 for daily execution, and another for the every 30-days >>>>> subscription. >>>>> >>>>> If I have misunderstood your question, please clarify and I'll try >>>>> again. >>>>> >>>>> Thank you, >>>>> >>>>> Lance Hunt >>>>> http://weblogs.asp.net/lhunt/ >>>>> >>>> >>>> >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |