export to excel 2007

Question

export to excel 2007

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

kalle_janosson SSC Journeyman Points: 94 More actions · Answer 1

Did anyone find a solution on how to export reports in Reporting Services to Excel2007 (.xlsx) instead of the default "Microsoft Office Excel 97-2003 Worksheet"?

The whole .csv -> .xlsx workaround is not a good solution for us.

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 2

I execute the stored procedure behind the report and the result I copy it into Excel2007.

In Theory, theory and practice are the same...In practice, they are not.

kalle_janosson SSC Journeyman Points: 94 More actions · Answer 3

What do you mean by executing the stored procedure behind the report? Did you execute the stored procedure on the SQL Server and copy pasted the result into Excel2007 without any Reporting Services interaction?

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 4

yap, each report has a stored procedure or sql statement behind it. you will find it in .rdl file.

and execute on sql server, of course.

In Theory, theory and practice are the same...In practice, they are not.

kalle_janosson SSC Journeyman Points: 94 More actions · Answer 5

I see. Well, that option is even less of an option for our 1000+ users because of knowledge and security issues. Thank you anyway

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 6

maybe rs2005 has the option to export to excel2007.

In Theory, theory and practice are the same...In practice, they are not.

kalle_janosson SSC Journeyman Points: 94 More actions · Answer 7

I guess you mean rs2008. We're on rs2005 already. Good question. We're actually about to upgrade but that is a process that will take some time, and this problem needs to be addressed right away.

edit: typo

kalle_janosson SSC Journeyman Points: 94 More actions · Answer 8

Found this:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/9d8cd766-ff78-490f-8a5d-bb4a0debdfc1/

"SSRS does not export directly to the xlsx format (the Office 2007 format). It does export to the .xls format and that format is fully supported in Office 2007. Users will be able to open, view and edit the reports without any issue. As far as adding direct support for the Office 2007 formats is concerned, it is something that we are considering for a future release.

-Sean

--------------------------------------------------------------------------------

Program Manager, SQL Server Reporting Services"

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 9

yeah, I know it, but my users want to use excel2007 because of its capacity to store records (much greater than versions 2000-2003 of excel - 32768 rows)

In Theory, theory and practice are the same...In practice, they are not.

Mike Hayes-368824 Old Hand Points: 353 More actions · Answer 10

We addressed this issue by creating a view in SQL Server that displays the requested data, and giving the users the rights to select from that view. Excel can display the results from a view or query in a spreadsheet:

Go to the Data tab.

Select From Other Sources >> From SQL Server

Enter the server name and click Next.

Select the Database from the drop-down list, then select the view and click Next.

Give the data connection a name then click Finish.

Select where the data should go and click OK.

The downside to this method is that you loose formatting. Excel makes judgements about how to display the data based on the data itself - not on the table data type. If that is a big deal, you might be able to preformat a spreadsheet and have Excel import into that sheet.

If you need to give users the ability to define parameter values, you could try MSQuery: Data Tab: From Other Sources >> From MSQuery. I've never used it, but it looks like you have more control over the rows included.

Good luck.

Mike Hayes

lpeters Newbie Points: 6 More actions · Answer 11

This was very helpful. I did have to make a minor change to get it to work for my data.

Too large for my report

=Int((RowNumber(Nothing)-1)/60000)

This worked

=Int((RowNumber(Nothing)-1)/30000)

Thanks!

becklery Hall of Fame Points: 3315 More actions · Answer 12

When you will connect to office 2007 using SSIS choose the

1. Drag the OLE DB Source on the window

2. Double click OleDB source task

3. Select CreateNew Connection

4. In the connection Manager Option Choose the provider for excel 2007 the provider is " Microsoft 12.0 Access Database Engine OLeDB Provider"

5. Click on the Data links Button.

6 It will open Datalink Properties

7 Click on advanced tab

8 Click on the proper access permissions

9 Click on all tab

10 Select data Source and click on the button edit value

11 enter the path of your .xlsx or .xls file say okay

12 then select Extended properties and click on edit the value

13 Enter the following value "Excel 12.0;HDR=YES"

14 Click ok and exit from all the windows

Then Click opn

RAQ Report: Web-based Excel-like Java reporting tool[/url]

sarai2701 Newbie Points: 3 More actions · Answer 13

im new at Reporting services, so .. where do i put "Int((RowNumber(Nothing)-1)/60000)", can you show me with shot screens ?? please

sarai borbon

ingenieria en sistemas de informacion :*

