Excel formula references

I have a data list that will grow over time. The values are listed vertically in a column; most recent value at the bottom. I am trying to figure out how to setup a formula to figure out the standard deviation on the most recent 30 values automatically. For instance if the column contains 30 values and I add the 31st value, I'd like to have to have the standard deviation displayed in a cell and automatically shift from calculating on values 1-30 to values 2-31. Is this possible?

Jeff.

Looking in Books On Line (http://technet.microsoft.com/en-us/library/ms159220.aspx), I see that StDev is not a supported expression for export to Excel. Specifically:

For all other cases, such as an unsupported expression, a formula is not generated for Excel. Instead, the value is placed in directly in the cell.

This means that you would be required to calculate your standard deviation 'by hand' for the last 30 of the items in your list.

Is it a requirement that the calculation be done in the spreadsheet? How about having the standard deviation calculation done by a SQL query? TSQL has the STDEV to calculate a sample standard deviation and STDEVP to calculate a population standard deviation. Add a new dataset that calculates the correct standard deviation from the last 30 entries (sort in decending order and use the top command) and then display the value where the RowNumber equals the CountRows in your table.

Larry

excel formatting problem... could this be a bug?

Okay, I'll try my best to explain this....
I have a table on my report. Above the header for 5 of my columns, I need
to place a cell that spans all those columns. It looks like this:
--
| h |
--
| h | h | h | h | h |
--
| d | d | d | d | d |
--
h = header row
d = detail row
so, to do this I took the header cell for the left most column and merged it
with the
4 other header cells. Then I put a rectangle into that cell, and drew a
line through the middle to create the two level effect in the single header
cell. Then, where each 'h' is in the above diagram, I put a text box with
some text in it that serves as a label. So, basically all 5 of my detail
columns have their own individual header, as well as a header that applies to
all of them.
When I run the report, and export it to PDF, everything looks great. When I
export to excel, I get a strange effect. SRS seems to want to put the cell
for the textbox in the bottom right most 'cell' in the header in a row below
the other 4 in the second header 'row'.. So, it looks like this in Excel:
--
| h |
--
| h | h | h | h | |
--
| h |
| --|
| d | d | d | d | d |
--
h = header row
d = detail row
Anybody have any idea what's going on here? If I remove the 5th text box in
the header (the one that's causing the problem), excel formats fine.
It would be nice if I could merge cells across rows as well as columns,
because that would make this problem trivial.
Any help is greatly appreciated.
Jeff
JeffJeff,
Have you tried inserting a second table header row above the original table
header row? Then do the merging on the inserted header row. I just did this
on a report at my work. Doing so gave me this effect:
| H1 |
|H1|H2|H3|H4|
|D1|D2|D3|D4|
H1 = Table header inserted by me.
H2 = Orginal header inserted by RS
D..= Detail records
To do this right click on the handle for the exisitng table header row and
click Insert Row Above. The formatting stayed when I downloaded the report
to Excel. Hope this helps.
"Jeff" wrote:
> Okay, I'll try my best to explain this....
> I have a table on my report. Above the header for 5 of my columns, I need
> to place a cell that spans all those columns. It looks like this:
> --
> | h |
> --
> | h | h | h | h | h |
> --
> | d | d | d | d | d |
> --
> h = header row
> d = detail row
> so, to do this I took the header cell for the left most column and merged it
> with the
> 4 other header cells. Then I put a rectangle into that cell, and drew a
> line through the middle to create the two level effect in the single header
> cell. Then, where each 'h' is in the above diagram, I put a text box with
> some text in it that serves as a label. So, basically all 5 of my detail
> columns have their own individual header, as well as a header that applies to
> all of them.
> When I run the report, and export it to PDF, everything looks great. When I
> export to excel, I get a strange effect. SRS seems to want to put the cell
> for the textbox in the bottom right most 'cell' in the header in a row below
> the other 4 in the second header 'row'.. So, it looks like this in Excel:
> --
> | h |
> --
> | h | h | h | h | |
> --
> | h |
> | --|
> | d | d | d | d | d |
> --
>
> h = header row
> d = detail row
> Anybody have any idea what's going on here? If I remove the 5th text box in
> the header (the one that's causing the problem), excel formats fine.
> It would be nice if I could merge cells across rows as well as columns,
> because that would make this problem trivial.
> Any help is greatly appreciated.
>
> Jeff
> Jeff|||Hmmm.
I vaguely remember trying something like this, but maybe I didn't set it up
right. I'll give it another shot. Thanks.
"bsod55" wrote:
> Jeff,
> Have you tried inserting a second table header row above the original table
> header row? Then do the merging on the inserted header row. I just did this
> on a report at my work. Doing so gave me this effect:
> | H1 |
> |H1|H2|H3|H4|
> |D1|D2|D3|D4|
> H1 = Table header inserted by me.
> H2 = Orginal header inserted by RS
> D..= Detail records
> To do this right click on the handle for the exisitng table header row and
> click Insert Row Above. The formatting stayed when I downloaded the report
> to Excel. Hope this helps.
> "Jeff" wrote:
> > Okay, I'll try my best to explain this....
> >
> > I have a table on my report. Above the header for 5 of my columns, I need
> > to place a cell that spans all those columns. It looks like this:
> >
> > --
> > | h |
> > --
> > | h | h | h | h | h |
> > --
> > | d | d | d | d | d |
> > --
> >
> > h = header row
> > d = detail row
> >
> > so, to do this I took the header cell for the left most column and merged it
> > with the
> > 4 other header cells. Then I put a rectangle into that cell, and drew a
> > line through the middle to create the two level effect in the single header
> > cell. Then, where each 'h' is in the above diagram, I put a text box with
> > some text in it that serves as a label. So, basically all 5 of my detail
> > columns have their own individual header, as well as a header that applies to
> > all of them.
> >
> > When I run the report, and export it to PDF, everything looks great. When I
> > export to excel, I get a strange effect. SRS seems to want to put the cell
> > for the textbox in the bottom right most 'cell' in the header in a row below
> > the other 4 in the second header 'row'.. So, it looks like this in Excel:
> >
> > --
> > | h |
> > --
> > | h | h | h | h | |
> > --
> > | h |
> > | --|
> > | d | d | d | d | d |
> > --
> >
> >
> > h = header row
> > d = detail row
> >
> > Anybody have any idea what's going on here? If I remove the 5th text box in
> > the header (the one that's causing the problem), excel formats fine.
> >
> > It would be nice if I could merge cells across rows as well as columns,
> > because that would make this problem trivial.
> >
> > Any help is greatly appreciated.
> >
> >
> > Jeff
> > Jeff

Excel formatting - currency & percentages

Hi fellas,

this is another one of those "RS to Excel formatting" questions :)

I have reports with a large number of columns containing either percentages or currency figures. These numbers show up in Excel with the General format - how can i get Excel to recognise them for what they are? I preferably want to keep the $ and % signs included, as it makes the report a lot easier to read if they are retained, so formatting the numbers as decimals etc. (as other answers have suggested) is probably a last resort.

As a related question, is it possible to write a custom filter or renderer that can be inserted between RS and Excel?

Thanks for any answers/ideas!

sluggy

I believe if you use the c and p format that Excel will render it correctly. c# for currency where the # is the decimals to carry. p# for percentage where the # is the number of decimals to carry. Another useful one is n# (general number format with commas).|||

Thanks for the answer Lonnie, but it doesn't work. Using the currency fields as an example, i have always used FormatCurrency() to format them, and changing this to Format(value, "c0") has no effect on the problem.

Cheers

sluggy

|||Use the Format field on the textbox, and put the "c0" in there. This way the renderer can apply the formatting; in Excel's case, it will translate the value of the Format field to an Excel format and apply it to the cell that the textbox appears in.|||

Geoff,

thanks, that was the answer. There was a subtlety in there that tripped me up, and i'm going to mention it because it could help someone else.

In the Value property of the textboxes in the report, i was using FormatPercent() to format the results of an expression, but the output of that function (and its sisters) is a string, which meant that when i used a format code in the Format property it was not being applied. The solution was to get rid of any formatting in the Value property, move the formatting to the Format property, and ensure that the Value field only evaluated to numeric values so that the formatting could be applied. It might sound simple, but i never saw anything mentioning this in any of the doco i read :)

Many thanks,

sluggy

Excel formatting - currency & percentages

Hi fellas,

this is another one of those "RS to Excel formatting" questions :)

I have reports with a large number of columns containing either percentages or currency figures. These numbers show up in Excel with the General format - how can i get Excel to recognise them for what they are? I preferably want to keep the $ and % signs included, as it makes the report a lot easier to read if they are retained, so formatting the numbers as decimals etc. (as other answers have suggested) is probably a last resort.

As a related question, is it possible to write a custom filter or renderer that can be inserted between RS and Excel?

Thanks for any answers/ideas!

sluggy

I believe if you use the c and p format that Excel will render it correctly. c# for currency where the # is the decimals to carry. p# for percentage where the # is the number of decimals to carry. Another useful one is n# (general number format with commas).|||

Thanks for the answer Lonnie, but it doesn't work. Using the currency fields as an example, i have always used FormatCurrency() to format them, and changing this to Format(value, "c0") has no effect on the problem.

Cheers

sluggy

|||Use the Format field on the textbox, and put the "c0" in there. This way the renderer can apply the formatting; in Excel's case, it will translate the value of the Format field to an Excel format and apply it to the cell that the textbox appears in.|||

Geoff,

thanks, that was the answer. There was a subtlety in there that tripped me up, and i'm going to mention it because it could help someone else.

In the Value property of the textboxes in the report, i was using FormatPercent() to format the results of an expression, but the output of that function (and its sisters) is a string, which meant that when i used a format code in the Format property it was not being applied. The solution was to get rid of any formatting in the Value property, move the formatting to the Format property, and ensure that the Value field only evaluated to numeric values so that the formatting could be applied. It might sound simple, but i never saw anything mentioning this in any of the doco i read :)

