Export Report to File: files

Showing posts with label files. Show all posts

Thursday, March 29, 2012

Exporting report to WordML

I have been trying to use the XML export along with an XSL file to try and
generate WordML files from reporting services, and so far have had no real
success. I have tried using the XSLT Inference Tool and following the VERY
limited documentation that exists for it, but it just does not want to work
for me.
So, I have 2 questions:
1) Is there any good information/examples of using Word 2003, the XSLT
Inference Tool, and/or Visual Studio to get RS to output WordML files?
2) Why hasn't Microsoft released a WordML renderer for Reporting Services? I
can understand not having one for the old formats since MS is trying to phase
them out, but they have an XML renderer, and they are pushing the WordML
format, so it would seem to make sense to release a WordML renderer...Anyone have any thoughts/Hints/Tips for this?
"jwattsCOL" wrote:
> I have been trying to use the XML export along with an XSL file to try and
> generate WordML files from reporting services, and so far have had no real
> success. I have tried using the XSLT Inference Tool and following the VERY
> limited documentation that exists for it, but it just does not want to work
> for me.
> So, I have 2 questions:
> 1) Is there any good information/examples of using Word 2003, the XSLT
> Inference Tool, and/or Visual Studio to get RS to output WordML files?
> 2) Why hasn't Microsoft released a WordML renderer for Reporting Services? I
> can understand not having one for the old formats since MS is trying to phase
> them out, but they have an XML renderer, and they are pushing the WordML
> format, so it would seem to make sense to release a WordML renderer...
>

Exporting report to text files

Hi guys,

I have this problem with srs... That is when i launched a report it should automatically create a text file on my local machine... is this possible?

Thanks

Not sure what you mean here..

Have you looked at subscriptions with delivery to the file system?

|||

what i mean is that whenever i open a report it should auomatically export the the report into .txt file format without going through the export button.

Does SRS has that kind of feature or i need to create a separate assembly for it?

thanks

|||

I think you would have to do some custom programming. Have you looked at the ssrs web service methods available?

sql

Exporting report to multiple PDF's

Is it possile for a report to be exported to multiple PDF files, each pdf file would be for a group in the report and would ideally use field information for the group to construct the pdf filename?

The purpose of this is to provide a separate PDF for access over the web, so for example we have a scheduled report that generates a report grouped by sales region we would end up with SalesRegionA.PDF, SalesRegionB.PDF etc.

Hi Hugh,

What you can do is have a parameter in your report which will have the groups...ie you can have a parameter for Sales Region, then what you can do is run batch script, which has a command that calls a .rss file passing in some values -which will export your values to pdf...

you can create a batch script that calls the .rss file like below...

rs -i RunReports.rss -s http:..localhost/reportserver -1 0 -v directory="%1 -v reportname= "Name of report" -v filename="Name of file.pdf" -v format="pdf" -v params="this is were the parameter value goes" -v debug.

rs -i RunReports.rss -s http:..localhost/reportserver -1 0 -v directory="%1 -v reportname= "Name of report 2" -v filename="Name of file.pdf 2" -v format="pdf" -v params="this is were the parameter 2 value goes" -v debug.

