Report Shows Fewer Records After Grouping

[Betty Boop]

I have a report in CR. I have a formula field @Month which takes the Month from a string field ie. 13-Oct-2019 results in Oct. Before I group on that field I have 8 records in "Oct, Nov, Dec, Jan, Feb" several records in some of the months. When I group on the @Month field I only get two records... 1 in Feb and 1 in Nov.

The group headers work correctly, but I don't get all the records.

Also, if I sort on that field I get no records.

 

[lbass]

You need to show the content of your formula (and of any nested formulas). You should also show some sample data at the detail level, including the text field from which you have derived the month.

-LB

 

[Betty Boop]

Sorry. I tried to attach some jpg. but that didn't work so well. Hopefully this will work beter

https://files.engineering.com/getfi...827-bc9d-8c44723aac9e&file=Ungrouped_Data.JPG

https://files.engineering.com/getfi...989c-4277-8ffc-eccb61228886&file=grouping.JPG

https://files.engineering.com/getfi...-4ec7-9174-574fc78a1e03&file=Grouped_Data.JPG

my @Month formula is "mid({Command.TRANSFER_DATE},4,3)" with the months grouped in specified order
the Date Transferred field is also a formula "if({Command.NAME}) = previous ({Command.NAME}) and ({Command.PRIOR_ORG_CODE}) <> previous({Command.PRIOR_ORG_CODE}) then ({Command.TRANSFER_DATE})

If I don't group on anything, the expected records are all there (8 records). If I group on Dept all of the records group except one (J40). When I group on the @Month it doesn't show all records; one record in November and one record in February..

Hopefully this helps. Thanks for your assistance.

 

[lbass]

The problem is your conditional formula for transfer date, which is dependent upon the order of records. When you reorder records by grouping, the conditions no longer apply consistently. You can observe this by adding the name and prior org code to the detail section.

What are you trying to accomplish by adding these conditions to the Date Transferred formula? You might want to try adding both your month formula and your date transferred formula to the detail section and seeing how they change with grouping. My guess is you should be grouping on {@Month} and using it to display the transfer date, and that you don't need the {@date Transferred} formula. You also might consider converting the string date to an actual date in the command so you don't need to use special order for your grouping.

Your sample data is also confusing also because nowhere do you show a name field--is that the department? I assume prior org code is the "From Org Code".

-LB

 

[Betty Boop]

Thanks so much. This might be beginning to make sense. I took the name field out of the report for privacy reasons.

The fields I have in my report are:

@Dept (left({Command.PRIOR_ORG_CODE},3) As Dept
Command.Name As Name
Command.Prior_Org_Code as From Org Code
@To Org Code (previous ({Command.PRIOR_ORG_CODE})as To Org Code
Command.Transfer_Date as Transfer Date
@Month (mid({Command.TRANSFER_DATE},4,3) as Month

Formula Field (Date Tramsfered) - (if({Command.NAME}) = previous ({Command.NAME}) and ({Command.PRIOR_ORG_CODE}) <> previous({Command.PRIOR_ORG_CODE}) then ({Command.TRANSFER_DATE})

That formula field is used to suppress records using Section Expert (If IsNull ({@Date Transferred}) then true
Else if {@Date Transferred} = " " then true
Else False

That all seems to be working until I try to group the records in the report.

It does the same thing whether I use the Command.Transfer_Date or the @Date Transferred in the report.

 

[lbass]

You should temporarily remove your suppression formula so you can see what is going on and then take a look at your detail level data. Your suppression formula doesn't account for the first record so it will always be null for that record--since there is no previous record. However, how you handle this depends upon why you are suppressing records in the first place. Similarly, your {@date transferred} formula will be null because the previous record changes when you sort or group differently.

Is your goal to show the most recent transfer date per name, along with the correct current department? You would be better off creating a maximum transfer date field in the command, but first you would need to convert your string date to an actual date. How you do this depends upon your database. Please identify the type of database--is it Access? Oracle? Or?

-LB