Many thanks,

sluggy

Excel Formatting

I have a report that I exported to Excel which contains dates in one column.
About 10% of the dates show up as ######## accross the width of the cell.
The column is plenty wide enough and if you double-click one of these cells
the date shows up fine. It also does it on a column that has percentages.
Does anyone know how to prevent this from happening?What happens when you make the column very wide? Could it be trying to
format time in there as well?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:C87B46BC-65B1-4AB5-901E-E1707AFF4DA9@.microsoft.com...
>I have a report that I exported to Excel which contains dates in one
>column.
> About 10% of the dates show up as ######## accross the width of the cell.
> The column is plenty wide enough and if you double-click one of these
> cells
> the date shows up fine. It also does it on a column that has percentages.
> Does anyone know how to prevent this from happening?|||No. I made the column width 150 and it just added more # signs to fill up
the column. Is there a way I can send you the Excel file?
"Jeff A. Stucker" wrote:
> What happens when you make the column very wide? Could it be trying to
> format time in there as well?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:C87B46BC-65B1-4AB5-901E-E1707AFF4DA9@.microsoft.com...
> >I have a report that I exported to Excel which contains dates in one
> >column.
> > About 10% of the dates show up as ######## accross the width of the cell.
> > The column is plenty wide enough and if you double-click one of these
> > cells
> > the date shows up fine. It also does it on a column that has percentages.
> > Does anyone know how to prevent this from happening?
>
>sql

