Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Exporting SSRS reprot to Excel Expand / Collapse
Author
Message
Posted Monday, December 09, 2013 9:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 10, 2014 6:04 AM
Points: 52, Visits: 123
Hi Team,

I'm exporting a SSRS report to Excel. For some input parameters the number of rows generated by the SSRS is around 100,000(more than 65000 rows) and it throws an error while exporting.

One option what I tried is, to export as CSV and open the CSV and save it as Excel work book(I have office 2010 in my machine). It works, but the problem is, all the column header are displayed as text box names. For ex,(I'm looking for name Emp Name, but it is giving me Emp_Name).

Is there any solution or work around for this? Please suggest.

Regards

Jim
Post #1521389
Posted Monday, December 09, 2013 11:22 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 482, Visits: 842
you need to set the "DataElementName" property for that particular column(s) to your Desire Name

By Default its set to Blank and set the "DataElementStyle" property to "Element".

hope its helps.
Post #1521402
Posted Wednesday, December 11, 2013 4:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 10, 2014 6:04 AM
Points: 52, Visits: 123
Hi,

I tried the "DataElementName", but it is not allowing me to enter space. For ex, 'Employee Name' is not allowed. I changed the "DataElementStyle" to Element. But still it didnt work

Post #1521831
Posted Wednesday, December 11, 2013 5:21 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 8:31 AM
Points: 409, Visits: 386
As SSRS Excel export will only export 65000 rows, take the results in different sheets inside single file, change below property

In Row Group (On group with bigger data less than 65000 rows) properties -> PageBreak Click option "Between each instance of a group. , this will create seprate sheets in excel file with as per group specified.

Check the IntialPageName Property in Report, this will change the name of the sheets with Running Sr.No.

Hope this will help

Thanks & Regards,
Bhaskar Shetty
Post #1521845
Posted Wednesday, December 11, 2013 11:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 28, Visits: 348
Just a note SSRS2012 writes xlsx files about 1 Million rows. I know does not help with your older version but one more reason to upgrade
Post #1521996
Posted Thursday, December 12, 2013 3:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 10, 2014 6:04 AM
Points: 52, Visits: 123
Hi Bhaskar,

In my case, multiple sheet is not preffered. Is there any way to rename the CSV column header. It is giving text box names now. For ex, Emp_Name1 but I need Emp Name.

Please suggest..
Post #1522220
Posted Thursday, December 12, 2013 4:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 482, Visits: 842
Jim1234 (12/12/2013)
Hi Bhaskar,

In my case, multiple sheet is not preffered. Is there any way to rename the CSV column header. It is giving text box names now. For ex, Emp_Name1 but I need Emp Name.

Please suggest..


The column name comes from the DataElementName property, or if that is blank, the Name property. Unfortunatly, neither allow commas or quoting. you need to set it like "EmpName1".
Post #1522226
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse