Exceeding Excel Row Limit

We have a report that returns more than 65,000 rows that we need to render to
Excel. Is there a way to make RS parse the data into multiple worksheets, or
will we need to split the data into more than one report for rendering?I don't know the answer to your question, but why do you have such a
large report anyway? You're not the only one with such a monster, so
there must be some reason for it.. however I cannot think what it might
be. Please help me understand this...|||Good question! First of all (and probably most importantly), the users of
this report are very fond of Excel, and what the users want, the users get!
Secondly, these reports deal with telephone number data. It is common for a
user to need a list of all telephone numbers owned by a customer (filtered by
certain criteria, as all numbers can easily number in the hundreds of
thousands) in a format that is easily understood and comforting.
Aileen
"timseal" wrote:
> I don't know the answer to your question, but why do you have such a
> large report anyway? You're not the only one with such a monster, so
> there must be some reason for it.. however I cannot think what it might
> be. Please help me understand this...
>|||No, there is no way to do this. Plus, you are better off to not export as
Excel but to export as CSV. The current export uses Unicode which Excel
doesn't know how to handle. So I put in a link and use jump to URL.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Very nice and very fast.
If there is some way to split up the numbers then have a report with no
data, just several textboxes which link to a subset of the data (which will
be less than 65,000).
Also, I think that the most recent Excel takes more than 65,000 rows.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aileen" <Aileen@.discussions.microsoft.com> wrote in message
news:AFA747C3-F913-490B-BD88-E78284B30E27@.microsoft.com...
> We have a report that returns more than 65,000 rows that we need to render
> to
> Excel. Is there a way to make RS parse the data into multiple worksheets,
> or
> will we need to split the data into more than one report for rendering?
>|||XL 2003 takes 65,536 rows. No way to simply put > 65k rows in another
worksheet? Using a .csv really doesn't do much, since .csv is typically
registered to XL anyway, so clicking it to open will cause it to try to load
into XL.
regards
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OCiLzuenFHA.3288@.TK2MSFTNGP09.phx.gbl...
> No, there is no way to do this. Plus, you are better off to not export as
> Excel but to export as CSV. The current export uses Unicode which Excel
> doesn't know how to handle. So I put in a link and use jump to URL.
> Here is an example of a Jump to URL link I use. This causes Excel to come
> up with the data in a separate window:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> Very nice and very fast.
> If there is some way to split up the numbers then have a report with no
> data, just several textboxes which link to a subset of the data (which
> will be less than 65,000).
> Also, I think that the most recent Excel takes more than 65,000 rows.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
>
> "Aileen" <Aileen@.discussions.microsoft.com> wrote in message
> news:AFA747C3-F913-490B-BD88-E78284B30E27@.microsoft.com...
>> We have a report that returns more than 65,000 rows that we need to
>> render to
>> Excel. Is there a way to make RS parse the data into multiple
>> worksheets, or
>> will we need to split the data into more than one report for rendering?
>|||The CSV format is out, the users aren't comfortable with that. Good to know
about the jump to URL, we'll use that. Also, the idea to divide the data is
one we were considering if we couldn't just dump all data at once, we'll
probably go with that.
Thank you.
"Bruce L-C [MVP]" wrote:
> No, there is no way to do this. Plus, you are better off to not export as
> Excel but to export as CSV. The current export uses Unicode which Excel
> doesn't know how to handle. So I put in a link and use jump to URL.
> Here is an example of a Jump to URL link I use. This causes Excel to come up
> with the data in a separate window:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> Very nice and very fast.
> If there is some way to split up the numbers then have a report with no
> data, just several textboxes which link to a subset of the data (which will
> be less than 65,000).
> Also, I think that the most recent Excel takes more than 65,000 rows.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services|||This is transparent to the user. You do a jump to URL using CSV ASCII and it
comes right up in an Excel worksheet. They don't have to do anything. As far
as the users know it is Excel.
Have you tried exporting as Excel versus CSV? We are talking probably an
order of magnitude difference in speed (or more). Depending on what you are
doing it can make the difference between it being usable and not. When you
do CSV as an export and it does it as unicode it puts all the data in one
column. When you do it as CSV ASCII the sheet comes right up.
Even very simple data will take a very long time for 65,000 rows. I suggest
you do some timing to see what I am talking about.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aileen" <Aileen@.discussions.microsoft.com> wrote in message
news:CEDC0BEE-F67F-4BD3-A87F-117F9F8C0074@.microsoft.com...
> The CSV format is out, the users aren't comfortable with that. Good to
> know
> about the jump to URL, we'll use that. Also, the idea to divide the data
> is
> one we were considering if we couldn't just dump all data at once, we'll
> probably go with that.
> Thank you.
>
> "Bruce L-C [MVP]" wrote:
>> No, there is no way to do this. Plus, you are better off to not export as
>> Excel but to export as CSV. The current export uses Unicode which Excel
>> doesn't know how to handle. So I put in a link and use jump to URL.
>> Here is an example of a Jump to URL link I use. This causes Excel to come
>> up
>> with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> Very nice and very fast.
>> If there is some way to split up the numbers then have a report with no
>> data, just several textboxes which link to a subset of the data (which
>> will
>> be less than 65,000).
>> Also, I think that the most recent Excel takes more than 65,000 rows.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services|||That is the point (that CSV is registered to Excel). I want Excel to come up
with the CSV data. I just want it to come up fast when there is lots of
data.
And no, there is no way to sell it to put the additional rows into another
spreadsheet.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kizzy" <kizzy@.azstarnet.com> wrote in message
news:%23RyCDzenFHA.3936@.TK2MSFTNGP10.phx.gbl...
> XL 2003 takes 65,536 rows. No way to simply put > 65k rows in another
> worksheet? Using a .csv really doesn't do much, since .csv is typically
> registered to XL anyway, so clicking it to open will cause it to try to
> load into XL.
> regards
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OCiLzuenFHA.3288@.TK2MSFTNGP09.phx.gbl...
>> No, there is no way to do this. Plus, you are better off to not export as
>> Excel but to export as CSV. The current export uses Unicode which Excel
>> doesn't know how to handle. So I put in a link and use jump to URL.
>> Here is an example of a Jump to URL link I use. This causes Excel to come
>> up with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> Very nice and very fast.
>> If there is some way to split up the numbers then have a report with no
>> data, just several textboxes which link to a subset of the data (which
>> will be less than 65,000).
>> Also, I think that the most recent Excel takes more than 65,000 rows.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>>
>> "Aileen" <Aileen@.discussions.microsoft.com> wrote in message
>> news:AFA747C3-F913-490B-BD88-E78284B30E27@.microsoft.com...
>> We have a report that returns more than 65,000 rows that we need to
>> render to
>> Excel. Is there a way to make RS parse the data into multiple
>> worksheets, or
>> will we need to split the data into more than one report for rendering?
>>
>|||Personally, I would find a telephone directory search application to be
more comforting. Cheaper and easier for all concerned. I bet you
could have photocopied it all by now, but that's a ridiculous idea,
isn't it?|||I realize it comes up in Excel, but I haven't tried exporting using CSV as I
assumed it would have the same problem (and the users tend to freak over
minor issues and the .csv extention might trouble them). Definitely good to
know, I'll see if that will work for us.
Thanks again.
"Bruce L-C [MVP]" wrote:
> This is transparent to the user. You do a jump to URL using CSV ASCII and it
> comes right up in an Excel worksheet. They don't have to do anything. As far
> as the users know it is Excel.
> Have you tried exporting as Excel versus CSV? We are talking probably an
> order of magnitude difference in speed (or more). Depending on what you are
> doing it can make the difference between it being usable and not. When you
> do CSV as an export and it does it as unicode it puts all the data in one
> column. When you do it as CSV ASCII the sheet comes right up.
> Even very simple data will take a very long time for 65,000 rows. I suggest
> you do some timing to see what I am talking about.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services|||We do have an application with search functionality. Unfortunately the
searching process would frequently kill our application server due to the
volume involved, which is why we went to RS in the first place - we could
move the straight search/view to another server and leave the app available
for its intended purpose, which is editing the telephone number data.
"timseal" wrote:
> Personally, I would find a telephone directory search application to be
> more comforting. Cheaper and easier for all concerned. I bet you
> could have photocopied it all by now, but that's a ridiculous idea,
> isn't it?|||If you can group the data in a way that will put a limit of lines per group
you can then break the report up into multiple worksheets.
First put the grouping on and the put a page break at the end of the group
and it will split the data onto different worksheets.
"Aileen" wrote:
> I realize it comes up in Excel, but I haven't tried exporting using CSV as I
> assumed it would have the same problem (and the users tend to freak over
> minor issues and the .csv extention might trouble them). Definitely good to
> know, I'll see if that will work for us.
> Thanks again.
> "Bruce L-C [MVP]" wrote:
> > This is transparent to the user. You do a jump to URL using CSV ASCII and it
> > comes right up in an Excel worksheet. They don't have to do anything. As far
> > as the users know it is Excel.
> >
> > Have you tried exporting as Excel versus CSV? We are talking probably an
> > order of magnitude difference in speed (or more). Depending on what you are
> > doing it can make the difference between it being usable and not. When you
> > do CSV as an export and it does it as unicode it puts all the data in one
> > column. When you do it as CSV ASCII the sheet comes right up.
> >
> > Even very simple data will take a very long time for 65,000 rows. I suggest
> > you do some timing to see what I am talking about.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
>|||I ran into same problem this what I did to fix:
In the data I found something to group the data on. In my case it was Month.
Edit the new group and put a page break after or before the group. This will
Force a new page and when converted to excel it will put that month in its
own tab.
In you case You may be able to do location or department.
Hope this helps.
"Aileen" wrote:
> We have a report that returns more than 65,000 rows that we need to render to
> Excel. Is there a way to make RS parse the data into multiple worksheets, or
> will we need to split the data into more than one report for rendering?
>|||how about adding a page break?
"jc82196" <jc82196@.discussions.microsoft.com> wrote in message
news:05B1206C-7C90-42D7-B8F2-737CB21B56AC@.microsoft.com...
:I ran into same problem this what I did to fix:
: In the data I found something to group the data on. In my case it was
Month.
: Edit the new group and put a page break after or before the group. This
will
: Force a new page and when converted to excel it will put that month in its
: own tab.
: In you case You may be able to do location or department.
:
: Hope this helps.
:
: "Aileen" wrote:
:
: > We have a report that returns more than 65,000 rows that we need to
render to
: > Excel. Is there a way to make RS parse the data into multiple
worksheets, or
: > will we need to split the data into more than one report for rendering?
: >|||If you use the document map feature with your grouping you could break it up
to separate tabs very easily.
However, I think you're asking a bit much from a report generator. This is
not so much a report as a data dump.
jc82196 wrote:
>I ran into same problem this what I did to fix:
>In the data I found something to group the data on. In my case it was Month.
>Edit the new group and put a page break after or before the group. This will
>Force a new page and when converted to excel it will put that month in its
>own tab.
>In you case You may be able to do location or department.
>Hope this helps.
>> We have a report that returns more than 65,000 rows that we need to render to
>> Excel. Is there a way to make RS parse the data into multiple worksheets, or
>> will we need to split the data into more than one report for rendering?
Message posted via http://www.sqlmonster.com