Solved

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

Posted on 2010-11-19
6
884 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:mumbles22
6 Comments
 
LVL 11

Expert Comment

by:N R
ID: 34173598
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
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 250 total points
ID: 34173600
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
 

Author Comment

by:mumbles22
ID: 34173828
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 43

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 250 total points
ID: 34173931
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34176372
If all you need to do is add selection criteria, you can do that in the REPORT --. SELECT EXPERT

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 34179033
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question