Records Not Filtering Properly

I have a complicated HR Report that breaks down the positions available by department.
There is one department, #99900 that has 2 different levels to it; level 1 and level 2.

When I run my report, all the positions for Department 99900 are being placed on level 1, even though some belong on level 2. If I go to the next page, it shows me the department name for level 2, but no records. What can I do to fix this?

Thanks
metalteckAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mlmccConnect With a Mentor Commented:
Try adding a pagebreak after on the level group footer.

mlmcc
0
 
peter57rCommented:
It appears that you have not defined your grouping correctly.
I assume you must have two grouping ranks defined, GH1 for department and GH2 for Level.
Point at the group header labels in the left margin and check that you are using the correct fields.
0
 
metalteckAuthor Commented:
This is my code:


 SELECT "PRSAGDTL"."PAY_STEP", "PRSAGDTL"."PAY_RATE", "PAPOSITION"."JOB_CODE", "PAPOSITION"."R_POSITION", "PAPOSITION"."DEPARTMENT", "PAPOSITION"."DESCRIPTION", "PAPOSITION"."PAY_GRADE", "PAPOSITION"."OT_PLAN_CODE", "EMPLOYEE"."EMPLOYEE", "EMPLOYEE"."EMP_STATUS", "PRSHIFTTBL"."RATE", "PRSHIFTTBL"."SHIFT_CODE", "DEPTCODE"."NAME", "DEPTCODE"."DEPARTMENT", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."FIRST_NAME", "PAPOSITION"."END_DATE", "EMPLOYEE"."PAY_RATE", "DEPTCODE"."PROCESS_LEVEL", "PAPOSITION"."PROCESS_LEVEL"
 FROM   ((("PROD"."prod"."PAPOSITION" "PAPOSITION" LEFT OUTER JOIN "PROD"."prod"."PRSAGDTL" "PRSAGDTL" ON (("PAPOSITION"."COMPANY"="PRSAGDTL"."COMPANY") AND ("PAPOSITION"."SCHEDULE"="PRSAGDTL"."SCHEDULE")) AND ("PAPOSITION"."PAY_GRADE"="PRSAGDTL"."PAY_GRADE")) LEFT OUTER JOIN "PROD"."prod"."EMPLOYEE" "EMPLOYEE" ON "PAPOSITION"."R_POSITION"="EMPLOYEE"."R_POSITION") LEFT OUTER JOIN "PROD"."prod"."PRSHIFTTBL" "PRSHIFTTBL" ON "PAPOSITION"."SHIFT"="PRSHIFTTBL"."SHIFT") LEFT OUTER JOIN "PROD"."prod"."DEPTCODE" "DEPTCODE" ON "PAPOSITION"."DEPARTMENT"="DEPTCODE"."DEPARTMENT"
 WHERE  "PAPOSITION"."DEPARTMENT" NOT  LIKE 'Z%' AND  NOT ("PAPOSITION"."DEPARTMENT" LIKE '10001%' OR "PAPOSITION"."DEPARTMENT" LIKE '95130%' OR "PAPOSITION"."DEPARTMENT" LIKE '99990%') AND "PAPOSITION"."JOB_CODE"<>'XYZ' AND ("PAPOSITION"."END_DATE">={ts '1753-01-01 00:00:00'} AND "PAPOSITION"."END_DATE"<{ts '1753-01-01 00:00:01'})
 ORDER BY "DEPTCODE"."DEPARTMENT", "PAPOSITION"."PAY_GRADE" DESC, "PAPOSITION"."R_POSITION", "EMPLOYEE"."EMPLOYEE"

I have a total of 4 groups already and don't understand why that particular field is not going where it needs to. I have not place a grouping based on the level though.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
peter57rCommented:
'I have not place a grouping based on the level though.'

then I don't understand what your original question means.  How are controlling what records belong to  level1 and level 2.
0
 
metalteckAuthor Commented:
I'm not right now. Of all the departments, this department is the only one that has 2 levels in them. I tried placing a new grouping based on the levels right underneath the department, but when I scroll down to that particular department, it still does not place them in different places. I guess what I need is a suggestion of how to accomplish this.
0
 
metalteckAuthor Commented:
If i do place a group header based on levels, all the report does is under level one, it will have all the positions, but separate them by levels in the department. This is half what I want. I would like for the positions in level 2, to not appear on level one's page and only on level 2.
0
 
metalteckAuthor Commented:
Here is my report itself...if you scroll to department 99900 you will see that both levels are being shown for the level 1 and on the next page, which is level 2 is blank.
Salary-Grade-Report-by-Dept-1a.txt
0
 
metalteckAuthor Commented:
The previous file did not have any saved data, this one does.
Salary-Grade-Report-by-Dept-1a.txt
0
 
James0628Commented:
I tried d/l'ing both of the report files and CR 10 gives me "failed to open document", followed by "invalid report scema", on each file.  I don't know if there's something wrong with the files or if I got bad copies somehow.  You could try u/l'ing the file again, or wait and see if anyone else is able to run it.

 In the meantime, from your original message, I would have expected something like the following:

 In your data you have a DEPARTMENT and a LEVEL, with something like:

 DEPARTMENT     LEVEL
      99800                 1
      99900                 1
      99900                 2
      99999                 1

 In your report you have a group on DEPARTMENT and then LEVEL, which should give you something like:

 99800
     1
 99900
     1
     2
 99999
     1


 But then you said "I have not place a grouping based on the level though", which leaves me wondering.  If what you have isn't like what I described above, can you describe it in further detail?

 James
0
 
metalteckAuthor Commented:
To open the file, you need to download it and then rename the extension to .rpt instead of txt.
You are correct, the first 2 groups are DEPARTMENT and LEVEL and I did do as you mentioned above.
EX:
Department    
95000            
1
99900            
1
2            

What I have now is that it will page break on every department change. This works on every department, but for department 99900, since there are 2 levels, the example above shows how the results look. Everything shows up for the first page of department 99900, but the second page is blank.
I want to be able to have department 99900 split on both pages according to level.
0
 
metalteckAuthor Commented:
I tried this last night and saw that it worked, but now that you said the same thing, you verified it for me.
Thank you
0
 
James0628Commented:
FWIW, of course I changed the extension of the two files you u/l'ed from txt to rpt, and CR gave me the errors I mentioned.  I don't know if there was something wrong with the files on the EE server, or if maybe the d/l's were corrupt because the browser didn't interpret them correctly, because it thought they were text instead of "binary" (just a thought).  I suppose it's a moot point now, but it might be something that you'll have to deal with if you post files here again.

 IAC, I'm glad you found a solution.

 James
0
All Courses

From novice to tech pro — start learning today.