Solved

Business Objects Query Panel in Crystal Reports XI

Posted on 2011-03-09
19
892 Views
Last Modified: 2012-08-13
Hi Experts,

I am using Crystal Reports XI with Clarity CA Universe and am having trouble with table(s) linking, particularly, unable to properly link two tables ( CA Clarity Universe) using the standard Links Option. I try and link [Project Current Facts] to [Project - Investment Info] by using Project Id --> Id but i am not getting the correct results. When I pass the Project Name parameter, I get multiple results as opposed to a single Project Name based on Id. If I add an additional Link by Project Code, then it works but another filter no longer works (OBS).

Not sure if anyone has experienced this problem before or knows of better ways to link tables in Link Options for CR XI. Please note that this is as CA Universe Specific enquiry, not connecting to the transactional database direct.

Any tips/hints would be much appreciated. Many thanks in anticipation.

Kind Regards
0
Comment
Question by:Ausway
[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
  • 5
  • 4
  • 4
  • +2
19 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 35089567
Are you doing this in the Crystal Database Expert?

DO you have smart linking turned off?

mlmcc
0
 

Author Comment

by:Ausway
ID: 35089765
Hi, yeah, using Crystal Database Expert. Smart linking could not do the job. I used manual linking by key, link from project id to id.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35090307
Is smart linking off?  If not turn it off it sometimes seems to activate at the worst times.

mlmcc
0
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 

Author Comment

by:Ausway
ID: 35090895
Thanks. Turned off the smart linking, Still the same thing. I am using an inner join project id > id. Enforce Join To? Is that correct? The parameter passes to the projectdetails (project id) query and I want to match that to the project in project investments (id)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35091053
I have never used the enforce so I don't know what it does.

I suggested the smart linking off because I remember a report I did several years ago that had a similar problem and the problem was in how the tables were joined but I don't recall what the fix was.

mlmcc
0
 

Author Comment

by:Ausway
ID: 35091254
Thanks. I will play around a little more. See if different link options will make a difference,
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 500 total points
ID: 35094286
For the sake of clarification, when you use the Query Panel you drag and drop various universe objects create some rudimentary selection criteria within a single query.  When you say "am having trouble with table(s) linking" does that mean you've created more than one Universe query in your report or that you're attempting to join a Universe query with some other actual data source objects like tables?

There are significant limitations to using Universes as data sources within Crystal Reports, some of which being that you can only refer to a single context within any Universe query and that query can only use one SQL Statement.  It is possible to create multiple Universe queries within a single Crystal report and link them together, but there can be a huge performance impact.  Essentially, each query is treated as a SQL Command and must be executed 100% in entirety before the join(s) can even occur.  As such, each query must be as finite as possible in order to mitigate performance risks.

At this point, it seems like you have two issues: 1)  Design issues and 2) Universe-specific issues.  I'm not familiar with that OEM Universe, so I can't really speak to the data being returned, but I am familiar with using Universes as data sources with Crystal Reports and it's possible you're running into limitations.

Keep in mind that Universe support is basically an after market "bolt-on" feature in Crystal Reports.  Crystal wasn't designed to use them and most Universes aren't designed to be used as data sources for Crystal Reports. They're designed to be used as data sources for WebI, which consumes them in an entirely different manner. Here's a whitepaper on the limitations of using a Universe as a data source with Crystal Reports:

http://rdsrc.us/LR9sEL

~Kurt
0
 

Author Comment

by:Ausway
ID: 35100686
Thanks Kurt, great feedback! Proves what was suspected, Crystal limitations. I can freely do this in WebI.
I guess it is up to management now, either remove the fields from the report or attempt a second Universe query.
0
 
LVL 35

Expert Comment

by:James0628
ID: 35107266
I don't think this is relevant to your problem, but FWIW, the enforce options in the links control whether or not the query that CR generates will include tables that (in theory) aren't really needed.  Normally, if you add some tables to a report and link them together, but don't actually use fields from some of the tables, CR won't bother to include those tables in the query, since you're not really using any data in them.  The "enforce" options tell CR to include one linked table if the other one is used, even if you're not actually using anything in the first table.  The directions (to, from and both) tell it which way to apply the "enforcing".  From the CR Help:

 > For example, if you create a link from TableA to TableB using Enforce To
 > and select only a field from TableA, the join to TableB will be enforced,
 > and the Select statement that is generated will include both tables.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35110351
Thanks James for that explanation.

Since I normally don't include extra table unless required to make the links ( then they are "used") I never looked into what the enforce options do.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 35115015
I don't normally use/need them either (especially since almost all of my reports use stored procedures).  Just one of those things that I got curious about, for whatever reason, and looked up somewhere along the line.  :-)

 James
0
 

Author Closing Comment

by:Ausway
ID: 35185474
Thank you!
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 35186739
Why were those responses accepted as the solutions?

~Kurt
0
 
LVL 35

Expert Comment

by:James0628
ID: 35187667
Those two posts were definitely not the solution.  Skimming back through the posts, Kurt's first post seems like the closest thing to an actual solution (basically that you were running into a design limitation), and my previous post is at least somewhat relevant, since I was describing the "enforce" option that you had mentioned.


 mlmcc, maybe you should re-open the question?

 James
0
 
LVL 35

Expert Comment

by:James0628
ID: 35196823
mlmcc,

 I appreciate the note about the Request Attention link.  I just think of that as something for the OP to use.

 As for how to close the question, hopefully Ausway will re-close the question, but if not, Kurt's post # 35094286 seems like the "solution".  My post about the enforce option, while hopefully informative, was basically just to explain that I didn't think it was related to the problem, so I don't know if I'd include it.

 James
0
 

Expert Comment

by:thermoduric
ID: 35292496
Starting the automated closure procedure to implement the recommendations from the participating Expert(s).

- thermoduric -
EE Community Support Moderator
http://www.experts-exchange.com/Q_26903418.html

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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