Crystal Reports - Show group header info if no detail exists - Redux

[DrWerner]

Well, the first pass at this was OK. Now can we try it with an Oracle database?

I have 2 tables linked one to the other. Let's call them Header and Detail linked on OrderID.

It is POSSIBLE to have a Header with NO Detail as follows:

Header 1
Detail a
Detail b
Header 2
Header 3
Detail x
Detail y

So I have a group at OrderID of Header and at the Crystal Reports Group Header printing Header info only. The detail section within that group prints Detail info.

HOWEVER, if there are no Detail records for an OrderID, I don't get the group header to print showing the Header info.

My selection is ONLY on the Header table.

The whole thing is packaged up in a sub-report, but that doesn't seem to have any importance in the results.

I've tried changing my links between the two tables directionally. I've even tried making the inner join into both types of outer joins.

I can't get Crystal to make a group FOLLOWING a group - it only puts another layer within the same group. Bummer.

Any suggestions? This seems a common thing to me.

Thanks!

/DrW

 

[synapsevampire]

I'll assume that you have an order table, and a order details table, and that you always want the order number to show, and details when applicable.

Make a Left outer join between the Order table and the Order details table, and then DON'T place anything in the record selection formulas on the Order Details table.

You'll get all orders, and details when applicable.

If you need to limit rows on the details side, you might accomplish this using a subquery or subreport, depending upon technical information, such as Crystal version, database used, connectivity type, table layout, fitering, none of which you've supplied.

-k

 

[DrWerner]

K,

Thanks. I tried that, but unfortunately it is negated by a user-input date range record selection applicable for both headers and details.

Oracle 9
Crystal Reports 9.2

Never heard of a Crystal "sub-query." And this is already a sub-report. And I can't insert a sub-report into a sub-report. Nor will it let me insert a group within a group. Both of those would do the trick.

If only Crystal had a "details optional" setting I could check at the group level.

Any other suggestions?

/DrW

 

[vidru]

Could this be done using a SQL Command as the data source with a UNION query, where the first pass grabs the orders with details, and the second pass grabs the others? It should work, because your OrderDetails parameter would only need to reflect in the WHERE clause of the first pass.

Hope you're following me...

It's been a few years since I've written SQL against Oracle, so pardon my SQL Server syntax (but you should get the drift).

SELECT Orders.OrderID, Orders.Date, OrderDetails.Product, OrderDetails.Quantity, HasDetails = 1
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE <your selection here, using Orders AND OrderDetails>
UNION
SELECT Orders.OrderID, Orders.Date, NULL, NULL, HasDetails = 0
FROM Orders
WHERE Orders.Date <=,BETWEEN, etc.> <your param> AND NOT EXISTS (SELECT OrderID FROM OrderDetails WHERE OrderID = Orders.OrderID)

-dave

 

[DrWerner]

Dave,

I just thought of the same thing.

HOWEVER, my data subset selection is user input parameters in Crystal Reports, and I don't think that the COMMAND (manual input of queries) allows parameters to be carried in. If no parameters are allowed, it might not work because of the Group / Detail limitations of Crystal.

I'll check that out because it might solve the problem.

Thanks!

/DrW

 

[vidru]

I thought that you could define the parameters at the SQL Command window...

Is a view or stored proc out of the question?

-dave

 

[synapsevampire]

Why would you need the date range in the details?

That's what's losing the data.

When I say a subquery, I mean using REAL SQL and the Add Command to paste it in as the data source.

CR 9 was the first version to allow it, and it's well suited to this.

Or you might create a View or Stored Procedure on the Oracle side.

-k