Linking 3 Tables 4

[hhiebert]

I have one primary table and 2 foreign tables that I need to use in a report. The relationship between the primary and foreign table (in both cases) is one to many. For each row in the primary table, I need to show totals from fields in the foreign tables.

Let me try and explain.

Primary Table Foreign Table Foreign Table
CUST DEMAND TERM

The report should look like this:

Cust ID Demand Total Term Total
3 $460.55 $86.00
5 $983.00 $8438.00
16 $907.98 $5471.08 ....

What happens when I link the cust.ID from the primary table to the cust.ID in the Demand table and then the cust.ID from the primary table to the4 cust.ID in the Term table is that I get duplicated products.

I'm trying to stay away from using sub-reports (due to time). Is there another way of avoiding duplicate records?
hh...

 

[pelajhia]

If you have a unique identifier in the demand and term tables (eg: demandid), you can create a formula that reads the demandid into an array; if the id already exists in the array, the formula would show zero and not add it to the array. If the id does not exist in the array, show the dollar amount and add the id to the array.

Your detail section will still be fairly difficult to look at, but you can group and subtotal based on custid to get the report to look 'normal'.

 

[chelseatech]

The second one to many table will create a cartesian join and create a recordset with every combination of each record.

There are two potential solutions:
1) Subreport solution:
Do the Master table and one table in the main report, and the other table in a subreport.

2) SQL solution:
Use SQL to create a UNION of the fields you use in the report from the two lower lvel tables and then join this to the master table. In CR9 you can do this in the Report itself, while in earlier versions you will need to create a view in the DB. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lbass]

If Demand Total and Term Total are desired summaries of details in other fields, then:

1-Group on {CustomerID} and drag GroupName to GroupFooter.
2-Create a running total #DemandTotal by summing {Demand} on change of {DemandID} (if it exists) and reset on change of Group(CustomerID). Repeat for #TermTotal, summing {Term} on change of {TermID} (if it exists), and resetting on change of group.
3-Place #DemandTotal and #TermTotal in GroupFooter.

-LB

 

[hhiebert]

Chelsea (or others),
Can you let me know more about the 2nd option. I'm not familiar with using UNIONs and surprise, surpise, Crystal Decisions knowledgebase doesn't have much to add.

Can you help with some of these blanks?

SELECT demand.ID, etc. AS Products
FROM demand
UNION
SELECT term.ID, etc. AS Products
FROM term

..and then how do I join this to the master table?

LB,
This logic doesn't appear to work b/c of the cartesian joing caused by the second one-to-many table. While the demand fields are sorted by the unique ID #, the terms are repeated within each unique demand ID.

ex.

cust # 45 demand balance term balance
demand ID #3 14.00 term ID#1 97.54
demand ID #3 14.00 term.ID#2 84.12
demand ID #4 32.78 term.ID#1 97.54
demand ID #4 32.78 term.ID#2 84.12

Thanks to all for your input to date.
hh...

 

[hhiebert]

This is what I ended up using. Is there a better, more effective way?

SELECT "cust"."no", "cust"."branch", "cust"."surname", "cust"."name", prod='dmd', "dmd"."rowno", type="dmd"."type", "dmd"."sub", "dmd"."funds", "dmd"."benefit", "dmd"."closed", "dmd"."balance", prowno="dmd"."rowno_custdmd_cust"
FROM "pdmetro"."dbo"."cust" "cust"
INNER JOIN "pdmetro"."dbo"."dmd" "dmd"
ON "cust"."rowno" = "dmd"."rowno_custdmd_cust"
WHERE &quot;dmd&quot;.&quot;status&quot; <> 'closed' and &quot;dmd&quot;.&quot;contract&quot; = 0 and &quot;cust&quot;.&quot;status&quot; <> 'closed'
UNION ALL SELECT &quot;cust&quot;.&quot;no&quot;, &quot;cust&quot;.&quot;branch&quot;, &quot;cust&quot;.&quot;surname&quot;, &quot;cust&quot;.&quot;name&quot;, prod='term',&quot;term&quot;.&quot;rowno&quot;, type=&quot;term&quot;.&quot;code&quot;, &quot;term&quot;.&quot;sub&quot;, &quot;term&quot;.&quot;funds&quot;, &quot;term&quot;.&quot;benefit&quot;, &quot;term&quot;.&quot;closed&quot;, &quot;term&quot;.&quot;balance&quot;, prowno=&quot;term&quot;.&quot;rowno_custterm_cust&quot;
FROM &quot;pdmetro&quot;.&quot;dbo&quot;.&quot;cust&quot; &quot;cust&quot;
INNER JOIN &quot;pdmetro&quot;.&quot;dbo&quot;.&quot;term&quot; &quot;term&quot;
ON &quot;cust&quot;.&quot;rowno&quot; = &quot;term&quot;.&quot;rowno_custterm_cust&quot;
WHERE &quot;term&quot;.&quot;status&quot; <> 'closed' and &quot;term&quot;.&quot;contract&quot; = 0
and &quot;term&quot;.&quot;status&quot; <> 'cancelled' and &quot;cust&quot;.&quot;status&quot; <> 'closed' hh...

 

