Evaluation of formulas issue 2

[ebutter]

V.10
XML data using ADO.NET (xml) driver

Account Asset Table joined to SubAccounts Table in a 1 to many relationship.

Two columns in my report look like this:

Net Equity % of Net Equity

Group 1 10,000 Net Equity/Total Net Equity
Details 5,000 Net Equity/Total Net Equity
etc.

I am using Running totals for all of the number fields which actually have 3 layers of grouping, and they are all working correctly. My problem is in the % of Net Equity column because Total Net Equity doesn't seem to be available (I think). I have to use Running Totals (I think) because the 1 to many data structure causes summing problems when I use the Sum function.

Lastly, I tried using the EvaluateAfter() command in my percent formula to tell to evaluate after the Running Total but that didn't work.

Is there a workaround for this problem?

Many thanks,
ebutter

 

[ebutter]

Are you saying to put--

{AssetYear.Value} = {?pm-AssetYear.Value}

into both the Main and Subreports selection formulas?

My links don't change though, do they? I'm still linking on AssetYear.Value as above and Window Years, correct?

You've been incredibly patient. I'm in EST and have to go see my kids, thanks!
ebutter

 

[lbass]

I am saying that:

{AssetYear.Value} = {?pm-AssetYear.Value}

...should automatically appear in your subreport selection formula if your linking is correct. It does not belong in your main report formula. I asked you to paste your subreport selection formula into the post--did you paste the main report formula instead?

-LB

 

[ebutter]

Here's a complete status.

-Yes. I pasted the wrong formula. And yes,{AssetYear.Value} = {?pm-AssetYear.Value} does exist at the end of the subreport record selection.

-I checked links again and both are there.
--AssetYear.Value to ?PM-AssetYear.Value with checkbox checked and AssetYear.Value selected.
--?WindowYears to ?WindowYears

-Subreport is in GH1 which is the AssetYear.Value group.

