Performance Problem--Am I at the limit?

[ebutter]

Crystal v.10
XML data using ADO.Net driver

I have a four column report like this:

Asset Name Beginning Value Growth% Income%

There is also two grouping levels based on good sized formulas.

This simple report is taking over 1 min to load--I assume because of all the complexity in my formulas. Is there a way to make this perform or are we asking too much with all of the conditional logic?

Record Selection:

({Asset.ObjectType} = "Qualified Plan" and
{ReportOptions.rptbsqualplanchkbox} = "1" and
{AssetYear.Value} = {Revision.planyear} and
{SubAssetYear.Value} in [0, {Revision.planyear}])
or
({Asset.ObjectType} = "Life Insurance" and
{ReportOptions.rptbslifeInscashvalchkbox} = "1" and
{AssetYear.Value} = {Revision.planyear})
or
({Asset.ObjectType} = "Stock Option" and
{ReportOptions.rptbsinvestablechkbox} = "1" and
{AssetYear.Value} = {Revision.planyear} and
{SubAssetYear.Value} in [0, {Revision.planyear}])
or
({Asset.ObjectType} = "Account Asset" and
{ReportOptions.rptbsinvestablechkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {Revision.planyear} and
{SubAssetYear.Value} in [0, {Revision.planyear}])
or
(({Asset.ObjectType} = "Business Asset" or {Asset.assetcategory} = "Business Use") and
{ReportOptions.rptbsbususechkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {Revision.planyear})
or
({Asset.assetcategory} = "Investable Asset" and
{ReportOptions.rptbsinvestablechkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {Revision.planyear})
or
({Asset.assetcategory} = "Personal Use" and
{ReportOptions.rptbsperusechkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {Revision.planyear})
or
({Asset.assetcategory} = "Real Estate Property" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {Revision.planyear})
or
({Asset.ObjectType} = "Discretionary Asset" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {Revision.planyear} and
{SubAssetYear.Value} = {Revision.planyear})

Group 1:

if {Asset.ObjectType} = "Stock Option" then
"Investable Asset"
else if ({Asset.ObjectType} = "Qualified Plan" and {Asset.radioreportoptions} = 0) then
"Qualified Plan"
else if ({Asset.ObjectType} = "Qualified Plan" and {Asset.radioreportoptions} in [1,2]) then
"Investable Asset"
else if {Asset.ObjectType} = "Account Asset" then
"Investable Asset"
else if ({Asset.ObjectType} = "Business Asset" or {Asset.assetcategory} = "Business Use") then
"Business Use"
else if {Asset.ObjectType} = "Life Insurance" then
"Life Insurance"
else if {Asset.assetcategory} = "Investable Asset" then
"Investable Asset"
else if {Asset.assetcategory} = "Personal Use" then
"Personal Use"
else if {Asset.assetcategory} = "Real Estate Property" then
"Real Estate Property"
else if {Asset.ObjectType} = "Discretionary Asset" then
"Investable Asset"

Group 2:

if {Asset.ObjectType} = "Discretionary Asset" and {SubAsset.assetclassactive} = 1
and {SubAsset.allocin} <> 0 then
{SubAsset.assetclass}
else if {Asset.assetclass} = "N/A" then
"Other"
else
{Asset.assetclass}

Every value in the report is a formula due to all of the conditional logic in the requirements.

There are 7 tables and all joins are inner:

Revision join to Asset join to AssetYear
Asset join to SubAsset join to SubAssetYear
Revision join to ClientInfo
Revision join to ReportOptions

I know this is a big question! Any thoughts?

 

[IdoMillet]

I don't have experience with xml & ado.net but to isolate where the problem is (date retrieval or formulas), you could remove the complex formulas and see how the load time changes.

The first order of the day however is to use the built-in performance info provided by Crystal. Under the Report menu there is an option called "Performance Information..."

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com