hi all,
I wanted to know how to export a report that returns more than 65,000 rows of data to MS Excel, it will fail because excel has a limit of 65K records per excel sheet.
What i want to know if there is some way to use multiple sheets (in the same excel file) to show more than 65K records
Eg we have 105K rows of data ...can we export 65K records in the first sheet, and the remaining 40K in the second sheet (of the same excel file)?
Appreciate the Help,
Siddharth
Hello Siddharth,
Using a Table, you can force an explicit page break after a certain number of rows. Because the Excel renderer creates a new worksheet for every explicit page break, you can use this technique to make sure no more than 65,536 rows are exported to a single sheet.
Create an outer table group using this group expression: =Int((RowNumber(Nothing)-1)/65000). Set Page break at end on the group.
Keep in mind that there isn't a one-to-one relationship between SSRS table rows and Excel rows unless the table is the only report item in the report body. So, if you have other report items in addition to your table you will have to also take that into account.
-Chris
|||
Hi Chris,
I knew page break created a new sheet in excel but didnt know how to do it. thanks a lot for thehelp its woking. appreciate it.
siddharth
sql
No comments:
Post a Comment