(note you can have mutiple statements, allowing you to pass different values in the reportname, params section...

Your .rss file will use these values to render through the reports and export the files, i beleive there are examples on the Web of this.

Or what you can do is use a data driven subscription which is much easier (you will have to create the parameter in your report still). You have to create a table in your SQL Server DB, to hold the parameters ie all the Sales Region and use the Subscription to look at this tables..to do this go to subscriptions in report manager (when viewing the report), then go to data driven subscriptions, then follow the steps which are pretty simple...

Hope this helps.

|||Thans for your suggestions, I'd sort of moved in the direction of a a data driven subscription anyway but I think there's still quite a bit more to do, especially working out how to trigger the subscription from an application - I've started looking at the fireevent api.

Tuesday, March 27, 2012

Exporting multiple tables as flat files

Hi.

I've tried to create a SSIS package to simply export a bunch of tables as flat files, and am having troubles because when the for each loop hits the second table the column mappings in the flat file destination are not synchronised with its schema.

I created a for each loop with an enumerator that returns the table names and sets a user variable.

I created a data flow task which dynamically connects to the table name variable.

In the Flat File Destination there is a column mapping property, but I don't know how to reset these mappings on each iteration.

Any ideas?

Are all the tables you are trying to export have exactly same column metadata? If not, you can't do this using a for loop. This is because SSIS cannot dynamically update the column mapping/schema. If the tables have different metadata, you need to create separate data flow tasks for them.|||Thanks.

Sunday, March 25, 2012

Exporting data to Excel files

Hi everybody, i'm new to SSIS, so it's possible that mine is a very stupid question

I have to develop a simple ETL package that reads data from a csv file and writes them to an xls file; the problem is that when the number of rows exceeds the maximum number of rows allowed for an xls file i get an error.

There is a way to solve this problem? for example adding a new sheet or creating a new file?

Thanks in advance

You can do this, but you'll have to do a little extra work. Basically, you need to preprocess your CSV to get the number of rows that will fit in a sheet, write them, then process the next set.

These posts might help:

http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||I don't see any other solution than create multiple sheets/files. That is an Excel limititaion, nothing that SSIS can do about it.|||seems like it may works, thanks!

Exporting data to a fixed-width flat file

Hi,
There's a lot of information on importing data from text files, but not a lot on exporting data to text files... I've checked but found no info on this.

I'm trying to export data from SQL Server to a fixed-width flat file and wondering if I'm doing it the right way.

I use a view as source (using a OLEDB connection manager) and I can see the data without problem.

I defined a Flat File Destination (using a flat file connection manager). When setting up the flat file connection manager, I am asked for a file... Does this mean one should create manually a template file with the desired output format? So I used a production file as template since we're replacing an existing process.

After having set up everything, I run the SSIS only to see all the data on the same row. There are no CRLF...

When I create the file connection manager, there's no way to mention the row delimiter. In the properties I see a "Row Delimiter" field and when I try with "{CR}{LF}" it makes no difference. Interesting to note that, contrary to the HeaderRowDelimiter field, the RowDelimiter field has no drop-down control to give choices.

So I had to return the CRLF as the last field of the source view (SELECT .... ,'CRLF' = CHAR(13) + CHAR(10) FROM ...) to make it work.

Seems odd... Is it the way to go?

Thanks

SSIS implements a row delimiter in an odd way - it parses it from the last field delimiter.

You could Open the Flat File Connection manager - Click on "Advanced" - scroll down to last column and - set ColumnDelimiter to "{CR}{LF}"|||

Hi TVM,

The ColumnDelimiter field is greyed out (unavailable)...

Thanks

|||I tried it out - it seems like the solution you are looking for is to use "ragged-right" format

In that case you can specify widths for columns and last column can be delimited with CR LF|||

Thanks I'll try that!!

EDIT: It works! Thank you very much

|||

fleo wrote:
Thanks I'll try that!!
EDIT: It works! Thank you very much

Please mark this thread as answered.|||

coming in later with a similar issue...

i had probs with a fixed width export, as well -- and the probs didn't seem "rational"; i just couldn't get the fixed-width SSIS solution to work. as soon as i setup up the ragged right solution, everything fell into place.

in my mind, fixed-width = ragged right.

thx/spirits,

seth j hersh

sql

Exporting data to a fixed-width flat file

Hi,
There's a lot of information on importing data from text files, but not a lot on exporting data to text files... I've checked but found no info on this.

I'm trying to export data from SQL Server to a fixed-width flat file and wondering if I'm doing it the right way.

I use a view as source (using a OLEDB connection manager) and I can see the data without problem.

After having set up everything, I run the SSIS only to see all the data on the same row. There are no CRLF...

So I had to return the CRLF as the last field of the source view (SELECT .... ,'CRLF' = CHAR(13) + CHAR(10) FROM ...) to make it work.

Seems odd... Is it the way to go?

Thanks

SSIS implements a row delimiter in an odd way - it parses it from the last field delimiter.

You could Open the Flat File Connection manager - Click on "Advanced" - scroll down to last column and - set ColumnDelimiter to "{CR}{LF}"|||

Hi TVM,

The ColumnDelimiter field is greyed out (unavailable)...

Thanks

|||I tried it out - it seems like the solution you are looking for is to use "ragged-right" format

In that case you can specify widths for columns and last column can be delimited with CR LF|||

Thanks I'll try that!!

EDIT: It works! Thank you very much

|||

fleo wrote:
Thanks I'll try that!!

EDIT: It works! Thank you very much

Please mark this thread as answered.|||

coming in later with a similar issue...

in my mind, fixed-width = ragged right.

thx/spirits,

seth j hersh

Exporting Data from tables to text file all at once

Hello !!!

Can u please suggest me , the way to Export the data from all the tables of the Data Base at a time in text files individually with file names similar to the table names .

So if i had 200 tables in my data base . i require 200 text files each having the data of each table in the data base.

File name of text file should be equal to that of the file name of table.

Quote:

Originally Posted by bvdrsganesh1981

Hello !!!

Can u please suggest me , the way to Export the data from all the tables of the Data Base at a time in text files individually with file names similar to the table names .

So if i had 200 tables in my data base . i require 200 text files each having the data of each table in the data base.

File name of text file should be equal to that of the file name of table.

Hi ,
You can take script the object in sql server along with the data with separeate object name.

Wednesday, March 21, 2012

Export/Import tables from MSQL from/to .xml files

When I try to export a sql table to a .xml file I get the following error
when I use either sqlxmloledb or sqlxmloledb.3.0 as the destination:
sqloledb must be specified as the data provider.
However, sqloledb does not appear in the list of ole db providers. Does
anyone know how I can register sqloledb as a data provider please
I also have a problem using sql query analyzer to access a .xml file. All of
the examples in the help files show encoded xml data rather than accessing a
.xml data file on disk. Does anyone know how to link in the .xml file to sq
l
script please.
Regards,
Alan de Wetsqloledb should already be part of the OS. If you install MDAC / SqlClient
Tools / VS, SQLOLEDB should be installed.
Check C:\Program Files\Common Files\System\Oledb\sqloledb.dll for
verification. If the dll is there and is registered, then SQLOLEDB should be
there.
To use SQLOLEDB as the provider in the connection string, append
Provider=SQLOLEDB to the connection string.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Alan de Wet" <AlandeWet@.discussions.microsoft.com> wrote in message
news:B79E3526-4685-44FB-8CA1-94B0E4D97F90@.microsoft.com...
> When I try to export a sql table to a .xml file I get the following error
> when I use either sqlxmloledb or sqlxmloledb.3.0 as the destination:
> sqloledb must be specified as the data provider.
> However, sqloledb does not appear in the list of ole db providers. Does
> anyone know how I can register sqloledb as a data provider please
> I also have a problem using sql query analyzer to access a .xml file. All
> of
> the examples in the help files show encoded xml data rather than accessing
> a
> .xml data file on disk. Does anyone know how to link in the .xml file to
> sql
> script please.
> Regards,
> Alan de Wet|||Thanks for your reply Chandra. SQLOLEDB does not appear in the list of ole d
b
providers when I try to do an export from the Import and Export tool in MSQL
and am entering the Destination details. I am not trying to do the export
from a VB application so I do not have a connection string. SQLOLEDB is in
C:\Program Files\Common Files\System\Ole DB (being Ole DB and not Oledb). Ho
w
do I check if it is registered please?
Regards,
Alan
"Chandra Kalyanaraman [MSFT]" wrote:

> sqloledb should already be part of the OS. If you install MDAC / SqlClient
> Tools / VS, SQLOLEDB should be installed.
> Check C:\Program Files\Common Files\System\Oledb\sqloledb.dll for
> verification. If the dll is there and is registered, then SQLOLEDB should
be
> there.
> To use SQLOLEDB as the provider in the connection string, append
> Provider=SQLOLEDB to the connection string.
>
> thanks
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Alan de Wet" <AlandeWet@.discussions.microsoft.com> wrote in message
> news:B79E3526-4685-44FB-8CA1-94B0E4D97F90@.microsoft.com...
>
>|||Is it true that SQLXMLOLEDB provider supports data export via DTS
Import/Export Wizard?
Best regards
Martin Rakhmanov
"Alan de Wet" wrote:
> Thanks for your reply Chandra. SQLOLEDB does not appear in the list of ole
db
> providers when I try to do an export from the Import and Export tool in MS
QL
> and am entering the Destination details. I am not trying to do the export
> from a VB application so I do not have a connection string. SQLOLEDB is in
> C:\Program Files\Common Files\System\Ole DB (being Ole DB and not Oledb).
How
> do I check if it is registered please?
> Regards,
> Alan
> "Chandra Kalyanaraman [MSFT]" wrote:
>|||After I installed sqlxml 2 new Data Providers (SQLXMLOLEDB and
SQLXMLOLEDB.3.0) appear in the list when I select my destination. I have
tried both of them. When I select either one I get a message saying: To
connect click properties and enter the required information. When I click on
properties I do not see sqloledb as a ole db provider but if I select any
other provider, click OK and then click next I get a message saying that
SQLOLEDB must be specified as the data provider. If I select either
sqxmloledb or sqlxmloledb.3.0 as the ole db provider under properties I am
then asked to enter Data Source, Location and Server Log On Information whic
h
does not make sense as I am trying to export to a .xml file.
So to answer your question, maybe it was supposed to but it does not appear
to support data export via DTS Import/Export Wizard.
Best Regards,
Alan de Wet
"jimmers" wrote:
> Is it true that SQLXMLOLEDB provider supports data export via DTS
> Import/Export Wizard?
> Best regards
> Martin Rakhmanov
>
> "Alan de Wet" wrote:
>

Export/Import tables from MSQL from/to .xml files

When I try to export a sql table to a .xml file I get the following error
when I use either sqlxmloledb or sqlxmloledb.3.0 as the destination:
sqloledb must be specified as the data provider.
However, sqloledb does not appear in the list of ole db providers. Does
anyone know how I can register sqloledb as a data provider please
I also have a problem using sql query analyzer to access a .xml file. All of
the examples in the help files show encoded xml data rather than accessing a
..xml data file on disk. Does anyone know how to link in the .xml file to sql
script please.
Regards,
Alan de Wet
sqloledb should already be part of the OS. If you install MDAC / SqlClient
Tools / VS, SQLOLEDB should be installed.
Check C:\Program Files\Common Files\System\Oledb\sqloledb.dll for
verification. If the dll is there and is registered, then SQLOLEDB should be
there.
To use SQLOLEDB as the provider in the connection string, append
Provider=SQLOLEDB to the connection string.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Alan de Wet" <AlandeWet@.discussions.microsoft.com> wrote in message
news:B79E3526-4685-44FB-8CA1-94B0E4D97F90@.microsoft.com...
> When I try to export a sql table to a .xml file I get the following error
> when I use either sqlxmloledb or sqlxmloledb.3.0 as the destination:
> sqloledb must be specified as the data provider.
> However, sqloledb does not appear in the list of ole db providers. Does
> anyone know how I can register sqloledb as a data provider please
> I also have a problem using sql query analyzer to access a .xml file. All
> of
> the examples in the help files show encoded xml data rather than accessing
> a
> .xml data file on disk. Does anyone know how to link in the .xml file to
> sql
> script please.
> Regards,
> Alan de Wet
|||Thanks for your reply Chandra. SQLOLEDB does not appear in the list of ole db
providers when I try to do an export from the Import and Export tool in MSQL
and am entering the Destination details. I am not trying to do the export
from a VB application so I do not have a connection string. SQLOLEDB is in
C:\Program Files\Common Files\System\Ole DB (being Ole DB and not Oledb). How
do I check if it is registered please?
Regards,
Alan
"Chandra Kalyanaraman [MSFT]" wrote:

> sqloledb should already be part of the OS. If you install MDAC / SqlClient
> Tools / VS, SQLOLEDB should be installed.
> Check C:\Program Files\Common Files\System\Oledb\sqloledb.dll for
> verification. If the dll is there and is registered, then SQLOLEDB should be
> there.
> To use SQLOLEDB as the provider in the connection string, append
> Provider=SQLOLEDB to the connection string.
>
> thanks
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Alan de Wet" <AlandeWet@.discussions.microsoft.com> wrote in message
> news:B79E3526-4685-44FB-8CA1-94B0E4D97F90@.microsoft.com...
>
>
|||Is it true that SQLXMLOLEDB provider supports data export via DTS
Import/Export Wizard?
Best regards
Martin Rakhmanov
"Alan de Wet" wrote:
[vbcol=seagreen]
> Thanks for your reply Chandra. SQLOLEDB does not appear in the list of ole db
> providers when I try to do an export from the Import and Export tool in MSQL
> and am entering the Destination details. I am not trying to do the export
> from a VB application so I do not have a connection string. SQLOLEDB is in
> C:\Program Files\Common Files\System\Ole DB (being Ole DB and not Oledb). How
> do I check if it is registered please?
> Regards,
> Alan
> "Chandra Kalyanaraman [MSFT]" wrote:
|||After I installed sqlxml 2 new Data Providers (SQLXMLOLEDB and
SQLXMLOLEDB.3.0) appear in the list when I select my destination. I have
tried both of them. When I select either one I get a message saying: To
connect click properties and enter the required information. When I click on
properties I do not see sqloledb as a ole db provider but if I select any
other provider, click OK and then click next I get a message saying that
SQLOLEDB must be specified as the data provider. If I select either
sqxmloledb or sqlxmloledb.3.0 as the ole db provider under properties I am
then asked to enter Data Source, Location and Server Log On Information which
does not make sense as I am trying to export to a .xml file.
So to answer your question, maybe it was supposed to but it does not appear
to support data export via DTS Import/Export Wizard.
Best Regards,
Alan de Wet
"jimmers" wrote:
[vbcol=seagreen]
> Is it true that SQLXMLOLEDB provider supports data export via DTS
> Import/Export Wizard?
> Best regards
> Martin Rakhmanov
>
> "Alan de Wet" wrote:

Monday, March 19, 2012

Export valid XML files

Ladies and Gents,
I cannot seem to find an (easily) workable solution anywhere on the web.
All I need to do, is return the contents of some SQL server tables as XML
data sets.
I was able to form "proper" XML files by adding a top-level root element
(still unclear on why that is not built in). Now the issue that remains is
that SQL server will return the results with a ROWGUID and a full line
of undescores (_________________) before the actual XML.
I am trying to keep the process as simple as can get, avoiding SQLXML
and IIS -- I just want to find a solution where I can create a simple (!)
DTS package that will deliver a properly formatted XML file with a top
level element, one that IE will be able to display properly without
any further editing.
1) is this possible (anything is possible -- can this be done via some form
of "FOR XML" statement)
2) if there is no easy solution to 1, what methods would you recommend to
make the process
most maintanable? My requirements are simple -- I need to produce a
"feed" -- a set of properly formatted
XML files, based on many queries -- probably about 15-20 files
alltogether, so I need this to be
as simple as possible.
TIA,
EugeneCould you be more specific? Are you trying to export the database schema or
the data in your tables?
As simple as possible? A FOR XML query in a SQLXML template.
ML|||If you follow the "Microsoft" way, you set up a "web service" using the HTML
capabilities of SQL Server. The template supplies the formatting you need an
d
returns data in the proper format.
I do not have a link, but you can push a root tag into your FOR XML return.
The examples I have seen use the template, as well.
If the return is not valid, you can also throw the data through an XSLT
transform and get it to look however you desire. This is an extra step,
however.
NOTE: I am fairly fond of the whole SOA/web service concept, but it is not
for everyone. For SQL Server 2000, the link into IIS is not the best
implementation (HTTP endpoints in SQL 2005 are much better), but they provid
e
a means of moving towards SOA. SOA is not a silver bullet, but it makes a lo
t
of sense if you can make the paradigm change to working with messages instea
d
of CRUD.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Eugene" wrote:

