FTP Task in a SQL Server Agent Scheduled Job - Corrupts Excel file?

We are not certain if this has happened due to the SSIS FTP Task, but incidently the Excel file that is being copied from the FTP site using an SSIS FTP Task got corrupted a couple hours after the package was scheduled as a SQL Server Agent Job on SQL Server 2005.

I had a SQL Server 2000 DTS package doing the same thing, but it was never an issue then. I was using the FTP Task there along with an Excel Data source in that and has been working for a couple years atleast with never any corruption related issues.

In the SSIS SQL Server 2005 package I am using an FTP Task with an Excel Connection Manager and Excel source and the Excel file got corrupted within a couple hours of the package being scheduled as a SQL Server Agent job.

Has anyone experienced this issue? Any inputs will be appreciated.

Just as an fyi, the excel file has a lot of vlookups.

Thanks,

MShah

The FTP task does not delete existing file when downloading new one with the same name (we have recently found it and planning to fix for SP2). So if the downloaded file is smaller than existing one, it overwrites the beginning of existing file with new content, but the remaining file content might be from previous file.

The workaround is to use File System Task to delete existing file before downloading new one.

Fromatting excel column

Hi,

I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.

Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.

Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?

Thanks

Interesting report on the behavior your seeing. My experience was quite the opposite when working with an Excel import package.

I found the Excel connector to be VERY tightly bound to the defined data types within the actual Excel file. I also found that it seemed to determine the type by the top few rows and not necessarily by the whole data set.|||chandrala's issues are with an Excel destination, not a source though.|||Ya my issue is with destination if I create a new excel file or if I already have an excel file it doesnt matter. It is kind of dumb to expect that the file already has to have some data before the export to determine the type. Most exports are into an unpopulated file. Please let me know how to tackle this.|||I'm having the same issue. Anyone come up with a resolution?|||On the destination connection, I had first row has column names. Set this to no, and everything worked.|||

Grant Mackay wrote:

On the destination connection, I had first row has column names. Set this to no, and everything worked.

This make sense as SSIS try to guess the data type based on the first values of the column. If you have the column names in you first row; then SSIS will treat it as String; which be correct. May that be your problem?

I also found few trheads taking about data conversition when excel is the destination:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=64475&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193526&SiteID=1

Fromatting excel column

Hi,

I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.

Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.

Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?

Thanks

Interesting report on the behavior your seeing. My experience was quite the opposite when working with an Excel import package.

I found the Excel connector to be VERY tightly bound to the defined data types within the actual Excel file. I also found that it seemed to determine the type by the top few rows and not necessarily by the whole data set.|||chandrala's issues are with an Excel destination, not a source though.|||Ya my issue is with destination if I create a new excel file or if I already have an excel file it doesnt matter. It is kind of dumb to expect that the file already has to have some data before the export to determine the type. Most exports are into an unpopulated file. Please let me know how to tackle this.|||I'm having the same issue. Anyone come up with a resolution?|||On the destination connection, I had first row has column names. Set this to no, and everything worked.|||

Grant Mackay wrote:

On the destination connection, I had first row has column names. Set this to no, and everything worked.

This make sense as SSIS try to guess the data type based on the first values of the column. If you have the column names in you first row; then SSIS will treat it as String; which be correct. May that be your problem?

I also found few trheads taking about data conversition when excel is the destination:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=64475&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193526&SiteID=1

sql

from excel to sqlServer

I am having An excel file containing name and phone numberHow can I insert this data to a database table (sqlserver2005)?Hope some one can help mesujith

Do you have SQL Server Management Studio installed? If you do, you can import your Excel spreedsheet to SQL Server as a table.

1.Right click on your database name, follow the Tasks>>Import Data...;

2. Choose excel as you data source; browser to where your excel file is;

3. follow along the instruction on following windows and click NEXTs until you finish.

If you have problem to finsh, please post back here.

|||

Hi,

Yes you can import export wizard as mentioned in the previous post or you can take a look at this thread:http://forums.asp.net/thread/1442470.aspx

AndHow to import data from Excel to SQL Server demonstrates how to import data from Microsoft Excel worksheets into Microsoft SQL Server databases by using a variety of methods

Hope it helps.

From Excel to Analysis Services

Have any of you heard of an application that makes it possible for normal business people with no IT knowledge to directly from Excel “publish” a table to SQL Server and Analysis Services?

I’m searching for an Excel add-in that sends the data in a normal Excel table with a few dimensions and some measures, as a data set (preferably via a web service over https) to a server application. The server creates a table in a specific SQL Server database, and automatically creates a cube in a specific analysis service database. The cube on the server is automatically processed and the data is available to all users in the company using their BI GUI.

Any ideas where to find such tool?

