[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Subquery in Crystal Reports

Posted on 2010-11-22
6
Medium Priority
?
1,868 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 2000 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
Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

 

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

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

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 …
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

656 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