Crystal Reports SQL Expression

Question

I am having trouble figuring out how to pull a value from a secondary table, to use as selection criteria on a per-record basis.

I am working with Crystal Reports 2011 on Windows 7, over an ODBC connection to an Oracle 11g database.

I am creating an employee directory that utilizes information from two locations:

1. table: TEACHERS
2. view: PVSIS_CUSTOM_TEACHERS

The teachers table is set up with your predictable fields: id, lastname, firstname, telephone, address, city, state, zip, etc. etc. etc.

The view has the following fields available:

1. TEACHERID
2. FIELD_NAME
3. TEXT_VALUE

The database application I am using allows me to create "custom fields" that are related back to the main teachers table. In truth, the fields I am creating are actually stored in a separate table, but are then accessible through the PVSIS_CUSTOM_TEACHERS view. Since the database application allows for any number of "custom fields" to be created, the view can have any number of records in it that can be tied back to the records within the teachers table.

There are MANY custom fields that have been created, but for the purposes of my current project, only 3 of them matter:

• empDirSupRecord
• empDirSupPhone
• empDirSupAddr

The view for my personal teacher record would look like this:

TeacherID    Field_Name         Text_Value
1            empDirSupRecord    
1            empDirSupPhone     1
1            empDirSupAddr      1
1            AnotherField       another_value
1            YetAnotherField    yetanother_value

(This would indicate that I've asked for my phone and address to be suppressed, but would still want my name to be included in the directory)

These fields will each contain a '1' if the user has asked that their phone number, or address not be published, or if we need to suppress the entire record altogether.

When I first started my report, I pulled both the table and view into the database expert and linked them together with teachers.id = pvsis_custom_teachers.teacherid. However, this causes each teacher's name to print on the report once for every record with their teacher id in the view. Since that's not the behavior I want, I removed the view from the database expert, and tried using SQL Expression fields to retrieve the contents of the custom field. This is where I'm currently stuck. I would need to write the sql in a way that selects the correctly named field, for each of the teacher records as the record is being processed by the report.

Currently, my sql expressions statement is written as:

(SELECT text_value FROM pvsis_custom_teachers WHERE field_name = 'empDirSupRecord' AND teacherid = '1')

What I need to do is figure out how to get the report to intelligently select the record for teacherid = (whatever teacherid is currently being processed). I'm not sure if SQL Expression fields are the way to go to accomplish this, so am definitely open to alternate suggestions if my current approach will not work.

Thanks for taking a look. :-)

Solution

You're almost there with the SQL Expression. You can refer back to the main query with double quoted field names. So what you're looking for is:

case when "teacher"."id" is null then null
else (SELECT max(text_value)
      FROM pvsis_custom_teachers 
      WHERE field_name = 'empDirSupRecord' AND teacherid = "teacher"."id")
end

Note that CR will likely complain without the null check and use of max(), since it wants to be sure that only a scalar will ever be returned.

The alternative, and likely less-performance-intensive way to do this, is to join the table and view like you first described. Then, you can group by {teacher.id} and keep track of each field name in the view via variables. This will require more work and more formulas, though. Something like this, for example:

//Place this formula in the Group Header
whileprintingrecords;
stringvar empDirSupRecord:="";

//Place this formula in the Details section
whileprintingrecords;
stringvar empDirSupRecord;
if {pvsis_custom_teachers.field_name} = 'empDirSupRecord'
      then empDirSupRecord:={pvsis_custom_teachers.text_value}

//Place this formula in the Group Footer
whileprintingrecords;
stringvar empDirSupRecord;

Comments

[Brian Brock]

Thanks for the response! I think that's got me going again! :)