Hi All...
Unless I'm missing it, I don't see a way to set a data-driven email
subscription to be scheduled to send on the "last day" of each month (at
10:00PM).
Is there a way to do this in SQL 2000 Reporting Services Enterprise Edition?
Thanks in advance!
Kind regards - FredHi Fred
Here is a work around
1. Create a shared schedule and set it up never to fire , you can back
date it .
2. Create your subscription and set it to use this schedule
3. Get the subscription id from sql server using a query like
select * from reportserver..subscriptions
4. Write some code to call the FireEvent function using
"TimedSubscription" as the first param and the subscriptionid as the
second param. The user needs to have "Generate events" permission
http://msdn2.microsoft.com/en-us/library/aa225803(SQL.80).aspx
5. Schedule the code .
You can do step 4 in various ways but I suppose the easiest is to
script a .rss file , using vb.net and use windows scheduler to execute
the rss code. The code will look something like
Sub Main()
Dim rs As New ReportingService()
<Check whether today is last day of the month and if it is>
rs.FireEvent("TimedSubscription", "<subscription id>")
End Sub
If you save that as a rss file, you can execute it using "rs.exe -i
<your rss file name> -s http://servername/reportserver"
Cheers
Shai
On Nov 20, 2:49 am, "Fred Block" <fblock_no_spamm...@.w-systems.com>
wrote:
> Hi All...
> Unless I'm missing it, I don't see a way to set a data-driven email
> subscription to be scheduled to send on the "last day" of each month (at
> 10:00PM).
> Is there a way to do this in SQL 2000 Reporting Services Enterprise Edition?
> Thanks in advance!
> Kind regards - Fred|||Took me a while to figure it out, but it works great. It might not be
exactly what you want, but who knows it may work out for you. I got a
request from a user that they get the previous month's report on the
first of the month at 7am (time doesn't matter here). So for example,
on August 1st at 7am, she will receive a report for July1-31st. You
can change the time to be 12:00am or whatever.
First in your report make two datasets with the following:
Dataset 1 (I called mine StartofMonth):
select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
Dataset 2 (I called mine EndofMonth):
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
In my main dataset, the code looks for data between two parameters
@.startdate and @.enddate. Default these parameters (under
Report>Report Parameters...) to the corresponding datasets. The
"field" here should just be ID. Run the report, it should pull data
from the first to the last of last month.
Now go to your subscription and make your schedule. Under Schedule
Details (when you select to make your own schedule) click the Month
radio button. Pick all the month (using the check boxes) and then
select the radio labeled "On Calendar day(s):" and place a 1 in that
box. Now just adjust your start time as you see necessary. August
1st is coming soon, so you can see the result of your effort soon!!!
Only issue with this work around is that whenever your users open the
report (if they ever will) the dates will default to the first and
last day of the previous month. Oh well... they can change it
manually I guess or you can make a specific report (duplicate) that is
just for subscriptions. Also, for sanity, you might want to display
your parameters on the report so you see that it worked correctly.
Let me know if that works out for you!|||Hi All,
Thanks to you "both"! I'll check out these ideas and methods. Thanks for
taking the time to help me with this!
Kind regards - Fred
Showing posts with label email. Show all posts
Showing posts with label email. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
Report Subscriptions
Is there any way to not to email a report if there is no data?
yes you can do this but it need to develope some custome code to do so read this article to know how it contains how to email report in PDF formate so you just check in case no data
http://www.gotreportviewer.com/
|||Hello Tamer,
This link doesn't help me to get any solution, if you have any other solution please let me know.
Report subscription email Report Delivery Options
Is it possible to make this screen to link to exchange to i can look people's addresses up when i click on "To:" like i do in outlook.
Not out of the box. You might be able to do so by building a custom delivery extension and implementing ISubscriptionBasedUIUserControl to pull back your email addresses and pre-populate them in a combo-box you show in the Report Manager. This should work in theory, but I've never done it myself.
Monday, March 12, 2012
Report Services and Web Services?
Hi All,
I have spent the last few weeks working with SQL Server 2000 Reporting
Services and have gotten to the point of successfully creating EMail
Subscriptions. I now want to extend this so this process is performed
automatically, ie by clicking an 'Add me to this Reports Subscription list'
from within ur web application along side the link to run the report.
I'm aware of the RS.EXE utility but I'm not sure this is the answer, I'm
thinking this might lie within Web Services, as I hear Reporting Services is
basically a collection of such WSs which the localhost reporting services
interface plugs into.
So I'm wondering if this is correct and if so how I go about plugging into
the WSs to script the creation of a Report Subscription. I'm guessing it
might be possible to create the required subscriptions and then just use the
script/WSs to add a users email to the email list for the subscription,
although I'm wondering if it'll be that simple!
Thanks in advance,
Stephen
.ive done this using the reporting services soap api (although i am
using 2005, it was available in 2000)
heres a starting point ..
http://msdn2.microsoft.com/en-US/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx
its not too difficult, especially if your contriving much of the
schedule type values in your app rather than having to recreate a
scheduling interface for the user.
db
http://www.osix.net
the open source institute
home of the geek programming challenges
StephenMcC wrote:
> Hi All,
> I have spent the last few weeks working with SQL Server 2000 Reporting
> Services and have gotten to the point of successfully creating EMail
> Subscriptions. I now want to extend this so this process is performed
> automatically, ie by clicking an 'Add me to this Reports Subscription list'
> from within ur web application along side the link to run the report.
> I'm aware of the RS.EXE utility but I'm not sure this is the answer, I'm
> thinking this might lie within Web Services, as I hear Reporting Services is
> basically a collection of such WSs which the localhost reporting services
> interface plugs into.
> So I'm wondering if this is correct and if so how I go about plugging into
> the WSs to script the creation of a Report Subscription. I'm guessing it
> might be possible to create the required subscriptions and then just use the
> script/WSs to add a users email to the email list for the subscription,
> although I'm wondering if it'll be that simple!
> Thanks in advance,
> Stephen
> .|||Thanks for the reply bb, but is this .Net, migth it be poss to plug into this
from VB6 or Classic ASP, as the app is in old school VS?!
Thanks,
Sephen
"bb" wrote:
> ive done this using the reporting services soap api (although i am
> using 2005, it was available in 2000)
> heres a starting point ..
> http://msdn2.microsoft.com/en-US/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx
> its not too difficult, especially if your contriving much of the
> schedule type values in your app rather than having to recreate a
> scheduling interface for the user.
> db
>
> http://www.osix.net
> the open source institute
> home of the geek programming challenges
>
>
> StephenMcC wrote:
> > Hi All,
> >
> > I have spent the last few weeks working with SQL Server 2000 Reporting
> > Services and have gotten to the point of successfully creating EMail
> > Subscriptions. I now want to extend this so this process is performed
> > automatically, ie by clicking an 'Add me to this Reports Subscription list'
> > from within ur web application along side the link to run the report.
> >
> > I'm aware of the RS.EXE utility but I'm not sure this is the answer, I'm
> > thinking this might lie within Web Services, as I hear Reporting Services is
> > basically a collection of such WSs which the localhost reporting services
> > interface plugs into.
> >
> > So I'm wondering if this is correct and if so how I go about plugging into
> > the WSs to script the creation of a Report Subscription. I'm guessing it
> > might be possible to create the required subscriptions and then just use the
> > script/WSs to add a users email to the email list for the subscription,
> > although I'm wondering if it'll be that simple!
> >
> > Thanks in advance,
> >
> > Stephen
> >
> > .
>
I have spent the last few weeks working with SQL Server 2000 Reporting
Services and have gotten to the point of successfully creating EMail
Subscriptions. I now want to extend this so this process is performed
automatically, ie by clicking an 'Add me to this Reports Subscription list'
from within ur web application along side the link to run the report.
I'm aware of the RS.EXE utility but I'm not sure this is the answer, I'm
thinking this might lie within Web Services, as I hear Reporting Services is
basically a collection of such WSs which the localhost reporting services
interface plugs into.
So I'm wondering if this is correct and if so how I go about plugging into
the WSs to script the creation of a Report Subscription. I'm guessing it
might be possible to create the required subscriptions and then just use the
script/WSs to add a users email to the email list for the subscription,
although I'm wondering if it'll be that simple!
Thanks in advance,
Stephen
.ive done this using the reporting services soap api (although i am
using 2005, it was available in 2000)
heres a starting point ..
http://msdn2.microsoft.com/en-US/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx
its not too difficult, especially if your contriving much of the
schedule type values in your app rather than having to recreate a
scheduling interface for the user.
db
http://www.osix.net
the open source institute
home of the geek programming challenges
StephenMcC wrote:
> Hi All,
> I have spent the last few weeks working with SQL Server 2000 Reporting
> Services and have gotten to the point of successfully creating EMail
> Subscriptions. I now want to extend this so this process is performed
> automatically, ie by clicking an 'Add me to this Reports Subscription list'
> from within ur web application along side the link to run the report.
> I'm aware of the RS.EXE utility but I'm not sure this is the answer, I'm
> thinking this might lie within Web Services, as I hear Reporting Services is
> basically a collection of such WSs which the localhost reporting services
> interface plugs into.
> So I'm wondering if this is correct and if so how I go about plugging into
> the WSs to script the creation of a Report Subscription. I'm guessing it
> might be possible to create the required subscriptions and then just use the
> script/WSs to add a users email to the email list for the subscription,
> although I'm wondering if it'll be that simple!
> Thanks in advance,
> Stephen
> .|||Thanks for the reply bb, but is this .Net, migth it be poss to plug into this
from VB6 or Classic ASP, as the app is in old school VS?!
Thanks,
Sephen
"bb" wrote:
> ive done this using the reporting services soap api (although i am
> using 2005, it was available in 2000)
> heres a starting point ..
> http://msdn2.microsoft.com/en-US/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx
> its not too difficult, especially if your contriving much of the
> schedule type values in your app rather than having to recreate a
> scheduling interface for the user.
> db
>
> http://www.osix.net
> the open source institute
> home of the geek programming challenges
>
>
> StephenMcC wrote:
> > Hi All,
> >
> > I have spent the last few weeks working with SQL Server 2000 Reporting
> > Services and have gotten to the point of successfully creating EMail
> > Subscriptions. I now want to extend this so this process is performed
> > automatically, ie by clicking an 'Add me to this Reports Subscription list'
> > from within ur web application along side the link to run the report.
> >
> > I'm aware of the RS.EXE utility but I'm not sure this is the answer, I'm
> > thinking this might lie within Web Services, as I hear Reporting Services is
> > basically a collection of such WSs which the localhost reporting services
> > interface plugs into.
> >
> > So I'm wondering if this is correct and if so how I go about plugging into
> > the WSs to script the creation of a Report Subscription. I'm guessing it
> > might be possible to create the required subscriptions and then just use the
> > script/WSs to add a users email to the email list for the subscription,
> > although I'm wondering if it'll be that simple!
> >
> > Thanks in advance,
> >
> > Stephen
> >
> > .
>
Saturday, February 25, 2012
Report Server Email Question
Is there anyway to use exchange groups for the email addresses? I have about
25 email addresses and it is very difficult to paste that many in the To:
email address list.
Thanks.Figured it out. Have to use the email address of the exchange group.
"Ed Willis" <ed_willis@.acsi.orgnospam> wrote in message
news:OE8c9y0wEHA.3612@.tk2msftngp13.phx.gbl...
> Is there anyway to use exchange groups for the email addresses? I have
> about 25 email addresses and it is very difficult to paste that many in
> the To: email address list.
> Thanks.
>
25 email addresses and it is very difficult to paste that many in the To:
email address list.
Thanks.Figured it out. Have to use the email address of the exchange group.
"Ed Willis" <ed_willis@.acsi.orgnospam> wrote in message
news:OE8c9y0wEHA.3612@.tk2msftngp13.phx.gbl...
> Is there anyway to use exchange groups for the email addresses? I have
> about 25 email addresses and it is very difficult to paste that many in
> the To: email address list.
> Thanks.
>
Report Server Email Delivery setup
I have Report Server setup and I want to add email delivery functionality.
According to Books Online, to configure a Report Server for email delivery
only the following is necessary in RSReportServer.config:
1) SMTPServer - Note I entered just the mail/smtp server host name, not
fully qualified, not a UNC name...just the servername (i.e: Server1). The
values section of the help info says the entry can be "an ip address, a DNS
resolvable name or the name of the computer hosting the SMTP service. I chose
the latter.
2) SMTPAccountName
I added both the above settings, but under "New Subscription" only "Report
Server file share" shows up under "Delivered By". I restarted the Report mgr
service and iisreset.
What's missing?Under C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config
<Extension Name="Report Server Email" ... >
<Configuration>
<RSEmailDPConfiguration>
<From>Type and Email ID Here</From>
Once you add some email id over here which is from email then you will be
able to see the Email Delivery option
"pdx" wrote:
> I have Report Server setup and I want to add email delivery functionality.
> According to Books Online, to configure a Report Server for email delivery
> only the following is necessary in RSReportServer.config:
> 1) SMTPServer - Note I entered just the mail/smtp server host name, not
> fully qualified, not a UNC name...just the servername (i.e: Server1). The
> values section of the help info says the entry can be "an ip address, a DNS
> resolvable name or the name of the computer hosting the SMTP service. I chose
> the latter.
> 2) SMTPAccountName
> I added both the above settings, but under "New Subscription" only "Report
> Server file share" shows up under "Delivered By". I restarted the Report mgr
> service and iisreset.
> What's missing?
According to Books Online, to configure a Report Server for email delivery
only the following is necessary in RSReportServer.config:
1) SMTPServer - Note I entered just the mail/smtp server host name, not
fully qualified, not a UNC name...just the servername (i.e: Server1). The
values section of the help info says the entry can be "an ip address, a DNS
resolvable name or the name of the computer hosting the SMTP service. I chose
the latter.
2) SMTPAccountName
I added both the above settings, but under "New Subscription" only "Report
Server file share" shows up under "Delivered By". I restarted the Report mgr
service and iisreset.
What's missing?Under C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config
<Extension Name="Report Server Email" ... >
<Configuration>
<RSEmailDPConfiguration>
<From>Type and Email ID Here</From>
Once you add some email id over here which is from email then you will be
able to see the Email Delivery option
"pdx" wrote:
> I have Report Server setup and I want to add email delivery functionality.
> According to Books Online, to configure a Report Server for email delivery
> only the following is necessary in RSReportServer.config:
> 1) SMTPServer - Note I entered just the mail/smtp server host name, not
> fully qualified, not a UNC name...just the servername (i.e: Server1). The
> values section of the help info says the entry can be "an ip address, a DNS
> resolvable name or the name of the computer hosting the SMTP service. I chose
> the latter.
> 2) SMTPAccountName
> I added both the above settings, but under "New Subscription" only "Report
> Server file share" shows up under "Delivered By". I restarted the Report mgr
> service and iisreset.
> What's missing?
Report Server cannot load the Report Server Email extension.
Hi,
I have this error in the EventLog :
Report Server cannot load the Report Server Email extension.
Somebody has an idea that the origin of the error?
thank you for your help.
--
--sa.--You should look in the report server log files for the exact reason. Most
likely it is because you did not specify a SMTP server and a From address in
the RSReportServer.config file.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Devkam via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54CA3C211DED0@.SQLMonster.com...
> Hi,
> I have this error in the EventLog :
> Report Server cannot load the Report Server Email extension.
> Somebody has an idea that the origin of the error?
>
> thank you for your help.
> --
> --sa.--
I have this error in the EventLog :
Report Server cannot load the Report Server Email extension.
Somebody has an idea that the origin of the error?
thank you for your help.
--
--sa.--You should look in the report server log files for the exact reason. Most
likely it is because you did not specify a SMTP server and a From address in
the RSReportServer.config file.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Devkam via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54CA3C211DED0@.SQLMonster.com...
> Hi,
> I have this error in the EventLog :
> Report Server cannot load the Report Server Email extension.
> Somebody has an idea that the origin of the error?
>
> thank you for your help.
> --
> --sa.--
Report Server - email
How do I get the Report Server email option popping up in the subscriptions
page..It says in the help you have to have email delivery setup ?
does that mean install Outlook ?Just open your reportserver config file (hope you know here it is) and just
search for SMTP and give the server name and username etc... you should get
that option as well.
Amarnath
"Charlie" wrote:
> How do I get the Report Server email option popping up in the subscriptions
> page..It says in the help you have to have email delivery setup ?
> does that mean install Outlook ?
page..It says in the help you have to have email delivery setup ?
does that mean install Outlook ?Just open your reportserver config file (hope you know here it is) and just
search for SMTP and give the server name and username etc... you should get
that option as well.
Amarnath
"Charlie" wrote:
> How do I get the Report Server email option popping up in the subscriptions
> page..It says in the help you have to have email delivery setup ?
> does that mean install Outlook ?
Monday, February 20, 2012
Report send to many people
Hello i have a question,
I need to create a report that will take parameter Customer_ID and will be send to his email which is stored in my database. And i want it to happen to all of my customers so basically i need to pass this parameter automatically.
My queston is, where in a report designer or whereever it is set it that it sends this report via email and this email is taken from a field in a report?
And how exactly pass this parameter, will i have to do it programaticly by calling this report so many times or somehow the report services can manage that?
thank u for help
Marek
SSRS supports data-driven subscriptions where the Report Server will automatically send the report to the subscribed recipients. Another option is to create an application that does this programatically by calling down to the SSRS APIs.
Subscribe to:
Posts (Atom)