Solved

Subquery in Crystal Reports

Posted on 2010-11-22
6
1,744 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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