Automating report execution - saving to separate files

I'm trying to do something which I hope can be accomplished relatively simply.

I have a report similar to bank statements let's say. When run, it currently prints out each person's statement into one file, with page breaks sepearating each person's statement. What I need to do, is when the report is run, save each person's report into a seperate file for the purpose of emailing to them later.

I could easily modify my report to just output for one particular person, but I'm not sure if there's a way to "bulk render" all the reports and have them saved to sepearate files.

I should also add that I'm using an MS Access Data Project (ADP) as the front end to my app - connected to a SQL Server 2005 DB. I currently display the reports by embedding a web browser object into an Access form and rendering the report via HTML.

Thanks in advance,

H

I actually figured out an answer to my own question which I'll post here in case others need to do the same thing. If anyone else has an alternative suggestion please feel free to let me know.

There's a VBA function called "URLDownloadToFile". If I use the reporting services URL access and specify type XLS or PDF, I can pass this as the URL and save the file to a specific folder. Kind of nice actually - and very simple.

Automatically setup table from dataset

I need to add tables to an existing "report" from datasets that have 50+
columns. Each table is a different dataset. Is there anyway to replicte the
behiour of the wizard on an existing report? Adding columns one at a time is
a pain, it'd be easier for me to add them all and then remove the few I don't
need.
And yes I know that db tables that wide and reports with this much info are
somewhat useless but I'm forced to swim in the pond I'm in.Can't you just copy and paste a previously created table? It will maintain
all the properties of the table.
>I need to add tables to an existing "report" from datasets that have 50+
> columns. Each table is a different dataset. Is there anyway to replicte
> the
> behiour of the wizard on an existing report? Adding columns one at a time
> is
> a pain, it'd be easier for me to add them all and then remove the few I
> don't
> need.
> And yes I know that db tables that wide and reports with this much info
> are
> somewhat useless but I'm forced to swim in the pond I'm in.

Automatically print multiple copies of the same report with different label on letter size p

Hi,

I am new to reporting services and I'm really stuck on a design problem. Can someone please help me?

I would like to design my own print function. When a user clicks on the print icon (preferably the one that came with reporting services), the report is automatically printed twice, once with "For Person A" and the second time with "For Person B" on it. It doesn't matter where these two labels are placed on the page. These two reports need to be printed on letter-size paper regardless of user's selection. How do I do this with minimum amount of code?

Any help would be greatly appreciated!

Depending on how the names of person A and person B actually get onto the report, you could add a grouping by person with a page break and actually make the report have 2 pages, 1 per person.|||

Hi

Thanks for your help.

The names are constant - ie they can be hardcoded.

If I make the report 1 page per person, does that mean the user will see two copies of the report in preview? Is there a way so that the user sees one copy but can print multiple copies with a single click?

Thanks!

|||

Hi

If there is a field with "Person A" and "Person B" you can add
a grouping by that field and set 'Page Break at end' on the grouping to true.

If this is a static value that has to be hard coded I cannot think of anything.

Problem when using the group by person approach is that the report will display
twice in the browser, each seperated by a pagebreak.

G

|||More specifically, the report will indicate that it has multiple pages (hence allowing the user to browse to the next page) but by default only the first page will show. When you export or print, all pages will be outputted.|||

Is there a way to do this without showing multiple copies (show one copy of the report in preview but prints two)?

Ideally I would like to create a print function that can be used across all reports, instead of changing all existing reports.

Thanks

|||

In that case your only other option is write your own rendering extension. That's quite involved and probably not how far you would want to go in this case.

You can't override the default print function.

|||Ok, got it. Thanks for your help :)

Automatically generating PDF with ReportViewer

Hi, I've been using the Report viewer control for a bit, and was wondering how I can automatically run the report as a PDF. I can run it and prompt the user to open or save the PDF, but I'm not sure how to auto generate it. This is the code which prompts the user:

ProtectedSub Page_SaveStateComplete(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.SaveStateComplete

Dim pdfContentAsByte() =Me.ReportViewer1.LocalReport.Render("pdf",Nothing,Nothing,Nothing,Nothing,Nothing,Nothing)

Me.Response.Clear()

Me.Response.ContentType ="application/pdf"

Dim modCodeAsString = Request.QueryString("modCode")

Me.Response.AddHeader("Content-disposition","attachment; filename=ModuleCode-" & modCode &".pdf")

Me.Response.BinaryWrite(pdfContent)

Me.Response.End()

EndSub

Has anybody done this?

Thanks

Hi,

Do you want to save the PDF file which generates by the reporting service automatically, right?

Based on my understanding, I think when you have generate the byte[] content for your PDF file, what you should do is to use FileStream class and FileInfo class to create the corresponding file, and write the content into files, save it onto your hard disk.

If you want the method be called automatically, you can invoke the method when page loads.
For more reference on FileStream and FileInfo, see:

http://msdn2.microsoft.com/en-us/library/system.io.fileinfo.aspx
http://msdn2.microsoft.com/en-us/library/system.io.filestream.aspx

Thanks.

|||

Thanks for that, I'll have a look. Also, when I ask the user to save as .xls or .pdf for example, how can I specify that the file will be saved on the D drive, rather than the C drive as the C drive is full?

Thanks

|||

Hi,

When you are creating and storing the file, you can specify the actual disk drive where to store the file. Also, you can import the "System.Runtime.InteropServices" namespace to check the free disk volume, so that you can determine which disk drive to store the file.

Thanks.

Automatically Export reports in SQL Reporting services 2000

Is it possible to have a report automatically export the results to Excel, when the report is generated? ...rather than having to select the format and click Export? .Pl let me know-Thanks.

I found this article some days ago!

http://ryanfarley.com/blog/archive/2006/01/27/15689.aspx

sql

Automatically Export reports in SQL Reporting services 2000

Is it possible to have a report automatically export the results to Excel, when the report is generated? ...rather than having to select the format and click Export? .Pl let me know-Thanks.

I found this article some days ago!

http://ryanfarley.com/blog/archive/2006/01/27/15689.aspx

Automatically Delete Snapshots

Does anyone know of a way to set up the Report Server to automatically delete
snapshots based on age.
I know I could write code to do this. I was hoping there might be a setting
you could manage that said to remove all snapshots in this folder older then
30 days everyday at midnight.
DaveDo you mean execution snapshots? Why do you want to delete them? Or are you
taking about aging of history snapshots? If so, this one is on the feature
list for a future version but in the meantime you can write some code to do
it yourself.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dlloyd" <Dlloyd@.discussions.microsoft.com> wrote in message
news:29A9EA76-A6C3-45D4-9EDB-6F8A45215C34@.microsoft.com...
> Does anyone know of a way to set up the Report Server to automatically
> delete
> snapshots based on age.
> I know I could write code to do this. I was hoping there might be a
> setting
> you could manage that said to remove all snapshots in this folder older
> then
> 30 days everyday at midnight.
> Dave|||Thanks Brian...
I know I can write code to do it. I was hoping there might be a way to
configure the Report Server itself to remove reports older than a configured
number of days.
I'll do it myself and wait for the feature in another release.
Dave
"Brian Welcker [MS]" wrote:
> Do you mean execution snapshots? Why do you want to delete them? Or are you
> taking about aging of history snapshots? If so, this one is on the feature
> list for a future version but in the meantime you can write some code to do
> it yourself.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dlloyd" <Dlloyd@.discussions.microsoft.com> wrote in message
> news:29A9EA76-A6C3-45D4-9EDB-6F8A45215C34@.microsoft.com...
> > Does anyone know of a way to set up the Report Server to automatically
> > delete
> > snapshots based on age.
> >
> > I know I could write code to do this. I was hoping there might be a
> > setting
> > you could manage that said to remove all snapshots in this folder older
> > then
> > 30 days everyday at midnight.
> >
> > Dave
>
>sql

Automatically Add date Range - 7 Days

Hello,

I'm writting a Crystal report on all order sent out in the past week.
I all works fine, but I am now trying to take away all the steps I can to make it easier and quicker to run.
The report has to inputs, the ClientCode and the Date Range.
Is there any way/Code that I can use to replace the need to input a date that will get the report to take todays date as the End range ( which is does at the moment ) and the - 7 days and use the result as the Start Range?

Thanks in advanceYou could use the LastFullWeek function in your record selection.

{table.field} = LastFullWeek

GJ

automaticall render as pdf after parameter selection

I am using reporting services and have a report that requires three
parameters. After the parameters are selected and the report is generated by
clicking the View Report button, I want the report to default to pdf view
instead of having to select that format and export after running it. Is this
possible? This way users can print or save from the pdf view without having
to take the extra step of exporting.
Thank you for your help!
SharlynI'm also interested in it.
tnx.
Tom.sql

automatic report printing - sp2

Hi,
Is it possible to print the report automatically as it loads?
Shimonthere is a sample to add a delivery extension to print a report.
but this works for scheduled reports.
SP2 allow the end user to directly print the report without having to export
it in PDF format.
"'? ?." <@.discussions.microsoft.com> wrote in message
news:37A5CE58-ADFF-4FAC-B04F-519D7143E7C9@.microsoft.com...
> Hi,
> Is it possible to print the report automatically as it loads?
> Shimon|||I know there is a new option to directly print the report.
My question is: can I invoke the print action directly when the report loads
- without the need to press the print button?
"Jéjé" wrote:
> there is a sample to add a delivery extension to print a report.
> but this works for scheduled reports.
> SP2 allow the end user to directly print the report without having to export
> it in PDF format.
> "'? ?." <@.discussions.microsoft.com> wrote in message
> news:37A5CE58-ADFF-4FAC-B04F-519D7143E7C9@.microsoft.com...
> > Hi,
> >
> > Is it possible to print the report automatically as it loads?
> >
> > Shimon
>
>|||sorry, but I don't see any rs command to render to the printer directly.
The current print preview & print functionality is an ActiveX control.
I presume that this ActiveX simply call reportserver to generates a TIFF
image (or PDF or any other format) and send it the printer.
Its a client side event and action, not a server side generation.
"'? ?." <@.discussions.microsoft.com> wrote in message
news:062F8D4D-EEE7-4400-AC10-147363442D37@.microsoft.com...
>I know there is a new option to directly print the report.
> My question is: can I invoke the print action directly when the report
> loads
> - without the need to press the print button?
> "Jéjé" wrote:
>> there is a sample to add a delivery extension to print a report.
>> but this works for scheduled reports.
>> SP2 allow the end user to directly print the report without having to
>> export
>> it in PDF format.
>> "'? ?." <@.discussions.microsoft.com> wrote in message
>> news:37A5CE58-ADFF-4FAC-B04F-519D7143E7C9@.microsoft.com...
>> > Hi,
>> >
>> > Is it possible to print the report automatically as it loads?
>> >
>> > Shimon
>>|||This could probably be made to work if the report web page was hosted by
another controlling app. E.g. if it is a frame in a larger web page than
the controlling page could trigger the print. Or if it's a vb app with a
web browser control, then the exe can control what occurs on the web page.
"'? ?." <@.discussions.microsoft.com> wrote in message
news:37A5CE58-ADFF-4FAC-B04F-519D7143E7C9@.microsoft.com...
> Hi,
> Is it possible to print the report automatically as it loads?
> Shimon

Automatic refreshing

I am wondering if it is possible to have a report generated by RS refresh periodically automatically. This could be realized by inserting a few lines of JavaScript to the report including the reload() function, but I do not know if there is anyway to do such thing.

Thanks in advance for any tip!

You could create an HTML page with an <iframe> in it and set the iframe src property to point to the report. Then add a http-equiv to the html page to refresh it however often you need. - RKS|||

That is a good tip. Thanks!

I have just realized there is another issue. The page refreshing is different from clicking the "Refresh" button of the report when there are parameters. Page refreshing uses the default values of paramters, but the report "Refresh" button uses the chosen values. For example, suppose patient ID is a parameter and we would like to allow the user to choose a patient ID and the report would refresh itself every minute using that particular patient ID. Using page refreshing, we would need to create a report for each patient to achieve this. Any idea to address this?

|||There is an Autorefresh property of the report that refreshes an HTML view based on the entered property.

In your designer, open the report, look at the report properties and you'll see it.|||

Perfect!!! Thanks a lot, Andy!

I have just tested it and it worked exactly the way we want.

Automatic Refresh Data

Is there any way in Reporting Service so that It will automatic
refresh data in the report with specified time interval ?
ThanksOn Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> Is there any way in Reporting Service so that It will automatic
> refresh data in the report with specified time interval ?
> Thanks
Yes, in the Reports Project, select the 'Layout' tab. Then select the
'Report' tab at the top and then 'Report Properties...' On the
'General' tab, select 'Auto Refresh' near the bottom and select the
number of seconds between refreshes.
Regards,
Enrique Martinez
Sr. Software Developer|||On Apr 1, 7:46 am, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> > Is there any way in Reporting Service so that It will automatic
> > refresh data in the report with specified time interval ?
> > Thanks
> Yes, in the Reports Project, select the 'Layout' tab. Then select the
> 'Report' tab at the top and then 'Report Properties...' On the
> 'General' tab, select 'Auto Refresh' near the bottom and select the
> number of seconds between refreshes.
> Regards,
> Enrique Martinez
> Sr. Software Developer
Thanks
it has done my work.|||On Apr 1, 12:47 am, sqlpr...@.gmail.com wrote:
> On Apr 1, 7:46 am, "EMartinez" <emartinez...@.gmail.com> wrote:
>
> > On Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> > > Is there any way in Reporting Service so that It will automatic
> > > refresh data in the report with specified time interval ?
> > > Thanks
> > Yes, in the Reports Project, select the 'Layout' tab. Then select the
> > 'Report' tab at the top and then 'Report Properties...' On the
> > 'General' tab, select 'Auto Refresh' near the bottom and select the
> > number of seconds between refreshes.
> > Regards,
> > Enrique Martinez
> > Sr. Software Developer
> Thanks
> it has done my work.
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Developersql

Automatic Printing of Reporting Services reports through subscriptions

To all the techies,

We are trying to schedule the report for automatic printing using subscriptions. Does any one have any idea of how to achieve this?

Any inputs appreciated.

AM

You can take a look at the custom extention sample for Reporting Service. This extention allows you to deliver your subscription to a network printer known by the reporting server.

The default location is: C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Extension Samples\PrinterDelivery Sample.

|||i have read the install sheet that goes with the sample. my question is how does one get it going?

Automatic Printing of Reporting Services reports through subscriptions

To all the techies,

We are trying to schedule the report for automatic printing using subscriptions. Does any one have any idea of how to achieve this?

Any inputs appreciated.

AM

You can take a look at the custom extention sample for Reporting Service. This extention allows you to deliver your subscription to a network printer known by the reporting server.

The default location is: C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Extension Samples\PrinterDelivery Sample.

|||i have read the install sheet that goes with the sample. my question is how does one get it going?

automatic printing from trigger

Hi,
I am new to Sql Server 2000. I am looking for help on how to automatically generate a report to a network printer after a record is added/updated/changed in a table. How should I do this? With a Trigger? With DTS?
Please help.With a Trigger ...|||Thanks for the info. I think I was unclear in my initial question and would like to know what are the exact steps needed to perform this function? I don't know how to create a trigger to do this and would be looking for an example of some sort or instructions for the code.

Any help greatly appreciated.|||What about sp_OA... procedures, write component in any language
and use these SP for communication.

Export with DTS or SQLDMO objects.

Multitask temp tables with :
1.X=OBJECT_ID('temp..#temptbl')
2.Send X to object
3.Get temp name
use tempdb
GO
Y=OBJECT_NAME(X)

Too much work to be done ...
MSSQLSERVER2K has native support for e-mail and pager.

Automatic PDF/TIFF generation and saving?

Ello folks...
Question for you all - I'd like to make a call from a webpage (asp or aspx)
that will call up a report with a given set of parameters, format it as a pdf
or tiff file, and save it to a location. Any ideas?
I know I can use the /ReportServer/ instance to directly call the report,
pass it the parameters I need, and render it out as a different format, but
it's the saving part that's throwing me.
Thanks!
- TerosTeros,
How about a subscription instead, or does it have to be on request?
A subscription can do this for you, but rendering on demand will always
ask.
Sorry, I don't think it can be done.
Chris
Teros wrote:
> Ello folks...
> Question for you all - I'd like to make a call from a webpage (asp or
> aspx) that will call up a report with a given set of parameters,
> format it as a pdf or tiff file, and save it to a location. Any
> ideas?
> I know I can use the ReportServer instance to directly call the
> report, pass it the parameters I need, and render it out as a
> different format, but it's the saving part that's throwing me.
> Thanks!
> - Teros|||I have seen someone say you can save the output stream as a file . I looked
for a previous post but didn;t find it.
The other way is to create a data-based subscription... in the subscription
row you can choose the filename and path...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Teros" <Teros@.discussions.microsoft.com> wrote in message
news:1BCA0EE4-E9B9-4BE9-8F35-55A0EB09FD96@.microsoft.com...
> Ello folks...
> Question for you all - I'd like to make a call from a webpage (asp or
> aspx)
> that will call up a report with a given set of parameters, format it as a
> pdf
> or tiff file, and save it to a location. Any ideas?
> I know I can use the /ReportServer/ instance to directly call the report,
> pass it the parameters I need, and render it out as a different format,
> but
> it's the saving part that's throwing me.
> Thanks!
> - Teros|||Check out the web method - RenderStream - you can render to a stream then
save the stream to a file
"Teros" wrote:
> Ello folks...
> Question for you all - I'd like to make a call from a webpage (asp or aspx)
> that will call up a report with a given set of parameters, format it as a pdf
> or tiff file, and save it to a location. Any ideas?
> I know I can use the /ReportServer/ instance to directly call the report,
> pass it the parameters I need, and render it out as a different format, but
> it's the saving part that's throwing me.
> Thanks!
> - Teros|||Teros,
AFAIK there is no way to do this from URL access. We do what you're doing
by calling the web service and saving the stream off to a file.
Ted
"Teros" wrote:
> Ello folks...
> Question for you all - I'd like to make a call from a webpage (asp or aspx)
> that will call up a report with a given set of parameters, format it as a pdf
> or tiff file, and save it to a location. Any ideas?
> I know I can use the /ReportServer/ instance to directly call the report,
> pass it the parameters I need, and render it out as a different format, but
> it's the saving part that's throwing me.
> Thanks!
> - Teros|||Thanks for the input - the report does need to be rendered on demand, so the
subscriptions wouldn't work. I'm playing with the renderstream now to see
what I can play with. It's looking pretty hopeful thus far.
Thanks!|||For PDF or TIFF files, you use the Render() method. RenderStream is used for
things like images on an HTML page.
Ted
"Mary Bray [SQL Server MVP]" wrote:
> Check out the web method - RenderStream - you can render to a stream then
> save the stream to a file
> "Teros" wrote:
> > Ello folks...
> >
> > Question for you all - I'd like to make a call from a webpage (asp or aspx)
> > that will call up a report with a given set of parameters, format it as a pdf
> > or tiff file, and save it to a location. Any ideas?
> >
> > I know I can use the /ReportServer/ instance to directly call the report,
> > pass it the parameters I need, and render it out as a different format, but
> > it's the saving part that's throwing me.
> >
> > Thanks!
> > - Teros|||Any luck Teros? I'm trying to do the same thing (I think). We have a report
that will be called from a third party application, and when it is called we
want it to create a pdf to a specific location, and then close. I'd be very
interested in knowing how you did it.
Michael C.
"Teros" wrote:
> Thanks for the input - the report does need to be rendered on demand, so the
> subscriptions wouldn't work. I'm playing with the renderstream now to see
> what I can play with. It's looking pretty hopeful thus far.
> Thanks!
>

Automatic PDF output

Normally if I want a report in PDF I have to first view the report in the web browser, then select export as PDF and save the PDF file.

Is there a way to avoid the first step (web browser viewing) and have the report immediatly in PDF as soon as I click the "View Report" button? (without using email/file subscription)

Thank you,

Roberto

use this link

http://www.codeproject.com/sqlrs/PDFUsingSQLRepServices.asp

Automatic page numbering

We are using Crystal 10 and at the bottom of each page are using the automatic page numbering (page X of XX) . We have a report footer that only occurs on the last page of the report. When the last page contains only this footer, the automatic page numbering does not work properly. The first page says 1 of 1 and the second page also says 1 of 1. The automatic page numbering does not pick up on the fact that the report has 2 pages and generate the numbers properly. Any suggestions.Use this forumula

WhilePrinitingRecords;
Numbervar N;
N:=N+1'
And place this in Report footer

Automatic Email subject

Good Morning,

I have created a varible in a report which displays as a text box i.e contains data like "LWD Apps 450" where the value will change each day. The report is emiled to a list of users, is there a way to automatically enter this value so that it is in the subject bar when the email is automatically sent?

Thanks in advance.

Steve

Hi Steve,

The entry form for subscriptions only allows for two variables (@.ReportName and @.ExecutionTime).

