I am a newbie to Crystal Reports, so pardon me if my terminology is incorrect or this is really basic. I am running CR 2008 and connecting to Salesforce.com stored procedures (Salesforce reports).

I have two data sets [Loans] and [Assets]. Any given loan record can have any given asset records associated to it. Any given account record can have any loan record related to it.   I would like to group related loans and accounts and determine the loan to value (absolute value of the loan divided by the value of the collateral).

Sample Data Set:

Loans:
Loan 1 -$110
Loan 2 -$90
Loan 3 -$50
Loan 4 -$40

Assets:
Asset 1 $220
Asset 2 $180
Asset 3 $60
Asset 4 $40
Asset 5 $80

Asset 1 and Asset 2 are used as collateral for Loan 1 and Loan 2.
Asset 3 and Asset 4 are used as collateral for Loan 3.
Asset 5 is used as collateral for Loan 4.

In this scenario above I am expecting 3 groups. Each group would have a loan to value of 50%.

I am able to group on loans and assets respectively, but then I get duplicate values whenever there is more than one connection to any given record. i.e if I group on loans, then my assets are double counted. If I group on assets, then my loans are double counted. I would like to total the value of the loans and then total the value of the collateral for any set of related loans and assets.

I was able to create a running total formula that summarized the distinct count for "Loans" and evaluate on change of field "Assets" which is generate the correct "break" points in the data set, but I don't believe it is possible to group on a running total field.

Any thoughts on how one might accomplish creating groups like this? Any help would be most appreciated. Thanks so much.


Edited by Owen Smith - 11 Sep 2013 at 10:50am

DBlank will recognize my solution...
a stored proc, that would create an different identifier that would be a 'transaction' this would remove the duplication.

The other solution that pops to mind is subreports...but for them to not suffer from the same issue of duplication would be to again have a 'transaction'.

To be fair, I think that this is the part of the data that DBlank is looking for...is there something that ties all the data together or is that data really like:
asset 1 and 2 are used as collateral on loan1
asset 1 and 2 are used as collateral on loan2
if this is the case, there probably isn't any simple, non-trivial solution to this using CR

one would think that there is a customer tied to both loans and assets, and they hopefully are the same customer. Then you could group by the customer and sum the assets and loans to get the final value.

that's my 2 cents

I am not sure if Common Table Expressions are valid in a command object, but for a stored proc in Sql Server you could use them to dedupe your data.

something like:
with cte as (
select
    rowid = row_number() over (partition by table.assetNo order by table.assetNo),
table.assetNo,
table.loanNo,
any other info you want
from table
)select * from cte where rowid = 1


HTH