Crystal vs Crystal SQL Query Designer

[shelby55]

Hi

I am using Crystal 8.5 with a SQL Server 7 database. I have created a query in Crystal SQL Query Designer but when I base a report on it, a field I created is missing. Does this make sense? The field was using Convert with a date to show a different format. The Query itself worked fine but when utilizing the query as the report source, the field didn't show up in the field list.

Any and all assistance is greatly appreciated - thank you.

Shelby

 

[synapsevampire]

Crystal no longer suggested using their Query designer in CR 8.5, and it doesn't ship with CR 9 (you can download it though).

As for the converted field, use a SQL Expression to create it within Crystal.

I'd suggest creating a View on the SQL Server database, keep in mind that there's a freebie developers version of SQL Server that you can download for dev/testing, though it would be SQL Server 2000, it should still work OK for most functionality.

Another lesser known solution is to use an ADO connection and paste in the SQL.

-k

 

[shelby55]

Hi synapsevampire

Thanks for the note. Just out of curiosity, do you know why the Query was discontinued (or is the error I noted one of the reasons!)? I ask because I just interviewed for a job with a major car manufacturing plant and they informed me that they use DB2, create queries in Crystal SQL Query Designer (they mentioned version 8.5) and run reports from there.

 

[rhinok]

The SQL Query designer was discontinued because CR9 allows one to embed SQL Queries directly within the report itself as a 'SQL Command'. Multiple SQL Commands can be used within one report as if they were tables, whereas only one Crystal Query could be used by a report and no other tables or data sources could be joined to it.

SQL Query was very useful if you didn't have permissions to write objects in the DB itself (Views, Stored Procedures and Functions). It was also good for simple DB Querying, once again assuming that you didn't have access to a DB tool like ISQL, Query Analyzer, Toad, etc...

 

[shelby55]

Hi rhinok

Thanks for your reply but I was aware of the changes in Version 9 but was asking specifically about synapsevampire's post indicating that Crystal advised against using their Version 8.5 SQL Query. Why was the product shipped with 8.5 but Crystal advised against using it? Thanks.

 

[rhinok]

You missed the point. They recommend against using the SQL Query designer because it is obsolete and because there are better ways to retrieve data, including upgrading to newer software. CR10 will be out within a month, which places your potential client 2 base versions behind, not including Service Packs.

The SQL Query Designer works well, but it has severe limitations and 'features'. For example, one such feature is that some fields, depending on certain circumstances, are interpreted as memo fields (even when you've explicitly declared their length in the query) and aren't available for use in reports within formulas.

Here's a knowledgebase article explaining one of the circumstances. The underlying cause may be the source of your problem.

http://support.businessobjects.com/library/kbase/articles/c2006832.asp

 

[pgtek]

hi
they included the query designer to get you to call support for help lol lol

I used it once and never again

I used MS sql instead

cheers



pgtek

 

[bikejockey]

In your Query, did you give the field a name? In order for the field to have a name you would need to do one of the following in your SQL

NewField = CONVERT (.....)

or
CONVERT(....) AS NewField where

NewField is the name in you want it to have.

You may also need to do a verify datebase in the Crystal IDE of that report before it shows up.