How to Control Sheet Name of Exported Excel Report?

Is there a way to control the sheet name when a report is exported to Excel? I have four matrices where a page break is made between each of them. Once exported to Excel, the resulting file has "Sheet1", "Sheet2", "Sheet3" and "Sheet4" as the names of its worksheets. Would it be possible to change it to a more user-friendly name?I am looking for the sames answer. Has anyone found this information out yet?|||

This is not possible with RS 2000 or 2005 by itself. It's on our wishlist for a future release.

You can do this today with a third party tool SoftArtisans OfficeWriter. With OfficeWriter you can name sheets at design time:

http://officewriter.softartisans.com/officewriter-250.aspx

|||Chris -- Can the OfficeWriter name sheets in a current report when it is exported or would I have to re-create the report?|||

Here is something that is "almost as good"...... you can make Excel create a Document Map for each of your reports - whether they are tables or matrices. This example uses a table and 2 matrices, with both matrices on one sheet.

Put your table in a rectangle; in the rectangle properties enter text for the Label property. Put the matrices in a second rectangle and also enter the Label property. When you generate the report, a document map is created - when you render this to Excel, a Document Map is created on one sheet, and the reports are on 2 separate sheets. The sheets still have names 'Document Map', 'Sheet1', Sheet2', but at least the user can see from the document map what each sheet represents.

How to Control Sheet Name of Exported Excel Report?

Is there a way to control the sheet name when a report is exported to Excel? I have four matrices where a page break is made between each of them. Once exported to Excel, the resulting file has "Sheet1", "Sheet2", "Sheet3" and "Sheet4" as the names of its worksheets. Would it be possible to change it to a more user-friendly name?I am looking for the sames answer. Has anyone found this information out yet?|||

This is not possible with RS 2000 or 2005 by itself. It's on our wishlist for a future release.

You can do this today with a third party tool SoftArtisans OfficeWriter. With OfficeWriter you can name sheets at design time:

http://officewriter.softartisans.com/officewriter-250.aspx

|||Chris -- Can the OfficeWriter name sheets in a current report when it is exported or would I have to re-create the report?|||

Here is something that is "almost as good"...... you can make Excel create a Document Map for each of your reports - whether they are tables or matrices. This example uses a table and 2 matrices, with both matrices on one sheet.

Put your table in a rectangle; in the rectangle properties enter text for the Label property. Put the matrices in a second rectangle and also enter the Label property. When you generate the report, a document map is created - when you render this to Excel, a Document Map is created on one sheet, and the reports are on 2 separate sheets. The sheets still have names 'Document Map', 'Sheet1', Sheet2', but at least the user can see from the document map what each sheet represents.

How to Control Sheet Name of Exported Excel Report?

Is there a way to control the sheet name when a report is exported to Excel? I have four matrices where a page break is made between each of them. Once exported to Excel, the resulting file has "Sheet1", "Sheet2", "Sheet3" and "Sheet4" as the names of its worksheets. Would it be possible to change it to a more user-friendly name?I am looking for the sames answer. Has anyone found this information out yet?|||

This is not possible with RS 2000 or 2005 by itself. It's on our wishlist for a future release.

You can do this today with a third party tool SoftArtisans OfficeWriter. With OfficeWriter you can name sheets at design time:

http://officewriter.softartisans.com/officewriter-250.aspx

|||Chris -- Can the OfficeWriter name sheets in a current report when it is exported or would I have to re-create the report?|||

Here is something that is "almost as good"...... you can make Excel create a Document Map for each of your reports - whether they are tables or matrices. This example uses a table and 2 matrices, with both matrices on one sheet.

Put your table in a rectangle; in the rectangle properties enter text for the Label property. Put the matrices in a second rectangle and also enter the Label property. When you generate the report, a document map is created - when you render this to Excel, a Document Map is created on one sheet, and the reports are on 2 separate sheets. The sheets still have names 'Document Map', 'Sheet1', Sheet2', but at least the user can see from the document map what each sheet represents.

How to Control Sheet Name of Exported Excel Report?

