Suppressing Data Based on Not Having Certain Criteria

[lesleycampbell]

I am new at this, so I apologize in advance for any confusing explanations.

I have a report that is grouped by branch, department, and then employee. I only want certain departments to show so I have selection criteria set. I want only certain history event codes to show on the report as well, so I have a subreport with the record selection criteria on the subreport.

Report Example:
(Group) Branch
(Group) Department
(Group) Employee Name
(subreport) history event / date / description

I have 10 history events that I want to show. I want to highlight the employee name group that does not contain a certain history event code of those 10. 'PPA' is the code that I am looking for to make sure it is there. If it's missing, I want the employee name group to be highlighted in order to audit which employees need to be updated.

I can not figure out how to accomplish what I'm trying to do. Please help.
I'm welcome to any answers, questions, criticisms, etc. Thank you!

 

[briangriffin]

This will be much easier without the subreport - is it definitely needed?

Your data seems to suggest that it isn't, since you're linking the subreport using employee name or ID, and you're also grouping Branch--> Department --> Employee.

If it is needed we can work with it, but eliminating the subreport will also speed up processing (usually by a lot).

 

[lesleycampbell]

By all means, if I do NOT have to use a subreport, I would rather not. I have only been working with CR for about 4 months so I am learning everyday...mostly by trial and error. Thank you!

 

[briangriffin]

Not to worry - most new CR users (and a lot of experienced users) fall into the trap of using subreports for everything. Can you please list the tables (and joins if possible) for both the main report and the subreport?

 

[lesleycampbell]

The only tables I am gathering information from are the {EmployeeMaster} and {History} tables. They are joined by EmployeeID fields.

The Department is field in the EmployeeMaster table and I am restricting the departments to certain depts.
I am then restricting the history events to certain history event codes in the 'Event' field from the History table.

Is that what you are looking for?

 

[lesleycampbell]

I'm trying to use the report as an audit report for the history events. The department classification tells me the employees in a certain dept and I need to know by the history codes the employees who have had a certain process started but not completed. So, I need a way to have my first criteria for the event field to limit it to only show the 10 codes I need to see and then the 2nd criteria to see the ones that are missing the one 'PPA' event code. Does that make sense?

 

[briangriffin]

So it sounds like you can just put the event info from the History table in the details section, keep your current groups, and get rid of the subreport, right?

Once you get there, create this formula and put it in the details section:

if EventTable.Code = "PPA" then 1 else 0

Next, insert a sum of the formula field into the Employee group footer. Any Employee that does not have a PPA code will have a 0 for this summary.

Finally, in the Section Expert click on the Employee group header, click the Color tab, and enter this formula:
if sum({@YourDetailsFormula},EmployeeGroup) = 0 then yellow else white

With any luck, the group header for employees with no PPA records will be in yellow and the rest will be white. You can suppress the formula in the details, and also the summary field.

You can also filter on the summary if you only want employees with no PPA record, or you could use the Group Sort Expert to rearrange the Employee group so that all the employees with no PPA record are together.

Depending on your version of CR, some of these steps may vary slightly.

 

[lesleycampbell]

IT WORKED!!! THANK YOU, THANK YOU, THANK YOU!!!

 

[lesleycampbell]

Ok, the detail record select stopped working when I added the branch parameter. Why would that happen? This is why I went with a subreport in my original report. Thank you again for your help.

 

[briangriffin]

Refresh the report, then under the Database dropdown menu select Show SQL Query. Please paste that into this thread and we'll see what's happening.

Also, you're saying that without the parameter the report is returning all the records you expect?

 

[pmax9999]

Also, post the reports Record Selection formula - I suspect the problem is in there.

Pete

 

[lesleycampbell]

I fixed it. I had to put a parenthesis around the branch parameter formula in the record selection.

Thank you!!