Solved

Postgre DB to Crystal Reports Memo - Number or Text Field

Posted on 2010-08-26
14
608 Views
Last Modified: 2012-06-21
I'm connecting to a Postgre SQL DB (in linux) to create a custom report. Most if not all fields in the DB are in Memo type. Crystal Reports won't allow parameters to be created on memo type fields.

Is there any easy way around this. I'm trying to create parameters for two of the memo type fields?

0
Comment
Question by:RandyMFire
14 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 33535977
What version of Crystal?

Are the memo fields actually limited in length?

Are you trying to use memo field values as parameter values or simply for record selection?

You might be able to use a formula like
Left({AMemoField},10)

mlmcc
0
 

Author Comment

by:RandyMFire
ID: 33538296
I'm not at work but I have CR 2008

I'm not sure what the length of the memo field is. Like I said it is in a Postrge DB, which I'm not familiar with at all.

I'm trying to use the memo field for record selction only.

Unless I can use the formula  as a record selection parameter, I don't think that will work, although I will try it in the morning.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 33540091
Please explain in more detail your environment including version iof postgres and calrify what you are trying to do as your posts are unclear in the first post yopu mention parameters and in your last post you talk about formuals and record selection

I use Postgres 8.03 and run reports using CRXI R2 and have no issues using memo fields in the record selection formula i.e. {Table.memoField} = {?MyParam}

0
 

Author Comment

by:RandyMFire
ID: 33641013
Not sure asking developer on what version of Postgre
I have a Linux Server where there is a Medical Program using Postgre DB
I am using Crystal Reports 2008
I am trying 2 things now.
Accessing from a XP SP3 laptop using ODBC (RDO)

One trying to load the Stored Procedure table where the developer created a stored procedure for me to convert the 2 fields (in question) . I've tried options and checked the stored procedurs box but still not bringing up the table to move the stored procedure over form available data sources to selected tables. Ths is my first issue.

The second and original problem
 CR will not allow me to pick from the fields view to create a parameter. They show up in the Field Explorer as field type Memo. The developer stated he has made them varchar(255) type.
I need to create a parameter so the users can pick a specific record to show all of the fields I have placed in the report.
The only records I can select for a parameter right now is on the data_created field, which will bring up to many reports for any given day.

Any help would be appreciated. Sorry for the long delay. Was off for a while.
0
 
LVL 34

Expert Comment

by:James0628
ID: 33643971
I haven't used Postgres, but FWIW ...

 I use MS SQL and have had some problems using MS SQL Memo fields in CR.  I actually don't have any problem using one in a record selection formula or comparing it to a string parameter.

 Hmm.  Re-reading your original post, you weren't trying to create Memo type parameters, were you?  That's not an option.  Just use String parameters.

 Assuming that that wasn't the problem ...

 While I don't have any problem using Memo fields in a record selection formula, there is at least one thing that CR will not let me do with them.  CR will not let me create a group on one.  A simple workaround for me is to put the field in a formula.  I don't have to use Left () as mlmcc suggested, or anything like that.  If I just create a formula and put that field in it, with nothing else, I can then create a group on that formula.  Weird, but there you go.  :-)

 I have no idea if it will help with your problem, but it's simple enough to try.

 James
0
 

Author Comment

by:RandyMFire
ID: 33645782
No I wasn't trying to create a memo type parameter.
I was trying to create a parameter from a field showing memo in the field explorer.
The original field type in the Postrgre DB was coded as varchar (255), but when I bring in the table into CR it shows it as memo??? Which means I can't create a parameter for that field.
Is this soemthing that CR does or a bug or is my ODBC not correct??? I don't know. This is why I'm asking if there is a way around this? I thought creating a stored procedure was a way but then read on....

The other question was I had the developer of the program create a stored procedure in the DB. When I go to DB explorer the stored procedures table doesn't even show up to try and move it over with the rest of the tables I want to work with. I've already checked the check box under properties for stored procedures and that didn't make the stored procedures table visable...So I'm stuck..
0
 
LVL 19

Expert Comment

by:GJParker
ID: 33645832
As I said in my earlier post this is not a CR issue as I do this kind of thing all the time with postgres. Did you find out what version youa re reporting against ? and what ODBC client are you using you can find out teh name and vesrion nunber from teh drivers tab in ODBC data source administrator
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:RandyMFire
ID: 33645967
Not sure but I think this is what you are asking see attched screenshot.
postgrever.jpg
0
 

Author Comment

by:RandyMFire
ID: 33646040
Here are screenshots of the Data Source.
odbc1.jpg
odbc2.jpg
odbc3.jpg
odbc4.jpg
0
 
LVL 19

Accepted Solution

by:
GJParker earned 500 total points
ID: 33646107
Which of these drivers are you using for your connection ?

I use the ANSI version and {Table.memoField} = {?MyParam} works fine for me, when you connect to the DB can you see the memo fields ? are they in the list of available tables and fields in the record selection formula editor ?
0
 

Author Comment

by:RandyMFire
ID: 33646244
Using the highlighted one Unicode. Will try using ANSI..Yes they are in the list of available fields. I can even drag and drop into the report, just won't let me use as parameter. Give me a minute to see what happens when I switch to ANSI....
0
 
LVL 19

Expert Comment

by:GJParker
ID: 33646288
"just won't let me use as parameter"

what does this mean ? can you list the exact steps you are doing please
0
 

Author Comment

by:RandyMFire
ID: 33646425
GJParker
You are a frickin genius. That was exactly it. I was originally advised to set the ODBC up with the UNICODE driver. When you said yours was in ANSI, I changed mine to check, and sure enough, the fields were able to be selected from the parameter pull down. Thanks. Really do appreciate you working this out with me.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 33646454
Glad to help
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now