Formatting SQL field output (Bold & multi-line 'Can Grow')

[Former Member]

I have a subreport that I need to conditionally format the output from a SQL field in the Details section].  Let's call this field Output.Description of 256 characters.

1) IF the Output.Description field contains a colon (":") then I want to format the output for EVERY character before and including the quote to be BOLDED and UNDERLINED.  Otherwise, I do nothing - leave the output as-is but still do item #2 below.

2) I also want to INDENT each and every line AFTER the first line about 3-5 characters so that the output is easy to read.

How can I do this?

Output looks something like this:

     Hello world, welcome to planet

        Earth

     Hello world: welcome to planet

        Earth

Thanks!!!

[abhilash_kumar · Accepted solution]

Hi Tom,

1) Here's how would do Part 1. Create a formula with this code:

stringvar str := Output.Description;

if instr(str,":") > 0 then

    '<b><u>'&left(str,instr(str,":"))&'</b></u>'&

    Mid(str,instr(str,":")+1)

else

    str;

Right-click the field > Format Field > Paragraph tab > Look for 'Text Interpretation' and choose 'HTML Text' from the drop-down.

2) For Part 2, it would help if the Output.Description has a 'New Line Feed' to separate each line. So, in the above example, how does the word 'Earth' end up on a new Line? Does the field already have it setup that way or is it caused due to the fact that you've turned on the 'Can Grow' feature?

-Abhilash

[Former Member · Accepted solution]

Wow!  Thanks, Abhilash!

I will try your response to #1 on Monday AM but it does look like it should work!  For #2 the Output.Description field is set to "Can Grow".  I am aware that if I have a new field I can indent the second line but in this case the field is the same for the first and subsequent lines (2 and higher).  Hence, if I write "Hello world, welcome to planet Earth" and it fits on the first line then there is no need for any adjustments of lines #2 and higher.  However, if the text does not fit into the first line then lines #2 and higher need to be indented 3-5 spaces for readability under the 'Can Grow' checkbox selection.

Does this help?

Thanks again!!!

[abhilash_kumar · Accepted solution]

Well, that's the difficult part! Since the field does not have a New Line feed, you would need to set the line to break after a set number of characters. You also need to make sure one particular word breaks as a whole and then set the indents for the 2nd line.

Let me think about how to do this and I'll post a formula.

-Abhilash

[Former Member · Accepted solution]

Abhilash!

As an update/fyi, i have CR Xi if that makes a difference.  That is, the "Paragraph tab>Look for 'Text Interpretation' and choose 'HTML Text' from the drop-down" that you suggested doesn't exist.  I have the Paragraph and HTML tabs but within the Paragraph tab I don't have the "Text Interpretation". 

How does this affect things?

😉

THANKS!

[abhilash_kumar · Accepted solution]

Maybe the placement of the option has changed, however the idea is to select the option so that CR can interpret the HTML tags in the formula. What do you have in the HTML tab?

-Abhilash

[Former Member · Accepted solution]

Ok.. what do I have in the HTML tab:

************************************************************************

Hyperlink type: [Note: All six (6) below are radio buttons]

No hyperlink                         An Email address

A website on the Internet       A File

Current website field value     Current email field value     [These two are grayed out]

DHTML Viewer only

Report Part Drilldown            Another Report Object       [These two are grayed out]

Hyperlink Information

No Hyperlink defined

This field grayed out when "No hyperlink" radio button is off.   [Note: When I selected "A website on the Internet", I actually put your code in this area under the "X+2" but didn't get anything to work.  I changed Output.Description to {Output.Description} by selecting/copying the correct table.field from the report.]

Helpful hint:

A hyperlink is not associated with this object.  To create a hyperlink, please choose one of the other hyperlink types.

                                                                OK          Cancel        Help buttons

**************************************************************************************************

THANKS again!!!!

[Former Member · Accepted solution]

Let me update one thing that I've noticed. I erred on the section of the report.  It's not the Detail section.  It's the Group Header 1g section.  FWIW, I don't see a Paragraph>Text Interpretation for many fields in the report except (primarily) for most of the Main Reports grayed-out fields in the Design tab>Page Header.  I see nothing under Horizontal Alignment.

THANKS again!

[JWiseman · Accepted solution]

hi Tom, not sure why your version of cr doesn't have the options that Abhilash mentioned.

here's an alternative way to do what you want:

1) create a new formula called sBefore with syntax like

