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
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Thursday, March 29, 2012
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 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.
> >> >
> >>
> >>
> >>
>
>
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.
> >> >
> >>
> >>
> >>
>
>
Tuesday, March 27, 2012
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
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,
> >>
> >>
> >>
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 Large File Size
When I export a matrix report to Excel (65 rows by 228 columns, about 15000
cells) the file size exceeds 15mb.
As the Excel file is sent out daily as an e-mail attachment in an SRSS
subscription I receive a lot of complaints about this.
Other export formats for the same report such as Web archive are tiny, but
the subscribers insist on Excel.
I have applied the latest service packs to SQL Server 2005.
Any tips/workarounds would be appreciated.
Regards
Ian OlsenOn Mar 22, 7:47 am, Wrighty <wrig...@.donotspam.com> wrote:
> When I export a matrix report to Excel (65 rows by 228 columns, about 15000
> cells) the file size exceeds 15mb.
> As the Excel file is sent out daily as an e-mail attachment in an SRSS
> subscription I receive a lot of complaints about this.
> Other export formats for the same report such as Web archive are tiny, but
> the subscribers insist on Excel.
> I have applied the latest service packs to SQL Server 2005.
> Any tips/workarounds would be appreciated.
> Regards
> Ian Olsen
The only other options are to use a compression tool to zip the file
before sending it (most likely, not a snapshot option, but a custom
one) or to export to CSV instead (which might save a little file size
but you loose formatting). The only other thing would be to have the
users save a web archive, etc as an excel file. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer
cells) the file size exceeds 15mb.
As the Excel file is sent out daily as an e-mail attachment in an SRSS
subscription I receive a lot of complaints about this.
Other export formats for the same report such as Web archive are tiny, but
the subscribers insist on Excel.
I have applied the latest service packs to SQL Server 2005.
Any tips/workarounds would be appreciated.
Regards
Ian OlsenOn Mar 22, 7:47 am, Wrighty <wrig...@.donotspam.com> wrote:
> When I export a matrix report to Excel (65 rows by 228 columns, about 15000
> cells) the file size exceeds 15mb.
> As the Excel file is sent out daily as an e-mail attachment in an SRSS
> subscription I receive a lot of complaints about this.
> Other export formats for the same report such as Web archive are tiny, but
> the subscribers insist on Excel.
> I have applied the latest service packs to SQL Server 2005.
> Any tips/workarounds would be appreciated.
> Regards
> Ian Olsen
The only other options are to use a compression tool to zip the file
before sending it (most likely, not a snapshot option, but a custom
one) or to export to CSV instead (which might save a little file size
but you loose formatting). The only other thing would be to have the
users save a web archive, etc as an excel file. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer
Excel Export - How to stop Merged Columns?
Hello All,
I am having a lot of fun when exporting to excel. When there is a control (image etc) above a table and the sides of the image stop half way through a column this creates a merged column in excel. This creates headaches for clients when trying to chart this data. Is there anyway around this? Or any secrets?
I placed the image into a header and set the device info url to "rc:SimplePageHeaders=true" this helped by placing the header into an excel header upon rendering BUT there was no image in there'
Thanks in advance
SheaThat is one of the drawbacks of Excel exporting is the columns need to line up. Try this. If the image is wider than the first column and ends in the middle of the second column. insert a column between the first and second columns and make it so its edge lines up with the edge of the image. reduce the width of the now third column so the new column and it are the same width as the original second column. Then merge the two cells in the table. That may help with your problem.
"Shea Strickland" wrote:
> Hello All,
> I am having a lot of fun when exporting to excel. When there is a control (image etc) above a table and the sides of the image stop half way through a column this creates a merged column in excel. This creates headaches for clients when trying to chart this data. Is there anyway around this? Or any secrets?
> I placed the image into a header and set the device info url to "rc:SimplePageHeaders=true" this helped by placing the header into an excel header upon rendering BUT there was no image in there'
> Thanks in advance
> Sheasql
I am having a lot of fun when exporting to excel. When there is a control (image etc) above a table and the sides of the image stop half way through a column this creates a merged column in excel. This creates headaches for clients when trying to chart this data. Is there anyway around this? Or any secrets?
I placed the image into a header and set the device info url to "rc:SimplePageHeaders=true" this helped by placing the header into an excel header upon rendering BUT there was no image in there'
Thanks in advance
SheaThat is one of the drawbacks of Excel exporting is the columns need to line up. Try this. If the image is wider than the first column and ends in the middle of the second column. insert a column between the first and second columns and make it so its edge lines up with the edge of the image. reduce the width of the now third column so the new column and it are the same width as the original second column. Then merge the two cells in the table. That may help with your problem.
"Shea Strickland" wrote:
> Hello All,
> I am having a lot of fun when exporting to excel. When there is a control (image etc) above a table and the sides of the image stop half way through a column this creates a merged column in excel. This creates headaches for clients when trying to chart this data. Is there anyway around this? Or any secrets?
> I placed the image into a header and set the device info url to "rc:SimplePageHeaders=true" this helped by placing the header into an excel header upon rendering BUT there was no image in there'
> Thanks in advance
> Sheasql
Friday, March 23, 2012
excel data pivot/unpivot to sql server 2005 table
The following is a SAMPLE data from an excel spreadsheet. This SAMPLE data has many other fields as date. Here I have only used two date columns i.e. 28 Dec 2006 and 29 Dec 2006
This data needs to be exported into sql server 2005 table which has the fields below where I have placed the data into a table.
How can this be done please?
data:
Ref Sector Name 28 Dec 2006 29 Dec 2006
1 Sovereign RUSSIA 05 null 173.21
2 Sovereign RUSSIA 07 102.99 102.22
3 Sovereign RUSSIA 10 114.33 104.63
4 Sovereign RUSSIA 18 115.50 145.50
...
sql server table
create table tblData
(
DataID int,
Ref int,
Sector varchar(20),
Name varchar(20),
Date datetime,
value decimal(6,2)
)
DataID Ref Sector Name Date value
1 1 Sovereign RUSSIA 05 28 Dec 2006 null
2 1 Sovereign RUSSIA 05 29 Dec 2006 173.21
3 2 Sovereign RUSSIA 07 28 Dec 2006 102.99
4 2 Sovereign RUSSIA 07 29 Dec 2006 102.22
5 3 Sovereign RUSSIA 10 28 Dec 2006 114.33
6 3 Sovereign RUSSIA 10 29 Dec 2006 104.63
7 4 Sovereign RUSSIA 18 28 Dec 2006 115.50
8 4 Sovereign RUSSIA 18 29 Dec 2006 145.50
...
First import the data into temp table..
Then use the UNPIVOT operator to get the required data..
Here the complete query,
Code Snippet
/*
create table tblData
(
DataID int identity(1,1),
Ref int,
Sector varchar(20),
Name varchar(20),
Date datetime,
value decimal(6,2)
)
*/
Code Snippet
Create Table #tempdata (
[Ref] int ,
[Sector] Varchar(100) ,
[Name] Varchar(100) ,
[28-Dec-2006] float ,
[29-Dec-2006] float
);
Insert Into #tempdata Values('1','Sovereign','RUSSIA05',NULL,'173.21');
Insert Into #tempdata Values('2','Sovereign','RUSSIA07','102.99','102.22');
Insert Into #tempdata Values('3','Sovereign','RUSSIA10','114.33','104.63');
Insert Into #tempdata Values('4','Sovereign','RUSSIA18','115.50','145.50');
Go
Code Snippet
Declare @.UnPivotColumns as varchar(max)
Select@.UnPivotColumns = ''
Select @.UnPivotColumns = @.UnPivotColumns + ',[' + name + ']' from tempdb.Sys.columns
Where object_id = object_id('tempdb..#tempdata')
and column_id > 3
Select@.UnPivotColumns = Substring(@.UnPivotColumns, 2, len(@.UnPivotColumns)-1)
Insert Into tblData(ref,sector,name,date,value)
Exec ('Select ref,sector,Name,cast(date as datetime),[value]
from #tempdata unpivot([value] for [date]
in (' + @.UnPivotColumns + ') )as uptv')
Drop table #tempdata;
--To fill the missed values when the value is null
Insert Into tblData
select
fulldata.*,
tbl.value
from
(
select * from
(select distinct ref,sector,namefrom tblData) data
cross join (select distinctDatefrom tblData) dates
) fulldata
left outer join tblData tbl
on tbl.ref = fulldata.ref
and tbl.sector = fulldata.sector
and tbl.name = fulldata.name
and tbl.Date = fulldata.date
where
tbl.Date is null
Select * from tblData
Excel columns to a temp table in SQL
I would like to transfer all the cells of an excel sheet to a temp table
in such a way that there is no data loss and no rows of columns of data
are made to Null or omitted.
I am keeping the datatypes of columns in the temp table as Varchar(255).
Thanks
Clayton.Use the Import Export wizard to do this for you. It will create an SSIS package that you can re-use and adapt to meet your needs. If it needs amending then please reply here asking for help and please try and be more specific.
in such a way that there is no data loss and no rows of columns of data
are made to Null or omitted.
I am keeping the datatypes of columns in the temp table as Varchar(255).
Thanks
Clayton.Use the Import Export wizard to do this for you. It will create an SSIS package that you can re-use and adapt to meet your needs. If it needs amending then please reply here asking for help and please try and be more specific.
Regards
-Jamie|||
I mean to say that when i transform a excel thru an excel source to a SQL destination database the problem is that the columns which are teh minority datatype in any row which contains a majority of integer numbers get passed
as NULL.how do i prevent this?
eg: suppose in a column named Year i have and i enter 2000,20004,2005,2006,abc,2003,2002
then this column with abc goes as blank i want to prevent this and i want abc to go to the SQL Destination DB.
Thanks
Clayton
The wizard produces a SSIS package.
1. Open it up.
2. Open up the data-flow
3. Right-click on the source adapter and select "Show Advanced Editor..."
4. Click on the "Input and Output Properties" tab
5. Expand "Excel Source output"
You will see that the datatypes of the columns are defined in here. Under "Output Columns" change the datatypes to whatever you require (e.g. DT_STR with a langth of 255)
Hope this helps
-Jamie|||This is the oldest Excel driver problem in the book, and is documented in the BOL topic on the Excel Source (which is currently being expanded with still more "known issues" information).
If you would like to write to me directly, douglasl@.microsoft.com, I will send you a Word document that discusses at greater length all the potential known issues with Excel in SSIS.
Missing values. The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data types of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values in fields that contain data of the other type. Most cell formatting selections in the Excel worksheet do not affect this data type determination. As one possible solution, you can use an OLE DB Connection Manager instead of the Excel Connection Manager, and modify this behavior of the Excel ISAM driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties on the All page of the Connection Manager dialog box. Use a semicolon to separate this name-value pair from the Excel version specifier. For more information, see
-Doug
Monday, March 19, 2012
Example Project/Package for SQl Server 2005 "Text Extraction"
Does any kind person have a simple example Package for undertaking 'Text Extraction' on one or two columns of text data in a SQL Server table (the data is not in Unicode)?
Check out the sample @. http://aspalliance.com/889
With this example - users will be able to import sample data from a flat file to SQL Server 2005 database.
Let us know if you are looking for any other specific extraction examples.
Thanks,
Loonysan
What do you mean by "test extraction"? Are you talking about the TERM EXTRACTION component?
-Jamie
|||
Yes, sorry, "Term Extraction"!
|||Some Good Pointers for 'Term extraction'
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/datasol.asp (Check out the Example on Extracting Attributes)
http://www.sqlserverdatamining.com/DMCommunity/_Tutorials/688.aspx (Need to register for accessing this site - Registration is Free) - Tutorial on Text Classification using SQL Server 2005.
Thanks,
Loonysan
Subscribe to:
Posts (Atom)