Skip to Content
0

Group Conditional Formatting

Feb 21 at 05:34 PM

50

avatar image
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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
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
10 |10000 characters needed characters left characters exceeded
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
10 |10000 characters needed characters left characters exceeded
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.

0

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

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

Ian
0
Former Member

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

0

Please copy and paste your actual formula

Ian

0
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})

0

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?

0
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.

0

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

-Abhilash

0

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

0