Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Can Crystal do this? Post Reply Post New Topic
Author Message
User
Newbie
Newbie


Joined: 08 Jul 2010
Location: United States
Online Status: Offline
Posts: 4
Quote User Replybullet Topic: Can Crystal do this?
    Posted: 08 Jul 2010 at 4:30am
< ="Content-" content="text/; charset=utf-8">< name="ProgId" content="Word.">< name="Generator" content="Microsoft Word 12">< name="Originator" content="Microsoft Word 12"><>

Hi there, I have been on this forum a number of times before and have always received satisfactory responses for my questions.  However, I have always struggled to use crystal for running subqueries i.e. having one main report running on the results of a sub query. Below is what, I am trying to achieve is:

SELECT DISTINCT A. physician_name, A.account_number, A.date_of_service, A.patient_type, A.code

FROM error_fact A

WHERE C.account_number IN

(SELECT account_number FROM error_fact C

WHERE C.date_of_service >= "01/01/2010" AND C.patient_type = “O” AND C.code IN ("93307","93325","93320")))

 

In the aforementioned, I can’t apply the filter in the main query because if I do I will see accounts and their related information with codes in the condition (this is what I want) and not see other codes (this is not what I want) i.e. if an account has 6 codes, out of which 3 are the ones in the filter condition, I want to see all 6 and not just 3 which match the condition. That is why, I filter out accounts satisfying the condition and then query out all the information (everything I need on an account) in the main query.

For clarity, please see the example below

Account      Codes

A                   93307

                      93325

                      98789

 

B                   10000

 

If I apply filter in the main query, my result looks like

A       93307

          93325

 

And it does not show ‘98789’ (this is not acceptable), however it only shows account A and not B (this is desired)    

I want to know if this is possible in Crystal by means of passing the accounts from a sub-query to main query. Also, please notice that it will be a list of accounts that will have to go from sub-query to main query.

P.S. Each account has multiple codes.



IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Jul 2010 at 5:03am
Using the basic select expert to do this is not really feasible.
A few work arounds:
1. Write a command as your source with the more complex SQL statement instead of using tables and the select expert
2. Write a stored proc or view (or equivalent) using your complex join and use this as the source
3. Use formulas to flag your rows, use an insert summary and then remove groups using the select expert group selection
 
To explain # 3
First you need a group so group on Account
write a formula to 'flag' rows that are 'good'...
{@flag} as
if C.date_of_service >= date(2010,1,1) AND C.patient_type = “O” AND C.code IN ("93307","93325","93320") then 1 else 0
Insert SUmmary as a SUM of this formula at the Account group level
Go into the Select Expert
Click on SHow Formula
Toggle to Group Selection
Click on Formula editor
Then insert your condition
SUM(@flag,table.Account)>0
 
This will remove any group that has NULL or zero rows that meet your condition
NOTE the group name will still appear in teh group tree but not in the report canvas.
HTH


Edited by DBlank - 08 Jul 2010 at 5:07am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.