Excel Format different from PDF and Image

I have a report that looks good in .PDF and .tif format, but really bad
in Excel format. Can anything be done about this?
Thank you!Carol,
At the heart of things is the fact that .PDF and .TIF simply render an
image of the report while Excel is trying to insert the entire report,
including headers and footers, into a viable spreadsheet.
Without seeing the Excel output, it's tough to know for sure what
issues you're having but typically the problem with Excel is it inserts
additional columns (and sometimes rows as well) that don't exist in the
original report design.
The cause is that the left and right borders of some report objects
(text boxes, etc.) are not lining up in the different report sections
and/or data regions. For example, if there's a text box in the page
header that's 1" wide and then a 1.25" wide table column in the body
(under the 1" header text box), Excel will render that into two
columns. That's a pretty basic example but the problem greatly
exacerbates if you're using free-form data regions or multiple tables
or tables with subreports.
Unfortunately, the only way to resolve this is to manipulate everything
in the report design to line up as much as possible. It helps to use
the various width and height properties to set things to match up
exactly as opposed to relying just on "eyeballing" it.
Hope this helps.

Excel format

Hi,
Anyone know how can I export data to Excel 97/2000?,
because in my company everybody has Excel 97/2000 and it's
impossible change version.
Thanks and sory for my English :)Install SP1. From SP1 Readme:
<
Rendered reports can now be opened in Excel 97 and later. Previously, the
file format was MIME Encapsulation of Aggregate HTML (MHTML), which could
only be opened in Excel 2002 and later. The format for rendered reports is
now Binary Interchange File Format (BIFF), which can be opened in Excel 97
and later.
/>
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<jcrf@.discussions.microsoft.com> wrote in message
news:3c5c01c4ac41$35d10900$a501280a@.phx.gbl...
> Hi,
> Anyone know how can I export data to Excel 97/2000?,
> because in my company everybody has Excel 97/2000 and it's
> impossible change version.
> Thanks and sory for my English :)

Excel Files Too Large

Does anyone know if SP1 includes any fixes to the file size of Excel files. We are trying to replace an old reporting tool with RS; however, when exporting a relativly small report the Excel file in 5-15 MB. Has SP1 addressed this issue? There are similar problems with PDF files as well.SP1 includes a major change to the Excel renderer. Rather than generating
MHTML (which ends up being quite large), we now generate native Excel files
(which tend to be smaller unless you have a large images in your report).
This has the added benefit of working with older versions of Excel.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"sstevens" <sstevens@.discussions.microsoft.com> wrote in message
news:3CFB37E3-C8E4-4B06-8CBB-1B2CE31EE347@.microsoft.com...
> Does anyone know if SP1 includes any fixes to the file size of Excel
files. We are trying to replace an old reporting tool with RS; however,
when exporting a relativly small report the Excel file in 5-15 MB. Has SP1
addressed this issue? There are similar problems with PDF files as well.|||Thanks Chris! I just got SP1 up and running. With doing nothing but installing SP1 the render time and the file size have both been cut in half.
"Chris Hays [MSFT]" wrote:
> SP1 includes a major change to the Excel renderer. Rather than generating
> MHTML (which ends up being quite large), we now generate native Excel files
> (which tend to be smaller unless you have a large images in your report).
> This has the added benefit of working with older versions of Excel.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "sstevens" <sstevens@.discussions.microsoft.com> wrote in message
> news:3CFB37E3-C8E4-4B06-8CBB-1B2CE31EE347@.microsoft.com...
> > Does anyone know if SP1 includes any fixes to the file size of Excel
> files. We are trying to replace an old reporting tool with RS; however,
> when exporting a relativly small report the Excel file in 5-15 MB. Has SP1
> addressed this issue? There are similar problems with PDF files as well.
>
>sql

