Group by one, sort by another, with a twist

[TheEagleHasLanded]

Thanks to lbass I was able to group by one field and sort by another. However, this report has a slight twist.

When I am grouping the data, there is also sorting that is occurring within the group.

Group by field A, sort by field B (which is a formula field).

Outside of the grouping, I want to sort the groups by the first record that appears in the grouping using field C. (Because it is not really a numeric or alphabetic sort, I need to select the first row of the group and use that as the sort paramter - I can't use a Min or a Max for the summary field to allow the sort by group to work - basically I am looking for a 'select first N' type parameter).

I am using CR XI R2.

Does that make sense?

Thanks,
T

 

[TheEagleHasLanded]

If I do a "MODE" against a non-numeric field, will it try to pick up the first one?

It seems to be the case so far, but I need to run more test cases. I'm not sure this is a good approach for solving this issue.

Thanks,
T

 

[TheEagleHasLanded]

I take that back... it doesn't work.

 

[lbass]

What makes a record the "first" one in a group, since you could change the sort and the first record would change? Is "first" based on a date field or on a field that is sequential?

Please show some sample data, and use actual field names instead of generic terms.

-LB

 

[TheEagleHasLanded]

Here is what the sample report would look like:

Symbol Cusip ADP Code ID Under Security Description
MSFT 12335 A12344 1 NULL Microsoft
.wtmsft M2344 8TR333 NULL 1 Warrants - MSFT 10/30

BCE 23444 B21344 2 NULL Bell
.wtsbce 23455 F21234 NULL 2 Warrant - Bell 10/20
.wtsbce2 26445 3SS444 NULL 2 Warrant - Bell 10/7

1. I created a formula (group1) that says if ({table.Under} = NULL then {table.ID} else {table.Under}

2. I did a grouping based on this new formula (group1).

3. After the grouping, the data is sorted by {table.Under} ascending to ensure that the NULL record shows up first.

4. I now want to do a sorting of all the groups by ADP Code. So I want to ensure that this first row is picked up for the sorting. If I do a Maximum or Minimum on the ADP Code for the group, I will not get the right result. So if there is a way for me to tell Crystal to use the ADP Code of the first record in the grouping, then I would be golden!

Hopefully that clarifies things. Any insight would be greatly appreciated.

Thanks,
T

 

[lbass]

Are you essentially saying you want to sort the groups by the ADP record when the {table.under} is null? Is there always a null {table.under} record (and only one) in each group?

-LB

 

[TheEagleHasLanded]

Unfortunately it is possible to have a record that has a value that has a non-null Under that can exist on it's own (or several grouped together). I also double-checked the data and there will always be a non-null value for ID.

Thanks,
T

 

[lbass]

Well, is there always only one non-null value for ID per group?

If this isn't the case, a more extended sample of data that shows all potential variations in the data would help.

-LB

 

[TheEagleHasLanded]

Hi LB,

Here is the more accurate data set. (ID can never be null)

Symbol Cusip ADP Code ID Under Security Description
MSFT 12335 A12344 1 NULL Microsoft
.wtmsft M2344 8TR333 77 1 Warrants - MSFT 10/30

BCE 23444 B21344 2 NULL Bell
.wtsbce 23455 F21234 61 2 Warrant - Bell 10/20
.wtsbce2 26445 CSS444 33 2 Warrant - Bell 10/7

JNJ 23434 D2344 23 NULL Johnson and Johnson

.PFE 25675 E12345 22 99 Call - Pfizer

.KO 923432 F23455 101 53 Call - KO Common Shares
.KO 924555 G34456 103 53 Put -KO Preferred Shares

The above samples will cover all possible scenarios.


Thanks,
T

 

[lbass]

Try a formula like this:

if isnull({table.under}) then
" "+{table.ADPCode} else
{table.ADPCode}

Insert a minimum on this formula, and then do a sort descending.

If this still doesn't accomplish what you want, please show the results you get and then show the results you would expect using sample data.

-LB

 

[TheEagleHasLanded]

It got me closer to what I need but not quite there.

The problem with the logic is that the records that don't have the {table.under} value will have regular ADP Codes, whereas the other will have the " " before the ADP code. The Minimum on the formula pulls correct ADP Code sequence, but what is happening is that the sorting will have all the " " + ADP Code appear first then the regular ADP Codes.

In this case if there was a way to do a min and then an rtrim, we would be set.

Based on the above data, if there was a record for

Symbol Cusip ADP Code ID Under Security Description
Company ABC 12335 A33344 200 88 Company ABC

The logic you have would list this after the BCE group sample (above) whereas it should really appear before that group because the ADP Code for this would be A33344. The blank space is allowing those (where {table.under = null) to be listed first and the other (non null) entries afterwards but the codes should really be sorted together.

I will try and get actual data but I am working with extremely sensitive information.


Thanks,
T

 

[lbass]

I thought that was what you WANTED to see--those with a null "Under" first. I think you should show a sample of data and then show how you want it sorted--a before and after view. You don't really have to provide any sensitive info to do this. Also please identify the field you are grouping by.

-LB

 

[TheEagleHasLanded]

Hi LB,

I have attached an Excel spreadsheet with before and after data and the logic that is being used. I highlighted the derive element on the spreadsheet too.


Thanks!
T

 

[IdoMillet]

There is no need to show what you are sorting on... Show what you want to show...

- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[TheEagleHasLanded]

In the spreadsheet, the "after" is what I am hoping to accomplish (the end result), but I can't seem to get that result.

Before is simply the data set. Any suggestions? This is why I was hoping that when you do a group sort you can select "First N" rather than like "First Nth largest", or "Minumum" or "Maximum" which doesn't give me the desired result.

Thanks,
T

 

[IdoMillet]

I guess my comment was not clear enough. It was in response to your sentence: "The problem with the logic is that the records that don't have the {table.under} value will have regular ADP Codes, whereas the other will have the " " before the ADP code."

So, again, There is no need to show what you are sorting on... Show what you want to show...

- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[TheEagleHasLanded]

Sorry, Ido, maybe I am just a bit slow this morning, but I am confused as to what you want me to respond.

I just want to get to the "After" result that is on the spreadsheet and have no idea how to accomplish it.

Ultimate goal is to sort the groups by ADP ascending.

The whole {table.under} was just to ensure that that record appears first within the grouping if that record exists, otherwise within the grouping we look at the lowest ADP Code.

Is my attached excel spreadsheet not clear?

Thanks,
T

 

[TheEagleHasLanded]

I guess that this can't be done?

If not, then I may have to build a view/key subgrouping/sorting prior to data retrieval.

LB/Ido - thanks for responding and trying.

Thanks,
T

 

[lbass]

If you can do that, it would be best. Otherwise, I would ask whether you had any other fields that might help with the problem, e.g., is there some field that you can group on to arrive at the groups you want at least (other than the combo of the two fields)?

-LB