Exporting in SSRS 2005

Hi,

I'm developing a web app in .NET 2.0 using SSRS 2000 and 2005. Some of the reports need to be exported directly to PDF when clicked, but the problem is in the fact that the user must select the parameters from the report manager window.

Is there any way to allow the direct export to PDF when generate is clicked, or to limit the options the user has to export to in the report manager.

P.S. If it is possible, I DO NOT want to accomplish this using code.

Thanks,

Hello,

Here's how to get rid of the other export options:

Navigate to {System Root}:\Program Files\Microsoft SQL Server\MSSQL.{RS_Instance_ID}\Reporting Services\ReportServer

Open rsreportserver.config in your favorite text editor

Search for <Render>

You should see the available rendering extensions here

<Extension Name="XML" ... />

<Extension Name="EXCEL" ... />

...

In order to just have PDF available, comment out all but the line for PDF

<!-- <Extension Name="XML" ... /> -->

<!-- <Extension Name="EXCEL" ... /> -->

...

Save the file and close it

Please note, that if you do this, these options won't be available to anyone in Report Manager.

Hope this helps.

Jarret

|||Jarrett. I also had need to do this and it worked. However, I now need to restore one of the ones I had removed. I uncommented out the one I want back, saved the file, and started up Report Manager. The options under export didn't change. The uncommented one didn't reappear. Any help would be appreciated. Thanks, Jim.|||Restart your SQL Server service and ReportServer service completely. Otherwise, check if you did not accidentally make a spelling mistake or something in the Config file. Eduard

Exporting in SSRS 2005

Hi,

I'm developing a web app in .NET 2.0 using SSRS 2000 and 2005. Some of the reports need to be exported directly to PDF when clicked, but the problem is in the fact that the user must select the parameters from the report manager window.

Is there any way to allow the direct export to PDF when generate is clicked, or to limit the options the user has to export to in the report manager.

P.S. If it is possible, I DO NOT want to accomplish this using code.

Thanks,

Hello,

Here's how to get rid of the other export options:

Navigate to {System Root}:\Program Files\Microsoft SQL Server\MSSQL.{RS_Instance_ID}\Reporting Services\ReportServer

Open rsreportserver.config in your favorite text editor

Search for <Render>

You should see the available rendering extensions here

<Extension Name="XML" ... />

<Extension Name="EXCEL" ... />

...

In order to just have PDF available, comment out all but the line for PDF

<!-- <Extension Name="XML" ... /> -->

<!-- <Extension Name="EXCEL" ... /> -->

...

Save the file and close it

Please note, that if you do this, these options won't be available to anyone in Report Manager.

Hope this helps.

Jarret

|||Jarrett. I also had need to do this and it worked. However, I now need to restore one of the ones I had removed. I uncommented out the one I want back, saved the file, and started up Report Manager. The options under export didn't change. The uncommented one didn't reappear. Any help would be appreciated. Thanks, Jim.|||Restart your SQL Server service and ReportServer service completely. Otherwise, check if you did not accidentally make a spelling mistake or something in the Config file. Eduard

Export to Fixed Length Format text file without header row.

