Multiple 'details' fields on a report

[martindell]

OK, I know I'm being dumb but I'm new to Crystal and really stuck - I'd appreciate any advice on the following.

I'm creating a report to issue a statement of account to a customer. It needs to detail items ordered and payments received (often multiple instances of both) and work out whether there is a balance owed or overpayment (hah!)

I'm querying three tables, orders, items and payments, where rows on the items and payments tables relate to a unique order id row. Basically, payments and items know which order id they belong to so I'm reporting on items and payments that belong to a particular order id.

I can't get a report to work that displays all the payments AND all the items - either of them but not both at the same time. This is a subreport in itself otherwise I'd use a subreport to do this.

I've done all the help files and examples I can find but am getting nowhere.

Thanks in advance
Martin

 

[fredp1]

Hi MArtin,

This sounds like a clasic Left Outer Join issue.
You firstly need to provide details on your version of Crystal and the DB that you are using...
Also some sample data on the problem that your see with the results that you expect is a standard requirement to get some detailed help.

But gererally, You need to link the Order table to the Item table using a left outer join and also line the Order table to the payment table again using a left outer join.
You can do this in the main report as long as you do not filter any fields on the right hand side, (The Items and Payment Table)
If you do need to filter the right handside tables, a subreport(s) will be need. (Subreports can sometimes be avoided if used purley to pass shared variables by using sql expressions making the reports run faster).

What specifically have you tried?
Fred

 

[Katy44]

You should be able to filter the right hand side tables as long as, for every field you are filtering on you also check if it is null as well. This needs to be part of an "or" statement and needs to be the first thing checked in the or.

 

[martindell]

Thanks for taking the time to reply. I'm using Crystal 10 and have been using the report wizards to query a local MySQL database - I've heard of a left join but wouldn't know what to do with one! Here's my table structure for the report I'm playing with.


# Table structure for table `agents` (these are the clients)

`a_id` int(4) unsigned NOT NULL auto_increment,
`a_name` varchar(60) NOT NULL default 'Un named agent',
`a_status` enum('live','hold','deleted') NOT NULL default 'live',
`a_logo` tinyint(1) NOT NULL default '0',
`a_contact_name` varchar(60) NOT NULL default '0',
`a_tel` varchar(20) NOT NULL default '0',
`a_fax` varchar(20) NOT NULL default '0',
`a_mob` varchar(20) NOT NULL default '0',
`a_email` varchar(60) NOT NULL default '0',
`a_url` varchar(255) NOT NULL default '0',
`a_addr1` varchar(255) NOT NULL default '0',
`a_addr2` varchar(255) NOT NULL default '0',
`a_town` varchar(100) NOT NULL default '0',
`a_region` varchar(100) NOT NULL default '0',
`a_postcode` varchar(20) NOT NULL default '0',
`a_country` varchar(20) NOT NULL default '0',
`a_reg_name` varchar(100) NOT NULL default '0',
`a_reg_nif` varchar(16) NOT NULL default '0',
`a_reg_addr1` varchar(255) NOT NULL default '0',
`a_reg_addr2` varchar(255) NOT NULL default '0',
`a_reg_town` varchar(100) NOT NULL default '0',
`a_reg_region` varchar(100) NOT NULL default '0',
`a_reg_postcode` varchar(20) NOT NULL default '0',
`a_reg_country` varchar(20) NOT NULL default '0',
`a_ext_id` varchar(60) NOT NULL default '0',
PRIMARY KEY (`a_id`),
KEY `a_id` (`a_id`)
TYPE=MyISAM;

# Table structure for table `orders` (an order belongs to an agent and contains items and revenue)

`order_id` int(11) NOT NULL auto_increment,
`a_id` int(11) NOT NULL default '0',
`order_company` enum('kyero','domodo') NOT NULL default 'kyero',
`order_value` float NOT NULL default '0',
`order_payment` enum('outstanding','partial','full','other') NOT NULL default 'outstanding',
`order_payment_type` enum('cash','transfer','cheque','card') default NULL,
`order_created` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`order_id`)
TYPE=MyISAM;

# Table structure for table `revenue` (shows payments made against an order)

revenue_id int(11) NOT NULL auto_increment,
order_id int(11) NOT NULL default '0',
revenue_type enum('bank transfer','cheque','cash') NOT NULL default 'bank transfer',
revenue_date datetime NOT NULL default '0000-00-00 00:00:00',
revenue_amnt float NOT NULL default '0',
PRIMARY KEY (revenue_id),
KEY revenue_id (revenue_id)
TYPE=MyISAM;

