Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: Crystal XI R2 - how to resolve trap issue. Post Reply Post New Topic
Author Message
Ashish B
Newbie
Newbie
Avatar

Joined: 13 Apr 2009
Location: India
Online Status: Offline
Posts: 3
Quote Ashish B Replybullet Topic: Crystal XI R2 - how to resolve trap issue.
    Posted: 13 Apr 2009 at 7:14am
Reporting Tool - Crystal XI R2
Database MS SQL Server

Hi Friends,

My Crystal report is based on Two Fact tables and one Dimension table. Some complex calculation formulas involves both the fact tables and dimension table. But it creates traps and shows wrong result set.
 
I used Union All in the commend table, Where on database query produces right results but in Crystal it shows blank for some colmns from fact table. Confused

Can anyone please suggest solution how this can be resolved?

Any help is appreciated.

Thanks in advance.

Regards,
Ashish
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 14 Apr 2009 at 12:51pm
You could take your query and use it in the report as a Command instead of linking the tables.
 
If Commands are available for your database, "Add Command" will appear inside the Database Connection after you connect.  Click on it and either enter or paste in your SQL.  If your SQL has parameters, create the parameter inside the Edit Command box NOT the way you normally do parameters for the report (they won't work otherwise.)
 
You can then create your report based on the fields in the SQL.
 
-Dell
IP IP Logged
Ashish B
Newbie
Newbie
Avatar

Joined: 13 Apr 2009
Location: India
Online Status: Offline
Posts: 3
Quote Ashish B Replybullet Posted: 14 Apr 2009 at 9:41pm
 
Hi Dell,
 
 
Thanks for your useful suggestions. Can you please, explian is there any way to resolve said trap issue with query beside what I have treid (Union All) and which is producing wrong result set.
 
Any help is appreciated Smile
 
Thanks in advance.
 
 
Regards,
Ashish


Edited by Ashish B - 14 Apr 2009 at 9:42pm
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 15 Apr 2009 at 6:47am
This is more of a SQL question than a Crystal question, but I'll give it a shot.
 
Where on database query produces right results...
 
If there is a query that is giving the correct results when run in a query tool like Toad, use that query.
 
You could also use sub-queries in your From clause.  The format might look something like this:
Select
  <fields, calculations...>
from
  (Select <fields>
   from Fact1
      inner join Dimensions on ...
   where ...) as qry1
  full outer join
  (Select <fields>
   from Fact1
     inner join Dimensions on...
   where ...) as qry2 on ...
where ....
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.000 seconds.