running total or variable in subreport?

[52]

This is going to make me crazy:

I have 2 tables (as an example):

tblOrderTable, tblOrdersOnHold (same fields in both tables) CustomerID, ProjectID, CostOfItem.

The other table is tblCustomer: CustomerID, Name, State
Here's what I'm looking for our of my report:

In short, I need to combine CostOfItem from both tables according to State.

Ex...
Group 1 (header): (State)
Total Order.CostOfItem + OrdersOnHold.CostOfItem
-----------------------------------------------------------
Group 2 (header): (Customer)

(I can't change the database - I have to work with what I have.) I found some info on Running Totals, but the help screens say these options can't be used with ODBC and I haven't had much luck using them. My other option is to use subreports (One for Orders and one for OrdersOnHold, but I can't seem to get the data from the subreports to the main report). Any help would be GREATLY appreciated. Thanks.

 

[synapsevampire]

First, I don't think that you need subreports.

Second, I've not heard of ODBC causing problems when using Running Totals.

Place the tblCustomer table first, and then join the other 2 tables to it.

Group by State, and then by Customer.

Create a formula containing the 2 fields you want to sum.

Place the formula in the details section, and it appears that you should suppress the Details section.

Right click and select Insert Summary->sum and select for all groups (and perhaps grand totals).

Drag the totals to the Group Header.

Delete (or suppress) the formula in the details section.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

If you have 2 one-to-many relationships to the Customer table, I think you will either need subreports or will need a way to write a UNION query in SQL). You might get away without these if there is only one record in the first 2 tables for each project.

The comment about ODBC is probably not related to running totals, but is related to the join option entitled

"Read All of One, All of the other"

Which would solve your problem without subreports, but can't be done in ODBC (only in Datafile mode). Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[52]

Ok, I tried to simplify my problem and was hoping there was a simple answer. I need to include all customers from both OrdersOnHold and Orders - So I needed to add a 'dummy' Customer table to link to one. I also need to read all records from OrdersOnHand and from Orders - so I changed the linking to right outer join to get the report to read all the records (If I don't do this, I'll just receive the first record of the Customer - customers can have more than one order and more than one OrderOnHold).

Is there a way to combine the two tables into one since they are virtually the same table?

 

[kenhamady]

Unless you can write a UNION query in SQL, you will need a subreport.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[52]

I've used the subreports.

Now I have two variables being passed to Group 2 - once a value shows in a group it continues to show until the numbers in that group change.

Also, I'll need the total my two variables to be combined for Group 1 for a grand total.

Thanks for all the help so far!!

 

[synapsevampire]

I guess I don't understand the requirements, I'd think that you could use the Customer with Left Outer to each of the other tables.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

k,

He would get table inflation, since "customers can have more than one order and more than one OrderOnHold".

52,
Make sure the subreport is in GH2, and then use shared variables to bring the value back to the main report. To add them up you will need to use Running totals with variables, described in faq149-182. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[synapsevampire]

Ahhh, I guess I should have read the posts more closely.

You might consider doing this on the database side with Views/Queries/Sp's.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[52]

I got it!!! Thank you SO much.