I am trying to generate a fixed-length format text file from a parameterised
SSRS report for my client and the only way that comnes close seems to be
to save as CSV with a single column query.
I have created a large, parameterised, UNIONed SQL statement to generate
the lines with the correct padding and alignment. This also concatenates
the columns so that the query effectively only returns a single column which
I can then Save As... CSV format. The problem with this is that I always
get a header row. (I concatenated in the query columns to avoid the commas
and quotes that come with CSV exports.)
I have experimented with the Noheader switch on the URL but the effectiveness
of this seems to disappear once the report a) stops for parameter input and
then b) renders to HTML before allowing me to save to CSV. I believe the
NoHeader option will only be effective if I determine my parameters ahead
of time and incorporate them into the URL also and render to CSV on that
URL as well.
All this combines to make for a complicated URL, certainly not something
my client would expect to create manually. Now I could write a small application
to generate offer them available parameters and then to this URL but I am
unsure if this effort is the way to go.
Options appear to be:
* bcp
* DTS
* application to allow users to input params and then generates the URL
* application to simply write the file and remove SSRS from the equation
* custom rendering extension
* purchase some kind of solution
It seems a lot of effort when I could just get them to manually deleted the
header row, however this approach is too unprofessional for my liking.
Ideally I would like to not have my client reliant on my code for new formats
or for when this format changes, hence a report writer based solution would
be great.
What is the best way to export to a fixed-length format text file from a
parameterised SSRS report?
cheers,
Paul.Unfortunately, as far as I am aware, there is no built in feature that
allows you to supress headers when exporting as csv.
As far as reporting services is concerned, the options are like you
said:
a) to build a custom renderer
b) to build a custom interface instead of using the report manager web
application
Both of these will require considerable effort to ultimately remove a
header row from a csv file
c) you could include a text box on the report with a hyperlink to
navigave to the required url (go to properties -> advanced ->
navigate). The user would then have to click on this text box to
download the report as csv with the appropriate noheaders tag. The
navigation url could be built dynamically using the report and
parameter information available from the formula fields, along with
the export format and noheaders tag appended on the end.
I have never tested this solution but it looks feasable if you are
happy for your users to click on a hyperlink to download.
Regards,
Rowen|||One suggestion is that
1. just make one more copy of the same report name it differently
2. In the copy of the report remove the header and keep the detail alone.
3. In the original report give a hyperlink using "Action" and name it
"Export to CSV" (or whater you like)
4. in the textbox ->Action, refer the copy of the report which is without
header using URL.
So what happens is users can also see the report with header and the link
will create the csv without header.
Amarnath
"Paul Ritchie" wrote:
> I am trying to generate a fixed-length format text file from a parameterised
> SSRS report for my client and the only way that comnes close seems to be
> to save as CSV with a single column query.
> I have created a large, parameterised, UNIONed SQL statement to generate
> the lines with the correct padding and alignment. This also concatenates
> the columns so that the query effectively only returns a single column which
> I can then Save As... CSV format. The problem with this is that I always
> get a header row. (I concatenated in the query columns to avoid the commas
> and quotes that come with CSV exports.)
> I have experimented with the Noheader switch on the URL but the effectiveness
> of this seems to disappear once the report a) stops for parameter input and
> then b) renders to HTML before allowing me to save to CSV. I believe the
> NoHeader option will only be effective if I determine my parameters ahead
> of time and incorporate them into the URL also and render to CSV on that
> URL as well.
> All this combines to make for a complicated URL, certainly not something
> my client would expect to create manually. Now I could write a small application
> to generate offer them available parameters and then to this URL but I am
> unsure if this effort is the way to go.
> Options appear to be:
> * bcp
> * DTS
> * application to allow users to input params and then generates the URL
> * application to simply write the file and remove SSRS from the equation
> * custom rendering extension
> * purchase some kind of solution
> It seems a lot of effort when I could just get them to manually deleted the
> header row, however this approach is too unprofessional for my liking.
> Ideally I would like to not have my client reliant on my code for new formats
> or for when this format changes, hence a report writer based solution would
> be great.
> What is the best way to export to a fixed-length format text file from a
> parameterised SSRS report?
> cheers,
> Paul.
>
>
>
>

Export to Excel - Rare bug with formulas?

Hi,

I'm experiencing a problem in SSRS 2005 with exporting a report to Excel (from the web interface) where it messes up the cells that are referenced in some formulas. This issue seems to be quite rare as it has only happened a few times in several months, but it is a critical issue for me as the report is dealing with financial data and there is no room for mistake.

Basically the report contains a table of financial data, where one column in particular is derived from 3 other columns in the report at run-time. The derived column uses an expression such as:

Code Snippet

=ReportItems!textbox1.Value - ReportItems!textbox2.Value - ReportItems!textbox3.Value - ReportItems!textbox4.Value

This is just in a simple table with no grouping, where there is a header row, one data row, and a footer row.

99% of the time the report runs fine and is accurate. However on the rare occassion when the bug raises its head... Let's say the table in report has 15 columns and 100 rows for a given month. If the bug occurs, it seems to affect 2 rows. When viewing the exported file in Excel, the formula in the derived column in the first buggy row will (incorrectly) try to reference the cells in the Header Row - which is strange because the expression is only referencing other fields in the same row of data, nothing to do with headers or footers - and therefore prints "#VALUE" in the cell because the formula is trying to operate on non-numeric data! And then further down the report, the derived column in the next buggy row will be trying to reference the values in a row that is 4 rows above, instead of the values in it's own row, which again is just puzzling...

