How to exclude a record in main report if subreport contains a certain value

[former_member289643]

Hello,

I have created a report that includes two fields: appraised price, purchase price. It also includes a formula (appraised price - purchase price), and it also a running total of the values determined by the formula.

The report contains a subreport in the Details section (so that the subreport appears for each record in the main report), and I want to find a way to have the running total (in the main report) exclude records where the subreport contains a certain value.

In the example below, when the subreport value is no (e.g. the third record), I want the running total to skip that record. So, instead of having the end result of the running total be 95 (as shown), it would instead skip the third record, so that the running total would end up at 45 (30 through row 2, plus 15 for row 4).

      Appraised Price       Purchase Price       Appraised-Purchase    Running total          Subreport

1           100                           90                                10                             10                          yes

2            80                            60                                20                             30                          yes

3            90                            40                                50                             80                          no   

4           100                          85                                 15                             95                          yes

Suggestions?

[abhilash_kumar]

Hi Jon,

Here's what you need to do:

1) Insert a new details section - you should now have details a and details b

2) Move the Subreport to details a and place all the other fields on details b

3) Inside the Subreport, create a formula that returns either Yes or No based on the condition:

shared stringvar s;

s := If <condition> then 'Yes' else 'No';

Drag and drop this formula on a section on the Subreport

4) Back on the Main Report, create a formula with this code:

whileprintingrecords;

shared stringvar s;

numbervar rt;

if s = 'Yes' then rt := rt + {@Appraised_Price_formula};

5) Drag and drop this formula beside the  Appraised-Purchase   field on details b.

Hope this helps.

-Abhilash

[former_member289643]

Hi Abhilash,

I think you're close, but not quite there, and thought adding details may help.

The main report is a list of land purchases. Each record has an appraised price, and also the amount they actually cost. And I created a formula (DIS_Price_Difference) that is the difference between those two amounts.

Some of the money used for those land purchases came from a specific fund, and, in a running total of the price difference in the main report, I want to exclude purchases that used more than $1 from that fund.

The subreport I created lists payments from that fund (using data in the field called LRS_EXPEND.MONETARY_AMOUNT), by land purchase. Here's the formula I created (@DIS-yesno) and inserted into the subreport:

shared stringvar s;

s := if sum({LRS_EXPEND.MONETARY_AMOUNT}) < 1 then 'Yes' else 'No'

Then, after adding a new details section, I created (and inserted into Details B in the main report) a formula (called @DIS_running_total) that says

whileprintingrecords;

shared stringvar s;

numbervar rt;

if s ' 'Yes' then rt := rt + {@DIS_Price_Difference}

Unfortunately, for all records, both the ones I want to include in the running total and those I don't, the result is 0.00.

Ideas?

[abhilash_kumar]

Hi Jon,

The Subreport should be on a section 'above' the section that holds the @DIS_running_total and I hope that's how you've set this up.

Also, the section that holds the Subreport should NOT be suppressed! If you're worried about the space it might take up because of its contents, you could do this:

- Suppress every section inside the Subreport

- Back in the Main Report, right-click this Subreport > Format Subreport > Subreport tab > Check 'Suppress Blank Subreport'

- Section Expert > Select the Section that holds the Subreport > Check 'Suppress Blank Section'

After setting up the Subreport in this way, what does the piece of code below, when in the details b section, return:

whileprintingrecords;

shared stringvar s;

-Abhilash