CODEWITHSUNDEEP
crystal-reportsgrouping
user3811444
user3811444

Reputation:

Crystal report - Grouping from Formula and Running Totals

I am trying to create a Crystal Report. One of the parameters that it has, is a Boolean flag which change the groupings of the Report. What I'm trying to do is to add 3 levels of grouping from a formula.

The code that I wrote is:

if {?summarized_detailed} = true then
    'Store Code : ' + {SNV_SP_ProfitabilityAndBreakEvenPerStore;1.WhsCode}
else
    if {?season_supplier} = true then
    'Store Code : ' + {SNV_SP_ProfitabilityAndBreakEvenPerStore;1.WhsCode} + ChrW(10) + 'Season : ' + {SNV_SP_ProfitabilityAndBreakEvenPerStore;1.SEASON} + ChrW(10) + 'Brand : ' + {SNV_SP_ProfitabilityAndBreakEvenPerStore;1.BRAND}
    else
    'Store Code : ' + {SNV_SP_ProfitabilityAndBreakEvenPerStore;1.WhsCode} + ChrW(10) + 'Supplier : ' + {SNV_SP_ProfitabilityAndBreakEvenPerStore;1.CardCode} + ChrW(10)  + 'Brand : ' +  {SNV_SP_ProfitabilityAndBreakEvenPerStore;1.BRAND}

{?summarized_detailed} is the parameter (the flag for grouping). if he chooses true, the report must have the columns -> Store Code , Season or Supplier (it based on another flag-parameter) and Brand.

Output Layer that I have :

 
                                                                                                               Name of Col1        Name of Col2        Name of Col3
Store: Value for Store Code, Season or Supplier: Value for Season or Supplier, Brand: Value for Brand |      total Brand Col1     total Brand Col2    total Brand Col3 ....

With this Code, I take the result that I want. All database's rows are separated according to those groups. The problem is, that I want to have total sum for every column that I have in my report. But I can't do this, because the above formula, created only ONE union group. So, I can have a summary only for the details within every brand.

Output Layer that I want :

                                                         Name of Col1        Name of Col2        Name of Col3
Store: Value for Store Code                      |
       Seas or Sup: Value for Season or Supplier |
               Brand: Value for Brand            |      total Brand Col1    total Brand Col2    total Brand Col3

//when supplier or season within all groups changes (not included in report)
       --------------------------------------------------------------------------------------------
       Total Supplier or Season:                    total Sup/Season Col1 total Sup/Season Col2 total Sup/Seas Col3
//when store within all groups changes (not included in report)
       Total Store:                                     total Store Col1    total Store Col2    total Store Col3
       ---------------------------------------------------------------------------------------------

With total Brand Col1 I want to do a summary for all rows of details for each field of the same brand.

With total Supplier Col1 I want to sum all total Brands for each field of the same supplier.

With total Store Col1 I want to sum all total Suppliers for each field for every shop.

How can I compute those totals and display them only when Season or Supplier changes, and when Store changes with no regarding the change of the union Group ?

Or is there an easier and better way to make those 3 groups from formula according to the parameter but let them be separated so that I can control them and do my summarizes ?

Upvotes: 0

Views: 1829

Answers (1)

Odj fourth
Odj fourth

Reputation: 709

This is how I handle this type of requirement:

If you want a maximum of 3 groups, but potentially less, then you need to create two different formula fields. In the report design, you set grouping to group on Store_Code first, then the other two formula fields.

If the refresh/runtime parameter will be FALSE for when you want the three-level grouping, then the formulas should look something like this:

Group1_Formula1: if {?param} = TRUE then '' else {Season}

Then the next:

Group2_Formula: if {?param} = TRUE then '' else {Brand}

If you want totals/subtotal the just place them as summary fields in the group headers, with conditional display to suppress them as necessary.

The result will be that, even though grouping on all three levels is always taking place, for the subgroups below the always-active "Store_Code" each subgroup will have only a single group of all records if the {?Param} = TRUE, effectively meaning there is no grouping happening.

One caveat: This presumes you aren't using print-time evaluation, e.g. before/after printing, etc., in any of the related formulas necessary to perform your grouping.

Upvotes: 0

Related Questions