I am newbie with Crystal Report and 1

[TondoBoy]

I am newbie with Crystal Report and I would like to ask the experts a basic question. I am using Crystal Report 8.5 with VB and Access db.
1. Is there a direct way to use a SQL statement in Crystal Report? It is a complex SQL statement that retrieves data(SELECT), Total by group(SELECT SUM..),and sorts data within groups(ORDER BY) from three tables. It is in the same line as calling an SQL and executing a print command in VB.

 

[CrystalVisualBOracle]

If you're looking to create a report in CR which requires one generally simple query, then it is very feasible to create the query from within CR itself and simply modify and format the data there on the report. If you need complex query of any kind, I suggest creating the query in VB and passing it to the report using the RDC component of Crystal in VB. (I've used the "SQLQueryString=" command.) If by some chance you need multiple queries to create all of the data on the report, then you need to create a subreport.

In short, Crystal is not very friendly for those of us who like to write straight SQL to get their answers if that's what you were hoping. Crystal is picky as to which SQL functions it will recognize within the SQL query script itself. For example, if you want to sum a field you can't do it in the "select" statement. Crystal forces you to select all the rows and then create a formula field to sum the records returned. It's makes for an interesting experience at times.

Good Luck.

CrystalVisualBOracle

 

[TondoBoy]

Thanks, it sounds like coming from a voice of experience. I will try your advice and keep playing with it until I come into an acceptable solution.

 

[balves]

You can do the following:

1. Create a complex SQL command and test it outside of Crystal (such as using the SQL Server Enterprise) to make sure you are getting what you want for results.
2. Create a new Crystal Report.
3. For your data source, choose "Other Data Sources", then choose ADO.
4. Crystal will ask you to connect to the database.
5. Paste your SQL into the window provided.
Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer
(Currently available for consulting in Chicago)

 

[TondoBoy]

Thanks a million balves! It is the answer I was looking for.

 

[TondoBoy]

Brian,
It worked with SQL statement. One more thing, how do I go about creating similar reports, but passing parameters to the SQL from VB. Is it possible? I tried but Crystal Report would not let me do it.

Thanks again....

 

[balves]

You can use ADO in VB and replace the WHERE clause of your SQL or replace the entire SQL.

If you replace the entire SQL statement in VB, it MUST LINE UP field for field. In other words, if you created your report with a SQL statement and it has X fields in the SELECT, you better send X fields (of the same type) in the same order from VB.

Assuming you're using VB/ADO/Crystal 8/RDC, you can replace the entire SQL this way:

1. Create an ADO recordset in VB and open it
2. Use the following command to use the recordset created in #1 for the report (assuming your report is Report1, and your ADO recordset is adoRS):

Report1.Database.SetDataSource adoRS

Or, you can use the SQL in the report and just replace the WHERE clause using an attribute of the Database object.
Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer
(Currently available for consulting in Chicago)

 

[robertrobert]

Great tip on using ADO. What an incredibly powerful, yet poorly publicized, way to get Crystal Reports to use complex SQLs. This seems like a comparable alternative to basing a report on a SQL created in Crystal SQL Designer.

What are the advantages and disadvantages of these two methods?

Thanks,
Robert

 

[cvc]

hi
m using crystal reports 8 for the first time i dont know where to begin
all i want is, i should be able to pass query with dynamic parameters and get result in crystal reports?/
what is in requirements for it like
components/references to inlc
step by step code will b much appreciated

thanks in adv

shume

 

[dgillz]

Cvc-

Check out ken hamady's quick reference guide to using crystal with VB. It will get you jump started very easily.

Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office