I can't confirm that this happens when viewing the report initially from the web interface because the error doesn't get picked up until it is exported to Excel so that the users can work with the data.

Also, just thinking about it, and looking at these buggy rows in the export that I have saved I have noticed;

- The first buggy row is the 4th row of data, and references the header cells 4 rows above it (instead of the cells on the same row)

- The second buggy row is the 5th last row of data, and also references the cells 4 rows above it (instead of the cells on the same row)

Unfortunately I do not have any saved reports where this has happened in the past, so I can't really compare them to see if the same kind of pattern occurs.

And even more confusing is that simply exporting the report again will produce a correct report... However this can only happen after the user realises there is an error and goes back to do the export again, so that might also have something to do with it.

Has anyone else experienced this before, or have any suggestions for a workaround? I guess I could use an additional view to do the work, but I don't want to complicate maintenance on the report(s) that rely on the same data. I'm just baffled as to why this is even happening in the first place.

*Note: I haven't upgraded to SQL 2005 SP2 just yet, but the list of changes don't list a fix for this particular problem...

Thanks in advance for any advice

Bump Anyone have any ideas? Thanks.|||

Hmm, I dont know but I have a suggestion of what to maybe look at if you have not done so already.

Your expression has ReportItems.TextBox1, ReportItems.TextBox2, etc

You might want to check that these items exist and are not duplicated or being created in a different fashion, etc

If creating the reports in report designer or similar then the names for textboxes will get automatically generated so maybe they are getting generated differently for different reports?

Try giving the textboxes that you want to use names that the report designer does not use, like Column1, Column2, etc

Or even better that actual column names that matches your data

See if that has an affect, maybe an automatically created item is taking up that name and so therefore the values are coming from the wrong text box.

Just an idea?

|||

You are referring to textboxes for its values in the same row. I'm sure those textboxes have some expressions, say Fields!Field1.Value. So, why dont you use this expression in place of your report item value? This way, your report will be foolproof.

Shyam

|||

I have had the same problem. The only solution that i have found is to perform the calculation in a function into the code of report.

For example:

Public Function TitleSub(ByVal strValue As String) As String

Dim strRet As String

If Len(strValue) = 0 Then

strRet = "Employee"

ElseIf strValue = "Owner" Then

strRet = "President"

Else

strRet = strValue

End If

Return strRet

End Function

In the "Expression" invoke the function:

=Code.TitleSub(Fields!Title.Value)

Export to Excel - Rare bug with formulas?

Hi,

I'm experiencing a problem in SSRS 2005 with exporting a report to Excel (from the web interface) where it messes up the cells that are referenced in some formulas. This issue seems to be quite rare as it has only happened a few times in several months, but it is a critical issue for me as the report is dealing with financial data and there is no room for mistake.

Basically the report contains a table of financial data, where one column in particular is derived from 3 other columns in the report at run-time. The derived column uses an expression such as:

Code Snippet

=ReportItems!textbox1.Value - ReportItems!textbox2.Value - ReportItems!textbox3.Value - ReportItems!textbox4.Value

This is just in a simple table with no grouping, where there is a header row, one data row, and a footer row.

99% of the time the report runs fine and is accurate. However on the rare occassion when the bug raises its head... Let's say the table in report has 15 columns and 100 rows for a given month. If the bug occurs, it seems to affect 2 rows. When viewing the exported file in Excel, the formula in the derived column in the first buggy row will (incorrectly) try to reference the cells in the Header Row - which is strange because the expression is only referencing other fields in the same row of data, nothing to do with headers or footers - and therefore prints "#VALUE" in the cell because the formula is trying to operate on non-numeric data! And then further down the report, the derived column in the next buggy row will be trying to reference the values in a row that is 4 rows above, instead of the values in it's own row, which again is just puzzling...

I can't confirm that this happens when viewing the report initially from the web interface because the error doesn't get picked up until it is exported to Excel so that the users can work with the data.

