Solved

Subquery in Crystal Reports

Posted on 2010-11-22
6
1,751 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
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now