×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Report Shows Fewer Records After Grouping

Report Shows Fewer Records After Grouping

Report Shows Fewer Records After Grouping

(OP)
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.

RE: Report Shows Fewer Records After Grouping

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

RE: Report Shows Fewer Records After Grouping

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

https://files.engineering.com/getfile.aspx?folder=...
https://files.engineering.com/getfile.aspx?folder=...
https://files.engineering.com/getfile.aspx?folder=...

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.

RE: Report Shows Fewer Records After Grouping

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

RE: Report Shows Fewer Records After Grouping

(OP)
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.

RE: Report Shows Fewer Records After Grouping

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close