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.
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
-LB
RE: Report Shows Fewer Records After Grouping
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
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
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
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