Solved

Business Objects Query Panel in Crystal Reports XI

Posted on 2011-03-09
19
863 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

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 34

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 34

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 34

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now