0

# Group Conditional Formatting

Feb 21 at 05:34 PM

50

Former Member

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.

Abhilash Kumar
Feb 23 at 11:49 AM
0

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

Share
Ian Waterman Feb 22 at 09:06 AM
0

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

Show 9 Share
Former Member

Ok, all setup but I get an error "A group condition must be a string" in this section:

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

and it higlights the "Weekgroupdield" section.

but... way to think out of the box :-)

Thank you.

You have to replace WeekGroupField with formula or actual field you are using to create your week group

maximum({quantity}, {trx_dt}, Weekgroupfield)

Ian
Former Member

Correct, and that's what makes the error. i put the group field there.

Ian

Former Member

Code under "formate field" > "font" > "color"

Whileprintingrecords;

Global Numbervar MaxQty;

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

________________________________________________________________________

Code Under MaxQty formula

Whileprintingrecords;

Global Numbervar MaxQty;

MaxQty := maximum({IMINVTRX_SQL.quantity}, {IMINVTRX_SQL.trx_dt})

So you have two groups on date one for the day and one for the week?

If so then you condition formula should be

If Sum ({IMINVTRX_SQL.quantity}, {IMINVTRX_SQL.trx_dt}, "weekly") = MaxQty then crRed
Else crBlack

This will highlight the daily record which is max of the week.

Is that what you want?

Former Member

That didn't work either.

So in the second group, it gives total qty for each date. I just wanted it to highlight the max qty.

not sure what else to try.

But Thank you very much for trying and for your time.

I'll put this one on the side for now. If I find a way, I'll post it back here.

Thanks.

I posted a workaround, if you'd like to try that.

-Abhilash