Wednesday, February 15, 2012

Excel Issue

Does Microsoft have any plans for an excel - add on to Reporting Services?
I love the product, but my users hate the way RS renders to excel. There are
merged cells - and addition blank columns between the table columns. Sorting
is therefore a nightmare. My users would prefer reports from a visual basic
front end with all data coded to excel -- something I was hoping Reporting
Services would resolve. It has been my experience that Excel is every
accountants #1 software, it is to bad microsoft didn't take this into
consideration when developing RS.You can design reports to export properly to Excel.
What I do is the following. I add additional header rows and then merge
either the whole row together of a couple of cells and then put in what I
normally would have put in a textbox. You would think that merging cells
together this way would cause a problem with merged cells in Excel but it
does not. If you select your header and your data you can sort, while if you
have exported with a textbox above the table you get the problem with merged
cells when trying to sort.
Below is another solution from another poster. I haven't tried it but it
seems reasonable:
>>>>>>>>>>
Earlier this year I was trying to find a way around the merged cell issue.
(RS 2000 reports exported to Excel may not be sortable due to "merged
cells"). There were some newsgroup messages with ideas. I eventually decided
on this design workaround. Hopefully this can save someone else from doing
this research..
The Table can be the full width or slightly less of your page. I have best
results when it is less than the full width. I don't know if you can have
multiple tables in the body and still sort ok - I doubt it. Any/all other
objects in the body (logo images, textboxes) MUST be full width - from
leftmost edge to rightmost edge. Images can use Sizing = FitProportional to
appear ok. If any of those objects in the body is even slightly less than
full width (such as two text boxes adjacent to each other on the same row,
or a logo in the upper left and the title textbox in the upper right on the
same row), I've found that you will get merged cells in Excel and cannot
sort. I also tried all kinds of careful alignments of objects (matching the
columns in the table, etc) but it still caused merged cells. I don't believe
it matters what you have in the page header or footer - I can put multiple
textboxes on the same row in my page footer and it works ok.
Hope that helps,
Steve Nyberg
>>>>>>>>>>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"coding4life" <coding4life@.discussions.microsoft.com> wrote in message
news:1004438F-9090-4D8B-B900-090F83227184@.microsoft.com...
> Does Microsoft have any plans for an excel - add on to Reporting
> Services?
> I love the product, but my users hate the way RS renders to excel. There
> are
> merged cells - and addition blank columns between the table columns.
> Sorting
> is therefore a nightmare. My users would prefer reports from a visual
> basic
> front end with all data coded to excel -- something I was hoping Reporting
> Services would resolve. It has been my experience that Excel is every
> accountants #1 software, it is to bad microsoft didn't take this into
> consideration when developing RS.
>

No comments:

Post a Comment