> Ladies and Gents,
> I cannot seem to find an (easily) workable solution anywhere on the web.
> All I need to do, is return the contents of some SQL server tables as XML
> data sets.
> I was able to form "proper" XML files by adding a top-level root element
> (still unclear on why that is not built in). Now the issue that remains i
s
> that SQL server will return the results with a ROWGUID and a full line
> of undescores (_________________) before the actual XML.
> I am trying to keep the process as simple as can get, avoiding SQLXML
> and IIS -- I just want to find a solution where I can create a simple (!)
> DTS package that will deliver a properly formatted XML file with a top
> level element, one that IE will be able to display properly without
> any further editing.
> 1) is this possible (anything is possible -- can this be done via some for
m
> of "FOR XML" statement)
> 2) if there is no easy solution to 1, what methods would you recommend to
> make the process
> most maintanable? My requirements are simple -- I need to produce a
> "feed" -- a set of properly formatted
> XML files, based on many queries -- probably about 15-20 files
> alltogether, so I need this to be
> as simple as possible.
> TIA,
> Eugene
>
>|||I am trying to export the data.
Something along the lines of :
"ML" <ML@.discussions.microsoft.com> wrote in message
news:13E842CF-133A-42D8-BFCC-81FD7A89D685@.microsoft.com...
> Could you be more specific? Are you trying to export the database schema
or
> the data in your tables?
> As simple as possible? A FOR XML query in a SQLXML template.
>
> ML|||I am trying to export the data. Here's my query
SELECT
1 AS TAG
,NULL AS PARENT
,NULL AS [INSTITUTIONS!1]
,NULL AS [INSTITUTION!2!iconum]
,NULL AS [INSTITUTION!2!cusip]
,NULL AS [INSTITUTION!2!dlr]
,NULL AS [INSTITUTION!2!position]
,NULL AS [INSTITUTION!2!pct_held]
,NULL AS [INSTITUTION!2!position_change]
UNION
SELECT 2 AS TAG
,1 AS PARENT
,NULL AS [INSTITUTIONS!1]
,iconum AS [INSTITUTION!2!iconum]
,cusip AS [INSTITUTION!2!cusip]
,dlr AS [INSTITUTION!2!dlr]
,position AS [INSTITUTION!2!position]
,pct_held AS [INSTITUTION!2!pct_held]
,position_change AS [INSTITUTION!2!position_change]
FROM ##EUGENE_TEMP
ORDER BY [INSTITUTION!2!iconum]
FOR XML EXPLICIT
I get back something like :
XML_F52E2B61...
_____________________________
<INSTITUTIONS>
<INSTITUTION iconum="10134" cusip="151313103" dlr="2005-03-31T00:00:00"
position="22000" pct_held="0.002" position_change="22000"/>
</INSTITUTIONS>
I need to not have the top
XML_F52E2B61...
_____________________________.
SQLXML is not really an option now -- no ISS on the SQL box... Looks like
I'll just have to script a solution, unless
someone has another idea...
Thanks
"ML" <ML@.discussions.microsoft.com> wrote in message
news:13E842CF-133A-42D8-BFCC-81FD7A89D685@.microsoft.com...
> Could you be more specific? Are you trying to export the database schema
or
> the data in your tables?
> As simple as possible? A FOR XML query in a SQLXML template.
>
> ML|||If you're using BCP to write the results to a text file or using QA's
"Results to file" option, you'll have to remove the unwanted pieces of text
either manually or programatically (not in T-SQL).
Whether you like it or not, you will need a client application to read the
result.
Oh, and another thing - XML results might get split into several rows of
data if not read as an XML by an appropriate client.
If installing IIS creates a possible breach of security, you can still use
the IIS lock-down tool to keep your network secure. The power of SQLXML is
immense, so why not use it?
ML

