How to change or add SQL querys to the table in crystal reports?

I am new to Crystal reports and I have found that to get the desired data that I want to report on I have had to create a reasonability complex SQL selection script to the SQL Server database. The problem now is that I am not sure how to replace or change the original selection query script to my database from Crystal reports.  I have added the tables and linked the tables to the appropriate PK and FK. However I want to nest another sql select statement to get my desired results which I don’t seem to be able to do in the Database expert to link the tables?
I am using Crystal Reports 2008. I can see the SQL section code by choosing 'Database' > 'Show SQL query'.  But I cannot modify the selection code from here? Is it possible?
mumbles22Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Kurt ReinhardtConnect With a Mentor Sr. Business Intelligence Consultant/ArchitectCommented:
Crystal Reports generates the query in Database|Show SQL Query based on the tables, fields and selection criteria you use within the report.  It's created automatically and you can't manually modify it (in recent versions, anyway).  If you want to base a report on SQL, you need to use a view, stored proc or sql command as the data source for your report.

Since you already have the report written, it might be easiest to create a sql command that has everything you need inside it and then add it to the report.  Once it's it the report, don't join it to the other table - instead, start migrating the objects you use in the report to be based on the command, not the existing tables. Once you've fully migrated every field to use the command, you can safely delete the tables and your report will use the sql command solely.

~Kurt
0
 
Nathan RileyFounderCommented:
The show SQL query window just shows the underlying generated query, and does not allow you to edit it as it is auto generated based on the columns you use and groupings and conditions.

If you want to manipulate the underlying query, then it is best to use the data source as a view/stored procedure instead rather than selecting the tables.
0
 
mumbles22Author Commented:
Thank you for the quick response and I realise now that I could get around this by creating view and selecting that to get the data I want.
However based on your second para it sounds like I don't have to use the Database Expert to make the SQL selection?  Sorry I am very new to Crystal, so I don't know how to even connect to a database without selecting the tables in the Database Expert?  So if I was creating a report from scratch and I wanted to create the sql query first where would I do this? Or do I have to use the Database Expert to create the relationship first?  
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
zephyr_hex (Megan)Connect With a Mentor DeveloperCommented:
you can use "Add Command" and add a custom SQL query.

see here:
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_26616841.html

if you already have tables in your report, you can go to Database->Database Expert and instead of selecting a table, there is also an option there called Add Command.  use that instead.  if you use a Command, i'd recommend that you remove the tables you previously added via the Database Expert.

if this is a completely new report, with no tables added yet, then select Add Command in the Database Expert Wizard, instead of selecting tables.
0
 
mlmccCommented:
If all you need to do is add selection criteria, you can do that in the REPORT --. SELECT EXPERT

mlmcc
0
 
James0628Commented:
FWIW, Kurt and zephyr_hex were talking about using a CR Command, which is a query that you manually enter into the report.  You can also use a stored procedure or view as the datasource for a report.  When you open the database in the Database Expert, you should be able to see stored procedures and views along with the tables.

 If you're trying to replace the tables used in an existing report, then using a command, as Kurt suggested, may be the best solution.  I'm really not sure.  But, in general, you can also use a stored procedure or view.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.