Exclude suppressed groups values from subsequent groups summaries

[jeffinperth]

CR 9
SQL dbs

I need a way to exclude suppressed group values from subsequent group summaries.

I have a report that only displays groups - details are suppressed. There are 5 levels of groups, only groups 5 & 2 can be suppressed depending on the summaries in the group 5, i.e. if quantity evaluates to zero or maximum date is 4 months previous to last day of the month as defined by report run date. However any number field in the suppressed group becomes a part of the summaries in subsequent groups.

I can't use running sum a group.

I have tried using the suppression formula in another formula to zero out the values if they are to be suppressed and then summing that. This works for totals in groups 2 & 5 but not at group 1 or report footer because the new formula is at group level and not detail, so it can't be summed.

Can anyone see a way to do this? (It's probably 'just outside the box')

TIA
Sara (borrowing my work-mate Jeff's account)

 

[dgillz]

Do you mean the suppressed group's values become a part of the grand total?

I do not see how they can become a part of the subsequent group's totals.

Please explain how you are suppressing the group, and how you are totalling.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

Please identify the fields you are grouping on and also identify the kind of summaries you are doing, and what your suppression criteria are, and where you are applying them.

-LB

 

[jeffinperth]

Thanks dgillz & lbass

Basically the report is a dummy inventory with columns for part_no, desc, part_price, purchase_order, po_date, purchase_location, po_type (catalogue or non-catalogue), qty, tax, dollars.

The user enters a date parametre and from it the report works out the first and last dates of the month to use. The selection criteria returns all rows with a po_date <= @last_date_of_month

The first level of grouping (level 5) is on purchase_location and the summaries in it all find if the returned data has a po_date less and @first_date_of_month, if so set them to a dummy value or sum them in this fashion:

If {po_date} < @first_date_of_month then "dummy value i.e. 0, @first_date_of_month, etc" else {column} - there are formulas like this for purchase_order, po_date, purchase_location and a few other columns not mentioned.

Other summaries in purchase_location group are straight sums i.e. sum(qty, @PO_Location), sum(tax, @PO_Location), sum(dollars, @PO_Location)

The result of which is: if there have been purchase orders previous to the run month, they are summarised into a single entry with a @po_num of "0", @PO_Location = "STATIONERY", @po_date of first_date of month and summaries of qty, tax and dollars. Purchase orders entered during the month return as their data.

The next two groups are @po_num (level 4) and @po_date (level 3), both headers and footers are suppressed and they have no data in them.

The next group (level 2) and is a calculated control. Parts can have multiple prices and need to be listed separately. So the group is on @Item_Price, that being part_no + ' ' + ToText(part_price). The part_no, desc and part_price are in the GH and summaries of qty, tax and dollars are in the GF.

Summaries simply being:
If @Hide = True Then 0 Else Sum(qty,@Item_Price)

@Hide is the suppression formula, being:

If ( po_type = "Catalogue" and
sum(qty, @Item_Price) = 0 and
@last_date_of_month - max(po_date, @Item_Price) > 61
)
OR ( po_type = "Non-Catalogue and
@last_date_of_month - max(po_date, @Item_Price) > 121 )
then True

These totals function correctly.

I am also using the @Hide formula in the selection expert suppression formula box at GH2 & GF2 (@Item_Price), GF5 (@Location)

I am having trouble at group level 1, which groups on po_type - catalogue vs non-catalogue.

If I use a sum formula (right-click detail | Insert... | Summary) in GF1 then I get all the values for the suppressed rows in the summary, for example, 2 rows 8.32 + 99.00 total to 296.12 clearly not correct.

If I use the formulas that I've used in GF2 I get the same totals. The same sort of thing happens in the report footer.

BTW this is only 1 of 4 reports, but conquer it in one and conquer it in all. I'm sure that I can't see the solution because I've looked at it too long! Any help is much appreciated.

 

[lbass]

Your references to group "levels" are very confusing. You should be referring to Group #1, Group #2, etc. Why would your first group be level 5. Anyway, inserted sums will include suppressed values, so you either have to eliminate the values you are suppressing from the report, or instead of inserted summaries, use running totals where you explicitly exclude the suppressed records in the evaluation section->use a formula. You would enter the opposite of your suppression criteria, so for example, if you were suppressing with this formula:

{table.field} = "X"

Then in the running total->evaluation ->use a formula section, you would enter:

{table.field} <> "X"

The running totals need to be placed in footer sections. The resets should correspond to the footers you are using, so if you are totaling for Group #3, the reset should be on change of group #3. If at the grand total level, use reset never.

-LB

 

[jeffinperth]

lbass

Sorry for the confusion with the levels. The groups are:
Group #1 Hdr {table.po_type}
Group #2 Hdr @Item_price - suppress by @Hide
Group #3 Hdr @PO_Date - fully suppressed
Group #4 Hdr @PO_Num - fully suppressed
Group #5 Hdr @PO_Location
Detail - fully suppressed
Group #5 Ftr @PO_Location - suppress by @Hide
Group #4 Ftr @PO_Num - fully suppressed
Group #3 Ftr @PO_Date - fully suppressed
Group #2 Ftr @Item_price - suppress by @Hide
Group #1 Ftr {table.po_type}

I have tried excluding the groups through the Group Selection Expert and I have tried a running sum, but not tried excluding the groups within the running sum. I'll give it another go. Thanks for your help.