Excel files are too large

I have the following problem with Reporting Services:
My report has about 6500 lines and 100 columns. When I export it into Excel
the size of the end file is about 20 Mb. As I have to send it using
subscriptions, it is a problem â' too large file for e-mail. Are there any
ways to minimize the size of output Excel file?
Regards,
Boris.Workaround:
Create a webpage containing your report, post it on the webserver, and
e-mail a link to this page to your customer.
It is faster, and more friendly designed ( and it began to be a
standard in modern distributed applications)
You also have to take care about "Garbage Collection": set an
expiration date for reports to be deleted from the webserver.
Have fun :)|||Are you using Service Pack 1? We did some sizing work but it still might be
too big for you.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Boris" <frolovBA@.trytoguessHM.com> wrote in message
news:CBAA6D77-E111-469F-8261-C0CC9A78A82B@.microsoft.com...
>I have the following problem with Reporting Services:
> My report has about 6500 lines and 100 columns. When I export it into
> Excel
> the size of the end file is about 20 Mb. As I have to send it using
> subscriptions, it is a problem - too large file for e-mail. Are there any
> ways to minimize the size of output Excel file?
> Regards,
> Boris.
>|||Thank you for your advice, but users of this report want to receive it in
Excel.
Iâ've made a workaround: Reporting Services save the Excel file into
directory and then my program archive it and send it through e-mail. But may
be I miss some details that can optimize the size of Report?
Regards,
Boris
"katzirina" wrote:
> Workaround:
> Create a webpage containing your report, post it on the webserver, and
> e-mail a link to this page to your customer.
> It is faster, and more friendly designed ( and it began to be a
> standard in modern distributed applications)
> You also have to take care about "Garbage Collection": set an
> expiration date for reports to be deleted from the webserver.
> Have fun :)
>|||Yes I use SP1.
Regards,
Boris.
"Brian Welcker [MS]" wrote:
> Are you using Service Pack 1? We did some sizing work but it still might be
> too big for you.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Boris" <frolovBA@.trytoguessHM.com> wrote in message
> news:CBAA6D77-E111-469F-8261-C0CC9A78A82B@.microsoft.com...
> >I have the following problem with Reporting Services:
> > My report has about 6500 lines and 100 columns. When I export it into
> > Excel
> > the size of the end file is about 20 Mb. As I have to send it using
> > subscriptions, it is a problem - too large file for e-mail. Are there any
> > ways to minimize the size of output Excel file?
> >
> > Regards,
> > Boris.
> >
>
>|||When you create the spreadsheet manually, how large is it? I just tried a
very simple sheet with no formatting and a single number per cell and it was
5 MB. The only way this would work through compression in the delivery
provider. We do not support this but there might be a 3rd party that does.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Boris" <frolovBA@.trytoguessHM.com> wrote in message
news:6A26B3A5-0CF5-4F3B-8B1A-3CB4F36B453E@.microsoft.com...
> Yes I use SP1.
> Regards,
> Boris.
> "Brian Welcker [MS]" wrote:
>> Are you using Service Pack 1? We did some sizing work but it still might
>> be
>> too big for you.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Boris" <frolovBA@.trytoguessHM.com> wrote in message
>> news:CBAA6D77-E111-469F-8261-C0CC9A78A82B@.microsoft.com...
>> >I have the following problem with Reporting Services:
>> > My report has about 6500 lines and 100 columns. When I export it into
>> > Excel
>> > the size of the end file is about 20 Mb. As I have to send it using
>> > subscriptions, it is a problem - too large file for e-mail. Are there
>> > any
>> > ways to minimize the size of output Excel file?
>> >
>> > Regards,
>> > Boris.
>> >
>>|||When I create it manually itâ's about 6 Mb. Even if I made export to Excel
from Reporting Server, then open this 24 Mb file and save it â' it gets size
about 7 Mb. The only formatting I use are 0.5pt borders and number/date
formats.
Regards,
Boris.
"Brian Welcker [MS]" wrote:
> When you create the spreadsheet manually, how large is it? I just tried a
> very simple sheet with no formatting and a single number per cell and it was
> 5 MB. The only way this would work through compression in the delivery
> provider. We do not support this but there might be a 3rd party that does.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Boris" <frolovBA@.trytoguessHM.com> wrote in message
> news:6A26B3A5-0CF5-4F3B-8B1A-3CB4F36B453E@.microsoft.com...
> > Yes I use SP1.
> >
> > Regards,
> > Boris.
> >
> > "Brian Welcker [MS]" wrote:
> >
> >> Are you using Service Pack 1? We did some sizing work but it still might
> >> be
> >> too big for you.
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> Microsoft SQL Server
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Boris" <frolovBA@.trytoguessHM.com> wrote in message
> >> news:CBAA6D77-E111-469F-8261-C0CC9A78A82B@.microsoft.com...
> >> >I have the following problem with Reporting Services:
> >> > My report has about 6500 lines and 100 columns. When I export it into
> >> > Excel
> >> > the size of the end file is about 20 Mb. As I have to send it using
> >> > subscriptions, it is a problem - too large file for e-mail. Are there
> >> > any
> >> > ways to minimize the size of output Excel file?
> >> >
> >> > Regards,
> >> > Boris.
> >> >
> >>
> >>
> >>
>
>

