Difficult record selection formula

[ebutter]

Crystal V.10
XML using ADO.net driver

I'm having trouble trying to make a record selection formula work. I've stripped it

back to the essential stuff so as to make it understandable. This is a huge question--

if anyone dares wade in I thank you!

The report selects asset records based on several conditions. In some cases the records

come from a parent table and in some cases the records come from a child table. This is

a difficult to describe problem, so will include a lot of information. You should know

that the data structure works so far with many different reports.

Because this is xml and not relational data the data structure is a little different,

but the ADO.net driver treats it like a relational database. The structure:

Asset Table, has many
Asset Year Tables
(Asset Table holds static data about an asset. The Asset Year Tables hold values that

change on a yearly basis.)

Asset Table, has many (Left Outer Join)--the join seems correct
SubAsset Tables
(For instance, a Qualified Plan asset may have many sub-accounts. Thus, the Asset Table

hold parent level data that is potentially made of data rolled-up from the SubAsset

Tables. The user may choose not to enter any data at the SubAsset level. In that case,

the SubAsset level does not exist.)

SubAsset Table, has many (Left Outer Join)--the join seems correct
SubAsset Year Tables
(The relationships are the same as at the asset level, just down one level.)


Simplified report pulling just Qualified Plan asset records looks like the following.

There are three conditional states that effect how the record would be displayed:

State 1: Display record in its own Category

Qualified Plans (Asset Category--group level)
EB's Profit Sharing Plan 20,000

State 2: Display Asset Level record in the Investable Category

Investable Assets (Asset Category--group level)
EB's Profit Sharing Plan 20,000

State 3: Display SubAsset Level records in the Investable Category. Do not display

Asset Level records.

Investable Assets (Asset Category--group level)
Profit Sharing SubAsset1 5,000
Profit Sharing SubAsset2 15,000


I'm trying to place all of my conditional logic in the Record Selection formula. If you

think it should be broken up and placed in my Detail or Group sections--let me know.

The problems I'm having relate to the Parent-Child structure of the Asset-SubAsset

tables and how Crystal works with that structure. To understand this there are 4

Qualified Plan records:

1) Defined Benefit-- radioreportoptions = 0 (Display in QP Category), subaccounts exist
2) 401k-- radioreportoptions = 1 (Display asset level in Investable Category),

subaccounts exist
3) Profit Sharing-- radioreportoptions = 2 (Display subaccounts only), subaccounts have

there own names, values, etc.
4) Defined Contribution-- radioreportoptions = 0 (Display in QP Category), no

subaccounts exist

Record Selection Formula:

if({Asset.radioreportoptions} in [0, 1]) and IsNull({SubAsset.AssetID}) then
({Asset.ObjectType} = "Qualified Plan" and {ReportOptions.rptbsqualplanchkbox} = "1")

and
{AssetYear.Value} = {Revision.planyear}

else if ({Asset.radioreportoptions} in [0, 1]) and Not IsNull({SubAsset.AssetID}) then
({Asset.ObjectType} = "Qualified Plan" and {ReportOptions.rptbsqualplanchkbox} = "1")

and
{AssetYear.Value} = {Revision.planyear} and {SubAssetYear.Value} = {Revision.planyear}

else if ({Asset.radioreportoptions} in [2]) and IsNull({SubAsset.AssetID}) then
({Asset.ObjectType} = "Qualified Plan" and {ReportOptions.rptbsqualplanchkbox} = "1")

and
{AssetYear.Value} = {Revision.planyear}

else if ({Asset.radioreportoptions} in [2]) and Not IsNull({SubAsset.AssetID}) then
({Asset.ObjectType} = "Qualified Plan" and {ReportOptions.rptbsqualplanchkbox} = "1")

and
{AssetYear.Value} = {Revision.planyear} and {SubAssetYear.Value} = {Revision.planyear}



Here's an example of what I'm using in Details. It's pretty straightforward and seems

to be working correctly:

if({Asset.radioreportoptions} in [0, 1]) then
{AssetYear.GetEndingValue}
else
{SubAssetYear.GetEndValue}


Results:

1) Correctly places Defined Benefit, 401k, and Profit Sharing SubAccounts in the correct

groups.
2) Returns two duplicate Defined Benefit and 401k records instead of just one.
3) Correctly displays the two Profit Sharing SubAccounts with there correct values.
4) Does not display the Defined Contribution record at all.

Any thoughts on how to debug this? Because this is xml there is no sql query that you can look at.

If you made it this far, my sincere thanks!

 

[synapsevampire]

In general I would try to avoid lengthy descriptions and stick with technical information:

Example data (show tables, columns)
Expected output

I'll venture that:

2) Returns two duplicate Defined Benefit and 401k records instead of just one.

This sounds like row inflation. I would suggest that you right click each field in the details and select format section->Common->Suppress if duplicated. Often times people call rows duplicated because MOST of the columns are dupes, but this is NOT a dupe. In either case, the above solution should work as you can suppress based on another column being duped by entering the formula {table.field=previous({table.field}).

4) Does not display the Defined Contribution record at all.

Are you certain that the data is in the XML? I'd have to see some example data, which section of the report it is in to know, so again, please post technical information.

remember that you can right click any field and select browse data to get an idea of what is in there.

-k

 

[ebutter]

Thanks. I've tried your suggestion with the following results:

1) There is a Defined Contribution record in the xml. It does not show up in the Browse Data--the record selection is not grabbing it.
2) When I Suppress Duplicates and Blank Rows it works for the Asset Level records, but it wipes out the SubAsset Level records completely even though they are not duplicates.

Thanks,
ebutter