String editing 1

[CrystalBox]

Hello, I use Crystal 11 with MS ACCESS as my database. I ran into a problem with my pie chart. I'm using two related tables, [SECTION_CODE] & [SECTION_CODE_DESC]. The first contains the numerical codes and the second, the description of the code.

Example: code: 100/200.10 description: "Policy Violation - Discriminatory harassment". I use about ten different violation codes. The problem is that the code description are too long to place on the pie chart. I would like to shorten the code descriptions, if possible, so that I could place them in the chart. Thank you.

 

[MCuthill]

CrystalBox,

Off the top of my head, I think the simplest solution (especially for 10 codes) is an if statement to simply define your desired descriptions.

You can base it off either the current description or the code (I'd personally use the code).

{@YourNewFormula}

IF {Table.SECTION_CODE} = "100/200.10" THEN "Descrim Hrsmt" ELSE
IF {Table.SECTION_CODE} = ~next code~ THEN ~shortened description~

Hope this helps. Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

CrystalBox,

If there is a "standardized" way to shorten them (for example, do all start with "Policy Violation", and you wish that removed or changed)? If so, there are certainly options to do so - let us know.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[CrystalBox]

Thanks Mike, worked like a charm. I forgot about the "Else" function. I am curious about the "standardized" way to shorten labels, if you have time to show me. Thanks again.

 

[MCuthill]

CrystalBox,

You could leverage any of the STRING functions in Crystal if there are standardized structures or groups of structures.

The one that comes first to mind is REPLACE(), and could be used something like follows (assuming all have "Policy Violation - "):

{@YourNewDescription}

Replace({SECTION_CODE_DESC},"Policy Violation - ","")

The above would take all instances of [blue]Policy Violation -[/blue] and replace it with nothing (double quotes).

Left(), Right() & Mid() could also maybe be used, as well as InStr(). Of course, any of these can be combined with IF, CASE or other logical operators to group your modification if/as needed.

String Functions can also be nested, and work from the innermost function out to the outermost.

Assuming your same example above, lets say we want to remove all instances of "Policy Violation -" and also shorten all instances of "Harrassment" with "Hrsmnt". In this case, the order doesn't matter, as the second change is not conditional on the first. The formula for this would look like:
{@YourNewDescription}

Replace(Replace({SECTION_CODE_DESC},"Policy Violation - ",""),"Harrassment","Hrsmnt")

The combinations are near endless, you can also set up a test field to experiment with various combinations (in an new/empty report) by simply creating a formula field and typing in some random text (I also put quotes around it to ensure "text" formatting).

Hope this helps! Don't hesitate to ask if you see a trend and would like help with a formula. Crystal Reports Help is also great for the syntax of these functions.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[CrystalBox]

Thank you so much Mike. You've given me more than I expected. Wow, this is very useful information. I'm going to experiment with all your suggestions. Thanks again!