Technical Questions: Totalling with Suppression

Totalling with Suppression

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7622
Printed Date: 01 May 2024 at 12:41pm

Topic: Totalling with Suppression

Posted By: FrnhtGLI
Subject: Totalling with Suppression
Date Posted: 08 Sep 2009 at 1:03pm

Crystal XI

I have a report that displays Meters (D0 section of XML) and the Accounts (D1 sections) associated with them.

The users have the option of selecting a date range in order to display only those Accounts that fall within the date range. This, however, does not affect the XML. The date range suppressions have to happen within Crystal. A meter may have multiple accounts associated with it and an account may be associated to multiple meters, so the XML may look like this:

<D0>

<D1>

</D1>

<D1>

</D1>

</D0>

<D0>

<D1>

</D1>

<D1>

</D1>

</D0>

If the user chooses to print only those accounts that have a date of 122508 then Account 200000 displays for each meter, according to the sample data above. The second meter will be displayed under the first meter with the same account and date and deposit.

What I need to do is total the deposit amount for the group that contains these records, but since account 200000 displays on top of itself for a different meter, I do not want to include the second amount. So if it was only these two records, I would want the Total Deposit Amount to be 100.00 not 200.00.

Well, I can't do a manual running total that skips the amount if Account=previous(Account) because Account 300000 would be the previous account to the second instance of Account 200000.

I know this seems complex, so any help would be greatly appreciated.

Thanks!

Replies:

Posted By: DBlank
Date Posted: 08 Sep 2009 at 1:44pm

Are you grouping on Account ?

You can create a Runnning Total as

Field to summarize=Deposit

Type of SUmmary=SUM

Evaluate = On change of group (pick account group)

Reset = Never

Place in Report footer

If no group but all are in order...

You can create a Runnning Total as

Field to summarize=Deposit

Type of SUmmary=SUM

Evaluate = On change of field (account)

Reset = Never

Place in Report footer

Posted By: FrnhtGLI
Date Posted: 09 Sep 2009 at 5:41am

No. Grouping is being done by Meter. This is a Connections by Meter report.

I tried grouping by Account and creating a running total that changes on group, but this does not work. It sums all the suppressed accounts as well.

A programmer had suggested processing valid data levels into a table (memory location) then printing from the table of valid entries. But I don't believe I have this level of control within Crystal.

Posted By: DBlank
Date Posted: 09 Sep 2009 at 7:31am

Sorry, glossed over some of the suppression requirements.

If I understand the problem correctly I think you will need a sub report to do this (to keep your sort/group on Meter) .

IN the subreport if you group or sort by account

use the RT but on the evaluate change it to use a formula and include in your selection formula/params:

previous(account)<>account and date={?date}

Posted By: FrnhtGLI
Date Posted: 09 Sep 2009 at 9:29am

I got it to work without a sub report, here is what I did:

Created a DepositAmount formula that is:

global numbervar nDeposit := 0;
global stringvar sAccountNumbersSoFar;

     if - {@AccountSoFarRight}<>{ConnectionsByMeter04.D1_QRACCT }
             then nDeposit := mailto:%7b@TotalDepositAmount - {@TotalDepositAmount }
                 else nDeposit := 0;

sAccountNumbersSoFar := sAccountNumbersSoFar &

          (if( - {@TurnOnDate}>={@PRTOPT1Date } and mailto:%7b@TurnOnDate%7d%3c=%7b@PRTOPT2Date - {@TurnOnDate}<={@PRTOPT2Date })
               then chr(13) &{ConnectionsByMeter04.D1_QRACCT}
                   else '');

This formula determines if an account should display the deposit amount based on whether or not the current account number is the last account that was added to a string variable list.

The string variable list adds the current account number to the list if it falls within the date range.

The mailto:%7b@AccountSoFarRight - {@AccountSoFarRight } formula is:

right( mailto:%7b@AccountSoFar - {@AccountSoFar }, 6)

This takes the last 6 digits of mailto:%7b@AccountSoFar - {@AccountSoFar } (which just displays the sAccountNumbersSoFar string variable), which is the most recent account that fits within the range.

I then summarized the DepositAmount formula and get the correct numbers.

I know it's a stupid way to calculate totals, but this is what programming wanted. It will be changed in a couple of months, but I have my temporary solution.

Thanks for all the help.

Posted By: DBlank
Date Posted: 09 Sep 2009 at 9:30am