• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1922
  • Last Modified:

Subquery in Crystal Reports

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
IT-CICA
Asked:
IT-CICA
1 Solution
 
jasonduanCommented:
"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
 
peter57rCommented:
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
 
Kurt ReinhardtCommented:
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
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
IT-CICAAuthor Commented:
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
 
James0628Commented:
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
 
IT-CICAAuthor Commented:
Thanks - I created a Stored procedure and sub report to get the desired functionality.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now