Technical Questions: Link loop

Print Page | Close Window

Link loop

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7664
Printed Date: 05 May 2024 at 7:50am

Topic: Link loop

Posted By: elie234
Subject: Link loop
Date Posted: 11 Sep 2009 at 9:30am

I am doing a report where I have had to link to a table twice (x->y->z and x->z). While CR lets me do this I am getting back too few records than I should be. Any ideas on why that might be so? My hunch was that it had something to do with the link order, but I have experimented with that to no avail.

Replies:

Posted By: DBlank
Date Posted: 11 Sep 2009 at 9:38am

are these all inner joins?

Posted By: elie234
Date Posted: 11 Sep 2009 at 9:41am

Yes.

Posted By: DBlank
Date Posted: 11 Sep 2009 at 9:45am

You may want to try some outer joins to expand you data set.

Maybe x to y and z to y?

You may also end up needing to create a view , stored proc or use a command to get the data set you need.

I recall seeing your post on this a while ago but can't recall the specifics.

Do you have some sample data that from the tables including rows that are not appearing that you want to appear?

Posted By: DBlank
Date Posted: 11 Sep 2009 at 9:49am

Couple of gotcha's on joins...

1. if you do not enforce the joins in the set up you must use at least 1 field from each table in the report (anywhere even if not displayed) for the join to "activate".

2. Select statements can also impact joins and may change an outer join to an inner join.

Posted By: elie234
Date Posted: 11 Sep 2009 at 9:50am

Thanks for the advice!
When I try the outer joins I get an error that if the tables are already linked then the join type can't change.

Posted By: elie234
Date Posted: 11 Sep 2009 at 9:59am

If I were to use a command what should I put in the command? The fields from y and z?

Posted By: DBlank
Date Posted: 11 Sep 2009 at 10:11am

Maybe.. can't really say unless I know the tables teh contnet then linksand what you want to get out of them. The theory is that using a command you can write a SQL statement that has a bit more control on the join and selections.

Which table has the "missing records" in it? If you can track where the exclusion is occuring you can focus altering your process to account for that.

To change these to outer joins you may need to remove all of the links

and start your join process from scratch. (at the vary least romve your join from z to X)

Please keep in mind that I am guessing at this. Without the tables and you desired data output that is the best I can do...Maybe this....?????

x outer left join to y

y outer right join to z

z inner join to x

Posted By: elie234
Date Posted: 11 Sep 2009 at 11:46am

Thanks DBlank! I went with a command for the y and z tables and was then able to left join from x and I got the data I needed. I now see more clearly what the issue was which leads me to another question.

I am reporting on manufacturing production of different machines. Table Z deals with estimated production and Table X and Y deal with actual production. Two of the machines (call them A,B) are identical. The issue was that in Table Z there is only estimated production for machine A even though in actual production some of that production will be routed to machine B. I would like to be able to compare actual to estimated production for Machine B, which would mean somehow considering Machine B to be Machine A. Any ideas on how to do that? Would a formula work for that?

Posted By: DBlank
Date Posted: 11 Sep 2009 at 12:00pm

Not sure I exactly undertand the set up but one idea is to do a case statement in your command to convert "B" int "A" and then use that converted field for the join?

Will that work or give you an idea?

Posted By: elie234
Date Posted: 11 Sep 2009 at 12:23pm

Your idea sounds good but 2 q's (Please forgive my ignorance I am new to SQL commands):
1. Right now I don't have the X table in the command and am still linking it to Y and Z tables in db expert. I also have other tables linking from X (X is the main table in this report). So if I link from X to Y and Z in the command would it still be ok to link from X to other tables in db expert?
2. What would the SQL expression be to convert B into A?

Posted By: DBlank
Date Posted: 11 Sep 2009 at 2:03pm

No worries. I muddle through this myself. There are others on this site that can be much more help with SQL statements...

1. You can include tableX straight into the command but I am not sure it will give you the fields set that you want it to. It is really hard to say without the data and DB architecture. It might alter your results if you do this. You can get fancy with SQL statements where you have basically a query inside a query. If you are just learning SQL this might be too much to try but look for examples. You will proabably need it in the future or you will learn a great process to manipulte data before you get it ito crystal which can make the crystal design much more relaxing and straight forward.

If you need to add the conversion to the "X" table you can just replace the whole table with a second command and use that exactly the same as you are uing your X table.

Select *, (CASE when tablex.machinefield='B' then 'A' ELSE tablex.machinefield END) as your_desired_output_name_here

From tablex

2. The blue section in the statement above should convert the B to A. It is really only needed if there are more values then A or B. If it can only be A or B (and no NULLS or blanks) then you can just add a field instead of the case statement: 'A' as your desired ouput name