[lyanch]

You didn't mention if you are using CR9 or not. You still have the problem of summing and grouping your numbers.. although not as many duplicates, you will still have a lot to deal with.

If you are using CR9 you can do the following directly in Crystal, other wise you will need to create a SP or view in the database.

Select cust.no,
etc,
(Select sum(demamd) from demand where demand.cust = cust.no and other criteria) as demand,
(Select sum(term) from term where term.cust = cust.no and other criteria) as term
from cust
where cust criteria

Excuse the semi SQL but you should get the idea. You need to be sure to link the subquery to the cust.no in the outer query.

Lisa

 

[Ktwhite]

I am finalizing a Financial Report in Crystal 8.5, where I am having difficulty keeping the detail records with the correct group when the report is generated. In the example below, the report contains four hierarchal groups in the following order: 1). FUND, 2). ORG, 3). REVENUE, 4). PARENT ACCOUNT; and then sorted by CHILD ACCOUNT ( DETAIL) . I have a page break after each revenue group, and a page break after each ORG Group. This keeps the detail with the correct ORG, however, I get an blank header page between each change of ORG which I am trying to avoid. If I do not include the page break after each ORG, then the ORG spans the page, and becomes associated with the next set of detail incorrectly. Any Suggestions?

FUND 1000 header group 1
ORG 500 header group 2

6953 PARKING FUND TRANSFER 228,019 detail
6961 WATER O & M TRANSFER 677,849 detail

PARENT 6900 905,868 grp4 subtotal

5253 SALES TAX2 2,257 detail
5255 R & B 4,785 detail

PARENT 5200 7,042 grp4 subtotal


** TOTAL REVENUES 912,910 group 3 subtotal
Ken

 

[lbass]

hh-

Sorry for leading you astray. I use the running total method above successfully in multiple-table, one-to-many situations for summaries based on distinct counts or even averages of fields meeting certain criteria--but I forgot that this doesn't always work for sums. Have you ruled out using a subreport for one of the columns as Chelseatech earlier suggested? That might be the simplest solution.

-LB

 

[lbass]

hh-

On third(!) thought, please ignore my last post. You can use running totals to achieve your results. Create running totals as outlined in my first post, except for {#termbal}, instead of resetting on change of group, reset on change of {demandID}. This will give you the following:

cust # 45 dem bal #dembal term bal #termbal
demand ID #3 14.00 14 term ID#1 97.54 97.54
demand ID #3 14.00 14 term.ID#2 84.12 181.66
demand ID #4 32.78 46.78 term.ID#1 97.54 97.54
demand ID #4 32.78 46.78 term.ID#2 84.12 181.66

gf totals 46.78 181.66

This only works if there is more than one term ID, so if it is possible to have only one, use the following formula instead of {#termbal} in the group footer {@sumtermbal}:

if distinctcount({term.ID},{cust.ID}) = 1 then {termbal} else {#termbal}

In retrospect, this seems such a simple solution, but it took me quite a while to see it.

-LB

 

[lyanch]

lbass I agree that running totals will give the correct numbers, the problem is that with a cartesian product you could conceivable return hundreds of thousands of records to report a 1000 lines in the report. It works... but it can be very slow.. a big i/o hit on the db.. etc etc..

If you only have a few records it is no big deal..

Lisa

 

[lbass]

Lisa,

I often use multiple tables and work with large volumes of data with problems like the above to solve, so I'm very interested in alternative, faster approaches. However, I'm using CR 8.0 and also do not have permission to create stored procedures or views (and wouldn't know how to anyway!). From what I can gather, using the UNION statement or SQL subqueries would not be an option for me. I have only rarely used subreports, partly because they seem to make things even slower. Are there other methods that would increase speed that might be an option given the limitations I've mentioned?

-LB

 

[lyanch]

You will need to make at least one of the &quot;sums&quot; a subreport. Yes they are slow. It essentially does the same as a subselect, but crystal does it one line at a time after the original query is complete.

Lisa

 

[hhiebert]

Thanks Lisa,

that SQL worked wonders and I learnt something new.

Oh and yes, I was using CR 9.

hh...