Results 1 to 3 of 3

Thread: Summary Of Data

  1. #1
    Join Date
    Oct 2004
    Posts
    9

    Unanswered: Summary Of Data

    I have a data table on which I'm trying to create a new report. I already have three related reports, but none is sufficient. I'm not sure, though, if it's possible.

    The relevant data looks like this:

    Date
    clientid
    CommunityDistrict (abbreviated CD#)

    These rows are not unique.

    Report #1 (already working) shows me the number of client contacts for each date. At the end of the report, it shows the total.

    This is fairly simple to do: The report is sorted by date, and two counters count the rows. Counter one is shown at the end of each date, and then reset to 0 while counter two keeps accumulating.

    Report #2 Shows me what #1 does, but also adds a break out of the number for each CD#. This is done similarly to the above. For this one, I sort on CD# within the date, and I have 3 counter, the third one being used to show the number contacts within the CD for that date. The date shows, then on each line the amount of contacts for one CD#, then a total for the date. This repeats for each date. The report is not bad looking, and it's quite flexible, because it will work no matter how many CD#s are being served, and which ones were served in this time period.

    Report #3 Shows that same data, but in a grid like format. On each line, there is the date, the total contacts for the data, and then the contacts for each CD#. We have a preset number, and set of CD# that can be reported on, and there is always a number for each CD# - even if the number is 0. For this one, I have a counter for the date, then a counter for each of the CD#s and each one is shown for each row. The set of counters is hard coded. This is the main disadvantage of this report. It's much more compact than #2, and (according to my boss) visually appealing. The problem is that it's rigid. If there is ever a change in the CDs I have to change the report. It's not that big a deal to make the change, but it's not something that an end-user (even a reasonably well trained one) could do by himself. This is a real concern, because we've already had such changes, and it's an ongoing issue.

    What I am looking to do is create a report with the layout of #3 but the flexibility of #2. Is there any way to get the data into a form where this is possible?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's not exactly an SQL question, is it

    it's an application design problem

    this forum is for non-proprietary SQL

    your SQL is straightforward, even if your design problem (to show any number of CDs in a crosstab fashion) isn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Posts
    9
    Quote Originally Posted by r937
    that's not exactly an SQL question, is it

    it's an application design problem

    this forum is for non-proprietary SQL

    your SQL is straightforward, even if your design problem (to show any number of CDs in a crosstab fashion) isn't
    Well, that's what I was wondering about. I was hoping that it might be possible to use sql to get the data into a format that makes this a straightforward application design issue.

    I knew that it was a long shot. I figured that either I was overlooking something so obvious that I'd kick myself, or it couldn't be done. So, you're telling me it's the latter.

    <sigh> It wasn't what I WANTED to hear, but it was certainly helpful, since it will keep me from wasitng any more time barking up that tree.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •