Hi everyone,
I need someone's help with regards to running totals and suppressed records. I have a CR 8.5 report that goes like this:
Group #1: State (i.e. AL)
Group #2: Recruiter's Name (i.e. John Smith)
Columns: Placement's Name/Start Date/6 Month Status
Record: (i.e. Mark White/10-31-2000/Employed)
Record: (i.e. Mark White/12-10-2001/Employed)
Record: (i.e. Susie Young/06-20-2002/Unemployed)
Record: (i.e. Amanda Zellers/03-05-1999/Employed)
As you can see, the report is first grouped by state and then grouped by the recruiter's name. Below are three columns for the placement's name (the person seeking to be employed), the start date (the date the person started the new job), and the 6 month status (whether the person is still with the job or whether the person quit/was fired/etc.). Finally, the records are sorted by Placement Name (ascending order) and then by Start Date (ascending order so that the earliest date is first).
You will also notice that a person can appear more than once. Mark White, above, has two records as he started his first job on 10-31-2000 (which he is still employed with) and then he started a new, second job on 12-10-2001 (which he is still employed with as well).
I only wanted the report to show the most recent Start Date so I supressed the duplicate records like so: In the Section Expert, I highlight the Details section, click on the X+2 button by the Suppress (No Drill-Down) option, and enter this formula: {Placement_Name} = Next({Placement_Name})
This formula only kept the last record for each Placement Name (which was the record with most recent Start Date because the start dates were sorted in ascending order).
With the duplicate records suppressed, the report now looks like this:
Group #1: State (i.e. AL)
Group #2: Recruiter's Name (i.e. John Smith)
Columns: Placement's Name/Start Date/6 Month Status
Record: (i.e. Mark White/12-10-2001/Employed)
Record: (i.e. Susie Young/06-20-2002/Unemployed)
Record: (i.e. Amanda Zellers/03-05-1999/Employed)
For each Recruiter, I wanted to find the total count of all Placement's still Employed. So I created a running total like so:
Step 1. Created a counter formula and placed it in Group Header #2 (Recruiter's Name):
whileprintingrecords;
numbervar counter := 0;
Step 2. Created an evaulate formula and placed it in the Details section:
whileprintingrecords;
numbervar counter;
If {6_Month_Status} = "Employed"
then counter := counter + 1
else counter := counter + 0
Step 3. Create a display formula and placed it in the Group Footer #2 (Recruiter's Name):
whileprintingrecords;
numbervar counter;
But when I previewed the results, Recruiter John Smith had a running end total of 3. The report looked like this:
Group #1: State (i.e. AL)
Group #2: Recruiter's Name (i.e. John Smith)
Columns: Placement's Name/Start Date/6 Month Status/RunTotal
Record: (i.e. Mark White/12-10-2001/Employed/2)
Record: (i.e. Susie Young/06-20-2002/Unemployed/2)
Record: (i.e. Amanda Zellers/03-05-1999/Employed/3)
As you can see, Mark White was counted twice (despite suppressing one of the records), Susie was not counted (because she has a 6 Month Status = "Unemployed"
, and Amanda was counted once to bring the end total to 3. I only wanted Mark to be counted once since he now only appears once in the report. The end total should only have been 2.
How do I prevent the running total from counting suppressed records???
Sorry for the lengthy explanation but I wanted to be as thorough as possible. Please help!!!
I need someone's help with regards to running totals and suppressed records. I have a CR 8.5 report that goes like this:
Group #1: State (i.e. AL)
Group #2: Recruiter's Name (i.e. John Smith)
Columns: Placement's Name/Start Date/6 Month Status
Record: (i.e. Mark White/10-31-2000/Employed)
Record: (i.e. Mark White/12-10-2001/Employed)
Record: (i.e. Susie Young/06-20-2002/Unemployed)
Record: (i.e. Amanda Zellers/03-05-1999/Employed)
As you can see, the report is first grouped by state and then grouped by the recruiter's name. Below are three columns for the placement's name (the person seeking to be employed), the start date (the date the person started the new job), and the 6 month status (whether the person is still with the job or whether the person quit/was fired/etc.). Finally, the records are sorted by Placement Name (ascending order) and then by Start Date (ascending order so that the earliest date is first).
You will also notice that a person can appear more than once. Mark White, above, has two records as he started his first job on 10-31-2000 (which he is still employed with) and then he started a new, second job on 12-10-2001 (which he is still employed with as well).
I only wanted the report to show the most recent Start Date so I supressed the duplicate records like so: In the Section Expert, I highlight the Details section, click on the X+2 button by the Suppress (No Drill-Down) option, and enter this formula: {Placement_Name} = Next({Placement_Name})
This formula only kept the last record for each Placement Name (which was the record with most recent Start Date because the start dates were sorted in ascending order).
With the duplicate records suppressed, the report now looks like this:
Group #1: State (i.e. AL)
Group #2: Recruiter's Name (i.e. John Smith)
Columns: Placement's Name/Start Date/6 Month Status
Record: (i.e. Mark White/12-10-2001/Employed)
Record: (i.e. Susie Young/06-20-2002/Unemployed)
Record: (i.e. Amanda Zellers/03-05-1999/Employed)
For each Recruiter, I wanted to find the total count of all Placement's still Employed. So I created a running total like so:
Step 1. Created a counter formula and placed it in Group Header #2 (Recruiter's Name):
whileprintingrecords;
numbervar counter := 0;
Step 2. Created an evaulate formula and placed it in the Details section:
whileprintingrecords;
numbervar counter;
If {6_Month_Status} = "Employed"
then counter := counter + 1
else counter := counter + 0
Step 3. Create a display formula and placed it in the Group Footer #2 (Recruiter's Name):
whileprintingrecords;
numbervar counter;
But when I previewed the results, Recruiter John Smith had a running end total of 3. The report looked like this:
Group #1: State (i.e. AL)
Group #2: Recruiter's Name (i.e. John Smith)
Columns: Placement's Name/Start Date/6 Month Status/RunTotal
Record: (i.e. Mark White/12-10-2001/Employed/2)
Record: (i.e. Susie Young/06-20-2002/Unemployed/2)
Record: (i.e. Amanda Zellers/03-05-1999/Employed/3)
As you can see, Mark White was counted twice (despite suppressing one of the records), Susie was not counted (because she has a 6 Month Status = "Unemployed"
, and Amanda was counted once to bring the end total to 3. I only wanted Mark to be counted once since he now only appears once in the report. The end total should only have been 2.How do I prevent the running total from counting suppressed records???
Sorry for the lengthy explanation but I wanted to be as thorough as possible. Please help!!!