Solved

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

Posted on 2010-11-19
6
882 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 42

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 33
Series Riser Colour Not Saving, Legend Edited Names Not Saving 4 13
string fuctions 4 25
Query Help - MSSQL - Averages 5 26
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 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