-Shared Variable formula is in the Report Footer of the Subreport.
WhilePrintingRecords;
shared numbervar grtot := {#SumGroup1};

-Shared Variable Reset formula is in GF1.
WhilePrintingRecords;
shared numbervar grtot := 0;

-% Details formula:
WhilePrintingRecords;
shared numbervar grtot;
if grtot <> 0 then
{@Get Value (Net Equity)} % grtot;

-Status of output for Window Years 2005, 2006, & 2007 show that all three years are correctly calculating the numbers in the Equity column and Years 2005 and 2006 are incorrectly using the 2007 grtot value in the calculation of the % numbers. 2007 numbers are correct since grtot is correct for that year:

Year 2005

Net Equity % Grand Total
Investable Assets (Grp2)
Large Cap Equity (Grp3)
Rampart Account (Dtl) 468,053 78.32 %
Subtotal (Grp3) 468,053 78.32 %

Emerging Mkts (Grp3)
Rabid Bros. (Dtl) 56,140 9.39 %
Subtotal (Grp3) 56,140 9.39 %

Investable Subtotal (Grp2) 524,193 87.71 %

Grand Total(Grp1) 524,193

ebutter

 

[lbass]

Please post the subreport record selection formula. Something is wrong here.

-LB

 

[ebutter]

Subreport Record Selection:

({Asset.ObjectType} = "Qualified Plan" and
{ReportOptions.rptbsqualplanchkbox} = "1" and
{Asset.radioreportoptions} = 0 and
{AssetYear.Value} = {?Window Years} and
{SubAssetYear.Value} in [0, {AssetYear.Value}])
or
({Asset.ObjectType} = "Qualified Plan" and
{ReportOptions.rptbsqualplanchkbox} = "1" and
{ReportOptions.rptbsinvestablechkbox} = "1" and
{Asset.radioreportoptions} in [1,2] and
{AssetYear.Value} = {?Window Years} and
{SubAssetYear.Value} in [0, {AssetYear.Value}])
or
({Asset.ObjectType} = "Life Insurance" and
{ReportOptions.rptbslifeInscashvalpctchkbox} = "1" and
{AssetYear.Value} = {?Window Years})
or
({Asset.ObjectType} = "Stock Option" and
{ReportOptions.rptbsinvestablepctchkbox} = "1" and
{AssetYear.Value} = {?Window Years} and
({SubAssetYear.Value} in [0,{AssetYear.Value}]))

or
({Asset.ObjectType} = "Account Asset" and
{ReportOptions.rptbsinvestablechkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{Asset.radioshowaccounttotal} = "0" and
{AssetYear.Value} = {?Window Years})
or
({Asset.ObjectType} = "Account Asset" and
{ReportOptions.rptbsbususechkbox} = "1" and
{SubAsset.assetcategory} = "Business Use" and
{Asset.checkboxshowonbalancesheet} = "1" and
{Asset.radioshowaccounttotal} = "1" and
{AssetYear.Value} = {?Window Years} and
{SubAssetYear.Value} in [0, {AssetYear.Value}])
or
({Asset.ObjectType} = "Account Asset" and
{ReportOptions.rptbsinvestablechkbox} = "1" and
{SubAsset.assetcategory} = "Investable Asset" and
{Asset.checkboxshowonbalancesheet} = "1" and
{Asset.radioshowaccounttotal} = "1" and
{AssetYear.Value} = {?Window Years} and
{SubAssetYear.Value} in [0, {AssetYear.Value}])
or
({Asset.ObjectType} = "Account Asset" and
{ReportOptions.rptbsperusechkbox} = "1" and
{SubAsset.assetcategory} = "Personal Use" and
{Asset.checkboxshowonbalancesheet} = "1" and
{Asset.radioshowaccounttotal} = "1" and
{AssetYear.Value} = {?Window Years} and
{SubAssetYear.Value} in [0, {AssetYear.Value}])
or
({Asset.ObjectType} = "Account Asset" and
{ReportOptions.rptbsrealestatechkbox} = "1" and
{SubAsset.assetcategory} = "Real Estate Property" and
{Asset.checkboxshowonbalancesheet} = "1" and
{Asset.radioshowaccounttotal} = "1" and
{AssetYear.Value} = {?Window Years} and
{SubAssetYear.Value} in [0, {AssetYear.Value}])

or
(({Asset.ObjectType} = "Business Asset" or {Asset.assetcategory} = "Business Use") and
{ReportOptions.rptbsbususepctchkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {?Window Years} and
({SubAssetYear.Value} in [0,{AssetYear.Value}]))
or
({Asset.ObjectType} <> "Account Asset" and
{Asset.assetcategory} = "Investable Asset" and
{ReportOptions.rptbsinvestablepctchkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {?Window Years})
or
({Asset.assetcategory} = "Personal Use" and
{ReportOptions.rptbsperusepctchkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {?Window Years})
or
({Asset.assetcategory} = "Real Estate Property" and
{ReportOptions.rptbsrealestatepctchkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {?Window Years})
or
({Asset.ObjectType} = "Discretionary Asset" and
{ReportOptions.rptbsinvestablepctchkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {?Window Years} and
{SubAsset.allocin} <> 0 and
({SubAssetYear.Value} in [0,{AssetYear.Value}]))
and
{AssetYear.Value} = {?Pm-AssetYear.Value}

 

[lbass]

Try putting parens around the whole set of "or" statements as below--otherwise the final line might only get tacked onto the last "or" statement:

(
(
{Asset.ObjectType} = "Qualified Plan" and
{ReportOptions.rptbsqualplanchkbox} = "1" and
{Asset.radioreportoptions} = 0 and
{AssetYear.Value} = {?Window Years} and
{SubAssetYear.Value} in [0, {AssetYear.Value}]
)
or
(
{Asset.ObjectType} = "Qualified Plan" and
{ReportOptions.rptbsqualplanchkbox} = "1" and
{ReportOptions.rptbsinvestablechkbox} = "1" and
{Asset.radioreportoptions} in [1,2] and
{AssetYear.Value} = {?Window Years} and
{SubAssetYear.Value} in [0, {AssetYear.Value}]
)
or
(
{Asset.ObjectType} = "Life Insurance" and
{ReportOptions.rptbslifeInscashvalpctchkbox} = "1" and
{AssetYear.Value} = {?Window Years}
)
or
(
{Asset.ObjectType} = "Stock Option" and
{ReportOptions.rptbsinvestablepctchkbox} = "1" and
{AssetYear.Value} = {?Window Years} and
({SubAssetYear.Value} in [0,{AssetYear.Value}])
)

or
(
{Asset.ObjectType} = "Account Asset" and
{ReportOptions.rptbsinvestablechkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{Asset.radioshowaccounttotal} = "0" and
{AssetYear.Value} = {?Window Years}
)
or
(
{Asset.ObjectType} = "Account Asset" and
{ReportOptions.rptbsbususechkbox} = "1" and
{SubAsset.assetcategory} = "Business Use" and
{Asset.checkboxshowonbalancesheet} = "1" and
{Asset.radioshowaccounttotal} = "1" and
{AssetYear.Value} = {?Window Years} and
{SubAssetYear.Value} in [0, {AssetYear.Value}]
)
or
(
{Asset.ObjectType} = "Account Asset" and
{ReportOptions.rptbsinvestablechkbox} = "1" and
{SubAsset.assetcategory} = "Investable Asset" and
{Asset.checkboxshowonbalancesheet} = "1" and
{Asset.radioshowaccounttotal} = "1" and
{AssetYear.Value} = {?Window Years} and
{SubAssetYear.Value} in [0, {AssetYear.Value}]
)
or
({Asset.ObjectType} = "Account Asset" and
{ReportOptions.rptbsperusechkbox} = "1" and
{SubAsset.assetcategory} = "Personal Use" and
{Asset.checkboxshowonbalancesheet} = "1" and
{Asset.radioshowaccounttotal} = "1" and
{AssetYear.Value} = {?Window Years} and
{SubAssetYear.Value} in [0, {AssetYear.Value}]
)
or
(
{Asset.ObjectType} = "Account Asset" and
{ReportOptions.rptbsrealestatechkbox} = "1" and
{SubAsset.assetcategory} = "Real Estate Property" and
{Asset.checkboxshowonbalancesheet} = "1" and
{Asset.radioshowaccounttotal} = "1" and
{AssetYear.Value} = {?Window Years} and
{SubAssetYear.Value} in [0, {AssetYear.Value}]
)

or
(
({Asset.ObjectType} = "Business Asset" or {Asset.assetcategory} = "Business Use") and
{ReportOptions.rptbsbususepctchkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {?Window Years} and
({SubAssetYear.Value} in [0,{AssetYear.Value}])
)
or
(
{Asset.ObjectType} <> "Account Asset" and
{Asset.assetcategory} = "Investable Asset" and
{ReportOptions.rptbsinvestablepctchkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {?Window Years}
)
or
(
{Asset.assetcategory} = "Personal Use" and
{ReportOptions.rptbsperusepctchkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {?Window Years}
)
or
(
{Asset.assetcategory} = "Real Estate Property" and
{ReportOptions.rptbsrealestatepctchkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {?Window Years}
)
or
(
{Asset.ObjectType} = "Discretionary Asset" and
{ReportOptions.rptbsinvestablepctchkbox} = "1" and
{Asset.checkboxshowonbalancesheet} = "1" and
{AssetYear.Value} = {?Window Years} and
{SubAsset.allocin} <> 0 and
({SubAssetYear.Value} in [0,{AssetYear.Value}])
)
)
and
{AssetYear.Value} = {?Pm-AssetYear.Value}

Let me know if this doesn't solve the problem.

-LB

 

[ebutter]

That was it! I need to do some testing now, but I think we're there.

Many, many thanks,
ebutter