Also, just thinking about it, and looking at these buggy rows in the export that I have saved I have noticed;

- The first buggy row is the 4th row of data, and references the header cells 4 rows above it (instead of the cells on the same row)

- The second buggy row is the 5th last row of data, and also references the cells 4 rows above it (instead of the cells on the same row)

Unfortunately I do not have any saved reports where this has happened in the past, so I can't really compare them to see if the same kind of pattern occurs.

And even more confusing is that simply exporting the report again will produce a correct report... However this can only happen after the user realises there is an error and goes back to do the export again, so that might also have something to do with it.

Has anyone else experienced this before, or have any suggestions for a workaround? I guess I could use an additional view to do the work, but I don't want to complicate maintenance on the report(s) that rely on the same data. I'm just baffled as to why this is even happening in the first place.

*Note: I haven't upgraded to SQL 2005 SP2 just yet, but the list of changes don't list a fix for this particular problem...

Thanks in advance for any advice

Bump Anyone have any ideas? Thanks.|||

Hmm, I dont know but I have a suggestion of what to maybe look at if you have not done so already.

Your expression has ReportItems.TextBox1, ReportItems.TextBox2, etc

You might want to check that these items exist and are not duplicated or being created in a different fashion, etc

If creating the reports in report designer or similar then the names for textboxes will get automatically generated so maybe they are getting generated differently for different reports?

Try giving the textboxes that you want to use names that the report designer does not use, like Column1, Column2, etc

Or even better that actual column names that matches your data

See if that has an affect, maybe an automatically created item is taking up that name and so therefore the values are coming from the wrong text box.

Just an idea?

|||

You are referring to textboxes for its values in the same row. I'm sure those textboxes have some expressions, say Fields!Field1.Value. So, why dont you use this expression in place of your report item value? This way, your report will be foolproof.

Shyam

|||

I have had the same problem. The only solution that i have found is to perform the calculation in a function into the code of report.

For example:

Public Function TitleSub(ByVal strValue As String) As String

Dim strRet As String

If Len(strValue) = 0 Then

strRet = "Employee"

ElseIf strValue = "Owner" Then

strRet = "President"

Else

strRet = strValue

End If

Return strRet

End Function

In the "Expression" invoke the function:

=Code.TitleSub(Fields!Title.Value)

Export to Excel

Hello again,
I found out, that there's a limitation in rendering reports to excel
format. The SSRS can only render reports with less than 256 columns!
Is there a work around to render reports with more than 256 columns?
In my case, it would be also possible to start a new sheet after 256
columns! May someone has any ideas how to cope with that!
Thanks,
FlorianYou can design your report in a way to only have a max of 256 columns in your
matrix and start a new matrix after.
and also it has a limitation on the number of rows I think it is in the
32000 row. so you have to put a page break after each matrix, this way you
will have a new sheet in Excel for each matrix.
Good Luck
"falbrech_www@.gmx.de" wrote:
> Hello again,
> I found out, that there's a limitation in rendering reports to excel
> format. The SSRS can only render reports with less than 256 columns!
> Is there a work around to render reports with more than 256 columns?
> In my case, it would be also possible to start a new sheet after 256
> columns! May someone has any ideas how to cope with that!
> Thanks,
> Florian
>|||Hi Paul,
> You can design your report in a way to only have a max of 256 columns in your
> matrix and start a new matrix after.
might be an idea. But I want to use the same report for HTML and in
this case all the columns should be shown as usual (column by column).
Is there a way to distinguish whether a report is rendered for HTML or
Excel within a report (see a previous post)? Then I might be able to
use more than one matrix for Excel and one matrix for HTML by hide or
unhide them.
> and also it has a limitation on the number of rows I think it is in the
> 32000 row. so you have to put a page break after each matrix, this way you
Thnaks for that hint. But this should be a problem! ;)
Florian|||Maybe you could not use the report to export to excel and use some of the
code in the example instead:-
http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/
It would work around the problem.
Dale
"falbrech" <falbrech_www@.gmx.de> wrote in message
news:1187077661.875246.296900@.57g2000hsv.googlegroups.com...
> Hi Paul,
>> You can design your report in a way to only have a max of 256 columns in
>> your
>> matrix and start a new matrix after.
> might be an idea. But I want to use the same report for HTML and in
> this case all the columns should be shown as usual (column by column).
> Is there a way to distinguish whether a report is rendered for HTML or
> Excel within a report (see a previous post)? Then I might be able to
> use more than one matrix for Excel and one matrix for HTML by hide or
> unhide them.
>> and also it has a limitation on the number of rows I think it is in the
>> 32000 row. so you have to put a page break after each matrix, this way
>> you
> Thnaks for that hint. But this should be a problem! ;)
> Florian
>|||Hi!
> Maybe you could not use the report to export to excel and use some of the
> code in the example instead:-
> http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-wor...
Thanks, but OLE Automation as it is decribed in the article is not an
option!
br,
Florian

