Conditional Formating Question 1

[halfbarrel]

Hello,
I am currently creating letters using oracle reports and have the letters all setup with one issue. I would like to be able to conditionally hightlight or underline a portion of a setence or paragraph in these letters. Every paragraph is a different field in these letters. Is there a way to do this with pl/sql.
Thanks,

Chris

 

[nagornyi]

To conditionally format a field, you may use SRW.SET_FONT_STYLE procedure in the field's format trigger. The question sounds, though, as you may need to apply formatting to a part of the field, and that can not be done directly. To do that, you will have first to break the field into several parts, which is a separate story..

 

[halfbarrel]

Nagornyi,
Thanks, for you response. Would this be possible if I used text labels for the paragraphs instead of fields. I know that you can manually bold or underline just parts of a text label. Can this be done dynamically if a certain sentence falls within this paragraph.
Thanks,

Chris.

 

[nagornyi]

Yes, SRW procedures can be used to format labels as well as fields, but you can not apply conditional formatting to a part of the label.
If you are looking for a certain sentence in a field and need to make it bold, think of the following approach.
1. Create 3 user parameters, they will serve as global variables:
p_text_before
p_the_sentence
p_text_after
2.Create a formula in the same group where the field is that would return somoe bogus value, but would have the parsing of the field in its body.
3. Create a label with the following text in it:
&<p_text_before>&<p_the_sentence>&<p_text_after>
4. Make the middle part bold, like this:
&<p_text_before>&<p_the_sentence>&<p_text_after>

Now, if there is something for the middle part, i.e. the p_the_sentence variable is not blank, the sentence will be bolded.

 

[halfbarrel]

Nagornyi,
Thanks, again. That sounds like it will work perfect.

Chris

 

[halfbarrel]

Nagornyi,
I having another problem with this. I have done what you suggested in the above post but am have a problem within these letters. It seems to me that the text in the paragraphs is off. For example some of these letters only have 4 paragraphs and different text, but after I run the report the letter after it is what has the 4 paragraphs with the text that should be on the previous letter. The query for the report is pulling the data correctly so I don't know where to look.
Here is what I'm doing inside my formula column:

if (instrPARA2,'We ask that you bring all current medications with you in their proper containers') > 0) then
_para2_text_before:=substrPARA2,1,instrPARA2,'We ask that you bring all current medications with you in their proper containers') - 1);
_para2_text:=substrPARA2,instrPARA2,'We ask that you bring all current medications with you in their proper containers'),length('We ask that you bring all current medications with you in their proper containers'));
_para2_text_after:=substrPARA2,length('We ask that you bring all current medications with you in their proper containers') + instrPARA2,'We ask that you bring all current medications with you in their proper containers'), lengthPARA2) - (length('We ask that you bring all current medications with you in their proper containers') + instrPARA2,'We ask that you bring all current medications with you in their proper containers')));
_para3_text_before:=ARA3;
_para3_text:='';
_para3_text_after:='';
_para4_text_before:=ARA4;
_para4_text:='';
_para4_text_after:='';
_para5_text:=ARA5;
_para6_text:=ARA6;
_para7_text:=ARA7;

elsif (instrPARA3,'We ask that you bring all current medications with you in their proper containers') > 0) then
_para3_text_before:=substrPARA3,1,instrPARA3,'We ask that you bring all current medications with you in their proper containers') - 1);
_para3_text:=substrPARA3,instrPARA3,'We ask that you bring all current medications with you in their proper containers'),length('We ask that you bring all current medications with you in their proper containers'));
_para3_text_after:=substrPARA3,length('We ask that you bring all current medications with you in their proper containers') + instrPARA3,'We ask that you bring all current medications with you in their proper containers'), lengthPARA3) - (length('We ask that you bring all current medications with you in their proper containers') + instrPARA3,'We ask that you bring all current medications with you in their proper containers')));
_para2_text_before:=ARA2;
_para2_text:='';
_para2_text_after:='';
_para4_text_before:=ARA4;
_para4_text:='';
_para4_text_after:='';
_para5_text:=ARA5;
_para6_text:=ARA6;
_para7_text:=ARA7;