/Erik

I don't know any turnkey solution which can solve your entire problem. But there are enough off-the-shelf technologies, such as SQL Server Integration Service, out there from which you can build your own solution.

Alternatively, take a look at the Visual Studio Tools for Office (the .Net interop assemblies for Office essenially) as you can pretty easily write some code behind your XL spreadsheet (or as an XL add-in) yourself that can pull data off the sheet into an ADO.Net recordset which you can then push to SQL Server (or XML or many other formats for that matter).

|||You can also use pivot tables in excel and publish cubes on the web using OWC.|||

That is exactly what I would prefer. I use OWC for publishing Aanalysis Services Cube data thru Excel onto Http:// - a basic publication site. It works fine for IT and Biz community with no maintenace. - raj

From Excel to a SQL Server database table

Is there a reference/tutorial to help a newbie to take data in an Excel Spreadsheet, and copy it into a table in a SQL server database?

-Larry

How to Import Data from Excel to SQL Server

From excel file into MS SQL server

I need to find a way to upload an Excel file into an MS SQL database
using a web control front end. I have my ASP.Net control (using C#)
uploading a file to a directory, but the server people now tell me that
I cannot have a writeable area for the web and have a DTS see it as this
is too much of a security risk. So, I need a way to read the file
directly into the database. I've no idea how to do this. Does anyone
have ideas? I know loading MS Office into the web server is out of the
question. The webserver and database server are not the same physical
machine.
Thanks.
Yes you can upload any file directly into SQL Server.
Here's an example:
http://SteveOrr.net/Articles/EasyUploads.aspx
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"no one" <noone@.yahoo.com> wrote in message
news:41FEF4E4.7EED35A4@.yahoo.com...
>I need to find a way to upload an Excel file into an MS SQL database
> using a web control front end. I have my ASP.Net control (using C#)
> uploading a file to a directory, but the server people now tell me that
> I cannot have a writeable area for the web and have a DTS see it as this
> is too much of a security risk. So, I need a way to read the file
> directly into the database. I've no idea how to do this. Does anyone
> have ideas? I know loading MS Office into the web server is out of the
> question. The webserver and database server are not the same physical
> machine.
> Thanks.
>
|||Thanks for the link, but this is not what I want to do. I want to put the
data from the file into a table, not the file itself.
"Steve C. Orr [MVP, MCSD]" wrote:
[vbcol=seagreen]
> Yes you can upload any file directly into SQL Server.
> Here's an example:
> http://SteveOrr.net/Articles/EasyUploads.aspx
> --
> I hope this helps,
> Steve C. Orr, MCSD, MVP
> http://SteveOrr.net
> "no one" <noone@.yahoo.com> wrote in message
> news:41FEF4E4.7EED35A4@.yahoo.com...
|||"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:
>
|||"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:
>
<snip>
Have a look at DTS - it can load directly from Excel (or most other things)
to MSSQL, and you can change the source and destination connections at
runtime. This link discusses executing a package from ASP:
http://www.sqldts.com/default.aspx?207
Otherwise, you can parse the file and generate your own INSERT statements
(slow), or convert it to a flat text file and then use bcp.exe or BULK
INSERT to load the data.
Simon
|||I'm not sure what all security restrictions they want you to
follow, but one option is to use Openrowset and read the
file into a table using T-SQL, e.g.
insert into YourTable
SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0; HDR=NO;Database=C:\YourFilet.xls',
'SELECT * FROM [Sheet1$]')
-Sue
On Tue, 01 Feb 2005 12:25:52 GMT, no one <noone@.yahoo.com>
wrote:
[vbcol=seagreen]
>Thanks for the link, but this is not what I want to do. I want to put the
>data from the file into a table, not the file itself.
>"Steve C. Orr [MVP, MCSD]" wrote:
|||Oh, I now see your dilemma. That's fairly complex functionality.
My only idea is this 3rd party product that can open an excel file from a
memory stream and will allow you to extract data from it:
http://www.SteveOrr.net/Reviews/AsposeWord.aspx
http://www.aspose.com/Products/Aspose.Excel/
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:
>

From excel file into MS SQL server

I need to find a way to upload an Excel file into an MS SQL database
using a web control front end. I have my ASP.Net control (using C#)
uploading a file to a directory, but the server people now tell me that
I cannot have a writeable area for the web and have a DTS see it as this
is too much of a security risk. So, I need a way to read the file
directly into the database. I've no idea how to do this. Does anyone
have ideas? I know loading MS Office into the web server is out of the
question. The webserver and database server are not the same physical
machine.

Thanks.Yes you can upload any file directly into SQL Server.
Here's an example:
http://SteveOrr.net/Articles/EasyUploads.aspx

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"no one" <noone@.yahoo.com> wrote in message
news:41FEF4E4.7EED35A4@.yahoo.com...
>I need to find a way to upload an Excel file into an MS SQL database
> using a web control front end. I have my ASP.Net control (using C#)
> uploading a file to a directory, but the server people now tell me that
> I cannot have a writeable area for the web and have a DTS see it as this
> is too much of a security risk. So, I need a way to read the file
> directly into the database. I've no idea how to do this. Does anyone
> have ideas? I know loading MS Office into the web server is out of the
> question. The webserver and database server are not the same physical
> machine.
> Thanks.|||Thanks for the link, but this is not what I want to do. I want to put the
data from the file into a table, not the file itself.

"Steve C. Orr [MVP, MCSD]" wrote:

> Yes you can upload any file directly into SQL Server.
> Here's an example:
> http://SteveOrr.net/Articles/EasyUploads.aspx
> --
> I hope this helps,
> Steve C. Orr, MCSD, MVP
> http://SteveOrr.net
> "no one" <noone@.yahoo.com> wrote in message
> news:41FEF4E4.7EED35A4@.yahoo.com...
> >I need to find a way to upload an Excel file into an MS SQL database
> > using a web control front end. I have my ASP.Net control (using C#)
> > uploading a file to a directory, but the server people now tell me that
> > I cannot have a writeable area for the web and have a DTS see it as this
> > is too much of a security risk. So, I need a way to read the file
> > directly into the database. I've no idea how to do this. Does anyone
> > have ideas? I know loading MS Office into the web server is out of the
> > question. The webserver and database server are not the same physical
> > machine.
> > Thanks.|||"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:

<snip
Have a look at DTS - it can load directly from Excel (or most other things)
to MSSQL, and you can change the source and destination connections at
runtime. This link discusses executing a package from ASP:

http://www.sqldts.com/default.aspx?207

Otherwise, you can parse the file and generate your own INSERT statements
(slow), or convert it to a flat text file and then use bcp.exe or BULK
INSERT to load the data.

Simon|||Oh, I now see your dilemma. That's fairly complex functionality.
My only idea is this 3rd party product that can open an excel file from a
memory stream and will allow you to extract data from it:
http://www.SteveOrr.net/Reviews/AsposeWord.aspx
http://www.aspose.com/Products/Aspose.Excel/

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:
>> Yes you can upload any file directly into SQL Server.
>> Here's an example:
>> http://SteveOrr.net/Articles/EasyUploads.aspx
>>
>> --
>> I hope this helps,
>> Steve C. Orr, MCSD, MVP
>> http://SteveOrr.net
>>
>> "no one" <noone@.yahoo.com> wrote in message
>> news:41FEF4E4.7EED35A4@.yahoo.com...
>> >I need to find a way to upload an Excel file into an MS SQL database
>> > using a web control front end. I have my ASP.Net control (using C#)
>> > uploading a file to a directory, but the server people now tell me that
>> > I cannot have a writeable area for the web and have a DTS see it as
>> > this
>> > is too much of a security risk. So, I need a way to read the file
>> > directly into the database. I've no idea how to do this. Does anyone
>> > have ideas? I know loading MS Office into the web server is out of the
>> > question. The webserver and database server are not the same physical
>> > machine.
>>> > Thanks.
>|||DTS has been mentioned.
3rd parties that also do the job: SQLWays , DBUnit.|||"no one" <noone@.yahoo.com> wrote in message
news:41FEF4E4.7EED35A4@.yahoo.com...
>I need to find a way to upload an Excel file into an MS SQL database
> using a web control front end. I have my ASP.Net control (using C#)
> uploading a file to a directory, but the server people now tell me that
> I cannot have a writeable area for the web and have a DTS see it as this
> is too much of a security risk. So, I need a way to read the file
> directly into the database. I've no idea how to do this. Does anyone
> have ideas? I know loading MS Office into the web server is out of the
> question. The webserver and database server are not the same physical
> machine.
> Thanks.

Did you know cross-posting is one of the things some ISPs pick to identify
spam?

This'd be a whole lot easier if your app was windows rather than web.
Coz you don't have any way to be running c# on our client machine.

Is this really an extranet app?
I'd be concerned about who's loading what out a spreadsheet onto my database
server.
It does sound like a good way to open up a hole for hackers to walk in
through.
Uploading excel spreadsheets is also a good way to get a big heap of bad
data into a system.

Anyhow, it piqued my interest so I did a search on "javascript excel"
Here's an interesting page I found.
http://www.planet-source-code.com/v...d=2180&lngWId=2

Some gotchas but maybe they're not a problem for you.

--
Regards,
Andy O'Neill

Freezing matrix groups

In Excel we can freeze panes and split report data so that when you scroll
over you don't loose focus of the groups on the leftmost pane. Can we do
something like this in Report Manager? I want to be able to freeze my row
groupings so that when the report is exported to Excel, the users won't have
to do it. So basically, my question is how can I "feeze" the row groups of a
matrix in Report Manager so that I don't lose focus of these when I scroll to
the right?On Oct 10, 11:00 am, dataGirl <dataG...@.discussions.microsoft.com>
wrote:
> In Excel we can freeze panes and split report data so that when you scroll
> over you don't loose focus of the groups on the leftmost pane. Can we do
> something like this in Report Manager? I want to be able to freeze my row
> groupings so that when the report is exported to Excel, the users won't have
> to do it. So basically, my question is how can I "feeze" the row groups of a
> matrix in Report Manager so that I don't lose focus of these when I scroll to
> the right?
You will want to right-click the top left-hand corner of the matrix
control and select Properties. Then select the Groups tab and below
'Rows' select the 'Edit...' button and select 'Group header should
remain visible while scrolling.'
Regards,
Enrique Martinez
Sr. Software Consultant

Freezed column headers in RS Webreports

Hi,
a lot of my users were asking me to have the possibilty of a freeze
pane (similar to excel) in the RS web reports because often the reports
are to long to see the column haeders. Especially when you have
drilldowns it would help.
Has somebody tried to implement such feature by DLL to RS 2000. Is this
feature planned for RS 2005 '
thanks
BBIf I remember correctly, that might be in SQL 2005
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
> Hi,
>
> a lot of my users were asking me to have the possibilty of a freeze
> pane (similar to excel) in the RS web reports because often the reports
> are to long to see the column haeders. Especially when you have
> drilldowns it would help.
>
> Has somebody tried to implement such feature by DLL to RS 2000. Is this
> feature planned for RS 2005 '
>
> thanks
>
> BB
>|||I am sorry for not answering your question directly... I also have not seen
any 3rd party prods which currently do that in sql 2000 either
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
> Hi,
>
> a lot of my users were asking me to have the possibilty of a freeze
> pane (similar to excel) in the RS web reports because often the reports
> are to long to see the column haeders. Especially when you have
> drilldowns it would help.
>
> Has somebody tried to implement such feature by DLL to RS 2000. Is this
> feature planned for RS 2005 '
>
> thanks
>
> BB
>|||This is available in RS 2005.
I don't know of anyone who has implemented this for RS 2000 - but in that
case, you would need to implement a full custom rendering extension yourself
for RS 2000 (which is a major effort and lots of work).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
> Hi,
>
> a lot of my users were asking me to have the possibilty of a freeze
> pane (similar to excel) in the RS web reports because often the reports
> are to long to see the column haeders. Especially when you have
> drilldowns it would help.
>
> Has somebody tried to implement such feature by DLL to RS 2000. Is this
> feature planned for RS 2005 '
>
> thanks
>
> BB
>|||Hi Robert,
I haven't had a chance to test this yet so, with fixed headers in 2005 will
the scroll position stay in place for auto-refreshing reports or will it be
a pain an spring back to the top?
Thanks in advance,
James Snape
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O8oAI$goFHA.904@.TK2MSFTNGP10.phx.gbl...
> This is available in RS 2005.
> I don't know of anyone who has implemented this for RS 2000 - but in that
> case, you would need to implement a full custom rendering extension
> yourself for RS 2000 (which is a major effort and lots of work).
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
> news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
>> Hi,
>>
>> a lot of my users were asking me to have the possibilty of a freeze
>> pane (similar to excel) in the RS web reports because often the reports
>> are to long to see the column haeders. Especially when you have
>> drilldowns it would help.
>>
>> Has somebody tried to implement such feature by DLL to RS 2000. Is this
>> feature planned for RS 2005 '
>>
>> thanks
>>
>> BB
>|||Auto-Refresh will remember the current page number, but it will not remember
the scroll position within the page.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"James Snape" <jim_snape.at.hotmail.com@.online.nospam> wrote in message
news:ubgRWP3oFHA.3988@.TK2MSFTNGP10.phx.gbl...
> Hi Robert,
> I haven't had a chance to test this yet so, with fixed headers in 2005
> will the scroll position stay in place for auto-refreshing reports or will
> it be a pain an spring back to the top?
> Thanks in advance,
> James Snape
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:O8oAI$goFHA.904@.TK2MSFTNGP10.phx.gbl...
>> This is available in RS 2005.
>> I don't know of anyone who has implemented this for RS 2000 - but in that
>> case, you would need to implement a full custom rendering extension
>> yourself for RS 2000 (which is a major effort and lots of work).
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
>> news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
>> Hi,
>>
>> a lot of my users were asking me to have the possibilty of a freeze
>> pane (similar to excel) in the RS web reports because often the reports
>> are to long to see the column haeders. Especially when you have
>> drilldowns it would help.
>>
>> Has somebody tried to implement such feature by DLL to RS 2000. Is this
>> feature planned for RS 2005 '
>>
>> thanks
>>
>> BB
>>
>

Freeze table header

When exporting a report to excel, Is there any way to keep the table
header from scrolling up in the excel output when a user scrolls down
through the data?
I noticed that anything that's put above the page header is frozen
when the report is exported to excel.
But when I tried placing the table headers in text boxes and putting
them in the page header area, the headers are out of alignment when
it's exported to excel.On the table you can have the header freeze when you scroll.Right click for
properties and last option.
Amarnath
"bevarg" wrote:
> When exporting a report to excel, Is there any way to keep the table
> header from scrolling up in the excel output when a user scrolls down
> through the data?
> I noticed that anything that's put above the page header is frozen
> when the report is exported to excel.
> But when I tried placing the table headers in text boxes and putting
> them in the page header area, the headers are out of alignment when
> it's exported to excel.
>|||I have that option set to True but it doesn't work when I export it to
Excel. It seems to work only when you are viewing the report within
report manager and you have multiple pages.
On Apr 25, 5:24 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> On the table you can have the header freeze when you scroll.Right click for
> properties and last option.
> Amarnath
> "bevarg" wrote:
> > When exporting a report to excel, Is there any way to keep the table
> > header from scrolling up in the excel output when a user scrolls down
> > through the data?
> > I noticed that anything that's put above the page header is frozen
> > when the report is exported to excel.
> > But when I tried placing the table headers in text boxes and putting
> > them in the page header area, the headers are out of alignment when
> > it's exported to excel.|||On Apr 26, 1:16 pm, bevarg <benovargh...@.gmail.com> wrote:
> I have that option set to True but it doesn't work when I export it to
> Excel. It seems to work only when you are viewing the report within
> report manager and you have multiple pages.
> On Apr 25, 5:24 am, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
> > On the table you can have the header freeze when you scroll.Right click for
> > properties and last option.
> > Amarnath
> > "bevarg" wrote:
> > > When exporting a report to excel, Is there any way to keep the table
> > > header from scrolling up in the excel output when a user scrolls down
> > > through the data?
> > > I noticed that anything that's put above the page header is frozen
> > > when the report is exported to excel.
> > > But when I tried placing the table headers in text boxes and putting
> > > them in the page header area, the headers are out of alignment when
> > > it's exported to excel.
You are correct. Unfortunately, you would have to manually set this in
Excel (via Window -> Freeze Panes).
Regards,
Enri|||What you can do is to just align all the table header on the header of the
page then remove the table header, so that header will be frozen. yes page
header is frozen and not table header.
Amarnath
"bevarg" wrote:
> I have that option set to True but it doesn't work when I export it to
> Excel. It seems to work only when you are viewing the report within
> report manager and you have multiple pages.
> On Apr 25, 5:24 am, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
> > On the table you can have the header freeze when you scroll.Right click for
> > properties and last option.
> >
> > Amarnath
> >
> > "bevarg" wrote:
> > > When exporting a report to excel, Is there any way to keep the table
> > > header from scrolling up in the excel output when a user scrolls down
> > > through the data?
> >
> > > I noticed that anything that's put above the page header is frozen
> > > when the report is exported to excel.
> > > But when I tried placing the table headers in text boxes and putting
> > > them in the page header area, the headers are out of alignment when
> > > it's exported to excel.
>
>

Freeze panes when report is exported to Excel

I am using RS 2005 and emailing reports in Excel to my end users. Is it possible to set the report in VS.Net to freeze the panes so that the user will always see the column A when they scroll to the right once they view the report in Excel?

I have the feeling the answer is no, but I thought I'd check.

Hi, there is a property named FixedHeader. I didn′t try that this deep yet (only for table header) , but it would be worth a try. I guess you will your own rendering extension as the rendering for Excel didn′t brought back the right results with fixed headers for me (worked well in HTML, but not in Excel)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

I'm having the exact same issue. Has anyone been able to accomplish this successfully?

Jens Suessmeyer: I tried the property option but that didn’t work for me. The closest to what I’m trying to accomplish (that I’ve gotten); it’s using a report header which freezes the first row (or whatever u have in the header) yet by design it doesn’t accomplish what I (and rs12345) am trying to do.

The only option that I see out there is purchasing OfficeWriter by SoftArtisans or just try to figure out how they do it and write the code (It seems they use scripts... not sure yet).

Any ideas anybody? (or links :) )

|||Sorry, we don't support programmatic setting of Freeze Panes. The only

thing Excel export uses Freeze Panes for is for the PageHeader when

SimpleHeaders=false (the default).

Excel export ignores the FixedHeaders property.

OfficeWriter would support your use case, because you author the report in Excel and can set the freeze panes when designing the report. You could also use the ExcelApplication class from OfficeWriter to modify the output file.

OfficeWriter doesn't use VBA scripting, if that's what you mean; it reads, modifies, and writes the binary BIFF8 Excel format.

Freeze panes when report is exported to Excel

I am using RS 2005 and emailing reports in Excel to my end users. Is it possible to set the report in VS.Net to freeze the panes so that the user will always see the column A when they scroll to the right once they view the report in Excel?

I have the feeling the answer is no, but I thought I'd check.

Hi, there is a property named FixedHeader. I didn′t try that this deep yet (only for table header) , but it would be worth a try. I guess you will your own rendering extension as the rendering for Excel didn′t brought back the right results with fixed headers for me (worked well in HTML, but not in Excel)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

I'm having the exact same issue. Has anyone been able to accomplish this successfully?

Jens Suessmeyer: I tried the property option but that didn’t work for me. The closest to what I’m trying to accomplish (that I’ve gotten); it’s using a report header which freezes the first row (or whatever u have in the header) yet by design it doesn’t accomplish what I (and rs12345) am trying to do.

The only option that I see out there is purchasing OfficeWriter by SoftArtisans or just try to figure out how they do it and write the code (It seems they use scripts... not sure yet).

Any ideas anybody? (or links :) )

|||Sorry, we don't support programmatic setting of Freeze Panes. The only

thing Excel export uses Freeze Panes for is for the PageHeader when

SimpleHeaders=false (the default).

Excel export ignores the FixedHeaders property.

OfficeWriter would support your use case, because you author the report in Excel and can set the freeze panes when designing the report. You could also use the ExcelApplication class from OfficeWriter to modify the output file.

OfficeWriter doesn't use VBA scripting, if that's what you mean; it reads, modifies, and writes the binary BIFF8 Excel format.

Freeze panes like excel

Is there a chance that columns used in sql reporting services can be freezed
as in excel work sheet.
Is it not implemented till date, if they implement when can we expect that
to happen for sql reporting services.
Freezin pane is one part they even want to freeze there own columns
dynamically.
Is there a chance in near future we have this provision, if we have it would
be great.
Any thought of when can we expect it.
Askin a lot :-)
NavinNavin,
There is a setting in RS that you can apply per object (matrix, table), in
which you can specifiy whether you want the header to remain visible when
scrolling. Look for this on the general tab. Name of the setting is "Header
should remain visible when scrolling".
(this is in RS2005)
regards,
Perry
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:9E12F763-4546-4C89-9347-C2E7F479E1B0@.microsoft.com...
> Is there a chance that columns used in sql reporting services can be
> freezed
> as in excel work sheet.
> Is it not implemented till date, if they implement when can we expect that
> to happen for sql reporting services.
> Freezin pane is one part they even want to freeze there own columns
> dynamically.
> Is there a chance in near future we have this provision, if we have it
> would
> be great.
> Any thought of when can we expect it.
> Askin a lot :-)
> Navin|||Freezing columns is big priority for us as well, and I've asked about
about it several times, but the feature is not available.
And I wouldn't expect it in a subsequent release, just based on the
technical issues to implement it, but maybe I'll be surprised.
NAVIN.D wrote:
> Is there a chance that columns used in sql reporting services can be freezed
> as in excel work sheet.
> Is it not implemented till date, if they implement when can we expect that
> to happen for sql reporting services.
> Freezin pane is one part they even want to freeze there own columns
> dynamically.
> Is there a chance in near future we have this provision, if we have it would
> be great.
> Any thought of when can we expect it.
> Askin a lot :-)
> Navin|||Seems certain part of my question solved, look forward for dynamic selection
as well, Thank for the info Perry
"Perry" wrote:
> Navin,
> There is a setting in RS that you can apply per object (matrix, table), in
> which you can specifiy whether you want the header to remain visible when
> scrolling. Look for this on the general tab. Name of the setting is "Header
> should remain visible when scrolling".
> (this is in RS2005)
> regards,
> Perry
>
> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
> news:9E12F763-4546-4C89-9347-C2E7F479E1B0@.microsoft.com...
> > Is there a chance that columns used in sql reporting services can be
> > freezed
> > as in excel work sheet.
> >
> > Is it not implemented till date, if they implement when can we expect that
> > to happen for sql reporting services.
> >
> > Freezin pane is one part they even want to freeze there own columns
> > dynamically.
> >
> > Is there a chance in near future we have this provision, if we have it
> > would
> > be great.
> >
> > Any thought of when can we expect it.
> >
> > Askin a lot :-)
> >
> > Navin
>
>|||unfortunatly it dont work the way i intended i want column to be scrolled as
well not only headers, supose i have 20 columns i want only two columns to be
freezed. and as i traversve through other columns the 2 columns which are
freezed still exists.
"Perry" wrote:
> Navin,
> There is a setting in RS that you can apply per object (matrix, table), in
> which you can specifiy whether you want the header to remain visible when
> scrolling. Look for this on the general tab. Name of the setting is "Header
> should remain visible when scrolling".
> (this is in RS2005)
> regards,
> Perry
>
> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
> news:9E12F763-4546-4C89-9347-C2E7F479E1B0@.microsoft.com...
> > Is there a chance that columns used in sql reporting services can be
> > freezed
> > as in excel work sheet.
> >
> > Is it not implemented till date, if they implement when can we expect that
> > to happen for sql reporting services.
> >
> > Freezin pane is one part they even want to freeze there own columns
> > dynamically.
> >
> > Is there a chance in near future we have this provision, if we have it
> > would
> > be great.
> >
> > Any thought of when can we expect it.
> >
> > Askin a lot :-)
> >
> > Navin
>
>|||Header scrolling dont work for matrix, only table header got that option.
"cowznofsky" wrote:
> Freezing columns is big priority for us as well, and I've asked about
> about it several times, but the feature is not available.
> And I wouldn't expect it in a subsequent release, just based on the
> technical issues to implement it, but maybe I'll be surprised.
> NAVIN.D wrote:
> > Is there a chance that columns used in sql reporting services can be freezed
> > as in excel work sheet.
> >
> > Is it not implemented till date, if they implement when can we expect that
> > to happen for sql reporting services.
> >
> > Freezin pane is one part they even want to freeze there own columns
> > dynamically.
> >
> > Is there a chance in near future we have this provision, if we have it would
> > be great.
> >
> > Any thought of when can we expect it.
> >
> > Askin a lot :-)
> >
> > Navin
>