Is there a way to control the sheet name when a report is exported to Excel? I have four matrices where a page break is made between each of them. Once exported to Excel, the resulting file has "Sheet1", "Sheet2", "Sheet3" and "Sheet4" as the names of its worksheets. Would it be possible to change it to a more user-friendly name?I am looking for the sames answer. Has anyone found this information out yet?|||

This is not possible with RS 2000 or 2005 by itself. It's on our wishlist for a future release.

You can do this today with a third party tool SoftArtisans OfficeWriter. With OfficeWriter you can name sheets at design time:

http://officewriter.softartisans.com/officewriter-250.aspx

|||Chris -- Can the OfficeWriter name sheets in a current report when it is exported or would I have to re-create the report?|||

Here is something that is "almost as good"...... you can make Excel create a Document Map for each of your reports - whether they are tables or matrices. This example uses a table and 2 matrices, with both matrices on one sheet.

Put your table in a rectangle; in the rectangle properties enter text for the Label property. Put the matrices in a second rectangle and also enter the Label property. When you generate the report, a document map is created - when you render this to Excel, a Document Map is created on one sheet, and the reports are on 2 separate sheets. The sheets still have names 'Document Map', 'Sheet1', Sheet2', but at least the user can see from the document map what each sheet represents.

How to continue package execution during error?

We have a package that loads the data from several excel files into database in a forloop.

Everything works files until the package hits the bad file.

My goal is to continue the loop to process the rest of the files by skipping the bad file and error. In each task OnError I am creating custom error message to send an error/ sucess summary email out at end of the process.

How can force the for loop to continue when there is an error?

Is there any way to reset the errors?

Thanks

R

The behavior is mostly controlled by three properties: MaxErrorCount, FailPackageOnFailure and FailParentOnFailure. You can increas error count and set "fail" properties to false to make package continue inspite of an error.

How to connect to Analysis Services with Excel 2007 Data Mining Add In?

I downloaded a trial version of office 2007 and the data mining addin. I do not have an automatic connection to Analysis Services, so most functionality does not work. Is it possible to configure a connection with a trial version? If so, does anyone know what the server name is and how to configure it?

the server name is typically the name of the computer that is running Analysis Services 2005 (the machine where SQL Server 2005 is installed). If this is installed on the same computer as Office 2007, then you can use "localhost" instead of the computer name.

In case you need a trial version of Analysis Services 2005, you can download it from here http://www.microsoft.com/sql/downloads/trial-software.mspx (download the SQL Setup, run it and make sure to include Analysis Services in the setup selection)

|||The localhost server name was rejected.
|||

The localhost server name was rejected after you installed the trial version of Analysis Services? You may also connect to any server in your organization.

Instructions on how to get the evaluation edition and upgrade are here: http://blogs.msdn.com/jamiemac/archive/2006/12/29/try-out-the-data-mining-addins-for-office-2007-free-for-60-days.aspx

|||Yes, it was after I installed the trial of analysis services sql 2005. I followed the directions of the link you provided without any success.
|||

Can you check to see if the server's started?

Go to the start menu, right-click on "My Computer" and select "Manage"

Open up "Services and Application" and select "Services" and look for Analysis Services (likely called "SQL Server Analysis Services") to check if it's started.

|||Is it even possible with a standalone home laptop? That's what I'm trying to do this on.
|||

Yes,

However, you need either to install SQL Server 2005 (Analysis Services) on the laptop ( or on anothe machine in your home network)

|||

I have all of the software installed on my laptop and it all works OK - by "home laptop" you mean a laptop that is not joined to a domain, correct?

After installing Analysis Services on your local machine, did you try to run the server configuration tool? If that failed, can you try to run SQL Server Management Studio and connect?

Please let us know. If you did install and it seems everything is running (i.e. the server is started), or the server will not start, you may have to call customer support.

|||In Excel, when you try to create a new connection to Analysis Service, you might want to share what is the error message when you click on Test connection (assuming you type localhost for the Server name).|||The error message when trying to test a connection to "localhost" is:

"Connect to Analysis Services:
Test Connection Failed. A connection cannot be made. Ensure that the server is running. No connection could be made because the target machine actively refused it."

|||

There are a few possible reasons:

- is SQL Server Analysis Services 2005 running on the local machine? (run "services.msc" from a command prompt and look for a services named "SQL Server Analysis Services")

- is AS running as the default instance or as a named instance?

In services.msc, a default instance would appear as "SQL Server Analysis Services(MSSQLSERVER)"

A named instance would appear as "SQL Server Analysis Services(INSTANCENAME)"

If running as a named instance, then please set the connection for the Excel add-ins to "localhost\INSTANCENAME"

|||It looks like I have SQL Server Express running, but that I may not have SQL Server Analysis Services...how do I get this cheaply?
|||

You can download freely an evaluation version from

http://www.microsoft.com/sql/downloads/trial-software.mspx

sql

How to connect to Analysis Services with Excel 2007 Data Mining Add In?

I downloaded a trial version of office 2007 and the data mining addin. I do not have an automatic connection to Analysis Services, so most functionality does not work. Is it possible to configure a connection with a trial version? If so, does anyone know what the server name is and how to configure it?

the server name is typically the name of the computer that is running Analysis Services 2005 (the machine where SQL Server 2005 is installed). If this is installed on the same computer as Office 2007, then you can use "localhost" instead of the computer name.

In case you need a trial version of Analysis Services 2005, you can download it from here http://www.microsoft.com/sql/downloads/trial-software.mspx (download the SQL Setup, run it and make sure to include Analysis Services in the setup selection)

|||The localhost server name was rejected.|||

The localhost server name was rejected after you installed the trial version of Analysis Services? You may also connect to any server in your organization.

Instructions on how to get the evaluation edition and upgrade are here: http://blogs.msdn.com/jamiemac/archive/2006/12/29/try-out-the-data-mining-addins-for-office-2007-free-for-60-days.aspx

|||Yes, it was after I installed the trial of analysis services sql 2005. I followed the directions of the link you provided without any success.|||

Can you check to see if the server's started?

Go to the start menu, right-click on "My Computer" and select "Manage"

Open up "Services and Application" and select "Services" and look for Analysis Services (likely called "SQL Server Analysis Services") to check if it's started.

|||Is it even possible with a standalone home laptop? That's what I'm trying to do this on.|||

Yes,

However, you need either to install SQL Server 2005 (Analysis Services) on the laptop ( or on anothe machine in your home network)

|||

I have all of the software installed on my laptop and it all works OK - by "home laptop" you mean a laptop that is not joined to a domain, correct?

After installing Analysis Services on your local machine, did you try to run the server configuration tool? If that failed, can you try to run SQL Server Management Studio and connect?

Please let us know. If you did install and it seems everything is running (i.e. the server is started), or the server will not start, you may have to call customer support.

|||In Excel, when you try to create a new connection to Analysis Service, you might want to share what is the error message when you click on Test connection (assuming you type localhost for the Server name).|||The error message when trying to test a connection to "localhost" is:

"Connect to Analysis Services:
Test Connection Failed. A connection cannot be made. Ensure that the server is running. No connection could be made because the target machine actively refused it."|||

There are a few possible reasons:

- is SQL Server Analysis Services 2005 running on the local machine? (run "services.msc" from a command prompt and look for a services named "SQL Server Analysis Services")

- is AS running as the default instance or as a named instance?

In services.msc, a default instance would appear as "SQL Server Analysis Services(MSSQLSERVER)"

A named instance would appear as "SQL Server Analysis Services(INSTANCENAME)"

If running as a named instance, then please set the connection for the Excel add-ins to "localhost\INSTANCENAME"

|||It looks like I have SQL Server Express running, but that I may not have SQL Server Analysis Services...how do I get this cheaply?|||

You can download freely an evaluation version from

http://www.microsoft.com/sql/downloads/trial-software.mspx

How to connect to Analysis Services with Excel 2007 Data Mining Add In?

