Solved

Subquery in Crystal Reports

Posted on 2010-11-22
6
1,777 Views
Last Modified: 2012-06-21
Hi
I am developing a report which will have registrants statistics of a conference selected by user.

The parent Conferences have ParentID  = NULL and  should be displayed as paramenter to be selected by user.

Once the user selects a parent conference, I need to display registrants from ALL child conferences and the parent conference itself grouped by the 'designations' of the registrants.

EG.

Designation                  Registrants
BA                                       143
CMA                                      33
Unknown                              90

I have come up with the following SQL:

Select
CASE Designation
when  '' then 'Unknown'
when NULL then 'Unknown'
else Designation
END as Designation
,Count(*) as Registrants
from vwMeetingReg
Inner JOIN vwPerson on vwMeetingReg.ID = vwPerson.ID
INNER JOIN vwProducts on vwMeetingReg.MeetingID = vwProducts.ID
WHERE vwProducts.Id in (select  ID from vwproducts where Id = 8543 Or ParentId = 8543)
Group by Designation

Here, '8543' is ID of the parent conference selected by the user
I am trying to figure out how to fit the WHERE clause in Crystal Report.

Any ideas?

Thanks in advance.
0
Comment
Question by:IT-CICA
[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:jasonduan
ID: 34189272
"CASE ... when NULL" does not work in Sql server.

You should use:

CASE
      when Designation = '' then 'Unknown'
      when Designation IS NULL then 'Unknown'
      else Designation
END as Designation
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34189354
If you are using the Add Command feature to create this data source then you can create a parameter in the same dialog box.  (You MUST create it there and not in the Parameter fields dialog in the main CR UI)
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 500 total points
ID: 34190128
What are you using as a data source within Crystal Reports?  Tables/Views, Stored Proc or SQL Command?  I see that you reference a View, but is it the data source or just used within a Command/Proc?

If you're using Tables/Views, then your only options for creating a correlated subquery are to create either a subreport or a SQL Expression field (and if you're using Oracle, you have limited options on creating a correlated SQL Expression).  Personally, I prefer SQL Expressions over subeports for performance and design considerations.

If you're using a Stored Proc or Command, then you can simply modify the existing query to include the subquery, as indicated above by peter.

Here are some links to SQL Expression documents I've created:

The Power and Possibilities of SQL Expressions
Complex SQL Expressions

FYI, you won't be able to pass a parameter into a SQL Expression field, but you can use the parameter against the primary recordset and then pass the actual database field into the SQL Expression.

~Kurt
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:IT-CICA
ID: 34190809
I can't download from the link you provided and I am new to SQL expressions - Could you please elaborate? Yes I am using views as datasource for the report (the db is MS SQL server)
0
 
LVL 35

Expert Comment

by:James0628
ID: 34203314
I could definitely be missing something, but couldn't the WHERE be written without the subquery as:

WHERE vwProducts.Id = 8543 Or vwProducts.ParentId = 8543


 James
0
 

Author Closing Comment

by:IT-CICA
ID: 34238692
Thanks - I created a Stored procedure and sub report to get the desired functionality.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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