Freeze Panes in Excel

Is there anything I can do so that when my users export their report from
reporting services to Excel that it freezes the columns'
I also need to set a default zoom %
Is there anyway to do this - thanksI also have that problem. Because when it expands, it can be really time
consuming..
"dragon" wrote:
> Is there anything I can do so that when my users export their report from
> reporting services to Excel that it freezes the columns'
> I also need to set a default zoom %
> Is there anyway to do this - thanks
>

Freeze Panes in Excel

hi,
In matrix row and column grouping there is one property "Group Header should
remain visible while Scrolling",which is equal to Freeze panes property of
Excel.
But when i am exporting the report into Excel i am not getting the same
effect of Static Header.I have to manually set it to get that effort in
exported excel.
So what is the solution of this?
Thanks in advance.
DibakarOn Mar 2, 4:42 am, Dibakar <Diba...@.discussions.microsoft.com> wrote:
> hi,
> In matrix row and column grouping there is one property "Group Header should
> remain visible while Scrolling",which is equal to Freeze panes property of
> Excel.
> But when i am exporting the report into Excel i am not getting the same
> effect of Static Header.I have to manually set it to get that effort in
> exported excel.
> So what is the solution of this?
> Thanks in advance.
> Dibakar
As far as I know, the freeze panes functionality only exists in the
Reporting Services view of the report; though when exported to PDF,
the groups that are frozen are repeated on every page. Sorry that I
could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Freeze pane in RS 2000 SP2 / RS2005 web reports