# Table structure for table `items` (shows items ordered)

`item_id` int(11) NOT NULL auto_increment,
`order_id` int(11) NOT NULL default '0',
`a_id` int(11) NOT NULL default '0',
`prod_id` int(11) NOT NULL default '0',
`item_value` float NOT NULL default '0',
`item_created` datetime NOT NULL default '0000-00-00 00:00:00',
`item_start_date` datetime NOT NULL default '0000-00-00 00:00:00',
`item_end_date` datetime NOT NULL default '0000-00-00 00:00:00',
`item_status` enum('live','hold','deleted') NOT NULL default 'live',
PRIMARY KEY (`item_id`),
KEY `item_start_date` (`item_start_date`),
KEY `item_end_date` (`item_end_date`),
KEY `item_status` (`item_status`)
TYPE=MyISAM;

# Table structure for table `products` (product definition of an item)

`prod_id` int(11) NOT NULL auto_increment,
`prod_name` varchar(50) NOT NULL default '0',
`prod_desc` text,
`prod_actions` varchar(255) NOT NULL default '0',
`prod_company` enum('kyero','domodo') NOT NULL default 'kyero',
`prod_value` float NOT NULL default '0',
`prod_valid_days` int(3) NOT NULL default '93',
`prod_status` enum('live','hold','deleted') NOT NULL default 'live',
PRIMARY KEY (`prod_id`)
TYPE=MyISAM;

What I'm trying to achieve is something like this:

agent details
order number

list of items/products ordered
subtotal of items and tax calculation

list of payments received

balance outstanding/overpayed

If I have just one item, everything works, if I have just one payment, that works too. The problems start when I have multiple of either. I've tried putting revenue in the details field of the report or items but it always does screwey things and repeat the same items for the number of payments or vice versa. as I said I'm at the wizard stage and have no idea about sql queries. Thanks in advance if you can steer me in the right direction.

Martin

 

[Katy44]

To create a left outer join:

You need to go to "Database Expert" in your Database menu. Then click on the "Links" tab. (Clicking "Auto-arrange" can help your view if it looks a bit of a mess)
You need to identify the link between the Order table and the Item table. If you right click on it and choose link options. Then select "Left Outer join". In that Link Options box, there is some text at the top, and you need to make sure it sayd "Orders ---> Items" (or something like that). If not, click OK and then right click the link again and click on "Reverse link" (the arrow on the links should change sides). Do this also for your Orders ---> Revenus link (I take it this is what you mean by payments?)

But I'm not sure if this will solve your problem. Which part of the above report is the subreport? The way I would set out the above report is to, for the moment, leave out the "list of payments received", sort out the rest and then add this as a subreport. You will need your left outer join from Order to Item for the main report.

Also, if you have any selection criteria that use fields from the Item table you will need alter this slightly as above.

Hope this has helped.

 

[martindell]

Thanks, that did help. I used a subreport for the items part and listed the payments in the usual way. The problem I had originally was that this report was a subreport already but I just rejigged things so that it was a main report.

Thanks for your time and help
Martin

 

[fredp1]

Katy,

My understanding is that you cannot have any filter on the right hand side. If you do, the outer outer join acts like a inner join. Whats really needed is a Join on a Subquery.

I've posted something out today to get clarification on the most efficent way to handle this in Crystal or otherwise.
Here is the tread..

http://www.tek-tips.com/viewthread.cfm?SQID=852317&SPID=767&page=1

as for Martin, the sub reports will work as long as the response time is acceptable.

Any feedback is welcomed.

Fred

 

[Katy44]

From my understanding of what was written, I think I struggled with this problem a while back.
If your critera is:

.....
AND...
AND {table1.field1}=1
...AND

where table1 is on the RHS of a left join, then you need to change it to:

.....
AND...
AND(isNull({table1.field1}) OR {table1.field1}=1)
...AND

