Troubleshooting a record selection formula 1

[ebutter]

V10
XML file with ADO.NET driver

I have the following record selection formula in a report that groups and lists assets:

{Asset.ObjectType} <> "Disablility Insurance" and
{AssetYear.Value} = {Revision.planyear} and
({Asset.ObjectType} = "Stock Option" and {ReportOptions.rptbsinvestablechkbox} = "1") or
({Asset.ObjectType} = "Account Asset" and {ReportOptions.rptbsinvestablechkbox} = "1") or
({Asset.ObjectType} = "Qualified Plan" and {ReportOptions.rptbsqualplanchkbox} = "1") or
(({Asset.ObjectType} = "Business Asset" or {Asset.assetcategory} = "Business Use") and
{ReportOptions.rptbsbususechkbox} = "1") or
({Asset.ObjectType} = "Life Insurance" and {ReportOptions.rptbslifeInscashvalchkbox} = "1") or
({Asset.assetcategory} = "Investable Asset" and {ReportOptions.rptbsinvestablechkbox} = "1") or
({Asset.assetcategory} = "Personal Use" and {ReportOptions.rptbsperusechkbox} = "1")

The complexity is required because the users have the ability to "shut-off" any of the asset types that they don't want to display. Currently, the report works with running totals to create subtotals and grand totals, but there is a hitch.

The dataset returned by the above formula is huge. Instead of giving me one record per asset, it is returning 31. I can make the report display correctly by suppressing the Detail section and grouping on AssetID, but this seems like a funky solution that may result in poor performance.

Can anyone look at that formula and tell me what I'm doing wrong? Thank you very much!

 

[Naith]

I don't think the problem is in your selection criteria.

It sounds to me like you have a cartesian product on your hands because your table joins are wrong.

You can verify this by taking the SQL generated by Crystal, and using it to interrogate the backend directly.

Check them with your data scheme or DBA to ensure you're driving your joins from the correct fields.

Naith

 

[lbass]

You could try adding one more set of parens, as in:

{Asset.ObjectType} <> "Disablility Insurance" and
{AssetYear.Value} = {Revision.planyear} and
(
({Asset.ObjectType} = "Stock Option" and {ReportOptions.rptbsinvestablechkbox} = "1") or
({Asset.ObjectType} = "Account Asset" and {ReportOptions.rptbsinvestablechkbox} = "1") or
({Asset.ObjectType} = "Qualified Plan" and {ReportOptions.rptbsqualplanchkbox} = "1") or
(({Asset.ObjectType} = "Business Asset" or {Asset.assetcategory} = "Business Use") and
{ReportOptions.rptbsbususechkbox} = "1") or
({Asset.ObjectType} = "Life Insurance" and {ReportOptions.rptbslifeInscashvalchkbox} = "1") or
({Asset.assetcategory} = "Investable Asset" and {ReportOptions.rptbsinvestablechkbox} = "1") or
({Asset.assetcategory} = "Personal Use" and {ReportOptions.rptbsperusechkbox} = "1")
)

Not sure if that's the way you wanted to cluster them, but I do think you need extra parens to clarify that specific "or" statements are not "or's" for the entire first part of the statement.

-LB

 

[ebutter]

Very interesting result! I tried your suggestion and got back 2 records per asset instead of 30--big change. So I tried this, but still get 2 records for each asset:

({Asset.ObjectType} <> "Disablility Insurance" and {AssetYear.Value} = {Revision.planyear}) and
(
({Asset.ObjectType} = "Stock Option" and {ReportOptions.rptbsinvestablechkbox} = "1") or
({Asset.ObjectType} = "Account Asset" and {ReportOptions.rptbsinvestablechkbox} = "1") or
({Asset.ObjectType} = "Qualified Plan" and {ReportOptions.rptbsqualplanchkbox} = "1") or
(({Asset.ObjectType} = "Business Asset" or {Asset.assetcategory} = "Business Use") and
{ReportOptions.rptbsbususechkbox} = "1") or
({Asset.ObjectType} = "Life Insurance" and {ReportOptions.rptbslifeInscashvalchkbox} = "1") or
({Asset.assetcategory} = "Investable Asset" and {ReportOptions.rptbsinvestablechkbox} = "1") or
({Asset.assetcategory} = "Personal Use" and {ReportOptions.rptbsperusechkbox} = "1")

Any idea what's causing this last doubling? Thanks very much.

 

[ebutter]

Got it working. Your fix was correct. There was an additional problem caused by my thinking that there had to be a duplicate formula in Details to have it displayed in the Group section. Once I deleted and suppressed Details I was all set. Thanks!

 

[synapsevampire]

You might find my FAQ of interest for record selection formulas:

faq767-3825

-k