Help with a Report Design

[bluize]

Hello everyone ...

I have a A5V10.5 database that I have setup to capture data from our college admission system. I receive a .csv file that I clean-up and then save as an Excel file for import into my data sets. There is one filed that I receive from the admission system which is Ethnic Code, which is a number relating to what ethnicity a student is, which I convert to its corresponding name, i.e., Hispanic, White (Non-Hispanic),Black/African-American Only, etc. I have been running a report that generates a report that Groups by Ethnic Name and does a count of the students. All is well.

In order for the college to comply with regulatory requirements they have changed the admission system as follows:

They kept the old field of Ethnic Code (for all the old students in the system) and added the following fields: Ethnic2, Ethnic3, Ethnic4, Ethnic5, Ethnic6, Ethnic7 and Ethnic8 which corresponds to the following - Hispanic, White (Non-Hispanic), Black (Non-Hispanic), Asian, American Indian or Alaskan Native, International, Hawaiian or Pacific Islander

Instead of entering a Number (1 for White, etc.) they enter a Y in the appropriate Ethnicity. I have converted the "Y" in the Ethnic(2-8) fields to be whatever the appropriate Name should be for that Field.

Now here is my problem, I need to put together a report that lists each of the Ethnic(2-8) fields with the count. I need to have them break on change of Ethnic(2-8) with a total count for that Ethnic Name and a grand total at the bottom.

I have never put together a report that breaks on multiple value changes and does totaling.

Any suggestions would be greatly appreciated.

Thanks,

Ben

[DaveM]

Re: Help with a Report Design

Basically use a group break on that field and then a calc field as total of the group. It is not that hard once you get your head around it.
First create a report and do the group break.

Then worry about the total of the group

You can then do a total of all at the last if you want to. Temprarily, it would tell you if you have them all in there.

[bluize]

Re: Help with a Report Design

Dave,

Maybe I'm missing something, there isn't just a single field to break on, there are actually a total of 7 with names like Ethnic2, Ethnic3, Ethnic4,etc. How do I get the Report Genie to have multiple breaks?

Ben

[Peter.Greulich]

Re: Help with a Report Design

Dumb question: Why on Earth did they create 7 new ethnic fields?

You may have to normalize that when you do your csv to Excel cleanup by merging them into one field. Or you might do it after you get the data into Alpha using a calc field expression such as:

case(isnotblank("Ethnic_Code'),Ethnic_Code,isnotblank("Ethnic2'),Ethnic2,isnotblank("Ethnic3'),Ethnic3,etc) and breaking on that field.

[bluize]

Re: Help with a Report Design

Peter, it isn't a dumb question, the answer is that for reporting purposes to the Higher Education Co Board and the Feds, people can now choose that they are a ethnic mix, i.e., White and Black or Hispanic, Black and Asian so an individual can select any or all the different ethnic types to identify their shelf.

Please forgive my ignorance, but I'm not following what your calc field is doing. Is it creating a field called Ethnic_Code and populating it with whatever value it finds in either Ethnic2,Ethnic3, etc. and then set my Group on Ethnic_Code?

Ben

[Peter.Greulich]

Re: Help with a Report Design

people can now choose that they are a ethnic mix, i.e., White and Black or Hispanic, Black and Asian so an individual can select any or all the different ethnic types to identify their shelf.

I see.

Well my method won't work if you can select more than one ethnicity. The idea was to break on that calc field, which would have just one value.

[Peter.Greulich]

Re: Help with a Report Design

Anyway, this gets complicated. How do you want to break - just on ethnicity, regardless if the total count exceeds the number of students?

[bluize]

Re: Help with a Report Design

I would say that it would be OK to report something like this for ones that have one or maybe multiples - Hispanic for a single Ethnicity and say Hispanic/Hispanic & 1 or More Races.

Ben

[Peter.Greulich]

Re: Help with a Report Design

Sorry I'm a little slow.

Let's say you have the following:
Joe - Caucasian
Jose - Hispanic
Marie - Caucasian & Hispanic
---
Total = 3


Then do you want to report:
Caucasians: 2
Hispanics: 2
---
Total = 4

[bluize]

Re: Help with a Report Design

In your example I would want to report:
Caucasians: 1
Hispanics: 1
Caucasians 1 or more races: 1
----
Total = 3

[Peter.Greulich]

Re: Help with a Report Design

Why wouldn't the latter be: Hispanics 1 or more races: 1

How do you decide which ethnicity takes precedence?

[bluize]

Re: Help with a Report Design

Everything goes off the base ethnicity, i.e., Caucasian, Hispanic, Black, Asian, etc. then derivations after that. In your example, I made a mistake since in the Admission System the data entry screen goes in the following order:

Hispanic
White
Asian Only
American Indian/Alaskan Only
International
Hawaiian/Pacific Islander Only

As the data entry people go down the screen as well as the registration forms filled out by the students, the registration form mirrors the data entry screen, so that the student picks the first primary race then any subsequent ones going from top to bottom.

So you are right, the count should be Hispanics 1 or more races: 1

[Peter.Greulich]

Re: Help with a Report Design

Got it.

I think you will need to create a calc field on the table level to see if any of the Ethnic2 or higher fields have a value and return "Caucasians 1 or more races" or "Hispanic", etc. Then break on that calc field.

[bluize]

Re: Help with a Report Design

So you mean create a field(s) in my set that is a calc field on each student record that gets populated as "Caucasians 1 or more Races" ? I've never created a calc field in my data set before, is there instructions somewhere and exactly how does this work?

[Peter.Greulich]

Re: Help with a Report Design

Not your set, your table. Create a new field called it Race or whatever. In field rules define it as a calc field. Then create the expression in the expression box.