Friday, March 9, 2012

export to PDF (reporting services)

Greetings!,

Does anyone here know where to configure the document properties/security settings of PDF files generated by reporting services?

thanks!

What are you wanting to achieve - specific settings?|||

I want to make the PDF not printable, not modifiable, set passwords...etc..

These properties are found under the Document Security tab under the File menu in Acrobat reader (if you open the exported PDF report).

|||The PDF export format from Reporting Services is not configurable for document security. Those features are part of the Adobe software.|||

Thanks Brad.

Any suggestions on other 3rd party components where I can do export to PDF (asp/htm to pdf) programatically?

|||

Hi,

ABCpdf from WebSuperGoo will allow you to do what you want. One word of caution they currently don't have a 64 bit version of their component.

Regards,

Sanjay

|||

In addition to Brian, you have to keep in mind that even the features of PDFing your reports is limited and can′t be compared to the original product as their are relying on some older engine (Guess it was 4.0).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Sanjay,

Thanks. What dou you mean about the 64 bit version? What would be the probable repercussions?

regards

|||

Hi,

Just that if you are planning to deploy an application using that component to a 64 bit server, it won't work. The ABCpdf component has to run in a 32 bit process. You can set IIS to run in 32 bit mode but that is a server wide setting. They are currently developing a 64 bit version but I haven't got a release date.

