Crystal Reports Help with ON LEFT query

Report Design
Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Help with ON LEFT query
	Page of 2 Next >>

Author	Message
abernut Newbie Joined: 25 Aug 2010 Location: United States Online Status: Offline Posts: 25	Topic: Help with ON LEFT query Posted: 26 Aug 2010 at 7:12am
	I use the following query in SQL but can't figure out where to use it in Crystal 10. I need it because sometimes the sales people use only the 5 digit zip and sometimes they use 12345-1234 ordersTable_sql AS ordersTable_sql INNER JOIN ZipCode_Table AS Zip ON LEFT(ordersTable_sql.ship_to_zip, 5) = Zip.ZipCode Thank you,
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 26 Aug 2010 at 8:45am
	if you do not want to use a SQL view or stored proc as your source then you wouldhave to do this in a crystal Command. I do not beleive you can set that kind of condition in the Database Expert Join section.
	IP Logged

abernut Newbie Joined: 25 Aug 2010 Location: United States Online Status: Offline Posts: 25	Posted: 26 Aug 2010 at 8:51am
	Thank you, I am BRAND SPANKING new to the world of Crystal. If you have the time can you elaborate on your response. Is this something I set up in the beginning when I am creating the report, ie.. Mike
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 26 Aug 2010 at 8:52am
	Which part, the COMMAND or how to use a view or stored proc?
	IP Logged

abernut Newbie Joined: 25 Aug 2010 Location: United States Online Status: Offline Posts: 25	Posted: 26 Aug 2010 at 9:01am
	C: All the above.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 26 Aug 2010 at 9:12am
	You have to choose a source for your data (Under Database > Database Expert). Your source can be a myriad of data types but I generally use SQL DBs at my shop. So you connect to the SQL and you can then expand the data source options to use the tables, views or stored procedures from that SQL DB. So if you have rights within your system you can create a SQL view (or sp)joining your tables via your WHERE clause. That view is now available as your source instead of the 2 tables which need to be joined via crystals less flexible means. This (or stored procs which also allow for parameters) is an EXTREMELY useful process as you can do all your data manipulation and joining on the server instead of crystal. Much faster and much more flexible in SQL. The COMMAND option is within crystal and lets you write SQL style select and joins. After you connect to the DB (same as above) there is an 'Add Command' option which opens a window where you can write your SQL command to grab your data. I have found COmmands to be very slow although others have not, so it may have to do with my environment. Does that help or do you need more? Edited by DBlank - 26 Aug 2010 at 9:16am
	IP Logged

abernut Newbie Joined: 25 Aug 2010 Location: United States Online Status: Offline Posts: 25	Posted: 26 Aug 2010 at 9:28am
	THANK YOU. The Add Command was EXACTLY what I was looking for. It allowed me to just paste the SQL query I was using on my SQL Server. Now I just have to make everything look nice and pretty for the big wigs. Mike
	IP Logged

abernut Newbie Joined: 25 Aug 2010 Location: United States Online Status: Offline Posts: 25	Posted: 26 Aug 2010 at 9:31am
	Sorry, but for got to ask... I can do this on Crystal Reports 10 but the rest of the company uses something called Seagate to do their Crystal reporting. I do not see the Add Command option there. And I have been having issues with creating a report in Crystal V10 and them not being able to open it with their Seagate Crystal viewer.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 26 Aug 2010 at 9:52am
	never used the version that ships with seagate. You may want to try a new post re: that issue as others may have used it.
	IP Logged

Jim257 Newbie Joined: 17 Aug 2010 Online Status: Offline Posts: 6	Posted: 27 Aug 2010 at 1:00am
	Seagate Crystal reports is a much older version. I know that V8.5 was still owned by Seagate. Here's some history if you're interested: http://en.wikipedia.org/wiki/Crystal_Reports So the problem you are having is a versioning one. You can't open a V10 report in V8.5. Is there are possibility of upgrading the older versions? V8.5 doesn't have the Add Command ability, you'd have to use a stored procedure stored on the database. Are stored procedures a possibility?
	IP Logged

Page of 2 Next >>

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.051 seconds.