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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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
mlmccCommented:
Try adding a pagebreak after on the level group footer.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.