Hi,
a lot of my users were asking me to have the possibilty of a freeze
pane (similar to excel) in the RS web reports because often the reports
are to long to see the column haeders. Especially when you have
drilldowns it would help.
Has somebody tried to implement such feature by DLL to RS 2000. Is this
feature planned for RS 2005 '
thanks
BBThe only way (that I know of) to accomplish what you're asking for is to have
a custom application that modifies the output file after it has been saved
and then forwards it to the person who initially requested it. We have one
non-RS custom application that does this (internal use only) and are working
on a way to do this with RS rendered reports.
Basically what your application has to do:
1- Client requests report
2- Application renders report in excel and saves to disk.
3- Appliation modifies the excel file properties (page margins, fit to
page, freeze panes, etc.)
4- Appliation passes modified file to the Client
It's a lot of work.
"BB_Reporting" wrote:
> Hi,
> a lot of my users were asking me to have the possibilty of a freeze
> pane (similar to excel) in the RS web reports because often the reports
> are to long to see the column haeders. Especially when you have
> drilldowns it would help.
> Has somebody tried to implement such feature by DLL to RS 2000. Is this
> feature planned for RS 2005 '
> thanks
> BB
>

Freeze Pane functionality during Export to Excel

Hi All,

I have created a Report using Sql Server 2005 Reporting Services.