Regards,

Sanjay

|||

Hi,

Does someone know if there is a.dll or patches (that I can register in my server) that Adobe released in order to toggle the default Document Security properties?

thanks

Export to multiple PDFs on-demand?

Is there an easy way to export a single report to multiple PDF files
on demand? I know that I can use a Data-Driven Subscription to
generate multiple files, but if I want to give this ability to a user
to look at the report, decide that it's good, and then save as PDF to
multiple files, can I do that?
Scenario: We have an invoicing system. The report generates invoices
for all of our customers at one time (we have the option to do
individual, but as the number of customers grows, this could get
tedious). After verifying that we've applied all credits or factored
in extra charges, we want to generate the reports. Currently, we
export to a PDF and manually split out each invoice into its own
file. I figure there's got to be an easier way to do this on demand,
but don't quite know where to start.
We're running SQL RS 2005 - SP2+hotfixes at this point. Any
suggestions on how to move forward would be appreciated.
Thanks.
-Pete SchottOn May 10, 12:53 pm, paschott <pasch...@.gmail.com> wrote:
> Is there an easy way to export a single report to multiple PDF files
> on demand? I know that I can use a Data-Driven Subscription to
> generate multiple files, but if I want to give this ability to a user
> to look at the report, decide that it's good, and then save as PDF to
> multiple files, can I do that?
> Scenario: We have an invoicing system. The report generates invoices
> for all of our customers at one time (we have the option to do
> individual, but as the number of customers grows, this could get
> tedious). After verifying that we've applied all credits or factored
> in extra charges, we want to generate the reports. Currently, we
> export to a PDF and manually split out each invoice into its own
> file. I figure there's got to be an easier way to do this on demand,
> but don't quite know where to start.
> We're running SQL RS 2005 - SP2+hotfixes at this point. Any
> suggestions on how to move forward would be appreciated.
> Thanks.
> -Pete Schott
As far as I know, this functionality does not exist (outside a custom
application). That said, you could group by customer and select 'Page
break at end' as part of the grouping properties of table/matrix
report controls. Also, you could include a subreport based on the
customer; however, these options only create separate pages. Sorry
that I could not be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Sunday, February 26, 2012