Export to Excel

We are converting Crystal V10 reports to SSRS and have ran into an
issue when exporting a large dataset 104,000 rows. In Crystal when the
users export this same data it spreads it over multiple worksheets does
any have a solution in SSRS that gives me this same ability.Yes it is possible in SSRS, when you have pagination and export to excel,
then it will in multiple sheets
Amarnath
"smiksuek" wrote:
> We are converting Crystal V10 reports to SSRS and have ran into an
> issue when exporting a large dataset 104,000 rows. In Crystal when the
> users export this same data it spreads it over multiple worksheets does
> any have a solution in SSRS that gives me this same ability.
>

Export SSRS Report to Excel - 65000 lines are limitation?

Currenly work with dataset which has 100000 records, when i export ssrs
report to excel it displayed error message "can't export more than 65000
lines"
I m blocked !!!!I believe that Excel 2007 now has a limit of 1 000 000 rows by 16 384
columns.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Nilesh" <Nilesh@.discussions.microsoft.com> wrote in message
news:1791E023-4FC2-4296-8D02-32F4586538B4@.microsoft.com...
> Currenly work with dataset which has 100000 records, when i export ssrs
> report to excel it displayed error message "can't export more than 65000
> lines"
> I m blocked !!!!
>

Export SSRS 2005 Report to Excel

Hi,

I have created a report in SQL Server 2005 Reporting Services. After I export it to Excel format, if I try to print it (Hardcopy), my Excel crashes. It shows an error and shut down. I'm using Excel 2000. It even shows me same error when I click on "Print Preview", in Excel.

Everytime I try to print the excel worksheet, a window pops up with the heading "Visual Studio Just-in-Time Debugger". The error is: "An unhandled win32 exception occurred in EXCEL.EXE [3460]".

Any help is greatly appreciated.

Perm

Hi Perm,

What happens when you save and re-open the Excel Report?

|||

Thanks for the reply.

I can save it and re-open it without any problem. Excel crashes only when I click on "Print Preview" or when I try to print a hardcopy.

|||This sounds like an Excel issue, but maybe I can help narrow down the problem. What report objects do you use in your report and how are they formatted? You can send the RDL and\or this information directly to me so I can take a look and try to see what the cause is. bradsy@.removemicrosoft.com|||Was this ever solved? I have a user who is running into the same issue and am having trouble finding a solution to this.

Export restrict to Excel only

Hi All,
I have a requirement to restrict the Export the SSRS report into EXCEL
format only.
Anyone have any idea?
Thanks.If you are posting a link to the report inside of an application, you could
just post one with the argument rs:format=excel in the link. That is only a
suggestion though.
"Shailesh K" wrote:
> Hi All,
> I have a requirement to restrict the Export the SSRS report into EXCEL
> format only.
> Anyone have any idea?
> Thanks.
>|||Thanks for quick response.
Actually we are using report viewer control to display the report on our web
page.
In ReportViewer's toolbar..there is dropdown to export format[pdf and excel].
We have requirement to show only Excel option in dropdown, so that user is
able to export in Excel only.
Sorry for incomplete description of problem in previous post!
-Shailesh
"beeradg" wrote:
> If you are posting a link to the report inside of an application, you could
> just post one with the argument rs:format=excel in the link. That is only a
> suggestion though.
> "Shailesh K" wrote:
> > Hi All,
> >
> > I have a requirement to restrict the Export the SSRS report into EXCEL
> > format only.
> >
> > Anyone have any idea?
> >
> > Thanks.
> >