Excel Files against SQL Server - validation

Hi
I'm rather new to ASP.NET (was working with win forms before) and having a few problems with my first app.
To the point - this is an internal system - every week about 50 branches send financial reports to the headquaters where those excel files are validated against data in sql server. after file is uploaded to the server I'm opening it using Excel object model and iterating through it line by line. Apart from format and datatype checking, for each line there are ~2 stored procedures invoked
Everything is working actually but my concern is performance.. After about 800 rows it is getting really slow.. Appreciate any comments on this.
thanks
AMWhen the Excel files are that many you have two options use DTS package through a stored proc you either use DTSRUN.exe or XP_CMDSHELL which is SQL Server Agent dependent. Try the links below for DTSRUN.exe sample code and XP_CMDSHELL configurations with permissions. What I am saying is to run DTS with SQL Server Agent dependent service like xp_CMDSHELL you must give the account used to install SQL Server Agent Admin permissions in Windows and SQL Server. The solution with the XP_CMDSHELL can be scheduled to run for four hours a day five days a week. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

http://www.sqlteam.com/item.asp?ItemID=19595

Excel File Size after upgrade to SRS 2005

We have upgraded our RS 2000 instance to SRS 2005. We have seen that
reports exported to excel have gotten huge since the upgrade. For
example, we had a report that when run in RS 2000, would export to an
excel file that was 900KB. The same report exported to excel in SRS
2005 is now 12 meg! Is this because the original report was designed
in VS 2003? Would it improve if the report was re-deployed from VS
2005?Hey just check in your (huge) excel file is there any spaces or no of sheets
have been added. If spaces are there on your sheets will also increase the
size of the file.
Amarnath
"dachrist28@.gmail.com" wrote:
> We have upgraded our RS 2000 instance to SRS 2005. We have seen that
> reports exported to excel have gotten huge since the upgrade. For
> example, we had a report that when run in RS 2000, would export to an
> excel file that was 900KB. The same report exported to excel in SRS
> 2005 is now 12 meg! Is this because the original report was designed
> in VS 2003? Would it improve if the report was re-deployed from VS
> 2005?
>sql

Excel file size - huge! - on Prod server

When I run a report in Reporting Services on the Report Server on my
local PC, I can save the report to Excel as reasonable file sizes eg.
700K. When I deploy the same report's rdl to the live server, run it
and then save the same report to my network share, the file size
explodes to several meg (anything from 5mb to 15 mb). Anyone know why
this happens and how I can prevent it?My guess is that your local one has SP1 and the live one does not. SP1
changed to native format instead of mhtml.
Check versions by going to //yourserver/reportserver
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vivienne" <vivienne00@.yahoo.com> wrote in message
news:1107967800.202356.71560@.c13g2000cwb.googlegroups.com...
> When I run a report in Reporting Services on the Report Server on my
> local PC, I can save the report to Excel as reasonable file sizes eg.
> 700K. When I deploy the same report's rdl to the live server, run it
> and then save the same report to my network share, the file size
> explodes to several meg (anything from 5mb to 15 mb). Anyone know why
> this happens and how I can prevent it?
>

Excel File Locked

I'm building a package that has a Data Flow which reads an Excel File using the Excel Source connection - during development I hit errors in steps after the excel read. This is causing the excel file to be locked up and the next run fails on excel connection problem. If I go to Excel I can't open the file either. I have to exit BIDS and then file gets released. Is there any setting / different approach that would prevent this from happening?

thx

I doubt there is a setting - its just unfortunate I guess.

This probably won't happen if you run the package from the command line - so try that!

-Jamie

Excel file loading in SSIS

Hi All,

We did the development of SSIS packges on 32 bit machine. We have few excel files which is loaded using SSIS.

Now the same was deployed to anothe rmachine(64 bit). This 64-bit machine does not have Microsoft office installed.

And all packages(which loads excel files) failed. Hence can someone answer my following questions:-

1) In order to load excel files using SSIS, is it necessary that Microsoft excel software should be installed on that machine?

2) If answer to above is yes, Can Microsoft excel viewer be used instead of Microsoft office(excel)?

Thanks

Sid

Hi,

I am not an expert in SSIS but i have tried importing excel source data to sql using ssis package in a machine that does not have MS Office installed, but it worked for me.

Thanks,

OM$.

|||

Thanks for reply: Here is my problem:

