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
> |
> |

Excel Exporting - Merged Columns

Hey Ppl,
When exporting to excel, the renderer merges columns. I have a Header that contains an image and two simple labels (image on left and two label starting from about center). When there is a table beneath the Header, the right hand side of the image and the left hand sides of the labels create merged colums where it intersects in the table below.
Is there a way to stop this' Or
Has anyone found a way around this'
I have tried overlapping (exporting places them side-by-side) , background image (this is not exported to excel)
It is driving me mad =S, ANY help appreciated
Thanks in Advance
SheaYou can use SimplePageHeaders device info - the page header is rendered like
an excel header and will not be part of the excel sheet. In this way will
not affect your sheet structure.
Excel doens't allow background image for individual cells, only for the
sheet.
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shea Strickland" <Shea Strickland@.discussions.microsoft.com> wrote in
message news:6D038509-CFDE-433B-AF35-34828E767329@.microsoft.com...
> Hey Ppl,
> When exporting to excel, the renderer merges columns. I have a Header that
contains an image and two simple labels (image on left and two label
starting from about center). When there is a table beneath the Header, the
right hand side of the image and the left hand sides of the labels create
merged colums where it intersects in the table below.
> Is there a way to stop this' Or
> Has anyone found a way around this'
> I have tried overlapping (exporting places them side-by-side) , background
image (this is not exported to excel)
> It is driving me mad =S, ANY help appreciated
> Thanks in Advance
> Shea|||Hi Nico,
This sounds like the fix, although I have been looking thought the report properties / sub menus pretty much everywhere and i cant find anything to do with SimplePageHeaders device info. Where is this located? or is this in a .config file?
Thanks for your help
Shea
"Nico Cristache [MSFT]" wrote:
> You can use SimplePageHeaders device info - the page header is rendered like
> an excel header and will not be part of the excel sheet. In this way will
> not affect your sheet structure.
> Excel doens't allow background image for individual cells, only for the
> sheet.
> --
> Nico Cristache [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shea Strickland" <Shea Strickland@.discussions.microsoft.com> wrote in
> message news:6D038509-CFDE-433B-AF35-34828E767329@.microsoft.com...
> > Hey Ppl,
> >
> > When exporting to excel, the renderer merges columns. I have a Header that
> contains an image and two simple labels (image on left and two label
> starting from about center). When there is a table beneath the Header, the
> right hand side of the image and the left hand sides of the labels create
> merged colums where it intersects in the table below.
> >
> > Is there a way to stop this' Or
> > Has anyone found a way around this'
> >
> > I have tried overlapping (exporting places them side-by-side) , background
> image (this is not exported to excel)
> >
> > It is driving me mad =S, ANY help appreciated
> >
> > Thanks in Advance
> > Shea
>
>|||It is a device info property - you need to set it on the url when you access
the report (rc:SimplePageHeaders=true)
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shea Strickland" <SheaStrickland@.discussions.microsoft.com> wrote in
message news:94539F7F-912A-4850-B3F6-2F61E0DC77E5@.microsoft.com...
> Hi Nico,
> This sounds like the fix, although I have been looking thought the report
properties / sub menus pretty much everywhere and i cant find anything to do
with SimplePageHeaders device info. Where is this located? or is this in a
.config file?
> Thanks for your help
> Shea
> "Nico Cristache [MSFT]" wrote:
> > You can use SimplePageHeaders device info - the page header is rendered
like
> > an excel header and will not be part of the excel sheet. In this way
will
> > not affect your sheet structure.
> >
> > Excel doens't allow background image for individual cells, only for the
> > sheet.
> >
> > --
> > Nico Cristache [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Shea Strickland" <Shea Strickland@.discussions.microsoft.com> wrote in
> > message news:6D038509-CFDE-433B-AF35-34828E767329@.microsoft.com...
> > > Hey Ppl,
> > >
> > > When exporting to excel, the renderer merges columns. I have a Header
that
> > contains an image and two simple labels (image on left and two label
> > starting from about center). When there is a table beneath the Header,
the
> > right hand side of the image and the left hand sides of the labels
create
> > merged colums where it intersects in the table below.
> > >
> > > Is there a way to stop this' Or
> > > Has anyone found a way around this'
> > >
> > > I have tried overlapping (exporting places them side-by-side) ,
background
> > image (this is not exported to excel)
> > >
> > > It is driving me mad =S, ANY help appreciated
> > >
> > > Thanks in Advance
> > > Shea
> >
> >
> >|||Thanks for the speedy responses =)
Ok i've done that i put it in a url and it worked (only when i format straight to EXCEL). It did not put the image in the header though (is this possible?).
Next question, I have a web treeview control dynamically built with the navigate url property of the node set to the path property of the CatalogItem.Path returned from the ws' ListChildren method. This then displays the report in an iFrame when clicked. I appended the "&rs:SimplePageHeader=true" to the naviaget url fine. But when you choose Excel from the export drop down it resets the url and looses the rs device info setting. I dont wish to automatically asume that the user would like to export the Excel, so it is necessary to show it first in a iframe instead of specifying the "rs:format=EXCEL". Is it possible to change the behaviour of the Excel export option in the dropdown or similar'
Thanks heaps
Shea
"Nico Cristache [MSFT]" wrote:
> It is a device info property - you need to set it on the url when you access
> the report (rc:SimplePageHeaders=true)
> --
> Nico Cristache [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shea Strickland" <SheaStrickland@.discussions.microsoft.com> wrote in
> message news:94539F7F-912A-4850-B3F6-2F61E0DC77E5@.microsoft.com...
> > Hi Nico,
> >
> > This sounds like the fix, although I have been looking thought the report
> properties / sub menus pretty much everywhere and i cant find anything to do
> with SimplePageHeaders device info. Where is this located? or is this in a
> ..config file?
> >
> > Thanks for your help
> > Shea
> >
> > "Nico Cristache [MSFT]" wrote:
> >
> > > You can use SimplePageHeaders device info - the page header is rendered
> like
> > > an excel header and will not be part of the excel sheet. In this way
> will
> > > not affect your sheet structure.
> > >
> > > Excel doens't allow background image for individual cells, only for the
> > > sheet.
> > >
> > > --
> > > Nico Cristache [MSFT]
> > > Microsoft SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Shea Strickland" <Shea Strickland@.discussions.microsoft.com> wrote in
> > > message news:6D038509-CFDE-433B-AF35-34828E767329@.microsoft.com...
> > > > Hey Ppl,
> > > >
> > > > When exporting to excel, the renderer merges columns. I have a Header
> that
> > > contains an image and two simple labels (image on left and two label
> > > starting from about center). When there is a table beneath the Header,
> the
> > > right hand side of the image and the left hand sides of the labels
> create
> > > merged colums where it intersects in the table below.
> > > >
> > > > Is there a way to stop this' Or
> > > > Has anyone found a way around this'
> > > >
> > > > I have tried overlapping (exporting places them side-by-side) ,
> background
> > > image (this is not exported to excel)
> > > >
> > > > It is driving me mad =S, ANY help appreciated
> > > >
> > > > Thanks in Advance
> > > > Shea
> > >
> > >
> > >
>
>sql

Excel Export-Extra columns

Hi,
I am trying to export my report to Excel.When I do this I get some extra columns(blank though) in the Excel sheet which r not there in my report.And I see some greentips in some of my cells in excel sheet(which is a warning that a number is exported as text).Why r this due to?How can i remove these?
Thank You,When you are using a list in your report, this will most likely happen as we
have to approximate the cell layout. Table and matrix work much better when
exporting. Also, make sure you have number formats on your values so they
will get explicit formats in Excel.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sudha" <Sudha@.discussions.microsoft.com> wrote in message
news:D67121D5-E109-448E-AE9A-5866EDCDD18F@.microsoft.com...
> Hi,
> I am trying to export my report to Excel.When I do this I get some extra
> columns(blank though) in the Excel sheet which r not there in my
> report.And I see some greentips in some of my cells in excel sheet(which
> is a warning that a number is exported as text).Why r this due to?How can
> i remove these?
> Thank You,|||Hi,
I am using only tables/matrix in my reports and not lists still i get some extra columns.How can i avoid this?
Thanx
"Brian Welcker [MSFT]" wrote:
> When you are using a list in your report, this will most likely happen as we
> have to approximate the cell layout. Table and matrix work much better when
> exporting. Also, make sure you have number formats on your values so they
> will get explicit formats in Excel.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Sudha" <Sudha@.discussions.microsoft.com> wrote in message
> news:D67121D5-E109-448E-AE9A-5866EDCDD18F@.microsoft.com...
> > Hi,
> >
> > I am trying to export my report to Excel.When I do this I get some extra
> > columns(blank though) in the Excel sheet which r not there in my
> > report.And I see some greentips in some of my cells in excel sheet(which
> > is a warning that a number is exported as text).Why r this due to?How can
> > i remove these?
> >
> > Thank You,
>
>|||Do you have page headers and footers? This can cause this as well. And when
you say you have tables / matrices, you mean in different reports, right? If
this is not the case, it would be interesting to see your reports and the
Excel output.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sudha" <Sudha@.discussions.microsoft.com> wrote in message
news:53373429-2B5D-4335-8113-819A969B3C15@.microsoft.com...
> Hi,
> I am using only tables/matrix in my reports and not lists still i get some
> extra columns.How can i avoid this?
> Thanx
> "Brian Welcker [MSFT]" wrote:
>> When you are using a list in your report, this will most likely happen as
>> we
>> have to approximate the cell layout. Table and matrix work much better
>> when
>> exporting. Also, make sure you have number formats on your values so they
>> will get explicit formats in Excel.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Sudha" <Sudha@.discussions.microsoft.com> wrote in message
>> news:D67121D5-E109-448E-AE9A-5866EDCDD18F@.microsoft.com...
>> > Hi,
>> >
>> > I am trying to export my report to Excel.When I do this I get some
>> > extra
>> > columns(blank though) in the Excel sheet which r not there in my
>> > report.And I see some greentips in some of my cells in excel
>> > sheet(which
>> > is a warning that a number is exported as text).Why r this due to?How
>> > can
>> > i remove these?
>> >
>> > Thank You,
>>|||I am using a table for my report and I'm not implementing a header nor
a footer. In the end of the table though, I used a line and a textbox
to somehow "appear as a footer" at the end of the report.
Upon export to excel, extra columns were inserted and I don't know how
does this happened or how I can remove them.
I'll be more than willing to provide you with screenshots of the
report in the designer view and its output in excel.
I hope you can advice me on this matter and any help will be greatly
appreciated.
Have a nice day and thanks in advance.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message news:<uh5BAZMYEHA.3260@.tk2msftngp13.phx.gbl>...
> Do you have page headers and footers? This can cause this as well. And when
> you say you have tables / matrices, you mean in different reports, right? If
> this is not the case, it would be interesting to see your reports and the
> Excel output.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Sudha" <Sudha@.discussions.microsoft.com> wrote in message
> news:53373429-2B5D-4335-8113-819A969B3C15@.microsoft.com...
> > Hi,
> >
> > I am using only tables/matrix in my reports and not lists still i get some
> > extra columns.How can i avoid this?
> >
> > Thanx
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> >> When you are using a list in your report, this will most likely happen as
> >> we
> >> have to approximate the cell layout. Table and matrix work much better
> >> when
> >> exporting. Also, make sure you have number formats on your values so they
> >> will get explicit formats in Excel.
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Sudha" <Sudha@.discussions.microsoft.com> wrote in message
> >> news:D67121D5-E109-448E-AE9A-5866EDCDD18F@.microsoft.com...
> >> > Hi,
> >> >
> >> > I am trying to export my report to Excel.When I do this I get some
> >> > extra
> >> > columns(blank though) in the Excel sheet which r not there in my
> >> > report.And I see some greentips in some of my cells in excel
> >> > sheet(which
> >> > is a warning that a number is exported as text).Why r this due to?How
> >> > can
> >> > i remove these?
> >> >
> >> > Thank You,
> >>
> >>
> >>

Excel Export: How to force SSRS to generate Excel AVG and SUM formulas?

Hi
We are using SSRS 2005 to generate excel sheets. At this point we need to
have Excel SUM and AVG functionality in the exported Excel sheets, but SSRS
puts the resulting number instead of the actual formula. The problem is that
when end user modifies data, the SUM and AVG fields don't update according
to the changes.
Is there any way to force SSRS to generate SUM and AVG formulas instead of
generating the result numbers?
Any help would be appreciated,
MaxHello Max,
This issue is a limitation in the current Reporting Services. Better
formula support is on our wishlist for a future release.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

excel export...

Hi Guys
When exporting my report to excel my field text gets exported as double
lines. Does anybody know a way around this?
eg:
Line 1
Line 2
turns to
Line 1
Line 2
Thanks
KezHi,
If the space between two rows are more than 0.125 in, then this is the limit
it removes automatically when export to excel. Try reducing your row height
and see how excel renders.
Amarnath.
"Kez" wrote:
> Hi Guys
> When exporting my report to excel my field text gets exported as double
> lines. Does anybody know a way around this?
> eg:
> Line 1
> Line 2
> turns to
> Line 1
> Line 2
>
> Thanks
> Kez|||Thanks
That did the trick
"Amarnath" wrote:
> Hi,
> If the space between two rows are more than 0.125 in, then this is the limit
> it removes automatically when export to excel. Try reducing your row height
> and see how excel renders.
> Amarnath.
>
> "Kez" wrote:
> > Hi Guys
> > When exporting my report to excel my field text gets exported as double
> > lines. Does anybody know a way around this?
> >
> > eg:
> > Line 1
> > Line 2
> >
> > turns to
> > Line 1
> >
> > Line 2
> >
> >
> > Thanks
> > Kez

Excel export with formulas

Hi All
Just wondering if there is an option to turn of the exporting of formulas to
Excel, or alternatively not having it create named ranges for the formulas.
I have one report that frequently has over 5500 rows and 5 columns, the last
two are formulas for calculating percentage of the total.
This results in around 20000 named ranges which makes it incredibly slow
(15-20 seconds) to open the workbook.
Thanks
PhillYou can set OmitFormulas in device info to turn off formulas.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_dc_v1_4d0x.asp
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
news:%23oti9n8XEHA.1000@.TK2MSFTNGP12.phx.gbl...
> Hi All
> Just wondering if there is an option to turn of the exporting of formulas
to
> Excel, or alternatively not having it create named ranges for the
formulas.
> I have one report that frequently has over 5500 rows and 5 columns, the
last
> two are formulas for calculating percentage of the total.
> This results in around 20000 named ranges which makes it incredibly slow
> (15-20 seconds) to open the workbook.
> Thanks
> Phill
>|||So how do I specify the OmitFormulas device info in a subscription?
Thanks
Phill
"Fang Wang (MSFT)" <fangw@.microsoft.com> wrote in message
news:Op1S2ZJYEHA.2812@.TK2MSFTNGP11.phx.gbl...
> You can set OmitFormulas in device info to turn off formulas.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_dc_v1_4d0x.asp
> Fang Wang (MSFT)
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
> news:%23oti9n8XEHA.1000@.TK2MSFTNGP12.phx.gbl...
> > Hi All
> >
> > Just wondering if there is an option to turn of the exporting of
formulas
> to
> > Excel, or alternatively not having it create named ranges for the
> formulas.
> >
> > I have one report that frequently has over 5500 rows and 5 columns, the
> last
> > two are formulas for calculating percentage of the total.
> >
> > This results in around 20000 named ranges which makes it incredibly slow
> > (15-20 seconds) to open the workbook.
> >
> > Thanks
> > Phill
> >
> >
>

Excel Export Troubles

I have a local report that I am trying to export to Excel. It uses a stored procedure( SQL 2005) as the data source. The stored procedure runs in 2 seconds and returns about 8000 rows. The export to excel never completes(does work if I limit the number of rows returned). I have set the executionTimeout="36000" in the web.config and set the table adapters Command timeout in the code high enough so that I am not getting timeout errors. The export to PDF works, though takes a few minutes. Looking for any thoughts or ideas on why Excel is so slow.

Here is one idea. Use a SqlDataAdapter and fill a DataSet using your Store Proc then use this free dll to export the DataSet data into CSV file. Excel can easily read the csv and you can format the report in any way you need to.

Here is the link: http://apriendavisualstudio.net/?p=23

Try it, it very simple nothing fancy. It works for me.

sql

Excel Export Report Footer Problem

We have a footer in our reports which displays a disclaimer in a textbox.
The disclaimer shows up fine when exported to PDF, but when exported to Excel
it chops off everything but the first 255 chars. Can someone explain why
this is happening?Frank,
How do you hide Footer for Browser/Web View and enable it only for Email
Subscription ( Email / Pdf / MHTML ) format. Is it possible to add Disclaimer
text on the Create Subscription Web method.
Let me know pls.
Thanks!
"Frank Hoover" wrote:
> We have a footer in our reports which displays a disclaimer in a textbox.
> The disclaimer shows up fine when exported to PDF, but when exported to Excel
> it chops off everything but the first 255 chars. Can someone explain why
> this is happening?|||I read in the documentation that excel has a limit of 255 characters for the
footer - looks like you hit the limit.
"Frank Hoover" wrote:
> We have a footer in our reports which displays a disclaimer in a textbox.
> The disclaimer shows up fine when exported to PDF, but when exported to Excel
> it chops off everything but the first 255 chars. Can someone explain why
> this is happening?

Excel export problems

Hi,

I am a beginner using Sql server 2000 with visualstudionet 2003. When I try to export a report to excel spreadsheet. some of the page headers are displaying in separate row. page headers seems to align perfectly with the table columns and they seem fine in preview. Can someone help me with this issue.

Thanks

Bhavya

How are you doing the export ?

Jens K. Suessmeyer.

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

I am exporting from Report Manager. Thanks for the resposne.

Bhavya

|||Its hard to tell without having you report design at hand, my suggestion would be to inspect the controls used one by one and eventually create a new report with importing the controls one by one.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Excel export problem

Maybe others have had this similar problem and can give me a hint on how to resolve it...
I have a text box across the top of a report that is causing merged columns when the report is exported to Excel. It's making my users unable to sort the exported table without great difficulty.
What options may I have for putting a title along the top and still now have to worry about the merged columns in Excel?
Thanks much in advance...Troy

Hello,

Excel renderer will try to preserve the layout of you report. As long as your Title will span multiple table columns (at design time) you will have the problem.

Can you use on single cell for your Title or split it between multiple rows?

Thank you,

Nico

Excel export problem

I have a report that the users want to export to Excel. One of the problems that I am having is that some of the cells in the excel export are renamed as _402, _404, etc. I want them to come in as B22, B24, etc. Any idea why this is happening and how I can stop it?

Joe

The Excel renderer gives unique names to cells that are the operands of formulas. Then, the formulas reference those unique names instead of the cell addresses. This is by design and there is currently no way to export live Excel formulas without using those unique names. That feature is on our list of hopeful features for a future release.

If you want to get rid of formula export entirely and just have the calculated values put into the cells, you can OmitFormulas DeviceInfo parameter:

http://msdn2.microsoft.com/en-us/library/ms159220.aspx

Best,
-Chris

|||

OK, I can see that, but how can I affect all Excel exports from the report viewer? Do I set a parameter in the report server config file? Thanks for the info.

Joe

Excel Export problem

Hello,
I am trying to export some reports to excel format, using Reporting Services
SP1. It contains an OLAP query and a matrix allowing drill-down.
Reporting services errors with:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
Object reference not set to an instance of an object.
An anyone help?
Thanks.
Ben.Can you post some code?
Dave
"Ben Mann" <BenMann@.discussions.microsoft.com> wrote in message
news:976C1E52-2D9F-4EA3-AC3E-1D7440B8FAB3@.microsoft.com...
> Hello,
> I am trying to export some reports to excel format, using Reporting
> Services
> SP1. It contains an OLAP query and a matrix allowing drill-down.
> Reporting services errors with:
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Object reference not set to an instance of an object.
> An anyone help?
> Thanks.
> Ben.|||Hello,
Im not sure how much help this might be, but this is the rdl source for the
report...
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<RightMargin>2.5cm</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<Top>1.82275cm</Top>
<rd:DefaultName>textbox3</rd:DefaultName>
<Height>0.5cm</Height>
<Width>9.75cm</Width>
<CanGrow>true</CanGrow>
<Value>="Team: " + Parameters!ReportTeam.Label</Value>
<Left>0.25cm</Left>
</Textbox>
<Textbox Name="ParameterDisplayYear">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<Top>1.32275cm</Top>
<Height>0.5cm</Height>
<Width>14cm</Width>
<CanGrow>true</CanGrow>
<Value>="Year: " + Parameters!ReportYear.Value</Value>
<Left>0.25cm</Left>
</Textbox>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>2.5cm</Height>
<ZIndex>1</ZIndex>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Measures_Leavers">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Measures_Leavers</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Measures_Leavers.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="Measures_Total_Sales">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Measures_Total_Sales</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Measures_Total_Sales.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>P</Format>
<FontFamily>Verdana</FontFamily>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>=iIF(Sum(Fields!Measures_Leavers.Value) /
Sum(Fields!Measures_Total_Sales.Value) > 0,
Sum(Fields!Measures_Leavers.Value) / Sum(Fields!Measures_Total_Sales.Value),
0)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.5cm</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>2.55291cm</Width>
</MatrixColumn>
<MatrixColumn>
<Width>2.75cm</Width>
</MatrixColumn>
<MatrixColumn>
<Width>2.5cm</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>LeaversSummary</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_PopulationDate_Year">
<GroupExpressions>
<GroupExpression>=Fields!PopulationDate_Year.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="PopulationDate_Year">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#b5ddad</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>PopulationDate_Year</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!PopulationDate_Year.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.5cm</Height>
</ColumnGrouping>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_ColumnGroup2">
<GroupExpressions>
<GroupExpression>=Fields!PopulationDate_Quarter.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>PopulationDate_Year</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<ReportItems>
<Textbox Name="PopulationDate_Quarter">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#b5ddad</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>10</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!PopulationDate_Quarter.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.5cm</Height>
</ColumnGrouping>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_ColumnGroup3">
<GroupExpressions>
<GroupExpression>=Fields!PopulationDate_Month.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>PopulationDate_Quarter</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<ReportItems>
<Textbox Name="PopulationDate_Month">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#b5ddad</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>9</ZIndex>
<CanGrow>true</CanGrow>
<Value>=MonthName(Fields!PopulationDate_Month.Value,
true)</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.5cm</Height>
</ColumnGrouping>
<ColumnGrouping>
<Height>0.5cm</Height>
<StaticColumns>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#b5ddad</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Leavers</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#b5ddad</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Total Sales</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#b5ddad</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Leavers (%)</Value>
</Textbox>
</ReportItems>
</StaticColumn>
</StaticColumns>
</ColumnGrouping>
</ColumnGroupings>
<Width>14.05291cm</Width>
<Top>2.32275cm</Top>
<Left>0.25cm</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_Consultant_Sales_Team">
<GroupExpressions>
<GroupExpression>=Fields!Consultant_Sales_Team.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Consultant_Sales_Team">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#99ccff</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>Consultant_Sales_Team</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Consultant_Sales_Team.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>2.25cm</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup2">
<GroupExpressions>
<GroupExpression>=Fields!Consultant_Name.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>Consultant_Sales_Team</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<ReportItems>
<Textbox Name="Consultant_Name">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#99ccff</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!Consultant_Name.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>2.75cm</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup3">
<GroupExpressions>
<GroupExpression>=Fields!Consultant_Post_Code_Area.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>Consultant_Name</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<ReportItems>
<Textbox Name="Consultant_PostcodeArea">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#99ccff</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!Consultant_Post_Code_Area.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1.25cm</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
<Image Name="image1">
<MIMEType />
<Height>1.32275cm</Height>
<Width>7.93651cm</Width>
<Source>External</Source>
<Style />
<Value>leaverssummarybyteam.gif</Value>
<Left>0.25cm</Left>
<ToolTip>Leavers Summary by Team</ToolTip>
<Sizing>AutoSize</Sizing>
</Image>
</ReportItems>
<Style />
<Height>5.25cm</Height>
<ColumnSpacing>1cm</ColumnSpacing>
</Body>
<TopMargin>2.5cm</TopMargin>
<DataSources>
<DataSource Name="MIS">
<rd:DataSourceID>922803da-5239-4648-8a28-ec0db0f80d99</rd:DataSourceID>
<DataSourceReference>MIS</DataSourceReference>
</DataSource>
</DataSources>
<Width>14.75cm</Width>
<DataSets>
<DataSet Name="LeaversSummary">
<Fields>
<Field Name="PopulationDate_Year">
<DataField>[PopulationDate].[Year].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PopulationDate_Quarter">
<DataField>[PopulationDate].[Quarter].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PopulationDate_Month">
<DataField>[PopulationDate].[Month].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Consultant_Sales_Team">
<DataField>[Consultant].[Sales Team].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Consultant_Type">
<DataField>[Consultant].[Type].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Consultant_Name">
<DataField>[Consultant].[Name].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Consultant_Post_Code_Area">
<DataField>[Consultant].[Post Code
Area].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_Leavers">
<DataField>[Measures].[Leavers]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_Total_Sales">
<DataField>[Measures].[Total Sales]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures__Leavers">
<DataField>[Measures].[%Leavers]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>MIS</DataSourceName>
<CommandText>= "SELECT { [Measures].[Leavers], [Measures].[Total
Sales], [Measures].[%Leavers] } ON COLUMNS, { Descendants([Consultant].[" +
Parameters!ReportTeam.Value + "], [Consultant].[Post Code Area], LEAVES) } ON
ROWS, { Descendants ([PopulationDate].[All PopulationDate].[" +
Parameters!ReportYear.Value + "], [PopulationDate].[Month], LEAVES)} ON PAGES
FROM PopulationActualAndSalesTarget"</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>2.5cm</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<PageHeight>29.7cm</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<PageWidth>21cm</PageWidth>
<rd:ReportID>d36619a3-0ff9-4f79-b9a9-52cd0865cb2c</rd:ReportID>
<BottomMargin>2.5cm</BottomMargin>
<ReportParameters>
<ReportParameter Name="ReportYear">
<DataType>String</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>Year</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>2004</Value>
</ParameterValue>
<ParameterValue>
<Value>2003</Value>
</ParameterValue>
<ParameterValue>
<Value>2002</Value>
</ParameterValue>
<ParameterValue>
<Value>2001</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="ReportTeam">
<DataType>String</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>Team</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>All Consultant</Value>
<Label>All Teams</Label>
</ParameterValue>
<ParameterValue>
<Value>Team 1</Value>
</ParameterValue>
<ParameterValue>
<Value>Team 2</Value>
</ParameterValue>
<ParameterValue>
<Value>Team 3</Value>
</ParameterValue>
<ParameterValue>
<Value>Team 4</Value>
</ParameterValue>
<ParameterValue>
<Value>Team 5</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>
Thanks.
Ben.
"Yoshi" wrote:
> Can you post some code?
> Dave
> "Ben Mann" <BenMann@.discussions.microsoft.com> wrote in message
> news:976C1E52-2D9F-4EA3-AC3E-1D7440B8FAB3@.microsoft.com...
> > Hello,
> >
> > I am trying to export some reports to excel format, using Reporting
> > Services
> > SP1. It contains an OLAP query and a matrix allowing drill-down.
> >
> > Reporting services errors with:
> > Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown. (rrRenderingError) Get Online Help
> > Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown.
> > Object reference not set to an instance of an object.
> >
> > An anyone help?
> >
> > Thanks.
> >
> > Ben.
>
>sql