elsif (instrPARA4,'We ask that you bring all current medications with you in their proper containers') > 0) then
_para4_text_before:=substrPARA4,1,instrPARA4,'We ask that you bring all current medications with you in their proper containers') - 1);
_para4_text:=substrPARA4,instrPARA4,'We ask that you bring all current medications with you in their proper containers'),length('We ask that you bring all current medications with you in their proper containers'));
_para4_text_after:=substrPARA4,length('We ask that you bring all current medications with you in their proper containers') + instrPARA4,'We ask that you bring all current medications with you in their proper containers'), lengthPARA4) - (length('We ask that you bring all current medications with you in their proper containers') + instrPARA4,'We ask that you bring all current medications with you in their proper containers')));
_para2_text_before:=ARA2;
_para2_text:='';
_para2_text_after:='';
_para3_text_before:=ARA3;
_para3_text:='';
_para3_text_after:='';
_para5_text:=ARA5;
_para6_text:=ARA6;
_para7_text:=ARA7;

else
_para2_text_before:=ARA2;
_para2_text:='';
_para2_text_after:='';
_para3_text_before:=ARA3;
_para3_text:='';
_para3_text_after:='';
_para4_text_before:=ARA4;
_para4_text:='';
_para4_text_after:='';
_para5_text:=ARA5;
_para6_text:=ARA6;
_para7_text:=ARA7;

end if;

return('XXX');

Let me know what you think. I'm kind of at a loss here.
Thanks,

Chris.

 

[nagornyi]

I would first suggest introducing some variables
to reduce the size of the code and enhance its clarity.
As for the wrong number of paragraphs, I think the reason is that parameters,
being global variables, keep their values from previous letter,
unless you nullify them. Thus, you need to add that section also (bold in the code.)

v_text char(81):= 'We ask that you bring all current medications with you in their proper containers';
v_len Number := length(v_text);
v_in_para2 Number := instr(:PARA2,v_text);
v_in_para3 Number := instr(:PARA3,v_text);
v_in_para4 Number := instr(:PARA4,v_text);


BEGIN
     [b]
      :p_para2_text_before:='';
      :p_para2_text:='';
      :p_para2_text_after:='';
      :p_para3_text_before:='';
      :p_para3_text:='';
      :p_para3_text_after:='';
      :p_para4_text_before:='';
      :p_para4_text:='';
      :p_para4_text_after:='';[/b]

      :p_para5_text:=:PARA5;
      :p_para6_text:=:PARA6;                            
      :p_para7_text:=:PARA7;

if (v_in_para2 > 0) then   
      :p_para2_text_before:=substr(:PARA2,1,v_in_para2 - 1);
      :p_para2_text:=substr(:PARA2,v_in_para2,v_len);
      :p_para2_text_after:=substr(:PARA2,v_len + v_in_para2, length(:PARA2) - (v_len + v_in_para2));
      :p_para3_text_before:=:PARA3;
      :p_para3_text:='';
      :p_para3_text_after:='';
      :p_para4_text_before:=:PARA4;
      :p_para4_text:='';
      :p_para4_text_after:='';                                       
 else

if (v_in_para3 > 0) then
      :p_para3_text_before:=substr(:PARA3,1,v_in_para3 - 1);
      :p_para3_text:=substr(:PARA3,v_in_para3,v_len);
      :p_para3_text_after:=substr(:PARA3,v_len + v_in_para3, length(:PARA3) - (v_len + v_in_para3));
      :p_para2_text_before:=:PARA2;
      :p_para2_text:='';
      :p_para2_text_after:='';
      :p_para4_text_before:=:PARA4;            
      :p_para4_text:='';
      :p_para4_text_after:='';
      
   else

 if (v_in_para4 > 0) then
      :p_para4_text_before:=substr(:PARA4,1,v_in_para4 - 1);
      :p_para4_text:=substr(:PARA4,v_in_para4,v_len);
      :p_para4_text_after:=substr(:PARA4,v_len + v_in_para4, length(:PARA4) - (v_len + v_in_para4));
      :p_para2_text_before:=:PARA2;
      :p_para2_text:='';
      :p_para2_text_after:='';
      :p_para3_text_before:=:PARA3;
      :p_para3_text:='';
      :p_para3_text_after:='';
      
   else
      :p_para2_text_before:=:PARA2;
      :p_para2_text:='';
      :p_para2_text_after:='';         
      :p_para3_text_before:=:PARA3;
      :p_para3_text:='';
      :p_para3_text_after:='';         
      :p_para4_text_before:=:PARA4;
      :p_para4_text:='';
      :p_para4_text_after:='';
      
   end if;
   
  return('XXX');

