Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 912
  • Last Modified:

Business Objects Query Panel in Crystal Reports XI

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
Ausway
Asked:
Ausway
  • 5
  • 4
  • 4
  • +2
1 Solution
 
mlmccCommented:
Are you doing this in the Crystal Database Expert?

DO you have smart linking turned off?

mlmcc
0
 
AuswayAuthor Commented:
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
 
mlmccCommented:
Is smart linking off?  If not turn it off it sometimes seems to activate at the worst times.

mlmcc
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
AuswayAuthor Commented:
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
 
mlmccCommented:
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
 
AuswayAuthor Commented:
Thanks. I will play around a little more. See if different link options will make a difference,
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
 
AuswayAuthor Commented:
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
 
James0628Commented:
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
 
mlmccCommented:
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
 
James0628Commented:
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
 
AuswayAuthor Commented:
Thank you!
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Why were those responses accepted as the solutions?

~Kurt
0
 
James0628Commented:
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
 
James0628Commented:
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
 
thermoduricModeratorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now