Maximum formula on record selection 1

[aspnetuser]

Hello all,
CR 8.5
SQL server 2000

I am confused.
I have the following data:
ID Score1 Score2
1 1 1
2 1 0
3 4 3

I need to write a CR formula that first determines the MAXIMUM ID (3) and then adds score1 and score2 (7).

Can someone help me on this?

Thanks,
Regards,
aspnetuser...

 

[IdoMillet]

One option is to sort by ID. The last record in the report (or within the group) is the one used in the Report Footer or Group Footer, so problem solved if the info is needed in the Group Header or the Report Footer.

If you need the info in report header or group header, then create a formula such as:
-----------------------------------
Split(Maximum(Cstr({ID}) + "::" + Cstr({Score1} + {Score2})),"::")[2]
-----------------------------------

Cheers,
- Ido


CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

You could create a formula for display in the detail section:

{table.score1} + {table.score2}

and then go to report->edit selection formula->GROUP and enter:

{table.ID} = maximum({table.ID})

If you want the maximum within the group the group selection formula would be:

{table.ID} = maximum({table.ID},{table.groupfield})

-LB

 

[aspnetuser]

Both great helps, but i need to go lbass's route.

however, when i try to do
{table.ID} = maximum({table.ID},{table.groupfield})

it says running sum?

 

[lbass]

Make sure you are putting the formula in the GROUP selection formula area, not the record selection formula area, and that you have a group on {table.groupfield}. Of course, you have to substitute your actual table and field names for {table.ID} and {table.groupfield}.

-LB

 

[aspnetuser]

it still gives me the same error?

 

[lbass]

Please post your exact group selection formula and confirm what your group field is.

-LB

 

[aspnetuser]

{vwWinnersByWeek_rpt.GameID} = maximum({vwWinnersByWeek_rpt.GameID},GroupName ({vwWinnersByWeek_rpt.Username}))

i am grouping on group 1 autopoolid
group 2 is username

 

[lbass]

Change that to:

{vwWinnersByWeek_rpt.GameID} = maximum({vwWinnersByWeek_rpt.GameID}, {vwWinnersByWeek_rpt.Username})

This will give you the maximum gameID per user. Make sure you are entering this in the right location. It will not work in report->edit selection formula->record. It must be placed in report->edit selection formula->GROUP.

-LB

 

[aspnetuser]

awesome!
your are so good at this!

one more issue.

now I have the correct data; i need to only show the top person...

Username Wins TBActual TBPoints diff
1,001
johnn 12.00 38 30 8.00
tester 12.00 38 38 0.00 winner bc diff 0
jason 9.00 38 42 -4.00

the report should just show tester because even though they had 12 wins each, the diff was 0 for tester?

possible?

 

[lbass]

I don't know your shorthand, e.g., what TB is, or what the logic is of choosing the diff = 0. Are you selecting the user with maximum wins and then using the minimum difference as a tie breaker? Are these records still at the detail level?

-LB

 

[aspnetuser]

wins is the sum of a formula in the details section

diff is tbactual - tbpoints

all records still at detail level

the report shoul only show user with max wins and if max wins ties only show user with diff cloest to zero

 

[aspnetuser]

i am so stuck. any other ideas?

 

[aspnetuser]

Can anyone help me on this?

 

[lbass]

Patience--most respondents here require sleep (I left you at 11:30 PM last night) and work full-time. I have only now been able to return to this.

Please note that you should have laid out the entire problem from the beginning, as I would have approached it differently. As I understand it, starting from the beginning, you have data like the following:

User ID Score1 Score2 Wins TBact TBpts diff
john 1 1 1 2 3 2 1
john 2 1 0 1 5 2 3
john 3 4 8 12 38 30 8
tester 1 5 12 17 2 2 0
tester 2 6 6 12 38 38 0
jason 1 3 3 6 16 12 4
jason 2 5 4 9 38 42 -4

Please confirm the assumptions I am making before I take this further:

1) {@Wins} = Score1 + Score2
2) You want the row with the highest ID number, NOT necessarily the highest {@Wins} per user. In other words, for tester, you want ID #2 even though {@Wins} is higher for ID#1.
3) You then want the user with the highest {@Wins} in the selected rows, and if there is a tie, break it with the lowest {@diff}. Your final report will show this one user.
4) I am assuming that TBactual and TBpoints are database fields, not report summaries or formulas.

I have a solution in mind, but would like you to verify the above. If I am wrong on any single point, please be sure to provide enough detail to understand. In particular, if any of these fields are formulas, please supply the exact formula.

-LB

 

[IdoMillet]

Consider revisiting the 2nd approach I outlined in my initial answer.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[aspnetuser]

Ok LB I appologize, lets start over because i am still stumped.

1,001 GROUP 1
tester GROUP 2
Username Wins TBActualTBPoints diff GameID
tester 1.00 51 30 21.00 1
tester 1.00 23 30 -7.00 2
tester 0.00 36 30 6.00 3
tester 0.00 23 30 -7.00 4
tester 0.00 47 30 17.00 5
tester 1.00 24 30 -6.00 6
tester 1.00 28 30 -2.00 7
tester 1.00 55 30 25.00 8
tester 1.00 45 30 15.00 9
tester 1.00 27 30 -3.00 10
tester 1.00 26 30 -4.00 11
tester 1.00 52 30 22.00 12
tester 1.00 48 30 18.00 13
tester 0.00 40 30 10.00 14
tester 1.00 58 30 28.00 15
tester 1.00 38 30 8.00 16


Ok this is the data as displayed by the db view with two groupings noted above. the report has only one data source.

Wins = formula that compares two fields (IF STATEMENT) returns a 1 for win a 0 for loss.
TBActual = xfield-yfield from sql view. We are only concerned with this number when the gameid is MAX. in this case it would be 38.
tb points is used to caluculate the difference formula in case of a tie. tie would be when two users have same max wins. max wins could be calced if we summed the total wins and suppressed these details.

end result should be the user with the max wins and if they tie with another user diff closest to 0.

follow me ? i do appreciate all the help, it is a huge learning experience...

 

[lbass]

First right click on {@wins} and insert a summary (sum) on it at the user group level. Then create a SQL expression {%maxID}:

(select max(AKA.`GameID`) from Table AKA where
AKA.`Username` = Table.`Username`)

Replace "Table" in both places with your table name and replace "GameID" and "Username" with your exact field names. Leave "AKA" as is, since it is an alias table name.

Then create a formula {@diffformaxID}:

if {Table.GameID} = {%maxID} then {@diff} else 999999

Place this formula in the details section and insert a minimum on it. Drag the user groupname into the footer next to the summaries.

Then go to report->topN->User Group tab->choose "sum of @wins"
->descending. Then choose "minimum of @diffformaxID"->ascending. Then go to the section expert and suppress the details and group header, and for the group footer section, go to suppress->x+2 and enter:

groupnumber <> 1

This will return one row: the maximum for {@wins} where the minimum {@diff} for the maximum ID per user is the tie-breaker.

-LB

 

[aspnetuser]

LB, I have to give you kudos for that incredible step by step solution! It worked for the first grouping however let me explain further about what I was selecting on.

For every group 1 there are many ids (1001, 1002,1003)
There are a different set of usernames for each group1. (tester, joe is 1001, Jason, jay is 1002.) There is also a set of weeks for each of these in which I have not grouped yet. For testing purposes I was using selection expert equal to week 1, id 1001 for the purposes of our examples. You solution worked great for this, now I want to remove the week = 1 and id = 1001 to the whole recordset. I think I will have to do some editing to the sql statement formula and other levels? Can you help me tinker it to work with this layout?

Thanks so much and I will be patience!

 

[lbass]

If you are trying to come up with one name as a winner, would you now be looking for this for each week within each ID? Please provide a sample display of the finished report which identifies your group structure and report sections. And please reveal ALL aspects of what you are looking for at one time.

-LB