When I export the report to excel, I want to freeze the pane of the headers in the exported excel.

Any pointers on how to achieve this will greatly help me.

Thanks,

S Suresh

There is no way to tell the Excel renderer to freeze an arbitrary pane. However, the renderer does set a freeze pane for the page headers (not the table headers), so that might be a possibility for you.|||Will you please tell me how to set a freeze pane fo rthe page headers? We do not want to export the page headers to the excel because it merges servera columns for one actual data column. That makes it impossible to change the sorting columns. Thanks,|||I'm not sure I understand what you mean, but the only thing exported to a freeze pane is the page headers. There is no way to put any other part of the report into a freeze pane.

If you are trying to have the headers not show up in Excel, you can have them export as "simple" headers. This would put them into Excel as actual Excel headers, and not as part of the worksheet. To do this, you need to set the SimplePageHeaders device info setting:
http://msdn2.microsoft.com/en-us/library/ms155069.aspx

Hope that helps...|||

Thanks a lot for your reply.

What I want to do is exactly what you said not to put page headers into the excel. My page header has two textboxes, so I do not know if that working. I will try your suggestion tomorrow. Thanks again!

|||

Geoff,

Thank you so much for your link. Looks like it is for sql sever reporting service 2005. We are using 2003 but I tried anyway.