ssurekha2000 SSCommitted Points: 1860 More actions · Answer 14

Exception of type 'System.OutOfMemoryException' was thrown.

when importing from excel to database i get the above error

i am importing from excel sheet and inserting in db

i am aware its the memory issue

i mean i tried with excel sheet having less columns , its gets imported properly

but if column or records increases than i get the issue

is it directly related to system memory or buffering or asp.net worker process as i have 2 GB RAM

file size is 255 KB with 107 records & 77 columns

still i get the error

ome mor ething i am not able to even import the excel with 10 records & 77 columns

but if the columns are reduced i can insert the records

?ex.GetBaseException()

{"Exception of type 'System.OutOfMemoryException' was thrown."}

[System.OutOfMemoryException]: {"Exception of type 'System.OutOfMemoryException' was thrown."}

Data: {System.Collections.ListDictionaryInternal}

HelpLink: null

InnerException: null

Message: "Exception of type 'System.OutOfMemoryException' was thrown."

Source: "mscorlib"

StackTrace: " at System.String.Concat(String str0, String str1)\r at MYPRJ._Default.cmdimport_Click(Object sender, EventArgs e) in D:\\MYPRJ\\MYPRJ\\Default.aspx.cs:line 731"

TargetSite: {System.String Concat(System.String, System.String)}

?ex.InnerException

null

?ex.GetObjectData

'System.Exception.GetObjectData(System.Runtime.Serialization.SerializationInfo, System.Runtime.Serialization.StreamingContext)' is a 'method', which is not valid in the given context

?ex.Source

"mscorlib"

?ex.GetHashCode()

40686163

?ex.Data

{System.Collections.ListDictionaryInternal}

[System.Collections.ListDictionaryInternal]: {System.Collections.ListDictionaryInternal}

IsFixedSize: false

IsReadOnly: false

Keys: {System.Collections.ListDictionaryInternal.NodeKeyValueCollection}

Values: {System.Collections.ListDictionaryInternal.NodeKeyValueCollection}

ex.GetType

'System.Exception.GetType()' is a 'method', which is not valid in the given context

ex.GetType()

{Name = "OutOfMemoryException" FullName = "System.OutOfMemoryException"}

[System.RuntimeType]: {Name = "OutOfMemoryException" FullName = "System.OutOfMemoryException"}

base {System.Reflection.MemberInfo}: {Name = "OutOfMemoryException" FullName = "System.OutOfMemoryException"}

Assembly: {mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089}

AssemblyQualifiedName: "System.OutOfMemoryException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

Attributes: Public | Serializable | BeforeFieldInit

BaseType: {Name = "SystemException" FullName = "System.SystemException"}

ContainsGenericParameters: false

DeclaringMethod: 'ex.GetType().DeclaringMethod' threw an exception of type 'System.InvalidOperationException'

DeclaringType: null

FullName: "System.OutOfMemoryException"

GenericParameterAttributes: 'ex.GetType().GenericParameterAttributes' threw an exception of type 'System.InvalidOperationException'

GenericParameterPosition: 'ex.GetType().GenericParameterPosition' threw an exception of type 'System.InvalidOperationException'

GUID: {ccf306ae-33bd-3003-9cce-daf5befef611}

HasElementType: false

IsAbstract: false

IsAnsiClass: true

IsArray: false

IsAutoClass: false

IsAutoLayout: true

IsByRef: false

IsClass: true

IsCOMObject: false

IsContextful: false

IsEnum: false

IsExplicitLayout: false

IsGenericParameter: false

IsGenericType: false

IsGenericTypeDefinition: false

IsImport: false

IsInterface: false

IsLayoutSequential: false

IsMarshalByRef: false

IsNested: false

IsNestedAssembly: false

IsNestedFamANDAssem: false

IsNestedFamily: false

IsNestedFamORAssem: false

IsNestedPrivate: false

IsNestedPublic: false

IsNotPublic: false

IsPointer: false

IsPrimitive: false

IsPublic: true

IsSealed: false

IsSerializable: true

IsSpecialName: false

IsUnicodeClass: false

IsValueType: false

IsVisible: true

MemberType: TypeInfo

Module: {CommonLanguageRuntimeLibrary}

Namespace: "System"

ReflectedType: null

StructLayoutAttribute: {System.Runtime.InteropServices.StructLayoutAttribute}

TypeHandle: {System.RuntimeTypeHandle}

TypeInitializer: null

UnderlyingSystemType: {Name = "OutOfMemoryException" FullName = "System.OutOfMemoryException"}