Export to Excel failed with ReportItems and sub report

Hi,

I have two RDL files, one is main.rdl, the other one is sub.rdl.

In the body of main.rdl, I threw in a subreport that links to sub.rdl. Follow by the subreport is a table, in which has a textbox called mytitle. In the page footer, I added one text box that references to mytitle by using ReportItems!mytitle.Value.

When this report rendered in HTML or PDF format, it worked fine. However, when exporting to excel format, it failed. I figured it is because there isn't a ReportItem in the subreport called mytitle. I tried adding a dummy textbox into the subreport and called it mytitle, but again, no luck.

Did anyone ever encounter the same problem? Is there a work around way?

Thanks,

How did the export fail? Did the report not export at all, or it was part of the report that didn't work? What error did you get? The excel renderer doesn't support nested tables/matrics. Did you have some complex in the subreport?|||

I've done more testing on the problem. I didn't use nested tables/matrics in main and sub reports. In my sub report, it has two tables, not nested, one follows the other.

The report was not exporting at all. It gave me an error when I tried to export to Excel in VS.NET 2005. The error message was:

An error occurred during local report processing.

An error occurred during rendering of the report.

An error occurred during rendering of the report

An unexpected error occurred in Report Processing.

The expression referenced a non-existing reportitem in the reportitems collection.