stringvar sb:= {Product_Type.Description};

numbervar sbi:= instr(sb, ':');

if sbi > 0

    then sb[1 to sbi + 1];

2) create another formula sAfter with syntax like

stringvar sb:= {Product_Type.Description};

numbervar sbi:= instr(sb, ':');

sb[sbi + 1 to length(sb)];

3) Insert a Text Object onto your report

4) drag sBefore into the text object and then double click on that formula and highlight it so it's bold and underlined.

5) type a space after sBefore and then drag sAfter into the text object and make sure it's not formatted.

cheers,

jamie

[Former Member · Accepted solution]

Hi Jamie!

Worked for the Bold/Underlining]!  But I have couple questions for clarification.  I created the sBefore and sAfter per your instructions:  Bold/Underlined the sBefore.  Did nothing for the sAfter, as you suggested.

I did not "..type a space after sBefore and then drag sAfter into the text object..." [My text object is the original text object..the actual field I wanted to bold/underline.  Hence, no new/additional text objects were introduced into this subreport.]

1) Do I really need the space between sBefore and sAfter?  When I did it initially I lost my text object! I don't have one now to my knowledge! ;-)]]]]]]]]]]]]]]]]]]]

2) What about the indenting for lines 2+ in all cases?  The field is approximately 256c.  For readability, the 2nd and subsequent lines are desired to be indented about 3-5 characters.

THANKS!!!!  You're a jewel!  😉

[JWiseman · Accepted solution]

hi Tom,

sorry, i completely missed the part of the hanging indent / second line + indent. i will have to get back to you if i can find anything.

Abhilash is on the right track though using html formatting for this as a hanging indent doesn't exist in crystal's Text Objects without any text interpretation.

it's pretty easy to indent the first line in the Text Object as there's a dialogue for that, but that dialogue doesn't allow the formatting for what you're looking for.

jw

[JWiseman · Accepted solution]

hi Tom,

it looks like the rtf idea is a no go as the tag that does a hanging indent does not work in a text object in crystal...what happens is that that rtf tag just deletes the first part of the text instead of creating a hanging indent. this in turn means that html would not work for this either as the text object interpretation for rtf supports the same kinds of formatting for html as well.

the only thing i could recommend at this point is to use the paragaph formatting in the text object so that it indents the first line which will have the sBefore formula. right click on the text object and choose Format > Paragraph Tab > Indent First Line.

sorry,

jamie

[Former Member · Accepted solution]

Jamie..

OK...

I used your suggestion on the sBefore and sAfter on my test system and things worked well a day or so ago.  Now, I've spent the last hour trying to do the same thing on the production system and it doesn't work!  ;-((

Here's what's happening from an output standpoint.  If I have the text "Important: Please read" I get "Important: Important: Please read".  I've attempted multiple (!!) times to copy the sBefore and sAfter onto the Text Object.  What I see in the CR subreport is @sBefore {....} @sAfter or some combination of that.  I can bold/underline the sBefore individually but the output is being replicated for the bold/underline portion.  That is, apparently the output is bold/underline the text with colon and then the field is printed 'again'.

How can I do this without the endless drag/drop testing?

Again, the test system works fine and I did it on nearly the first attempt!

Arg!  ;-((

THANKS!

[abhilash_kumar · Accepted solution]

A good way to troubleshoot that is to place the sBefore and sAfter formulas side by side; how does the output from both fields look like?

Increase the size of the Text object to confirm if sBefore and sAfter are the only formulas/fields in there.

-Abhilash

[Former Member]

Abhilash!

When I've placed sBefore and then sAfter next to each other I am still formatting the original text box.  Same with a space between them; same with sBefore before the text box and sAfter the text box.  It seems I tried all the various combinations possible!  I solved the issue by simply deleting the production DB text object and then copying my test DB setup that worked onto the production DB. 

I guess some guidance would be important on how to actually set it up AND test/correct it!

See one of my previous posts just a couple hours ago.

THANKS!!