and do this for all criteria on fields from table1.
The fact that isNull is evaluated first is critical because of the way Crystal evaluates this. (if '{table1.field1}=1' was evaluated first, and the field was null, then this section would be evaluated as NULL, not true, and the other side of the 'OR' wouldn't be evaluated).
I have told Crystal about this because, although it sort of makes sense when you think about the way the records are evaluated, it doesn't act in the same way as most people are used to and also the fact that no-one seems to know about it implies to me that Crystal don't know either.

I'm not sure if this is what you meant. Sorry if I've waffled on about something completely out of context!

 

[fredp1]

Hi Katy,

Thanks for responding!
I tried testing the theory on a report but got the wrong results. I'm just wondering where a obdc or a file db makes a difference to the results.
i also tried testing in SQL server query analyser and got the same (wrong) results as I would expect.
Whats has confused me is that there are some postings out there that support what your saying, but I beg to differ.

I also checked up on a text that I use and they specifically mention that 'the exact ordering of the Wheer clause is unimportant... SQL requires that the ordering of the predicates in a where clause must not affect the result set. They must be associative- the query , must return the same result regardless of the order in which they're procssed." Ken Henderson pg. 14 The Guru's Guide to Transact-SQL

If you can provide some sample data/example of the query that you have used, I'll like to check it out. I just can't help to thing its releated back to the file db's like Access or dBase etc, but I'm not sure.

Maybe that other thread that I started may help to clarify the situation.


Cheers

 

[Katy44]

I have only ever tried this with SQL Server.
I'll try to set one up using the Northwind db on Access.

 

[Katy44]

No I won't - since I've just read that you use SQL Server 2k too, I'll use the Northwind db in that! I only have Crystal 9 though.

 

[Katy44]

I've created a report to demo this on the Northwind db. Do you have an email address so I can send you the report?

 

[Katy44]

OK, if you create a report from the Northwind db - use the tables EmployeeTerritories and Territories.
Not all territories have employees associated.

Put TerritoryID from the territories table and EmployeeID from the other table in the details section of the report.

Now make the link between the two tables a left outer from Territories-->EmployeeTerritories. Refreshing the report should show a few blanks in the EmployeeID field.

Now add some criteria - I put:
{EmployeeTerritories.EmployeeID} in [1, 2, 3, 4, 5, 6, 7, 9]

The blanks will disappear on refresh, and there should be 45 records.

Change the selection formula to:
isNull({EmployeeTerritories.EmployeeID}) or {EmployeeTerritories.EmployeeID} in [1, 2, 3, 4, 5, 6, 7, 9]

The blanks are back and you should have 49 records.

 

[Katy44]

SQL requires that the ordering of the predicates in a where clause must not affect the result set. They must be associative- the query , must return the same result regardless of the order in which they're procssed"

I completely agree! Which is why I consider this to be a bug in Crystal rather than just the way Crystal works. This doesn't happen with ANSI-SQL, nor with any database I have worked with (although I admit that may be a limited test!).

A reason why this test may not have worked for you is if you have tables also joined to the RHS table, and criteria on fields in those tables. You need to repeat this for every criteria on a field from a table to the RHS of a left hand join.

 

[lbass]

Katy,

The issue is that if you have the following:

country empl
A 1
B 2
C 3
D 4
E
F
G 5

A left join with no selection will return Countries A to G.

A left join with a selection of isnull({table.empl}) or {table.empl} in [1,2,3] will return only countries A,B,C,E,F.

Therefore if you want to display all countries, but only the field from {table.empl} in [1,2,3], then you would need to use no select and, instead, to create a formula:

if isnull({table.empl}) or
not({table.empl} in [1,2,3]) then
"" else totext({table.empl})

-LB

 

[Katy44]

I see what you mean, but surely rather than doing the above in the selection criteria, you would create a formula to display the empl ids in the way you wanted to see. Why would you want to select records out in the usual way and then force them back in?

 

[lbass]

Not sure what you mean. I was suggesting that NO record selection on the employee table be used, and that instead the above formula be used for the correct display.

-LB

 

[Katy44]

Yes I see, I'm totally confusing myself!
Thanks.

 

[fredp1]

Hi Katy,

I'm not sure were we are all upto, but I agree with lbass. I also tried using the Northwind db, using ODBC, but again, the isnull on the RHS table didn't work.

Are you still confused or do you want to do some testing?
I'm happy to send you my hotmail email if you want me to test a report.
Cheers
Fred

 

[Katy44]

No, I had confused myself.
When you put criteria on the RHS of the left join in the report, the fields from the LHS which you made appear using the left join will disappear. This can be remedied by adding the isNull() check at the start of each criteria. But, and this is where I was confused, you will still lose the fields from the LHS that are filtered out by the criteria.
I see what you are getting at, but I haven't ever been in a situation where this is a problem, since you simply display the fields on the RHS as you would wish to see them. All I have ever had to worry about was why the fields from the LHS with no corresponding RHS record disappear, and the method above solves it.