Resolution : Max 65,536 Rows Limitation While Exporting SSRS Report to Excel 2003

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

The PowerPivot and Power View Add-Ins for Excel 2013

 

In Excel 2013, the PowerPivot add-in, introduced in Excel 2010, that enables you to efficiently work with and analyze large datasets (such as those with hundreds of thousands or even millions of records) has been made a much more integral part the program.
In fact, the PowerPivot technology that makes it possible for Excel to easily manage massive amounts of data from many related data tables is now part and parcel of Excel 2013 in the form of its Data Model feature. This means that you don’t even have to trot out and use the PowerPivot add-in in order to be able to create Excel pivot tables that utilize tons of data records stored in multiple, related data tables.
If you do decide that you want to use PowerPivot in managing large datasets and doing advanced data modeling in your Excel pivot tables, instead of having to download the add-in from the Microsoft Office website, you can start using PowerPivot simply by activating the add-in as follows:

• Choose File→Options→Add-Ins .
• Excel opens the Add-Ins tab of the Excel Options dialog box with Excel Add-Ins selected in the Manage drop-down list.
• Click the Manage drop-down list button and then select COM Add-Ins from the drop-down list before you select the Go button.

• Excel displays the COM Add-Ins dialog box that contains (as of this writing) three COM (Component Object Model) add-ins: Inquire Add-in, Microsoft Office PowerPivot for Excel 2013, and Power View.
• Select the check box in front Microsoft Office PowerPivot and Power View for Excel 2013 and then click OK.

• Closes the COM Add-Ins dialog box and returns you to the Excel 2013 worksheet window that now contains a PowerPivot tab at the end of the Ribbon.

Keep in mind that the Excel PowerPivot add-in is available in Office 2013 Professional Plus edition as well as all editions of Office 365, except for Small Business. However, PowerPivot is not supported in Excel 2013 running on the RT version of the Microsoft Surface tablet., but you have the Microsoft Surface tablet with Windows 8 Pro in order to install and use the PowerPivot add-in
Source
http://office.microsoft.com/en-in/excel-help/start-powerpivot-in-excel-2013-add-in-HA102837097.aspx
http://www.dotnetcurry.com/ShowArticle.aspx?ID=853

What version of Office am I using?

You can use the screen captures and steps below to see which version of Microsoft Office is installed on your computer.

Office 2003

Office 2003 has text-based menus on the gray bar: File, Edit, View, Insert, Format, Tools, Table, Window, and Help. The following shows Word 2003.

1. Click Help > About.
2. In the About dialog box, find the version and the Service Pack (SP). Product licensing appears in the This product is licensed to field.

Office 2007
Office 2007 introduced tabs and the Microsoft Office Button and a tab menu. The following shows Word 2007.

1. Click the Microsoft Office Button > Word Options.
2. In the Options dialog box, click Resources. Find the version and the Service Pack (SP).

Note To learn about licensing, in Resources, click About. On the About dialog box, see This product is licensed to.
 

Office 2010

Office 2010 introduces the File tab and the Microsoft Office Backstage view. The following shows Word 2010.

1. Click File > Help.
2. Under Product Activated, find the version.

Note To learn about licensing, see About Microsoft Word.
Office 2013
The latest version of Office still uses the File tab and the Microsoft Office Backstage view. The following shows Word 2013.

1. Click File > Account.
2. Under Product Information, find the version.

Note To learn about licensing, click About Word.
 

Office 2013 RT

Office 2013 RT also uses the File tab and the Microsoft Office Backstage view. The following shows Word RT.

1. Tap File > Account.
2. Under Product Information, find the version.

Note To learn about licensing, tap About Word.

Read write Excel using SSIS without installing MS Office

Many times, SSIS deployment is the quirt difficult in some cases. Suppose our SSIS is using excel files but MS office does not installed on the target environment.in that case, missing of data access component on the machine stops working of SSIS package. Then following any one installation on the machine can solve this issue.
2007 Office System Driver: Data Connectivity Components 

This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2007 (*.mdb and *.accdb) files and Microsoft Office Excel 2007 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported.ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.
For download this installer go to the URL
http://www.microsoft.com/en-us/download/details.aspx?id=23734

Microsoft Access Database Engine 2010 Redistributable 
This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported. ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.

For download this installer go to the URL
http://www.microsoft.com/en-us/download/details.aspx?id=13255