I used the way change the setting through adding render section in xml code but got error. The question I have is where should I put the render section in the xml code? Below is the code, please help me out. Thank you very much for your help!

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<Render>

<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ImageReport,Microsoft.ReportingServices.ExcelRendering">

<Configuration> <DeviceInfo>

<SimplePageHeaders>True</SimplePageHeaders>

</DeviceInfo> </Configuration> </Extension>

</Render>

<PageHeader>

<ReportItems>

<Textbox Name="textbox7">

Error message:

c:\catsreports\catsreports\Contractor.rdl(4): Deserialization failed: The element 'http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition:Report' has invalid child element 'http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition:Render'. An error occurred at , (4, 4).

Freeze Pane functionality during Export to Excel

Hi All,

I have created a Report using Sql Server 2005 Reporting Services.

When I export the report to excel, I want to freeze the pane of the headers in the exported excel.

Any pointers on how to achieve this will greatly help me.

Thanks,

S Suresh

There is no way to tell the Excel renderer to freeze an arbitrary pane. However, the renderer does set a freeze pane for the page headers (not the table headers), so that might be a possibility for you.|||Will you please tell me how to set a freeze pane fo rthe page headers? We do not want to export the page headers to the excel because it merges servera columns for one actual data column. That makes it impossible to change the sorting columns. Thanks,|||I'm not sure I understand what you mean, but the only thing exported to a freeze pane is the page headers. There is no way to put any other part of the report into a freeze pane.