|||This sounds like a problem in our code. Can you attach your RDL please?|||

RDL for the main report

|||

RDL for sub report

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DS">
<DataSourceReference>DS</DataSourceReference>
<rd:DataSourceID>fe1336f9-bea0-4334-8dca-de54f755ec93</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table2">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>DS2</DataSetName>
<Top>1.125in</Top>
<ZIndex>3</ZIndex>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ID_1">
<rd:DefaultName>ID_1</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>Turquoise</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Second Table</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
<Textbox Name="ForFooter">
<Top>2.25in</Top>
<ZIndex>2</ZIndex>
<Width>2.75in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Dummy TextBox for Footer in MainReport</Value>
</Textbox>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>20pt</FontSize>
<Color>SteelBlue</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.36in</Height>
<Value>SubReport</Value>
</Textbox>
<Table Name="table1">
<DataSetName>DS1</DataSetName>
<Top>0.5in</Top>
<Width>2in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ID">
<rd:DefaultName>ID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.21in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>First table</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.22in</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
</TableColumns>
<Height>0.43in</Height>
</Table>
</ReportItems>
<Height>4.79in</Height>
</Body>
<rd:ReportID>d7980735-ccd4-4952-b9e3-849d60c1bc55</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DS1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>Select 'Value in Sub Report' AS ID</CommandText>
<DataSourceName>DS</DataSourceName>
</Query>
<Fields>
<Field Name="ID">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ID</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="DS2">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>SELECT 'Second Table in SubReport' AS ID</CommandText>
<DataSourceName>DS</DataSourceName>
</Query>
<Fields>
<Field Name="ID">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ID</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||Is this going to be resolved in the next service pack?|||I've filed a bug in our internal bug tracking database. We'll consider it for the next service pack or a future release. Thanks for reporting it.|||We have actually fixed this issue in SQL Server 2005 SP1. Please try it and let us know if it still doesn't work for you.|||

In Portuguese: Excelente

In Inglish: It's Very Good...

Thiago Cruz

thiago.cruz@.dotnetraptors.com.br

|||This was working without SP1 and is now broken after installing SP1.|||

i have vs studio 2k5 sp1 installed and i am getting this error. I have office 2003 installed. Is there still a bug ticket out for this? For now what I do is wait until the report is loaded then delete Excel from the dropdown list so it can't be accessed ( so the user can't generate this error, but it would nice to be working.)

Here is the code to remove Excel from the drop down list ...

Code Snippet

Dim dropExport As DropDownList = ReportViewer1.Controls(1).Controls(5).Controls(0)

dropExport.SelectedValue = "EXCEL"

dropExport.Items.RemoveAt(dropExport.SelectedIndex)

And here is the stack trace of the error when I click Export while Excel is selected:

Code Snippet

[Exception: The expression referenced a non-existing reportitem in the reportitems collection.]

[Exception: An unexpected error occurred in Report Processing.]

[Exception: An error occurred during rendering of the report.]

[Exception: An error occurred during rendering of the report.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +553
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +941
Microsoft.Reporting.WebForms.ServerReport.Render(String format, String deviceInfo, NameValueCollection urlAccessParameters, String& mimeType, String& fileNameExtension) +97
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +126
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +153
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +202
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

|||

I have exactly the same error here. I have a list on the report body(to avoid the excel sub-reports rendering problem, I have to use list instead of table). There is a textbox and some other sub-reports on the list. A textbox in the header reference the list textbox.

The report looks ok for the screen view, but when I try the "Excel" export, I got the "The expression referenced a non-existing reportitem in the reportitems collection. " error. If I remove all sub-reports, it works fine.

My SQL server installed SP2. Below is the version message for the sql server [ Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86) Mar 3 2007 18:40:02 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2) ]. Appreciate any help!

