|
|
You're in the
sql server reporting services
group:How can I cache just the first page of a report like in Crystal?
sql server reporting services:
brings back 100,000 records, I don't want Reporting Services to read all 100,000 records into memory and end up with 2000 report pages in cache. Crystal Reports caches just the first page (unless you explicitly request otherwise). It won't generate all 2000 pages...just the first. So, for a big report, that means perhaps 50 records out of a 100,000. If I want to see the second page, Crystal will retrieve the next 50 records. How can I do the same so it doesn't take 5 minutes just to see the first page of my report through Reporting Services?
I spoke to one of the authors of the Wrox book, "Professional SQL
Server Reporting Services" at an ISV seminar several months back and casually asked him the question I posted on this newsgroup. He said he knew exactly what I was trying to do and that I should use some technique ("paging," I think it's called) to process only 1 page of data. In Crystal Reports, the report streams the dataset so only one-page worth's records are pulled back, even if your query is SELECT TOP 100,000 FROM Whatever. It's only when you insert a Page X of Y, does Crystal have to figure out what page Y is (e.g. Page 2000) and then has to cache the other 99,000 + records to figure out Y. That's why Business Objects suggests you don't add a Page X of Y to a footer (just add Page X). Since I don't use Crystal anymore, I need to accomplish the same efficiency with Reporting Services in regards to caching data. Can someone please explain to me how to use the Reporting Services paging technique (or whatever it's called) to do what I want?
RS is not architected this way. What you want to do is not possible. It
surprises me that Crystal works this way. For consistancy these records have to be somewhere. I pull up 50 records and look at it for 15 seconds. For consistancy the rest of the records need to have been retrieved or cached somewhere or else a record could have been edited and should no longer be part of your resultset. Regardless, you need to rethink what you are doing. Some options you have: 1. Schedule the report to run at partiuclar times. The user will see the report from the snapshot. 2. Redesign to not have 100,000 records. One thing that RS is great at is drill through. Design the report to show more of a summary and then have links that the user clicks on to drill through to detail. A human does not review 100,000 records. It is useless for them. If what you are doing is exporting to another product then use some other technology to export it. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] <steve.pantazis@gmail.com> wrote in message news:1111190028.089994.292890@g14g2000cwa.googlegroups.com... > How do you cache just a portion of a dataset in a report? If my query > brings back 100,000 records, I don't want Reporting Services to read > all 100,000 records into memory and end up with 2000 report pages in > cache. Crystal Reports caches just the first page (unless you > explicitly request otherwise). It won't generate all 2000 pages...just > the first. So, for a big report, that means perhaps 50 records out of > a 100,000. If I want to see the second page, Crystal will retrieve the > next 50 records. How can I do the same so it doesn't take 5 minutes > just to see the first page of my report through Reporting Services? >
I am not aware of any way to do this. I do know that if you have any
grouping, sorting, etc that Reporting Services is doing it at the server and doing it in RAM. I believe what you are talking about has to do with web services. That via web services you can request a page at a time. This has nothing to do with what is happening at the server, just how much data it is streaming back. If you have 100,000 records it will still be handling them (in RAM). It takes different amount of resources depending on what format you render it to. PDF takes much more resources than HTML. Are you integrating with your own app? If so are you using web services or url integration? With URL integration or the web portal (report manager) there is no way to do this. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] <steve.pantazis@gmail.com> wrote in message news:1111195402.451008.50800@g14g2000cwa.googlegroups.com... >I spoke to one of the authors of the Wrox book, "Professional SQL > Server Reporting Services" at an ISV seminar several months back and > casually asked him the question I posted on this newsgroup. He said he > knew exactly what I was trying to do and that I should use some > technique ("paging," I think it's called) to process only 1 page of > data. In Crystal Reports, the report streams the dataset so only > one-page worth's records are pulled back, even if your query is SELECT > TOP 100,000 FROM Whatever. It's only when you insert a Page X of Y, > does Crystal have to figure out what page Y is (e.g. Page 2000) and > then has to cache the other 99,000 + records to figure out Y. That's > why Business Objects suggests you don't add a Page X of Y to a footer > (just add Page X). Since I don't use Crystal anymore, I need to > accomplish the same efficiency with Reporting Services in regards to > caching data. Can someone please explain to me how to use the > Reporting Services paging technique (or whatever it's called) to do > what I want? >
We serve up reports by calling the report server via a URL (e.g.
http://ServerName/ReportServer?ReportName) from a list of reports in our web-based application. We don't use the Report Server web service, except via a separate console app, which we use to deploy reports, hide parameters, and generate snapshots. Our .NET app uses web services, but only to handle the communication with our SQL database. We use the default Report Viewer that the Report Server uses to render reports. [quoted text, click to view] >From what you are saying, it seems we would need to write some custom code using the Reporting Services web service to prevent the entiredataset from being transfered to the client.
That is what I suspect but I don't know for sure. I suggest reading up on
the web services and see if it will do what you want. You definitely have more control with Web Services than you do with URL integration. But, you give up things too. For instance drill through. Plus, it takes more effort. You'll have to decide if the extra effort and loss of functionality is worth the gain. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] <steve.pantazis@gmail.com> wrote in message news:1111269685.315294.245210@o13g2000cwo.googlegroups.com... > We serve up reports by calling the report server via a URL (e.g. > http://ServerName/ReportServer?ReportName) from a list of reports in > our web-based application. We don't use the Report Server web service, > except via a separate console app, which we use to deploy reports, hide > parameters, and generate snapshots. Our .NET app uses web services, > but only to handle the communication with our SQL database. We use the > default Report Viewer that the Report Server uses to render reports. >>From what you are saying, it seems we would need to write some custom > code using the Reporting Services web service to prevent the entire > dataset from being transfered to the client. >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |