Skip to Content
avatar image
Former Member

Group Conditional Formatting

Hi All,

I have a group that sums up qty by week.

So it's grouping by week, then showing the trxdate and summing up qty produced for each trxdate in that week then at the end is summing up all qty for that week. I'm trying to highlight or change the font color for the maximum qty during that week.

I have tried this:

If Sum ({quantity}, {trx_dt}, "daily") = maximum (Sum ({quantity}, {trx_dt}, "daily")) then crRed
Else crBlack

but I get this error: Thsi field cannot be summarized.

i have this formula in the format editor of the field.

Any idea how to accomplish that?

Thanks.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Feb 23 at 11:49 AM

    Hi Karim,

    You'd need a Subreport to be able to highlight the highest value. Try this please:

    1. Save a Copy of this report in question. Let's call the new report "Subreport".

    2. Open "Subreport" and create a formula with this code:

    shared currencyVar array maxs; 
    shared numbervar itr := itr + 1; 
    redim preserve maxs[itr]; 
    maxs[itr] := Sum({Quantity}, {trx_dt}, "daily");
    '';

    Place this formula field on Group Footer 2

    3. Create another formula with this code and place on the Group Header 1:

    shared currencyVar array maxs := 0; shared numbervar itr := 0;

    4. Create a third formula with this code and place this on Group Footer 1:

    shared currencyVar array maxs; 
    shared datevar array dd; 
    shared currencyvar array c; 
    shared numbervar cnt := cnt + 1; 
    redim preserve dd[cnt]; 
    redim preserve c[cnt]; 
    dd[cnt] := date(GroupName ({Date field used as the weekly group}, "weekly")); //Use the date field that is the weekly group
    c[cnt] := maximum(maxs); 
    '';

    5. Suppress ALL sections of this report and save.

    6. Open the Original report > Select Insert Subreport > Choose an existing report > Browse to the location where you saved "Subreport" and click OK.

    7. Place the Subreport on the Report Header.

    8. Double click the subreport and make sure ALL sections are suppressed. If you see any news Report Header sections that are not suppressed, go ahead and suppress them.

    9. Back in the Main Report, highlight this Subreport > Right click and select Format Subreport > Subreport tab > Check "Suppress Blank Subreport".

    10. Go to the Section Expert > Highlight the Report Header section that holds this subreport > Check "Suppress Blank Section".

    11. Create a formula in the Main Report with this code and place it on Group Header 1:

    shared datevar array dd; 
    shared currencyvar array c; 
    local numbervar j; 
    shared currencyvar maxv; 
    for j := 1 to ubound(dd) do 
    ( 
        if date(GroupName ({Date Field used as the weekly group}, "weekly")) = dd[j] then 
        (
         maxv := c[j];
         exit for;
        );
    );
    '';

    12. In the Main Report, highlight the Quantity summary field that is on Group #2 > Format Field > Font tab > Click the formula button beside "Color" and use this code:

    shared currencyvar maxv; 
    if CurrentFieldValue = maxv then crRed else crNoColor

    I hope this helps.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 22 at 09:06 AM

    There might be a better way but you can use a variable

    In Week Group header (NB semi colons and colons are important)

    @MaxDay

    Whileprintingrecords;

    Global Numbervar MaxDay;

    MaxDay := maximum({quantity}, {trx_dt}, Weekgroupfield)

    Change your conditional formula to use this variable

    Whileprintingrecords;

    Global Numbervar MaxDay;

    If Sum ({quantity}, {trx_dt}, "daily") = MaxDay then crRed
    Else crBlack

    Ian

    Add comment
    10|10000 characters needed characters exceeded

    • Sorry Misunderstood your need.

      Probably Abhilash solution will work. Failing that change report to use a command and summarise data by Date in SQL. Then your daily data will be detail and you can easily find Max day in a week.

      Ian