Excel 2003 has limitation of supporting only 65,536 Rows in the worksheet.
Suppose we have SSRS report which has more than 65,536 Rows data and the system where we export SSRS report to Excel Format having Excel 2003 installed on the system, then we got the error as shown in below post.
Let’s do workaround to resolve the issue.
Select table where rows are greater than the above said limit i.e. 65,536.
As shown in below snapshot.
Write the SQL query to fetch the result to shown in the SSRS report as shown in below snapshot.
Create the report using above query and drag the table into the layout and assign the fields from the dataset.
When we run the report we will get the Save icon from where we can store the report into different formats as shown in below snapshot.
When we using above shown Save icon to save the report in Excel we encountered the error as shown in below snapshot as we have the Excel 2003 installed on our local machine.
Lets resolve the error by moving the data to other sheet when we reached the maximum or defined rows per sheet. Let’s see how we can implement moving data to next sheet in same excel.
Go back to table we already designed in SSRS report. Select the Detail row and add Parent group as shown in below snapshot.
After selecting Parent group it will open the below screen where we have to write the expression .
Clicking on above expression button we will get the expression editor where we need to write the expression “=CInt(Ceilng(RowNumber(Nothing)/65000))” as shown in below screen.
Now press ok and run the report, we will get the below error as by default it will write the sorting expression for the group.
Now go to the group properties as shown in below screen and click on the sorting .
Delete the sorting expression in below screen.
Now delete the group column as it is dummy one and don’t want in report.
Click on the Group and click on delete as shown in below screen.
Only Delete the Column not the group associated with it as shown in below screen.
Now again go to the group properties and click on page break and select the check box as shown in below screen.
Now we want header should be in different sheets as well for that add column header repeated for every page as shown in below screenshot.
Now run the report and save it to the hard disk and now it’s not throwing the error . instead of it asking for the location where to store the excel.
Finally we have the report exported in excel as shown in below screen.
Source http://msbitips.com/?p=624
Suppose we have SSRS report which has more than 65,536 Rows data and the system where we export SSRS report to Excel Format having Excel 2003 installed on the system, then we got the error as shown in below post.
Let’s do workaround to resolve the issue.
Select table where rows are greater than the above said limit i.e. 65,536.
As shown in below snapshot.
Write the SQL query to fetch the result to shown in the SSRS report as shown in below snapshot.
Create the report using above query and drag the table into the layout and assign the fields from the dataset.
When we run the report we will get the Save icon from where we can store the report into different formats as shown in below snapshot.
When we using above shown Save icon to save the report in Excel we encountered the error as shown in below snapshot as we have the Excel 2003 installed on our local machine.
Lets resolve the error by moving the data to other sheet when we reached the maximum or defined rows per sheet. Let’s see how we can implement moving data to next sheet in same excel.
Go back to table we already designed in SSRS report. Select the Detail row and add Parent group as shown in below snapshot.
After selecting Parent group it will open the below screen where we have to write the expression .
Clicking on above expression button we will get the expression editor where we need to write the expression “=CInt(Ceilng(RowNumber(Nothing)/65000))” as shown in below screen.
Now press ok and run the report, we will get the below error as by default it will write the sorting expression for the group.
Now go to the group properties as shown in below screen and click on the sorting .
Delete the sorting expression in below screen.
Now delete the group column as it is dummy one and don’t want in report.
Click on the Group and click on delete as shown in below screen.
Only Delete the Column not the group associated with it as shown in below screen.
Now again go to the group properties and click on page break and select the check box as shown in below screen.
Now we want header should be in different sheets as well for that add column header repeated for every page as shown in below screenshot.
Now run the report and save it to the hard disk and now it’s not throwing the error . instead of it asking for the location where to store the excel.
Finally we have the report exported in excel as shown in below screen.
Source http://msbitips.com/?p=624
No comments:
Post a Comment