The only way I can think to modify the subscription on the fly would be to each day programmatically create a new subscription with a subject. Here's an msdn article with sample code:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

In the code, you would perform whatever operation you needed to get the same information that the RDL is getting. Then instead of this line:
extensionParams(4).Value = "@.ReportName was executed at @.ExecutionTime"
you would use this line:
extensionParams(4).Value = "LWD Apps " & variableThatIJustRetrieved

You would set the report to run once and the time to run right then (or a minute in the future). You would also want to delete the subscription after it has run, so that you would have a clean slate for tomorrow's run.

It's definitely kludgy though. Does anyone else have any thoughts?
-Jessica

Automatic Email subject

Good Morning,

I have created a varible in a report which displays as a text box i.e contains data like "LWD Apps 450" where the value will change each day. The report is emiled to a list of users, is there a way to automatically enter this value so that it is in the subject bar when the email is automatically sent?

Thanks in advance.

Steve

Hi Steve,

The entry form for subscriptions only allows for two variables (@.ReportName and @.ExecutionTime).

The only way I can think to modify the subscription on the fly would be to each day programmatically create a new subscription with a subject. Here's an msdn article with sample code:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

In the code, you would perform whatever operation you needed to get the same information that the RDL is getting. Then instead of this line:
extensionParams(4).Value = "@.ReportName was executed at @.ExecutionTime"
you would use this line:
extensionParams(4).Value = "LWD Apps " & variableThatIJustRetrieved

You would set the report to run once and the time to run right then (or a minute in the future). You would also want to delete the subscription after it has run, so that you would have a clean slate for tomorrow's run.

It's definitely kludgy though. Does anyone else have any thoughts?
-Jessica