Solved

Crystal Reports

Posted on 2014-03-27
8
587 Views
Last Modified: 2014-03-27
Is it possible to join multiple SQL sub queries in the add command of Crystal Reporting. I would like to have Query B join Query A as a left outer join using the report_Warranty field. Trying to avoid multiple input boxes

A: SELECT Report_Warranty, SUM(AMOUNT) AS TotalSales, COUNT(CUST_ID) AS CountofSales
FROM  SALES_Table
WHERE DATE BETWEEN {?BeginDate} AND {?EndDate}
GROUP BY Report_Warranty


B: SELECT    SUM(CLAIM_AMOUNT) AS ClaimTotal, COUNT(CUST_ID) AS ClaimCount, Report_Warranty
FROM    CLAIMS
WHERE Date BETWEEN {?BeginDate} AND {?EndDate}
GROUP BY Report_Warranty
0
Comment
Question by:Dave_CARSIT
[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
  • 3
  • 3
  • 2
8 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 39959669
It is possible but the moment you place fields from each query on the report , crystal engine will apply cross join.

You can use the first query in the main report and the subquery in  a subreport
0
 

Author Comment

by:Dave_CARSIT
ID: 39959713
Can you help with the correct join wording?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39959815
At least in CR2008 you can have more than 1 command for the report.  They can be linked in the linking expert

Try creating the 2 commands then linking them on the Report Warranty field

mlmcc
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:vasto
ID: 39959823
If you want to join then do this inside one command. Having 2 separate commands joined inside Crystal will make the report very slow
0
 

Author Comment

by:Dave_CARSIT
ID: 39959953
If you want to join then do this inside one command

That is my preference, if possible.

But I`m not sure of correct way to state (first time using commands). Can someone help with left out join statement.

Thank you
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39959972
Try this

SELECT ST.Report_Warranty, SUM(ST.AMOUNT) AS TotalSales, COUNT(ST.CUST_ID) AS CountofSales, SUM(CL.CLAIM_AMOUNT) AS ClaimTotal, COUNT(CL.CUST_ID) AS ClaimCount
FROM  SALES_Table ST INNER JOIN CLAIMS CL  ON ST.Report_Warranty = CL.Report_Warranty
WHERE ST.DATE BETWEEN {?BeginDate} AND {?EndDate} AND CL.DATE BETWEEN  {?BeginDate} AND {?EndDate}
GROUP BY Report_Warranty

mlmcc
0
 

Author Closing Comment

by:Dave_CARSIT
ID: 39960014
Thank you
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39960261
You probably should have given vasto an assist for his last comment about doing it in a single query.

The question can be reopened if you want to allow you to change the point allocation

mlmcc
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

691 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