Print Page | Close Window

Help with ON LEFT query

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10970
Printed Date: 04 May 2024 at 10:27am


Topic: Help with ON LEFT query
Posted By: abernut
Subject: Help with ON LEFT query
Date 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,


Replies:
Posted By: DBlank
Date 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.

Posted By: abernut
Date 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

Posted By: DBlank
Date Posted: 26 Aug 2010 at 8:52am
Which part, the COMMAND or how to use a view or stored proc?

Posted By: abernut
Date Posted: 26 Aug 2010 at 9:01am
C:
All the above.

Posted By: DBlank
Date 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?

Posted By: abernut
Date 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

Posted By: abernut
Date 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.

Posted By: DBlank
Date 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.

Posted By: Jim257
Date 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?

Posted By: abernut
Date Posted: 27 Aug 2010 at 1:03am
I have full access to the DB but just not sure what needs to be done.


Print Page | Close Window