Just some notes. I have found for my set up that commands really bog down performance. I use SQL as my source and I create views or stored procedures and use them as the source if the tables need to be manipulated. This is basically the same thing as a Command but I have more control, performance is not impacted and I can reuse these views for mutliple reports. The GUI is nice for building them as well (views not SPs).

Posted By: elie234
Date Posted: 14 Sep 2009 at 10:45am

Thanks so much for all your help. I am giving the SQL command a shot. I am getting a syntax error though. If I just do Select (CASE.....) as A from table X then it works. But if I do Select *, and then the CASE statement I get a syntax error.

Posted By: DBlank
Date Posted: 14 Sep 2009 at 10:52am

try and use each field you want to use from the table instead of the * (all) ....

select fieldname1, fieldname2, fieldname3,...,(Case ...)

Posted By: elie234
Date Posted: 14 Sep 2009 at 10:56am

Thanks the individual fields work.

Posted By: elie234
Date Posted: 14 Sep 2009 at 11:21am

How do I create a command that can reference two different databases? When I create the command I have to start it from one db and it is not recognizing the other.

Posted By: DBlank
Date Posted: 14 Sep 2009 at 11:36am

The same way you would a view or stored proc in SQL using a join type. Also if you are selecting fields from more than one table/source then you have to start each selected field with that source e.g. instead of fieldname1 use tableA.fieldname1 ).

an example:

SELECT tableA.fieldname1, tableA.fieldname2, tableA.fieldname3, tableB.fieldname1, ....

FROM

table A INNER JOIN table B ON tableA.field1=tableB.field1

Posted By: elie234
Date Posted: 14 Sep 2009 at 11:57am

When I try to do the join I am getting a syntax error saying the other db is an unknown catalog.

Posted By: DBlank
Date Posted: 14 Sep 2009 at 12:15pm

Not sure about that one.

You have access to creating views or stored procs in your SQL DB, correct?

I would move away fom the commands and create a view to use in the DB.

In the Enterprise Managee the GUI interface is relatively easy to use.

Expand your DB

Right click on the Views and select New View

This is a decent GUI interface to add tables, drag and drop your joins. Change them from inner to outer, plce conditions, add select criteria, etc.

it will write the SQL as you use the GUI.

You are suppose to be able to write case statements in the Column section although mine chokes on this option where as on my coworkers it is fine. Never worried about it as I can write those elsewhere.

Anyway this might be a better solution for you.

Is there any SQL DB folks nearby that can help you write this or teach you the interface?

Posted By: elie234
Date Posted: 14 Sep 2009 at 12:23pm

I thought about trying a view but I don't see an option for it. When I expand the DB in the expert I only see Add command and the tables. Does this mean I don't have access?
Unfortunantely we don't have any SQL DB people here.

Posted By: DBlank
Date Posted: 14 Sep 2009 at 12:40pm

You do it outside of crystal altogther. As I recall you have SQL 2000 ?

A prmary interface tools for that version is "Enterprise Manager". You use it for all sorts of things including creating views. If you have a SQL server it will be installed there but you can also have it installed on your local machine and set a connection to the server.

The rights aboiut creating views will have to do with server security (if it is on a server) and the SQL DB rights (where ever it is installed).

Posted By: DBlank
Date Posted: 14 Sep 2009 at 12:43pm

You can keep going with the Command option but you if you are going to be doing reports with this DB I would look at this as a longer term solution as this problem will constantly come up.

Not that I won't keep assisting but if you stick with the Command process you may want to start a new post re: the problem you are having and what your SQL looks like. My SQL is very limited compared to many others here and I think you may get better help on a new post.

Posted By: elie234
Date Posted: 14 Sep 2009 at 1:30pm

It is a Progress DB associated with our ERP software so I don't think I have the Enterprise manager, or at least I can't find it. I will look further to see if there is something like that.
Thanks for all your help, I will start a new post if I keep going with the command.

Posted By: DBlank
Date Posted: 14 Sep 2009 at 1:36pm

Sorry, thought it was a SQL DB for some reason and threw you a red herring.

I am not familiar with Progress so I can't speak to it's functionality or options. You may be "stuck" with a Command.

Posted By: Cordellv
Date Posted: 14 Sep 2009 at 2:04pm

I thought I could attached report that is running on a PROGRESS database where the SQL command references more than one table but I dont see how. If you will email me mailto:cvail@wsipc.org - cvail@wsipc.org I will send a copy of it to you. Then you can look at how it was created and give it a try and see if you can modify it enough to see how it works and maybe it will help you with your report. Cv Seattle, WA

-------------
Making Things Better One Day At A time

Print Page | Close Window