Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-11-19
6
Medium Priority
?
889 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 12

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 1000 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 44

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 1000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

721 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