Highlight Maximum Percentage

[mdsaville]

I have a report that is grouping some totals for users productivty. Once of the columns is the percentage based on the daily total. All my data is correct, but I would like to highlight either the maximum percentage or the entire row based on the maximum percentage. How can I do that?

 

[CR85user]

Hi mdsaville,

Please could you confirm which version of Crystal you are using as some later versions have much more functionality in this area than others.

'J

 

[mdsaville]

Crystal XI

 

[lbass]

Please show us the content of the formula you are using for the percentage or indicate whether this is an inserted summary in percentage format.

-LB

 

[mdsaville]

The percentages are a group level summary based on the total of the group one level higher. The summury is placed in the group footer. I'm not using a manually entered formula to calculate the percentage. Maybe the attached screen shot will help understand.

 

[lbass]

You would need to create a SQL expression to return the percentage. Please identify the {table.field} used for Pages indexed and for your groups #1,2, and 3.

-LB

 

[mdsaville]

Below is what each of my groups are by. And what each of my fields are calculated by. Please see the attachment for a screen shot of the percent summary.

Group 1 = command.logdate (daily)
Group 2 = command.username
Group 3 = command.actionnum

GF1 = Count(command.actionnum) "page indexed per day"
GF3 = Count(command.actionnum) "pages indexed"
GF3 = Percent of Count(command.actionnum) from GF1

 

[lbass]

If you are using a command as your datasource, then build the count summaries right into the command. I could help with this if you paste the command into the thread.

You could then use the summaries from the command in a formula to test for the maximum.

-LB

 

[mdsaville]

Here is my select statement.

--Indexed Documents
select e.actionnum, u.username, u.realname, aq.queuename, 
e.batchnum, idp.itemnum, idp.filepath, e.logdate 
from hsi.scanninglog as e
join hsi.archivedqueue as aq on aq.batchnum = e.batchnum
join hsi.itemdatapage as idp on e.batchnum = idp.batchnum
join hsi.useraccount as u on u.usernum = e.usernum
where e.eventnum = 2 and e.actionnum = 202 
and aq.queuename like 'HR - %' and
u.username <> 'ALLINADIP' and
e.logdate >= {?BeginDate} + ' 00:00:00' and
e.logdate <= {?EndDate} + ' 23:59:59'

 

[lbass]

I'm confused by what your groups are. In your earlier post, you show a group #1 on logdate, and you mention a count on actionnum, but then your links show a group #1 on itemno and a count on itemno. Please clarify what each group field is, and what it is you want to count.

-LB

 

[mdsaville]

That is a screen shot of the Percentage used in GF3 based on the count of itemnum in GF1 which is grouped by logdate.

Group 1 = command.logdate (daily)
Group 2 = command.username
Group 3 = command.actionnum

GF1 = Count(command.actionnum) "page indexed per day"
GF3 = Count(command.actionnum) "pages indexed"
GF3 = Percent of Count(command.actionnum) from GF1

 

[lbass]

Well, is it itemnumber or actionum that you are counting? The screenshot shows itemnumber and you are showing actionnum above. It also doesn't make sense that you are grouping on actionnum when you are limiting it to one value. I'm confused.

-LB

 

[mdsaville]

You are absolutely correct. I provided the wrong information on what I was counting. See below for the correct Grouping and Summary information.

Group 1 = command.logdate (daily)
Group 2 = command.username
Group 3 = command.actionnum

GF1 = Count(command.itemnum) "page indexed per day"
GF3 = Count(command.itemnum) "pages indexed"
GF3 = Percent of Count(command.itemnum) from GF1

 

[lbass]

Change your command to something like:

select e.actionnum, u.username, u.realname, aq.queuename,
e.batchnum, idp.itemnum, idp.filepath, e.logdate,
(
select count(D.itemnum) from
scanninglog A, archivedqueue B, useraccount C, idp D
where
A.batchnum = B.batchnum and
A.usernum = C.usernum and
A.logdate = scanninlog.logdate and
C.username = useraccount.username and
A.actionnum = scanninlog.actionnum and
A.eventnum = 2 and
A.actionnum = 202 and
B.queuename lik 'HR - %' and
C.username <> 'ALLINADIP' and
A.logdate >= {?BeginDate} + ' 00:00:00' and
A.logdate <= {?EndDate} + ' 23:59:59'
) as cntItemNo,
(
select count(D.itemnum) from
scanninglog A, archivedqueue B, useraccount C, idp D
where
A.batchnum = B.batchnum and
A.usernum = C.usernum and
A.logdate = scanninlog.logdate and
A.eventnum = 2 and
A.actionnum = 202 and
B.queuename lik 'HR - %' and
C.username <> 'ALLINADIP' and
A.logdate >= {?BeginDate} + ' 00:00:00' and
A.logdate <= {?EndDate} + ' 23:59:59'
) as cntatLogDate

from hsi.scanninglog as e
join hsi.archivedqueue as aq on aq.batchnum = e.batchnum
join hsi.itemdatapage as idp on e.batchnum = idp.batchnum
join hsi.useraccount as u on u.usernum = e.usernum
where e.eventnum = 2 and
e.actionnum = 202 and
aq.queuename like 'HR - %' and
u.username <> 'ALLINADIP' and
e.logdate >= {?BeginDate} + ' 00:00:00' and
e.logdate <= {?EndDate} + ' 23:59:59'

Then in the main report, create a formula {@Pct}:

{command.cntItemNo}%{command.cntatLogDate}

You can then write a conditional color formula for the Group #3 section like:

if {@Pct} = maximum({@Pct},{command.logdate}) then crYellow else crNoColor

-LB