Solved

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

Posted on 2010-11-19
6
887 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 11

Expert Comment

by:Nathan Riley
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 101

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 35

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

631 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