(I'm using VS2005 with SP1, and reportviewer control with asp.net)

|||

Could any body give any help?

Thanks!

Export to Excel failed with ReportItems and sub report

Hi,

I have two RDL files, one is main.rdl, the other one is sub.rdl.

Did anyone ever encounter the same problem? Is there a work around way?

Thanks,

I've done more testing on the problem. I didn't use nested tables/matrics in main and sub reports. In my sub report, it has two tables, not nested, one follows the other.

The report was not exporting at all. It gave me an error when I tried to export to Excel in VS.NET 2005. The error message was:

An error occurred during local report processing.

An error occurred during rendering of the report.

An error occurred during rendering of the report

An unexpected error occurred in Report Processing.

The expression referenced a non-existing reportitem in the reportitems collection.

|||This sounds like a problem in our code. Can you attach your RDL please?|||

RDL for the main report

|||

RDL for sub report

In Portuguese: Excelente

In Inglish: It's Very Good...

Thiago Cruz

thiago.cruz@.dotnetraptors.com.br

|||This was working without SP1 and is now broken after installing SP1.|||

Here is the code to remove Excel from the drop down list ...

Code Snippet

Dim dropExport As DropDownList = ReportViewer1.Controls(1).Controls(5).Controls(0)

dropExport.SelectedValue = "EXCEL"

dropExport.Items.RemoveAt(dropExport.SelectedIndex)

And here is the stack trace of the error when I click Export while Excel is selected:

Code Snippet

[Exception: The expression referenced a non-existing reportitem in the reportitems collection.]

[Exception: An unexpected error occurred in Report Processing.]

[Exception: An error occurred during rendering of the report.]

|||

(I'm using VS2005 with SP1, and reportviewer control with asp.net)

|||

Could any body give any help?

Thanks!

Export to Excel failed with ReportItems and sub report

Hi,

I have two RDL files, one is main.rdl, the other one is sub.rdl.

Did anyone ever encounter the same problem? Is there a work around way?

Thanks,

I've done more testing on the problem. I didn't use nested tables/matrics in main and sub reports. In my sub report, it has two tables, not nested, one follows the other.

The report was not exporting at all. It gave me an error when I tried to export to Excel in VS.NET 2005. The error message was:

An error occurred during local report processing.

An error occurred during rendering of the report.

An error occurred during rendering of the report

An unexpected error occurred in Report Processing.

The expression referenced a non-existing reportitem in the reportitems collection.

|||This sounds like a problem in our code. Can you attach your RDL please?|||

RDL for the main report

|||

RDL for sub report

In Portuguese: Excelente

In Inglish: It's Very Good...

Thiago Cruz

thiago.cruz@.dotnetraptors.com.br

|||This was working without SP1 and is now broken after installing SP1.|||

Here is the code to remove Excel from the drop down list ...

Code Snippet

Dim dropExport As DropDownList = ReportViewer1.Controls(1).Controls(5).Controls(0)

dropExport.SelectedValue = "EXCEL"

dropExport.Items.RemoveAt(dropExport.SelectedIndex)

And here is the stack trace of the error when I click Export while Excel is selected:

Code Snippet

[Exception: The expression referenced a non-existing reportitem in the reportitems collection.]

[Exception: An unexpected error occurred in Report Processing.]

[Exception: An error occurred during rendering of the report.]

|||

(I'm using VS2005 with SP1, and reportviewer control with asp.net)

|||

Could any body give any help?

Thanks!

Thursday, March 29, 2012

Exporting report to WordML

Exporting report to text files

Exporting report to multiple PDF's

Tuesday, March 27, 2012

Exporting multiple tables as flat files

Sunday, March 25, 2012

Exporting data to Excel files

Exporting data to a fixed-width flat file

Exporting data to a fixed-width flat file

Exporting Data from tables to text file all at once

Wednesday, March 21, 2012

Export/Import tables from MSQL from/to .xml files

Export/Import tables from MSQL from/to .xml files

Monday, March 19, 2012

Export valid XML files

Friday, March 9, 2012

export to PDF (reporting services)

Export to multiple PDFs on-demand?

Sunday, February 26, 2012

Export to Excel failed with ReportItems and sub report

Export to Excel failed with ReportItems and sub report

Export to Excel failed with ReportItems and sub report

Export Report to File

Blog Archive

About Me