I run those SSIS packages (which loads excel file) by double clicking it or running it through SSIS editor. It works great.

However if I run it using the command line dtexec.exe then following error arises: -

Error: 2007-08-03 16:53:27.42
Code: 0xC0202009
Source: PkgExtract Connection manager "SRC_Connection"
Description: An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2007-08-03 16:53:27.42
Code: 0xC020801C
Source: Data Flow Task - Extract Data Excel Source [1860]
Description: The AcquireConnection method call to the connection manager "SRC_Connection" failed with error code 0xC0202009.
End Error
Error: 2007-08-03 16:53:27.43
Code: 0xC0047017
Source: Data Flow Task - Extract Data DTS.Pipeline
Description: component "Excel Source" (1860) failed validation and returned error code 0xC020801C.
End Error

|||

See your other post on this.

There is no 64-bit driver for Excel. You need to run the 32-bit version of DTEXEC (located in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn).

|||

Thanks a ton. That was the solution and works perfectly fine

Excel file import problem

Hello:
I need to import an Excel file to SQL Server.
The .xls file has the column names which contains
dot inside, like AAA.BBB. When I import this file
in SQL using DTS Import/Export tool, it creates a table
with column names like AAA#BBB.
So, during import process the dots substitutes with #.
Could you, please, give me a hint how to fix the problem?
Thanks,
GB"GB" wrote:

> Hello:
> I need to import an Excel file to SQL Server.
> The .xls file has the column names which contains
> dot inside, like AAA.BBB. When I import this file
> in SQL using DTS Import/Export tool, it creates a table
> with column names like AAA#BBB.
> So, during import process the dots substitutes with #.
> Could you, please, give me a hint how to fix the problem?
> Thanks,
> GB
>
> Do not use import/export in dts - nto very flexible.
Use Transform Data Task
1- sel. excel conn. (choose .xls doc)
2- sel. sql conn. (choose dbase and passwords if needed)
3-highlight excel conn and ctrl sql conn
4- sel. Transform Data task
5- open Trans data Task(double click arrow)
Source is excel - choose table/view (select sheet in excel) OR SQL Query
Destination is SQL - choose table name or create new (you can change created
name(usually default sheet1$ if you have no named tabs) to another name and
dts will load a new tab into excel)
Transformation shows links between cols from source to destin.
(open transform to make sure all is ok)
click OK for transformation.
arrow should be highlighted - just execute it !|||
"GB" wrote:

> Hello:
> I need to import an Excel file to SQL Server.
> The .xls file has the column names which contains
> dot inside, like AAA.BBB. When I import this file
> in SQL using DTS Import/Export tool, it creates a table
> with column names like AAA#BBB.
> So, during import process the dots substitutes with #.
> Could you, please, give me a hint how to fix the problem?
> Thanks,
> GB
>
> I forgot 1 piece.
In DataTransformation Task
When you select the destination - Create the table - change AAA#BBB to AAA.B
BB
OR if you have alot of columns - copy the whole create stmt to textpad -
replace all # with . and recopy into dts.

Excel file as data source

Hi everyone!

I am trying to import data into my sqlserver 2005 database from an Excel 2000 file. The database is empty. I am using the worksheets from the file to create the tables and copy the rows. I am getting follwing errors:

- Pre-execute (Error)

Messages

Error 0xc0202009: {674E15E4-102E-4935-90A2-8B1FFFEFB11D}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source 64 - vw_TempOrderDetails" (5280) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)

Any suggestion is most welcome.

Regards

Have you read this:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=160340&SiteID=1 ?|||

Hi Mike,

Many thanks for the reply. I will check out this thread and come back.

Regards

|||

Hi!

I went throught the thread but still am unable to figure it out. Will search elsewhere and revert back.

Regards

|||

Though it looks the Import Export Wizard is not working properly ... you can use one of the alternative method .

1Use Distributed Queries2If you donot wantto configure a persistent connectionto the Excel workbookas a linked server, you can import datafor a specific purposeby using theOPENDATASOURCEor theOPENROWSET function. The following code samples also import the datafrom the Excel Customers worksheetinto new SQL Server tables:SELECT *INTO XLImport3FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',3'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]45SELECT *INTO XLImport4FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',6'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])78SELECT *INTO XLImport5FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',9'Excel 8.0;Database=C:\test\xltest.xls','SELECT * FROM [Customers$]')1011

http://support.microsoft.com/kb/321686

Hope this will help you

This is excerpt from msdnlink...

|||

Hi Satya,

Thanks for the reply. I hope this will work. But I will try to work around the alternative mentioned in the subject thread and present the solution.

BTW: I am from New Delhi, India.

Regards,

Venu

sql

Excel extraction issue

I have a spreadsheet that's designed to look like a form. Does anyone know how I would create a SSIS package to extract specific cell values?

TIA