I downloaded a trial version of office 2007 and the data mining addin. I do not have an automatic connection to Analysis Services, so most functionality does not work. Is it possible to configure a connection with a trial version? If so, does anyone know what the server name is and how to configure it?

the server name is typically the name of the computer that is running Analysis Services 2005 (the machine where SQL Server 2005 is installed). If this is installed on the same computer as Office 2007, then you can use "localhost" instead of the computer name.

In case you need a trial version of Analysis Services 2005, you can download it from here http://www.microsoft.com/sql/downloads/trial-software.mspx (download the SQL Setup, run it and make sure to include Analysis Services in the setup selection)

|||The localhost server name was rejected.|||

The localhost server name was rejected after you installed the trial version of Analysis Services? You may also connect to any server in your organization.

Instructions on how to get the evaluation edition and upgrade are here: http://blogs.msdn.com/jamiemac/archive/2006/12/29/try-out-the-data-mining-addins-for-office-2007-free-for-60-days.aspx

|||Yes, it was after I installed the trial of analysis services sql 2005. I followed the directions of the link you provided without any success.|||

Can you check to see if the server's started?

Go to the start menu, right-click on "My Computer" and select "Manage"

Open up "Services and Application" and select "Services" and look for Analysis Services (likely called "SQL Server Analysis Services") to check if it's started.

|||Is it even possible with a standalone home laptop? That's what I'm trying to do this on.|||

Yes,

However, you need either to install SQL Server 2005 (Analysis Services) on the laptop ( or on anothe machine in your home network)

|||

I have all of the software installed on my laptop and it all works OK - by "home laptop" you mean a laptop that is not joined to a domain, correct?

After installing Analysis Services on your local machine, did you try to run the server configuration tool? If that failed, can you try to run SQL Server Management Studio and connect?

Please let us know. If you did install and it seems everything is running (i.e. the server is started), or the server will not start, you may have to call customer support.

|||In Excel, when you try to create a new connection to Analysis Service, you might want to share what is the error message when you click on Test connection (assuming you type localhost for the Server name).|||The error message when trying to test a connection to "localhost" is:

"Connect to Analysis Services:
Test Connection Failed. A connection cannot be made. Ensure that the server is running. No connection could be made because the target machine actively refused it."|||

There are a few possible reasons:

- is SQL Server Analysis Services 2005 running on the local machine? (run "services.msc" from a command prompt and look for a services named "SQL Server Analysis Services")

- is AS running as the default instance or as a named instance?

In services.msc, a default instance would appear as "SQL Server Analysis Services(MSSQLSERVER)"

A named instance would appear as "SQL Server Analysis Services(INSTANCENAME)"

If running as a named instance, then please set the connection for the Excel add-ins to "localhost\INSTANCENAME"

|||It looks like I have SQL Server Express running, but that I may not have SQL Server Analysis Services...how do I get this cheaply?|||

You can download freely an evaluation version from

http://www.microsoft.com/sql/downloads/trial-software.mspx

How to connect mdf database?

Hello guys!

I'm really a newbie in sql stuff, but I have an mdf
file and I need to get the data from there to excel
(xls or csv). I have installed mssql server desktop
edition and I need to connect my mdf database to
server. How can I do this. I know there is some thing
called sp_attach... but how can I use it? I REALLY NEED
A DETAILED EXPLAINATION. Information that "use
sp_attach..." does'nt help me.

Pleeeeeeeaase help me,
Jessicawell you can use sqlserver enterprise manager right click on databases all tasks, attach database or use a stored procedure:

EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

to detach dabase :
right click on database name all tasks, detach database

or using stored procedure

EXEC sp_detach_db @.dbname = 'pubs'

example are from books online

bye

Originally posted by Jessica7
Hello guys!

I'm really a newbie in sql stuff, but I have an mdf
file and I need to get the data from there to excel
(xls or csv). I have installed mssql server desktop
edition and I need to connect my mdf database to
server. How can I do this. I know there is some thing
called sp_attach... but how can I use it? I REALLY NEED
A DETAILED EXPLAINATION. Information that "use
sp_attach..." does'nt help me.

