Subreports containing negative and positive values 1

[trialbyfire]

I am using crystal reports to connect to an odbc database to print invoices for our company. In the database file there are two types of charges/allow. Header and Item allowances/charges. These allowances and charges each print as seperate records when the subreport kicks off. However I need to be able to grab just the negative values for each and just the positive values for each and put them at the bottom of the report in summary fields. One for total charges. One for total discounts. Can anyone help?

Thanks,

Bob

 

[rogar]

Use two running totals with Evaluate &quot;On a Formula&quot;; one where {charge/allow} > 0 and the other when {charge/allow} < 0

 

[trialbyfire]

Rogar,

I am assuming then that I would put a running total formula into each of the subreports that are printing this data. Then fetch the total into the main report and add the positive values for each of the subreports and then add the negative values for each of the subreports and place each into the appropriate field. I have not used the functionality that you stated above. Could you possibly give me an example of formula that would be used?

Thanks,

 

[rogar]

I'm assuming the {charge/allowance} data is all in the same database field, with the difference being if it is positive or negative. To create a running total, right click on the data field in the detail section and select Running Total. Choose &quot;sum&quot; as type of summary. Under &quot;Evaluate&quot; choose &quot;On a Formula&quot; and click the formula button. The formula sets the condition for evaluating the formula. Under &quot;Reset&quot; select &quot;Never&quot;

You should create two separate RT's, one for the positive values with conditional evaluation on

{charge/allowance} > 0

and one for the negative values with conditional evaluation on

{charge/allowance} < 0

Place the RT fields in the report footer.

If these RT's are in a subreport, you should use two shared variables to bring them to the main report. In a formula in the subreport, declare:

shared NumberVar nPosTotal := {RTPosTotal); //use the name of your RT field
 shared NumberVar nNegTotal := {RTNegTotal}

Place this formula in a second subreport footer below the RT's.
Then in the main report footer, insert two formulas:

shared NumberVar nPosTotal

and

shared NumberVar nNegTotal

to display the totals in the main report

If the subreports are run more than once and you want totals for all the subreport instances, you should put another fromula in your report header to initialize the shared variables to zero and change the subreport footer formulas to:

shared NumberVar nPosTotal; 
  shared NumberVar nNegTotal;
  shared NumberVar nPosTotal:= shared NumberVar nPosTotal +{RTPosTotal);
  shared NumberVar nNegTotal  := shared NumberVar nNegTotal + {RTNegTotal}

Be sure to use &quot;:=&quot; to assign values to the variables and to separate staements in a formula with semicolons.

 

[rogar]

Correction. The subreport formula for cumulative totals should be like this:

 shared NumberVar nPosTotal; 
  shared NumberVar nNegTotal;
  nPosTotal:= nPosTotal + {RTPosTotal);
  nNegTotal := nNegTotal + {RTNegTotal}

You only use &quot;shared NumberVar&quot; in the declarations.

 

[trialbyfire]

Rogar,

This has been a great help. I am still having a few small problems however. This is working for the positive amounts however it is not grabbing the negative amounts and pulling them into the report. I have three subreports that are gathering this information. One for item disc/chrgs, one for order disc/chrgs, and one for header disc/chrgs. Many of the charges and discounts are on the order level. The Item discounts print in the detail part of the subreport. The other two print in the group footer. The order discounts however will print in the detail segment of the report while the charges will print in the footer. Anyway the report is grabbing the footer charges and not the detail disc. Also the report prints the charges on all following documents. The only way I have been able to keep this from happening is to put a report header in that sets the sharednumbervar to 0 but then nothing prints on the report. Any suggestions anyone. I am at a loss.

Thanks,

Bob

 

[rogar]

Your report is more complicated than I had visualized. If you are generating subtotals as well as grand totals of the disc/charges, you may have to use another shared variable to hold the grand totals so you can reset the subtotal accumulators.

If values are printing where you don't want them, you can use conditional suppression - click on the formula button next to &quot;Suppress&quot; on the Field Format dialog and write a boolean formula which evaluates to True when the value should not appear.

Try to follow the sequence of events Crystal uses as it evaluates the formulas and sets the shared variable values, to see how the values change as the report executes. It may help to put formula fields displaying the shared variable values into the report in various places, temporarily, to help your debugging. Once you have the problem solved, you can remove or suppress them.