Home > Archive > Visual Basic Crystal Reports > April 2005 > Grouping/totals question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Grouping/totals question
|
|
| Thomas Ruschak 2005-04-30, 9:01 am |
| Hi,
I've got a fairly simple report with a tough (to me) problem in it. I'm
using Crystal 8.5. (I also have Crystal 9 available, if it'd help to switch
to that)
There's a client table, and a G/L (general ledger) table. The entries
in the G/L table are organized into 'batches' based on a field value (batch
number). So, each client has zero, one, or more batches of entries in the
g/l table, each batch having 1 or more entries for that batch. The total
for a batch can be positive or negative, and I need to show any batch with a
positive or negative total, even if the total due for all batches for the
client is $0.
(Yes, I know, it's not a great design, but I didn't do it, and it wasn't
originally SQL-based anyway.... stay with me here :-)
Anyway, I've got a report grouped by client, then batch. What I need is
to print out for each client the batches they have, and what the total due
on them is. This isn't hard, using running totals, so I get something like
this:
Client 1
Batch 1 $-20
Batch 2 $30
---
$10
(if you can read that)
The problem is that if the client has no batches, I get this:
Client 1
---
$0
That's ugly, so I want to suppress the header/footer for the client if
that client has no batches with balances due.
So, I made a calculated field that's SUM( balance due, batch# ). Then I
made a running total to count that variable, accumulated when the batch#
changes, only if it's non-zero. This gives me the number of batches with a
non-zero total, and that works fine for the footer. However, it doesn't
work for the header, because that running total is not calculated soon
enough.
Is there a right way to do this sort of thing? I've tried messing with
the WhileReadingRecords/WhilePrintingRecords stuff, but I can't get it to
work. In the other report writer I use (R&R Report Writer), I can make a
total field "pre-processed" if I have to, which tells R&R to calculate the
damned thing BEFORE running the report. It's ugly & slow (since it has to
read the data an extra time), but it works. Is there any way to do this in
Crystal? (And yes, before you ask, there are compelling reasons I can't do
it in R&R, sorry)
If anyone has read this far, and can help, I'll be eternally grateful...
or pretty damn grateful at least :-) If there's a FAQ or tutorial somewhere
I should be reading instead, please let me know that.
Thanks,
Tom
| |
|
| Tom,
If you didn't do an outer join on the batch table, client records should not
be selected if there are no batches.
If you have an outer join, remove it.
HTH,
PJ
"Thomas Ruschak" <ruschak@wintek.com> wrote in message
news:1176e076epluq99@corp.supernews.com...
Hi,
I've got a fairly simple report with a tough (to me) problem in it. I'm
using Crystal 8.5. (I also have Crystal 9 available, if it'd help to switch
to that)
There's a client table, and a G/L (general ledger) table. The entries
in the G/L table are organized into 'batches' based on a field value (batch
number). So, each client has zero, one, or more batches of entries in the
g/l table, each batch having 1 or more entries for that batch. The total
for a batch can be positive or negative, and I need to show any batch with a
positive or negative total, even if the total due for all batches for the
client is $0.
(Yes, I know, it's not a great design, but I didn't do it, and it wasn't
originally SQL-based anyway.... stay with me here :-)
Anyway, I've got a report grouped by client, then batch. What I need is
to print out for each client the batches they have, and what the total due
on them is. This isn't hard, using running totals, so I get something like
this:
Client 1
Batch 1 $-20
Batch 2 $30
---
$10
(if you can read that)
The problem is that if the client has no batches, I get this:
Client 1
---
$0
That's ugly, so I want to suppress the header/footer for the client if
that client has no batches with balances due.
So, I made a calculated field that's SUM( balance due, batch# ). Then I
made a running total to count that variable, accumulated when the batch#
changes, only if it's non-zero. This gives me the number of batches with a
non-zero total, and that works fine for the footer. However, it doesn't
work for the header, because that running total is not calculated soon
enough.
Is there a right way to do this sort of thing? I've tried messing with
the WhileReadingRecords/WhilePrintingRecords stuff, but I can't get it to
work. In the other report writer I use (R&R Report Writer), I can make a
total field "pre-processed" if I have to, which tells R&R to calculate the
damned thing BEFORE running the report. It's ugly & slow (since it has to
read the data an extra time), but it works. Is there any way to do this in
Crystal? (And yes, before you ask, there are compelling reasons I can't do
it in R&R, sorry)
If anyone has read this far, and can help, I'll be eternally grateful...
or pretty damn grateful at least :-) If there's a FAQ or tutorial somewhere
I should be reading instead, please let me know that.
Thanks,
Tom
| |
| Thomas Ruschak 2005-04-30, 4:01 pm |
|
Unfortunately, that doesn't work. Even if the client has batches, if
there's nothing due for any of the individual batches, I don't want to print
the client.
That's what makes it hard -- I have to calculate the total due for each
batch of records, and if there's anything due for the batch as a whole, I
need to print the batch and its client.
For example:
client 1, batch 1, $1
client 1, batch 1, $-1
client 1 batch 2, $1
client 1, batch 3, $2
client 1, batch 3, $-1
client 2, batch 4, $1
client 2, batch 4, $-1
client 2, batch 5, $5
client 2, batch 5, $-3
client 2, batch 5, $-2
client 3, batch 6, $1
For this little set of data, I need to print the following:
client 1
batch 2, $1
batch 3, $1
client 3
batch 6, $1
Hopefully that's clearer. Even without an outer join, the data from
Client 2 will be in the result set. Nothing should be printed for client 2,
because it has no batches with outstanding balances, but how can I get
Crystal to recognize that? I need to know the balance due for each of
Client 2's batches before I print the header for Client 2, so I can decide
whether or not to suppress it.
Tom
"PJ" <pj.krueger@erols.com> wrote in message
news:OZEu3uYTFHA.2908@TK2MSFTNGP10.phx.gbl...
> Tom,
>
> If you didn't do an outer join on the batch table, client records should
> not
> be selected if there are no batches.
>
> If you have an outer join, remove it.
>
> HTH,
> PJ
>
>
> "Thomas Ruschak" <ruschak@wintek.com> wrote in message
> news:1176e076epluq99@corp.supernews.com...
> Hi,
>
> I've got a fairly simple report with a tough (to me) problem in it. I'm
> using Crystal 8.5. (I also have Crystal 9 available, if it'd help to
> switch
> to that)
>
> There's a client table, and a G/L (general ledger) table. The entries
> in the G/L table are organized into 'batches' based on a field value
> (batch
> number). So, each client has zero, one, or more batches of entries in the
> g/l table, each batch having 1 or more entries for that batch. The total
> for a batch can be positive or negative, and I need to show any batch with
> a
> positive or negative total, even if the total due for all batches for the
> client is $0.
>
> (Yes, I know, it's not a great design, but I didn't do it, and it
> wasn't
> originally SQL-based anyway.... stay with me here :-)
>
> Anyway, I've got a report grouped by client, then batch. What I need
> is
> to print out for each client the batches they have, and what the total due
> on them is. This isn't hard, using running totals, so I get something
> like
> this:
>
> Client 1
> Batch 1 $-20
> Batch 2 $30
> ---
> $10
>
> (if you can read that)
>
> The problem is that if the client has no batches, I get this:
>
> Client 1
> ---
> $0
>
> That's ugly, so I want to suppress the header/footer for the client if
> that client has no batches with balances due.
>
> So, I made a calculated field that's SUM( balance due, batch# ). Then
> I
> made a running total to count that variable, accumulated when the batch#
> changes, only if it's non-zero. This gives me the number of batches with
> a
> non-zero total, and that works fine for the footer. However, it doesn't
> work for the header, because that running total is not calculated soon
> enough.
>
> Is there a right way to do this sort of thing? I've tried messing with
> the WhileReadingRecords/WhilePrintingRecords stuff, but I can't get it to
> work. In the other report writer I use (R&R Report Writer), I can make a
> total field "pre-processed" if I have to, which tells R&R to calculate the
> damned thing BEFORE running the report. It's ugly & slow (since it has to
> read the data an extra time), but it works. Is there any way to do this
> in
> Crystal? (And yes, before you ask, there are compelling reasons I can't
> do
> it in R&R, sorry)
>
> If anyone has read this far, and can help, I'll be eternally
> grateful...
> or pretty damn grateful at least :-) If there's a FAQ or tutorial
> somewhere
> I should be reading instead, please let me know that.
>
> Thanks,
> Tom
>
>
>
| |
|
| If you can create a recordset with the client and the summed batch
information you could eliminate the unwanted data. Depending on the query
tool you are using it can be eliminated during the creation of the recordset
or in the selection to print. If the recordset has all client and batch
information, you would not need to worry about clients with no batches or
only 0 balance batches.
PJ
"Thomas Ruschak" <ruschak@wintek.com> wrote in message
news:1177c4rtfg6qa0@corp.supernews.com...
Unfortunately, that doesn't work. Even if the client has batches, if
there's nothing due for any of the individual batches, I don't want to print
the client.
That's what makes it hard -- I have to calculate the total due for each
batch of records, and if there's anything due for the batch as a whole, I
need to print the batch and its client.
For example:
client 1, batch 1, $1
client 1, batch 1, $-1
client 1 batch 2, $1
client 1, batch 3, $2
client 1, batch 3, $-1
client 2, batch 4, $1
client 2, batch 4, $-1
client 2, batch 5, $5
client 2, batch 5, $-3
client 2, batch 5, $-2
client 3, batch 6, $1
For this little set of data, I need to print the following:
client 1
batch 2, $1
batch 3, $1
client 3
batch 6, $1
Hopefully that's clearer. Even without an outer join, the data from
Client 2 will be in the result set. Nothing should be printed for client 2,
because it has no batches with outstanding balances, but how can I get
Crystal to recognize that? I need to know the balance due for each of
Client 2's batches before I print the header for Client 2, so I can decide
whether or not to suppress it.
Tom
"PJ" <pj.krueger@erols.com> wrote in message
news:OZEu3uYTFHA.2908@TK2MSFTNGP10.phx.gbl...
> Tom,
>
> If you didn't do an outer join on the batch table, client records should
> not
> be selected if there are no batches.
>
> If you have an outer join, remove it.
>
> HTH,
> PJ
>
>
> "Thomas Ruschak" <ruschak@wintek.com> wrote in message
> news:1176e076epluq99@corp.supernews.com...
> Hi,
>
> I've got a fairly simple report with a tough (to me) problem in it. I'm
> using Crystal 8.5. (I also have Crystal 9 available, if it'd help to
> switch
> to that)
>
> There's a client table, and a G/L (general ledger) table. The entries
> in the G/L table are organized into 'batches' based on a field value
> (batch
> number). So, each client has zero, one, or more batches of entries in the
> g/l table, each batch having 1 or more entries for that batch. The total
> for a batch can be positive or negative, and I need to show any batch with
> a
> positive or negative total, even if the total due for all batches for the
> client is $0.
>
> (Yes, I know, it's not a great design, but I didn't do it, and it
> wasn't
> originally SQL-based anyway.... stay with me here :-)
>
> Anyway, I've got a report grouped by client, then batch. What I need
> is
> to print out for each client the batches they have, and what the total due
> on them is. This isn't hard, using running totals, so I get something
> like
> this:
>
> Client 1
> Batch 1 $-20
> Batch 2 $30
> ---
> $10
>
> (if you can read that)
>
> The problem is that if the client has no batches, I get this:
>
> Client 1
> ---
> $0
>
> That's ugly, so I want to suppress the header/footer for the client if
> that client has no batches with balances due.
>
> So, I made a calculated field that's SUM( balance due, batch# ). Then
> I
> made a running total to count that variable, accumulated when the batch#
> changes, only if it's non-zero. This gives me the number of batches with
> a
> non-zero total, and that works fine for the footer. However, it doesn't
> work for the header, because that running total is not calculated soon
> enough.
>
> Is there a right way to do this sort of thing? I've tried messing with
> the WhileReadingRecords/WhilePrintingRecords stuff, but I can't get it to
> work. In the other report writer I use (R&R Report Writer), I can make a
> total field "pre-processed" if I have to, which tells R&R to calculate the
> damned thing BEFORE running the report. It's ugly & slow (since it has to
> read the data an extra time), but it works. Is there any way to do this
> in
> Crystal? (And yes, before you ask, there are compelling reasons I can't
> do
> it in R&R, sorry)
>
> If anyone has read this far, and can help, I'll be eternally
> grateful...
> or pretty damn grateful at least :-) If there's a FAQ or tutorial
> somewhere
> I should be reading instead, please let me know that.
>
> Thanks,
> Tom
>
>
>
| |
| Briscobar 2005-04-30, 9:01 pm |
| Since it doesn't appear that you're too concerned with performance (since
you can "Pre-process" in other reports), maybe you could try a subreport.
Place a copy of your report into your report (as a subreport), and link on
the ClientID (or Key, whatever). Edit the select statement of the subreport
to only select data for that Client. Place this subreport in a new group
header #1. Then, create a shared variable for the total balance due. Then,
suppress any data needed in the main report based on this shared variable.
If you need more help with that, post back.
--
KB
MCNGP #26
"Thomas Ruschak" <ruschak@wintek.com> wrote in message
news:1176e076epluq99@corp.supernews.com...
> Hi,
>
> I've got a fairly simple report with a tough (to me) problem in it.
I'm
> using Crystal 8.5. (I also have Crystal 9 available, if it'd help to
switch
> to that)
>
> There's a client table, and a G/L (general ledger) table. The entries
> in the G/L table are organized into 'batches' based on a field value
(batch
> number). So, each client has zero, one, or more batches of entries in the
> g/l table, each batch having 1 or more entries for that batch. The total
> for a batch can be positive or negative, and I need to show any batch with
a
> positive or negative total, even if the total due for all batches for the
> client is $0.
>
> (Yes, I know, it's not a great design, but I didn't do it, and it
wasn't
> originally SQL-based anyway.... stay with me here :-)
>
> Anyway, I've got a report grouped by client, then batch. What I need
is
> to print out for each client the batches they have, and what the total due
> on them is. This isn't hard, using running totals, so I get something
like
> this:
>
> Client 1
> Batch 1 $-20
> Batch 2 $30
> ---
> $10
>
> (if you can read that)
>
> The problem is that if the client has no batches, I get this:
>
> Client 1
> ---
> $0
>
> That's ugly, so I want to suppress the header/footer for the client if
> that client has no batches with balances due.
>
> So, I made a calculated field that's SUM( balance due, batch# ). Then
I
> made a running total to count that variable, accumulated when the batch#
> changes, only if it's non-zero. This gives me the number of batches with
a
> non-zero total, and that works fine for the footer. However, it doesn't
> work for the header, because that running total is not calculated soon
> enough.
>
> Is there a right way to do this sort of thing? I've tried messing
with
> the WhileReadingRecords/WhilePrintingRecords stuff, but I can't get it to
> work. In the other report writer I use (R&R Report Writer), I can make a
> total field "pre-processed" if I have to, which tells R&R to calculate the
> damned thing BEFORE running the report. It's ugly & slow (since it has to
> read the data an extra time), but it works. Is there any way to do this
in
> Crystal? (And yes, before you ask, there are compelling reasons I can't
do
> it in R&R, sorry)
>
> If anyone has read this far, and can help, I'll be eternally
grateful...
> or pretty damn grateful at least :-) If there's a FAQ or tutorial
somewhere
> I should be reading instead, please let me know that.
>
> Thanks,
> Tom
>
>
|
|
|
|
|