Memo Field to Upper Case in 8.5 2

[risman]

I'm using CR 8.5 - SQL - OLEDB - I'm trying to force a memo field,({vusrDiagnosticReportText.ReportText}), that is within a provided view, to UPPER CASE. I've tried using the @Null formula as described in

http://support.crystaldecisions.com/kbase/c2007285.asp,

but to no avail. Our vendor does not seem to want to set up breaking the memo field down to separate fields of less than 256 characters, as in (

http://support.crystaldecisions.com/kbase/c2011669.asp.)

Any other suggestions? Does Crystal 9 allow formatting of memo fields?

 

[synapsevampire]

Create a SQL Expression with the following:

CAST(UPPER(CAST(somememo AS VARCHAR(4000))) AS NTEXT)

Replacing somememo with your field name.

The outer cast may not be required, and note that I limited it's size to 4000 chars, adjust as required up to 8000 characters.

-k

 

[risman]

I was uncomfortable with trying something new, (SQL Expression), so I passed it along to the Vendor. The said that I cannot edit/format memo fields in 8.5. So I guess I am stuck. Thank you anyway!

 

[synapsevampire]

They lied.

Create a SQL Expression with:

upper(yourfieldname)

This assumes that by SQL you meant to say SQL Server.

Now display that SQL Expression field, it will be in uppercase.

Now send the formula along to your vendor with a bill, 10% of which is payable to me in 80 year old cognac, fine wine, good bubbly or microbrew beer

OK, thanks will suffice.

-k

 

[pelajhia]

synapsevampire - thanks for this and many other answers!

 

[pelajhia]

Oh, rats, I think I can't use this in my particular case because I, like risman, am using a view. I just checked and I don't get the 'sql expression' option in the field explorer. I am glad to have the tip, though, for other reports. Any suggestions for those stuck using views?

 

[risman]

Yeah, our vendor has supplied us with the views THEY think WE need. Thank you for your help though!

 

[lyanch]

You could format it with an "all caps" font. I currently don't have one so I can't give a reference, but I know that there are some available.

Lisa

 

[synapsevampire]

I don't understand why a View prevents you from using a SQL Expression...I had tested against a View.

Perhaps it's your means of connectivity, try changing (from direct to ODBC, or whatever) and see if it works.

-k

 

[pelajhia]

I have read that you don't get the sql expression option w/stored procedures, and when I didn't see it in the explorer in this report, I assumed the same condition applies. I am using 8.5 standard .
I can't even select 'view sql query' from the 'database' menu.
I am about to upgrade to 9 advanced; is that the version you are using, sv? Perhaps that would allow me to create a sql expression despite using a view.
(i really apologize for horning-in on your question, risman!)

 

[risman]

That's quite all right Pelajhia. The more info, the better. When I look at the view in the Field Explorer, it will not show this text field. But I am able to see, and enter a new SQL Expression and just type in the field name. One thing, I have this field only in a subreport within the main report. Should the SQL Express go in the subreport or the main report. Thanks Ris-

 

[lyanch]

If the field is only available in the subreport, then you should add the SQL Expression there.

Lisa

 

[risman]

I'm sorry, it's not available (or able to be seen), in either Field Explorer or in either report. But it does not error out if I just type it into the expression.

 

[synapsevampire]

Right, it won't show in the field explorer, you just need to have a bit of faith

pelajhia: I'm using version 8.5

What sort of database are you using?

If you're using a direct connection, you may not have SQL as an option, again, try changing your connectivity type to ODBC, OLE DB, etc.

-k

 

[pelajhia]

odbc, to oracle 8.i. I have asked my dba if he can slice up the memo field for me in the view; I guess risman's folks won't do that for him...but he can see the sql, so I guess that's fair.
After that I can use your solution, I think,sv. (Haven't heard back from the dba yet, but I remain hopeful.)

Thanks for all the help.

 

[pelajhia]

Correction: after that I can use part of the solution, assigning the field to a variable (and concatenating, of course).

 

[synapsevampire]

You don't need it sliced up, a SQL Expression will work anyway.

-k

 

[risman]

So I create an SQL Expression:
CAST(UPPER(CAST(vusrExamDiagnosticReport.ReportText AS VARCHAR))) AS NTEXT)

Then upon further investigation, do I understand correctly to put the Expression in a formula? Well, I tried it using:
UpperCase ({%ReportText})

I then got a "Parse Tree Error, Contact Seagate Software"