Running large reports off SQL server (i.e. Crystal Reports)

From: Lucas Tam (REMOVEnntp_at_rogers.com)
Date: 09/19/04 

Date: Sun, 19 Sep 2004 23:37:01 GMT

Hi all,

I need to run large reports off a SQL server using an application like
Crystal Reports. Unfortunately Crystal Reports for VS.NET runs into
memory problems if the result set is >50,000 rows. As such, I need to do
all my calculation, collating, etc on the server side and then send a
nicely formatted dataset to Crystal.

Typically how does one go about building queries that calculate data
without cursors? There wouldn't happen to be a book Building Report
Queries for Dummies?

For example, I have a table which has a bunch of records as follows:

Date Value
01/01/2004 10:55:00AM 1
01/01/2004 11:56:00AM 2
01/01/2004 13:24:00AM 4
01/01/2004 12:34:00AM 7
01/01/2004 11:20:00AM 9

And I need to report on 1/2 hours, Daily, Monthly, Yearly, etc.

If I use the middle tier to calcualte such data, I would need large
amounts of memory to replicate the tables while I do the calculations...
wouldn't using cursors in SQL server be more effecient if all I'm doing
is 90% read operations and 10% writes (writes only occur when the
reporting DB is syncronizing with the production DB)?

In anycase, how does one tackle report queries? Are there any tutorials
online with a set of templates or examples for different style of
calculation type queries?

Thanks. 

-- 
Lucas Tam (REMOVEnntp@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

Relevant Pages

  • RE: Alternative program calls
    ... Yes, exactly, I want to pick a table and close any queries and reports, etc. ... And, in this particular situation I am within a macro, ... or is it an issule of permissions at the SQL server or file ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL Server not recognizing aliased table
    ... SQL Server backends. ... So all my queries have to be Access queries against ... However, when I try to run the reports query, I get an error message: ... Almost all of our mdb's hit the SQL Server for reports. ...
    (microsoft.public.access.queries)
  • Re: Counting, summing, matching, summing again...
    ... Create Views, Stored Procs, and UDF's on your Sql server to handle ALL of ... Then use Access ADO, DAO, PassThru queries, or ... > I build reports that show data such as how many people from a census table ... I learned to do this all with queries ...
    (microsoft.public.access.forms)
  • Re: Access DB Upgrade
    ... I have an existing Access DB that consists of tables, forms, queries and reports. ... I need to get it online and into SQL server, what is the quickest and best way to do this? ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Future of ADPs
    ... SQL Server 2005 Express and ASP.Net, ... so I learned SQL-Server and ADP projects. ... that Microsoft really doesn't have an ... Reports to design your reports. ...
    (microsoft.public.access.adp.sqlserver)