If you are trying to have the headers not show up in Excel, you can have them export as "simple" headers. This would put them into Excel as actual Excel headers, and not as part of the worksheet. To do this, you need to set the SimplePageHeaders device info setting:
http://msdn2.microsoft.com/en-us/library/ms155069.aspx

Hope that helps...|||

Thanks a lot for your reply.

What I want to do is exactly what you said not to put page headers into the excel. My page header has two textboxes, so I do not know if that working. I will try your suggestion tomorrow. Thanks again!

|||

Geoff,

Thank you so much for your link. Looks like it is for sql sever reporting service 2005. We are using 2003 but I tried anyway.

I used the way change the setting through adding render section in xml code but got error. The question I have is where should I put the render section in the xml code? Below is the code, please help me out. Thank you very much for your help!

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<Render>

<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ImageReport,Microsoft.ReportingServices.ExcelRendering">

<Configuration> <DeviceInfo>

<SimplePageHeaders>True</SimplePageHeaders>

</DeviceInfo> </Configuration> </Extension>

</Render>

<PageHeader>

<ReportItems>

<Textbox Name="textbox7">

Error message:

c:\catsreports\catsreports\Contractor.rdl(4): Deserialization failed: The element 'http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition:Report' has invalid child element 'http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition:Render'. An error occurred at , (4, 4).

Freeze Header in both HTML and Excel?

I have a report with a single Table with the "Header should remain visible while scrolling" option checked. The header works correctly in HTML, but when exporting to Excel, the header is not frozen. Should this option work in Excel also?

Freezing headers is currently only supported in the ReportViewer Winforms control, Webforms control, and hence in report designer preview and HTML output, but not in Excel output.

-- Robert

|||Thank you for your quick response!