Pleeeeeeeaase help me,
Jessica|||Thank you Hotwebber!

I have opened SQL server enterprice and on the left panel
I right-clicked on database and All tasks, but there are only
backup database, restore database, import data and export
data. But no database attaching. I'M STILL STUCK.

Please help,
Jessica|||did you try stored procedure ??

Originally posted by Jessica7
Thank you Hotwebber!

I have opened SQL server enterprice and on the left panel
I right-clicked on database and All tasks, but there are only
backup database, restore database, import data and export
data. But no database attaching. I'M STILL STUCK.

Please help,
Jessica|||I don't know how to use this stored procedure.
Can you give me an exaple when I have a
file called database.mdf on my d:\ root directory

Jessica|||I don't know why but it says that I don't have any privat messages,
so I did'nt see your previous message also|||if you know original db name substitute MyDatabase in the sp command:

open sql query analizer this can be done from enterprise manager Tools or start - programs microsoft sql server - query analizer
paste the command

EXEC sp_attach_single_file_db @.dbname = 'MyDatabase',
@.physname = 'd:\database.mdf '

press F5 or click the green triangle

Hotwebber|||Sorry for being so dumb, but how can I know "original db name"? I only have one file called database.mdf

Jessica|||try using MyDatabase as name

How to configure SSAS 2005 for use with Excel

Using Excel I can connect to SSAS on localhost, but when I try to do the same from another computer, I can't connect to the server.

What can I configure to allow this connection ?

I already disactivated the firewall and I added a role to the cube that I want to use. But the connection error occures when listing the cube present on the server. I don't know what I can change now....

Thanks for your help.

hello,

Try downloading the Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider from the below link and try connecting it . It worked for me .

http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en

Check the Content in Microsoft SQL Server 2000 PivotTable Services for details

regards

varadaraj

How to configure SSAS 2005 for use with Excel

Using Excel I can connect to SSAS on localhost, but when I try to do the same from another computer, I can't connect to the server.

What can I configure to allow this connection ?

I already disactivated the firewall and I added a role to the cube that I want to use. But the connection error occures when listing the cube present on the server. I don't know what I can change now....

Thanks for your help.

hello,

Try downloading the Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider from the below link and try connecting it . It worked for me .

http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en

Check the Content in Microsoft SQL Server 2000 PivotTable Services for details

regards

varadaraj

How to CONCATENATE >50 fields in Excel table into SQL Insert State