END;
________________________________________________________________________________________________________

 

[halfbarrel]

Nagornyi,
I gave you code a try and although it does clean up the readability a lot, it wasn't the problem. It seems to me not like the parameters are keeping there variables, but like there are getting them before they are supposed to. The letter before always has what the correct text for the next letter would be. Also, I added the actual fields PARA2, PARA3, PARA4 in the letter and they had the correct text. This is confusing to me because I don't really understand at what point this little code snippet runs or anything. Let me know if you have any more ideas.
Thanks,

Chris.

 

[nagornyi]

I assume the code above is placed to some formula column, which is in the same group as fields PARA2, PARA3, etc.
In the layout, put the formula column to the same frame you placing the parameter labels. That would force the program unit to run. So that you would not see the formula output 'XXX' on each letter, make its text color white.

 

[halfbarrel]

Nagornyi,
I tried your idea above but it is still not working. I believe my problem is a repeating frame that I have. When I delete this frame everything seems to work right(The frame is need so that when a patient has multiple appointments for the same day in the same department it shows multiple appointment lines). I try to give you some details below. Let me know what you think.

Groups:
G_APPT_GROUP
APPT_DATE1
APPT_DEPARTMENT
APPT_LOCATION
MEDICAL_HIST_NO
G_REMINDER_DATA
CF_1 (FORMULA COLUMN)
DEPARTMENT_NAME
FIRST_NAME
FULL_NAME
LOC_ADDRESS
LOC_CITY
LOC_PHONE
LOC_STATE
LOC_ZIP
MAILING_ADDRESS
PARA1
PARA2
PARA3
PARA4
PARA5
PARA6
PARA7
G_APPT_DATA
APPT_TIME
RESOURCE_NAME

Here's how my layout is setup:

BEGIN G_APPT_GROUP FRAME
BEGIN G_REMINDER_DATA FRAME

DEPARTMENT_NAME
LOC_ADDRESS
LOC_CITY, LOC_STATE LOC_ZIP
LOC_PHONE

FULL_NAME
MAILING_ADDRESS

CF_1
Dear FIRST_NAME
PARA1

BEGIN G_APPT_DATA FRAME
APPT_DATE APPT_TIME RESOURCE_NAME
END G_APPT_DATA FRAME

P_PARA2_TEXT_BEFORE P_PARA2_TEXT P_PARA2_TEXT_AFTER

P_PARA3_TEXT_BEFORE P_PARA3_TEXT P_PARA3_TEXT_AFTER

P_PARA4_TEXT_BEFORE P_PARA4_TEXT P_PARA4_TEXT_AFTER

PARA5

PARA6

PARA7
Sincerely,


DEPARTMENT_NAME

END G_REMINDER_DATA FRAME
END G_APPT_GROUP FRAME

 

[nagornyi]

You need multiple appointment lines in the same reminder letter, i.e. for the same patient, correct? If so, it seems the group G_REMINDER_DATA should go before G_APPT_GROUP in the data and layout models. Otherwise you are getting all patients for current APPT_DATE/DEPARTMENT/LOCATION, not all appointments for the current patient.

 

[halfbarrel]

Nagornyi,
It does need to be grouped by PATIENT(MEDICAL_HIST_NO)/APP_DATE/DEPARTMENT/LOCATION because each Department/Location combo has a different letter format(PARA1,PARA2,PARA3,etc... and different for each).
Thanks,

Chris.

 

[nagornyi]

As I understand it so far, you are confirming waht I said. Patient is outer group, then goes appointment one, bringing different paragraphs for different departments.

 

[halfbarrel]

Nagornyi,
Thanks for your continued help. I have tried about every arrangment of my groups possible, but cannot get it right. Maybe I don't have them setup right at all. Let me explain the data a little and maybe you can tell me what you think. A patient can have multiple appointments in a day in any department/location combo. If a patient has more that one appointment in a day at the same department/location combo they need to be on the same letter(Same patient,department,location,date). Thus the G_APPT_DATA frame to display appointment time and Doctor name. The paragraphs sit in a separate table and every Department/Location combo has a different set of paragraphs. Knowing this could you suggest the appropiate groups for the fields and their order. I'll list the fields bellow.



APPT_DATE
APPT_DEPARTMENT
APPT_LOCATION
MEDICAL_HIST_NO - (Unique patient id)
CF_1 (FORMULA COLUMN)
DEPARTMENT_NAME
FIRST_NAME (patient)
FULL_NAME (patient)
LOC_ADDRESS (location)
LOC_CITY (location)
LOC_PHONE (location)
LOC_STATE (location)
LOC_ZIP (location)
MAILING_ADDRESS (patient)
PARA1
PARA2
PARA3
PARA4
PARA5
PARA6
PARA7
APPT_TIME
RESOURCE_NAME (doctor)

Thanks again,

Chris.

 

[nagornyi]

OK. If you generate letters for different patients in the same report run, the uppermost group should be for patients. Then goes group for Department/Location/Date, so that each unique combination of these values give rise to a new letter. Then, what is left for the detail section group is appointment info. So that if there are several appointments for the same Department/Location/Date, the appointment information (not sure whay that is: Doctor? Time? else?) would go to the same letter.
If still problems, could you please posst a sample of the query output, reduced to a reasonable size, and then how you would like the information grouped using the given data. Thanks.

 

[halfbarrel]

Nagornyi,
Here is a sampling of 3 records(bottom). Sorry for the unreadability it would probably be best to copy them out and view them in a text editor. I pipe delimeted them to make it a little easier. The first 2 records you will see are the same patient/date/department/location so I they would display on the same letter. The last letter is a different patient, same department, but different location so you will notice that the paragraphs are different. Here is how I would like the first letter to look.

Dermatology
136 N AVE
LACROSSE, WI 54601
(608)999-9999


JOHN E DOE
511 N PUMPKIN ST
LA CROSSE, WI 54601


Dear John,
We are looking forward to seeing you on 1/25/2005 with

JAMES D. HOGAN MD 08:45 AM
JERRY J. MILLER MD 03:30 PM

Dermatology is located on the 4th floor of Founders Building across from the main clinic at: 136 N Avenue, La Crosse, WI.

We ask that you bring all current medications with you in their proper containers. If you are unable to keep this appointment, kindly give 48 hours notice by calling (608)782-7300 or toll free 1-800-362-9567, extension 52382.

If you are scheduled to have tests prior to your appointment time, please refer to your original card for the times and locations of your tests.

When you arrive at the clinic, please allow adequate time for parking and to update any needed patient information.

Thank you for the the opportunity to serve you.
Sincerely,

Gundersen Lutheran, DERMATOLOGY



MEDICAL_HIST_NO | APPT_DATE | APPT_TIME | APPT_DEPARTMENT | APPT_LOCATION | LOC_ADDRESS | LOC_CITY | LOC_STATE | LOC_ZIP | LOC_PHONE | FULL_NAME | FIRST_NAME | MAILING_ADDRESS | DEPARTMENT_NAME | RESOURCE_NAME | PARA1 | PARA2 | PARA3 | PARA4 | PARA5 | PARA6 | PARA7

1|01/25/2005|08:45 AM|DERM|MAIN|136 N AVE|LACROSSE|WI|54601|(608)999-9999|JOHN E DOE|John|511 N PUMPKIN ST LA CROSSE, WI 54601|DERMATOLOGY|JAMES D. HOGAN MD|We are looking forward to seeing you on|Dermatology is located on the 4th floor of Founders Building across from the main clinic at: 136 N Avenue, La Crosse, WI.|We ask that you bring all current medications with you in their proper containers. If you are unable to keep this appointment, kindly give 48 hours notice by calling (608)782-7300 or toll free 1-800-362-9567, extension 52382.|If you are scheduled to have tests prior to your appointment time, please refer to your original card for the times and locations of your tests.|When you arrive at the clinic, please allow adequate time for parking and to update any needed patient information.|Thank you for the the opportunity to serve you.|

1|01/25/2005|03:30 PM|DERM|MAIN|136 N AVE|LACROSSE|WI|54601|(608)999-9999|JOHN E DOE|John|511 N PUMPKIN ST LA CROSSE, WI 54601|DERMATOLOGY|JERRY J. MILLER MD|We are looking forward to seeing you on|Dermatology is located on the 4th floor of Founders Building across from the main clinic at: 136 N Avenue, La Crosse, WI.|We ask that you bring all current medications with you in their proper containers. If you are unable to keep this appointment, kindly give 48 hours notice by calling (608)782-7300 or toll free 1-800-362-9567, extension 52382.|If you are scheduled to have tests prior to your appointment time, please refer to your original card for the times and locations of your tests.|When you arrive at the clinic, please allow adequate time for parking and to update any needed patient information.|Thank you for the the opportunity to serve you.|

8|01/26/2005|11:30 AM|DERM|OVIRH|57 NORTH MAIN|VIROQUA|WI|54665|(608)888-8888|JOHN C DOE|John|112 MANDARIN AVE VIROQUA, WI 54665|DERMATOLOGY|JAMES D. HOGAN MD|We are looking forward to seeing you on|We ask that you bring all current medications with you in their proper containers. If you are unable to keep this appointment kindly give 48 hours notice by calling (608)775-2382 or toll free at 1-800-362-9567, extension 52382.|If you are scheduled to have tests prior to your appointment time, please refer to your original appointment card for the times and locations of your tests.|Thank you for the opportunity to serve you.|||

Like I said earlier the paragraphs only seem to be wrong on the letter before a Department/Location combo change and the actual letter where it does change. The problem goes away if I delete the repeating frame for the group with appointment time and resource name(doctor). Let me know what You think. I'm sorry this is becoming so much trouble but like I said these groups have been really confusing me.
Thanks,

Chris.

 

[nagornyi]

I was able to get the data to excel, no problem. If you are not doing the bold text and just placing PARA1,2,3..., is it still the same problem? Please, restrict your SQL to the above 3 rows and let me know what are those 3 letters you are getting. Also, please let me know what groupping you ended up with. That shoul give the entire picture of the problem. Thanks.

 

[nagornyi]

I have created a temporary table in Oracle and populated it with 3 lines you posted. Here is how I was able to generate 2 letters, the first one with 2 appointments, second one with 1 appointment.
There are 2 groups only: G_VISITS and G_VISIT_APPOINTMENTS. The first group keeps all fields except for 2 that go to the second group, which are RESOURCE_NAME and APPT_TIME. The outer repeating frame for the first group I stretched to the entire page and set Max Record per page property to 1, to have one letter per page.

 

[halfbarrel]

Nagornyi,
I set the groups and frames up the same way and I am still having problems. Do you have the logic in your report to underline the text? My probelms are all with the the text labels for PARA2, PARA3, and PARA4. I don't do anything with PARA1, PARA5, PARA6, or PARA7 in the code for the formula column anymore and there are correct in every letter. The text lables for PARA2,PARA3, and PARA4 are correct if I take out the repeating frame for RESOURCE_NAME and APPT_TIME. Also, if I put in the actual fields for PARA2, PARA3, and PARA4 they are correct. It seems to me that all my problems stem between the formula column that does the splitting of text so that I can underline it and the repeating frame that shows multiple apppointments. Sorry for all the problems and thanks again,

Chris.

 

[nagornyi]

OK, I see the problem. Sometimes parameters behave like that if used as global variables. Let's replace parameters with placeholders.
1. Delete all the parameters.
2. In the group keeping PARA1,2,... create placeholders with the names, types, and lenths exactly as the parameters used to be.
3.You may not delete the parameters first, but you will have to give different names to the placeholders then, and correct the text of CF_1 function and labels accordingly.
How does it look now?