Have you tried using the Excel Source Adapter (http://msdn2.microsoft.com/en-us/library/ms181175.aspx)

Thanks

Anjan.

Excel Exports Hidden Images

Hello,
I think this may be a bug with SSRS 2005 but wanted to verify in case anyone
has a remedy. My report contains a table with an image on the detail row
whose visibility is toggled based on a value. Everything works as expected
when viewing the report onscreen or exporting to PDF. However, the Excel
Export ignores the visibility expression and displays the image for every
detail row. Any thoughts or workarounds?
Thanks in advance!
KathyOn Jul 6, 10:44 am, Kathy <K...@.discussions.microsoft.com> wrote:
> Hello,
> I think this may be a bug with SSRS 2005 but wanted to verify in case anyone
> has a remedy. My report contains a table with an image on the detail row
> whose visibility is toggled based on a value. Everything works as expected
> when viewing the report onscreen or exporting to PDF. However, the Excel
> Export ignores the visibility expression and displays the image for every
> detail row. Any thoughts or workarounds?
> Thanks in advance!
> Kathy
As far as I know, you are correct. Hopefully this will be corrected in
a future SP or in SSRS 2008. Sorry that I could not be of further
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you for the quick response. Have a lovely day!
"EMartinez" wrote:
> On Jul 6, 10:44 am, Kathy <K...@.discussions.microsoft.com> wrote:
> > Hello,
> >
> > I think this may be a bug with SSRS 2005 but wanted to verify in case anyone
> > has a remedy. My report contains a table with an image on the detail row
> > whose visibility is toggled based on a value. Everything works as expected
> > when viewing the report onscreen or exporting to PDF. However, the Excel
> > Export ignores the visibility expression and displays the image for every
> > detail row. Any thoughts or workarounds?
> >
> > Thanks in advance!
> > Kathy
>
> As far as I know, you are correct. Hopefully this will be corrected in
> a future SP or in SSRS 2008. Sorry that I could not be of further
> assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jul 9, 8:38 am, Kathy <K...@.discussions.microsoft.com> wrote:
> Thank you for the quick response. Have a lovely day!
> "EMartinez" wrote:
> > On Jul 6, 10:44 am, Kathy <K...@.discussions.microsoft.com> wrote:
> > > Hello,
> > > I think this may be a bug with SSRS 2005 but wanted to verify in case anyone
> > > has a remedy. My report contains a table with an image on the detail row
> > > whose visibility is toggled based on a value. Everything works as expected
> > > when viewing the report onscreen or exporting to PDF. However, the Excel
> > > Export ignores the visibility expression and displays the image for every
> > > detail row. Any thoughts or workarounds?
> > > Thanks in advance!
> > > Kathy
> > As far as I know, you are correct. Hopefully this will be corrected in
> > a future SP or in SSRS 2008. Sorry that I could not be of further
> > assistance.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Excel Exporting results in MIME message

I reviewed all post but have not seen the problem I have.
When exporting a report to Excel (regardless if I save or open the file) my
output is a multi-part MIME message. I get only one worksheet (there are 4
pages in the report) and it has the name of the exported report.
There is a section for each page in this message and a StyleSheet section.
Did I miss something in the report Services set-up?
Thanks
ShawnI took a better look at the rest of the posts and did find some with the same
problem. One of them suggested installation of SP1 in MS Office 2000. My
problem now is that I did install SP1 and nothing changed. If anyone has
another suggestion it will be greatly appreciated.
Thanks
Shawn
"Shawn Kralj" wrote:
> I reviewed all post but have not seen the problem I have.
> When exporting a report to Excel (regardless if I save or open the file) my
> output is a multi-part MIME message. I get only one worksheet (there are 4
> pages in the report) and it has the name of the exported report.
> There is a section for each page in this message and a StyleSheet section.
> Did I miss something in the report Services set-up?
> Thanks
> Shawn|||Close, it is not SP1. It is SP1 for Reporting Services that has the fix. SP1
no longer uses the MIME format.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shawn Kralj" <ShawnKralj@.discussions.microsoft.com> wrote in message
news:FE6555D6-71F4-4C29-90F4-DA92604B4816@.microsoft.com...
> I took a better look at the rest of the posts and did find some with the
same
> problem. One of them suggested installation of SP1 in MS Office 2000. My
> problem now is that I did install SP1 and nothing changed. If anyone has
> another suggestion it will be greatly appreciated.
> Thanks
> Shawn
> "Shawn Kralj" wrote:
> > I reviewed all post but have not seen the problem I have.
> >
> > When exporting a report to Excel (regardless if I save or open the file)
my
> > output is a multi-part MIME message. I get only one worksheet (there are
4
> > pages in the report) and it has the name of the exported report.
> >
> > There is a section for each page in this message and a StyleSheet
section.
> >
> > Did I miss something in the report Services set-up?
> >
> > Thanks
> >
> > Shawn|||Thanks Bruce. That worked. Installing SP1 for Reporting Services fixed the
problem.
Looks great.
Shawn
"Bruce L-C [MVP]" wrote:
> Close, it is not SP1. It is SP1 for Reporting Services that has the fix. SP1
> no longer uses the MIME format.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Shawn Kralj" <ShawnKralj@.discussions.microsoft.com> wrote in message
> news:FE6555D6-71F4-4C29-90F4-DA92604B4816@.microsoft.com...
> > I took a better look at the rest of the posts and did find some with the
> same
> > problem. One of them suggested installation of SP1 in MS Office 2000. My
> > problem now is that I did install SP1 and nothing changed. If anyone has
> > another suggestion it will be greatly appreciated.
> >
> > Thanks
> >
> > Shawn
> >
> > "Shawn Kralj" wrote:
> >
> > > I reviewed all post but have not seen the problem I have.
> > >
> > > When exporting a report to Excel (regardless if I save or open the file)
> my
> > > output is a multi-part MIME message. I get only one worksheet (there are
> 4
> > > pages in the report) and it has the name of the exported report.
> > >
> > > There is a section for each page in this message and a StyleSheet
> section.
> > >
> > > Did I miss something in the report Services set-up?
> > >
> > > Thanks
> > >
> > > Shawn
>
>

Excel exporting in RS2005

Hi,
We've been having 'Subreports within table/matrix cells are ignored.'
problems in one of the reports we have designed in RS 2000, this is
well documented as a limitation.
I would like to know if this is still a limitation in the upcoming 2005
Reporting Services?
Siew FaiHello Siew,
Based on my test, this is still a limitation in SQL 2005 RS. From the
feedback of product group, they will consider fix this issue when
re-writing the extension. If we have any update on this, we will let you
know.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Siew Fai" <siewfai.hoy@.gmail.com>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Excel exporting in RS2005
| Date: 1 Nov 2005 14:29:46 -0800
| Organization: http://groups.google.com
| Lines: 12
| Message-ID: <1130884186.657018.118660@.g47g2000cwa.googlegroups.com>
| NNTP-Posting-Host: 203.217.67.62
| Mime-Version: 1.0
| Content-Type: text/plain; charset="iso-8859-1"
| X-Trace: posting.google.com 1130884191 26947 127.0.0.1 (1 Nov 2005
22:29:51 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Tue, 1 Nov 2005 22:29:51 +0000 (UTC)
| User-Agent: G2/0.2
| X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1;
.NET CLR 1.1.4322),gzip(gfe),gzip(gfe)
| Complaints-To: groups-abuse@.google.com
| Injection-Info: g47g2000cwa.googlegroups.com; posting-host=203.217.67.62;
| posting-account=W-T95Q0AAABWrZAGzmtXCMOc3JBrVGnv
| Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
ne.de!border2.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.gigan
ews.com!postnews.google.com!g47g2000cwa.googlegroups.com!not-for-mail
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:55499
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi,
|
| We've been having 'Subreports within table/matrix cells are ignored.'
| problems in one of the reports we have designed in RS 2000, this is
| well documented as a limitation.
|
| I would like to know if this is still a limitation in the upcoming 2005
| Reporting Services?
|
|
| Siew Fai
|
||||Thanks for the prompt reply.
I will await any updates from you.
Regards,
Siew Fai
Peter Yang [MSFT] wrote:
> Hello Siew,
> Based on my test, this is still a limitation in SQL 2005 RS. From the
> feedback of product group, they will consider fix this issue when
> re-writing the extension. If we have any update on this, we will let you
> know.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | From: "Siew Fai" <siewfai.hoy@.gmail.com>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | Subject: Excel exporting in RS2005
> | Date: 1 Nov 2005 14:29:46 -0800
> | Organization: http://groups.google.com
> | Lines: 12
> | Message-ID: <1130884186.657018.118660@.g47g2000cwa.googlegroups.com>
> | NNTP-Posting-Host: 203.217.67.62
> | Mime-Version: 1.0
> | Content-Type: text/plain; charset="iso-8859-1"
> | X-Trace: posting.google.com 1130884191 26947 127.0.0.1 (1 Nov 2005
> 22:29:51 GMT)
> | X-Complaints-To: groups-abuse@.google.com
> | NNTP-Posting-Date: Tue, 1 Nov 2005 22:29:51 +0000 (UTC)
> | User-Agent: G2/0.2
> | X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1;
> .NET CLR 1.1.4322),gzip(gfe),gzip(gfe)
> | Complaints-To: groups-abuse@.google.com
> | Injection-Info: g47g2000cwa.googlegroups.com; posting-host=203.217.67.62;
> | posting-account=W-T95Q0AAABWrZAGzmtXCMOc3JBrVGnv
> | Path:
> TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
> ne.de!border2.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.gigan
> ews.com!postnews.google.com!g47g2000cwa.googlegroups.com!not-for-mail
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:55499
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi,
> |
> | We've been having 'Subreports within table/matrix cells are ignored.'
> | problems in one of the reports we have designed in RS 2000, this is
> | well documented as a limitation.
> |
> | I would like to know if this is still a limitation in the upcoming 2005
> | Reporting Services?
> |
> |
> | Siew Fai
> |
> |