SQL Server 2005, Excel 2003
I would like to populate some tables in a SQL database from some tables in
Excel.
Previously I have used the Concatenate function in Excel to construct SQL
Insert statement such as example below.
=CONCATENATE("INSERT INTO Product (Field1,Field2,Field3,Field4,Field5)
VALUES ('",B2,"','",C2,"',",D2,",",E2,",",F2,")")
However, there is a limit in using the Concatenate function in Excel and
this dows not work on a larger table (50 fileds). Please could you advice
how I could create the SQL insert statements using this Excel table to
populate the associate SQL table? Is there any better/alternative ways?
Many thanks in advance,
Hi Will,
The bcp utility will import the file into a table for you. You can learn
about it here:
http://technet.microsoft.com/en-us/library/ms162802.aspx
-Susan
"will~" wrote:

> SQL Server 2005, Excel 2003
> I would like to populate some tables in a SQL database from some tables in
> Excel.
> Previously I have used the Concatenate function in Excel to construct SQL
> Insert statement such as example below.
> =CONCATENATE("INSERT INTO Product (Field1,Field2,Field3,Field4,Field5)
> VALUES ('",B2,"','",C2,"',",D2,",",E2,",",F2,")")
> However, there is a limit in using the Concatenate function in Excel and
> this dows not work on a larger table (50 fileds). Please could you advice
> how I could create the SQL insert statements using this Excel table to
> populate the associate SQL table? Is there any better/alternative ways?
> Many thanks in advance,
>
|||On Thu, 28 Feb 2008 07:43:00 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:

>Hi Will,
>The bcp utility will import the file into a table for you. You can learn
>about it here:
>http://technet.microsoft.com/en-us/library/ms162802.aspx
>-Susan
BCP.EXE is quite particular about the format of an input file. While
you might be able to EXPORT from Excel in a format that can somehow be
made to work with BCP.EXE, it is not a simple process.
I would start by using the data import wizard. This can create a DTS
package (SQL Server 2000) or an SSIS package (SQL Server 2005).
Roy Harvey
Beacon Falls, CT

How to CONCATENATE >50 fields in Excel table into SQL Insert State

SQL Server 2005, Excel 2003
I would like to populate some tables in a SQL database from some tables in
Excel.
Previously I have used the Concatenate function in Excel to construct SQL
Insert statement such as example below.
=CONCATENATE("INSERT INTO Product (Field1,Field2,Field3,Field4,Field5)
VALUES ('",B2,"','",C2,"',",D2,",",E2,",",F2,")")
However, there is a limit in using the Concatenate function in Excel and
this dows not work on a larger table (50 fileds). Please could you advice
how I could create the SQL insert statements using this Excel table to
populate the associate SQL table? Is there any better/alternative ways?
Many thanks in advance,Hi Will,
The bcp utility will import the file into a table for you. You can learn
about it here:
http://technet.microsoft.com/en-us/library/ms162802.aspx
-Susan
"will~" wrote:
> SQL Server 2005, Excel 2003
> I would like to populate some tables in a SQL database from some tables in
> Excel.
> Previously I have used the Concatenate function in Excel to construct SQL
> Insert statement such as example below.
> =CONCATENATE("INSERT INTO Product (Field1,Field2,Field3,Field4,Field5)
> VALUES ('",B2,"','",C2,"',",D2,",",E2,",",F2,")")
> However, there is a limit in using the Concatenate function in Excel and
> this dows not work on a larger table (50 fileds). Please could you advice
> how I could create the SQL insert statements using this Excel table to
> populate the associate SQL table? Is there any better/alternative ways?
> Many thanks in advance,
>|||On Thu, 28 Feb 2008 07:43:00 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>Hi Will,
>The bcp utility will import the file into a table for you. You can learn
>about it here:
>http://technet.microsoft.com/en-us/library/ms162802.aspx
>-Susan
BCP.EXE is quite particular about the format of an input file. While
you might be able to EXPORT from Excel in a format that can somehow be
made to work with BCP.EXE, it is not a simple process.
I would start by using the data import wizard. This can create a DTS
package (SQL Server 2000) or an SSIS package (SQL Server 2005).
Roy Harvey
Beacon Falls, CT

How to close the blank page which is opening when clicked on export !

Hi
I tried to export my report to Excel and PDF, it is exporting
correctly.
But it leaves a page opened, which was opened when I clicked on Export
Link.
How to close that page?
Is there any setting available for it?
Or else how to close it using C#.Net code?
Any help is appriciated.
TIA
PuneetOn Sep 25, 2:16 pm, appu <ajmera.pun...@.gmail.com> wrote:
> Hi
> I tried to export my report to Excel and PDF, it is exporting
> correctly.
> But it leaves a page opened, which was opened when I clicked on Export
> Link.
> How to close that page?
> Is there any setting available for it?
> Or else how to close it using C#.Net code?
> Any help is appriciated.
> TIA
> Puneet
Since this is a browser window, it would be difficult to close the
container window via C#.NET (ASP.NET). I'm not familiar w/your
circumstances; however, you could try calling the report via URL where
you would only pop open a save dialog. This URL might be helpful from
a custom application.
http://SomeServerName/reportserver?/SomeReportDirectory/ReportName&rs:Command=Render&Param1=SomeValue&rs:Format=PDF
-or-
http://SomeServerName/reportserver?/SomeReportDirectory/ReportName&rs:Command=Render&Param1=SomeValue&rs:Format=Excel
The format is:
http://<ServerName>/reportserver?/<Directory>/<ReportName>&rs:Command=Render&<ParameterName>=<ParameterValue>&rs:Format=<PDF/CSV/XML/Excel/MHTML>
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant