Using Memo Field as Dynamic Parameter 2

[ladporter]

Hello

I am wanting to use a field (formation.tformation) as a dynamic parameter in my report. However as this field is "memo" type it does not appear in the drop down list in parameter dialog. I have searched the net and have read about using SQL. But I can't seem to see how to do this as in the Formula Workshop under SQL the memo field is not listed there either.

If you could point me in the right direction would be greatly appreciated.

PS Thanks to all who have helped me in the past on this forum.

Rgds
ladporter

 

[Turkbear]

Hi,
Which version of CR?

Try a substring of the memo field like
Left({formation.tformation},128)






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ladporter]

Hi Turkbear

Thanks for your prompt reply. I am using Crystal Reports XI. the field tformation is the name of a branch of our Scouting Association ie "Western Rivers Scout Group" and is memo type. I am trying to create a parameter in my report that will allow user to use drop down box to select scout group by name rather than a list of 5 didgit Group ID numhbers. As it is a memo field when I try to set up a dynamic parameter it does not list any memo fields like tformation. I can manipulate the string for group and sort purposes in the body of the report it's just finding some way to use this memo field as a dynamic parameter. If you can advise what I need to do and where I need to place formulae if any.

Hope that makes a bit more sense.

Cheers

 

[Turkbear]

Hi,
Perhaps you could create a view in the database using the GroupID and the name field that can serve both as a lookup table for the group ( show the name, use the GroupID in the selection criteria )and, by linking it to the table by the selected ID, you can restrict the data once the desired group is selected.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[randaklin]

Have a database running on Linux box w/ Postgre (for a Medical Program)
Trying to create a custom report
Can't get a parameter built on a field which is of type varchar(255)as stated by the developer.
The developer created a stored procedure but they are not visable under the DB expert to bring in. ???
Crystal won't show me any of the fields which are listed as this field for parameters purpose.
Already tried these things

http://www.evernote.com/pub/randaklin/crystalreports

Any help would be appreciated.
Crystal Reports 2008 Developer
Connecting via ODBC to Postgre DB
System DSN

 

[ladporter]

Turkbear

Sorry for delay in reply as I have been off work with ongoing health issues.

I don't have direct access to the database to create a view and am meeting resistance to change from the powers that be. However I think I found a way around it. I have created an SQL (PostgreSQL 8.4) command:

select cast(formation.tformation as char(128)), formation.formationid from formation;

This allows me to convert the memo type so it is available in the Dynamic Parameters drop-down. It appears to return the correct data. From your experience is this a smart way to go or would you suggest a better way.

Thanks

ladporter

 

[Turkbear]

Hi,
Since that puts the power of the database engine to use it is probably the most efficient way to do that - be sure to use a Command for all the data you need for the report since linking a command object to a table usually results in poor performance.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Just a comment. The command can be used to populate the picklist for the dynamic parameter without requiring the rest of the report to be based on a same or different command. As long as you don't link the command to a table or another command or reference the command in the main report, it will not affect performance (once the picklist is populated). Just ignore the warning that two different datasources are being referenced in the report. In the selection formula, you would reference the main report field, not the command field. The command should ONLY be used to populate the picklist.

-LB

 

[randaklin]

It turned out to me the ODB connection. If I changed the Manage DSN variable from PostgreUnicode to PostgreAnsi. The field was able to be displayed. Thanks for the help.

 

[ladporter]

My thanks to both Turkbear and lbass. I will try it both way and see how it goes. I am brushing up on my SQL skills on my linux box at home using psql.

I must say that this is the most helpful and informative forum that I have found on the net when in comes to Crystal Reports.

Thanks to one and all!

ladporter