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

 

[satinsilhouette]

Do you have the WhilePrintingRecords in your formulas? If so then you might need to make a separate section for the % of Net Equity and place it under the Total Equity section, the order of processing would allow the Total Equity to process first then the %. Then just underlay the Total Equity to the % Equity in the Section.

 

[ebutter]

Thank you! This is a clever and valuable tip!

 

[synapsevampire]

How could the details have 10,000 for the group entity yet there are only 5000 at the details?

Providing impossible sample data won't help you nor those trying to assist you.

Basically, if you have a details field with the equity, your formula for the percent of the group is:

if sum({table.equity},{table.groupfield}) > 0 then
{table.equity}/sum({table.equity},{table.groupfield})*100
else
0

If you have a details field with the equity, and your formula needs the percent of the total equity in the report then use:

if sum({table.equity}) > 0 then
{table.equity}/sum({table.equity})*100
else
0

You spent a lot of time explaining what you want to try, and I appreciate the efforts, however in future posts I would advise that you post technical information and requirements rather than chatting about what you think might work and what doesn't.

In particular:

Sample data (what's in the report isn't example data, what's in the recordset returned is)
Expected output (using text to describe what numebers should be there won't help as much as what the results should be)

Anyway, from the above you should be able to get any percentages.

You can also forego the percent formula and use:

{table.equity}%sum({table.equity},{table.groupfield})

-k

 

[ebutter]

v.10
xml data using ADO.NET (xml) driver

So far all attempts to manipulate evaluation time and use suggestions above have not worked.

Conditions:
-Suppression of rows in use to eliminate duplicate records due to data structure.
-Net Equity column numbers created using Running Totals

Case with incorrect % column:

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

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

Investable Subtotal (Grp2) 524,193 100.00 %

Grand Total(Grp1) 524,193



Case corrected with expected % column:

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

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

Investable Subtotal (Grp2) 524,193 100.00 %

Grand Total(Grp1) 524,193

Example Formula used to Detail level % Grand Total:

WhilePrintingRecords;
({@Get Value (Net Equity)} / {#SumNetEquityDetailsTotal}) * 100

This supposed to be Detail / Grand Total. The problem is that the Grand Total does not seem to be available at evaluation time.

Thanks,
ebutter

 

[lbass]

You could either insert a subreport in the GH#2 header that provides the Group #2 summary so that it is available via a shared variable for calculation in the main report at the detail level, or you could add a command that uses only the table from which the summary is generated and which then provides the summary for use at the detail level. For further help, provide more information about your tables and fields.

-LB

 

[ebutter]

Commands are not an option due to the xml data, so let's look at the subreport option.

Data Tables in Use for Above Example:

Asset Table joined to AssetYear Table--one to many
Asset Table joined to SubAsset Table--one to many
SubAsset Table joined to SubAssetYear Table--one to many

-Grouping data comes from Asset and Subasset Tables via formula.
-Asset values (Net Equity column) come from AssetYear and SubassetYear Tables via formula.
-% values are calculated on the report.

Thank you!
ebutter

 

[lbass]

There might be a way to simplify the subreport for this purpose, but I would have to understand your data better than I do. So save a copy of your report as a subreport and then insert it back into your original report in the Group Header #1 section (I was mistaken in my last post). Link the subreport to the main report on your Group #1 field only. Then in the subreport create a formula:

whileprintingrecords;
shared numbervar grtot := {#Grp1total};

Place this in the report footer of your subreport. Suppress all sections within the subreport.

Then in the main report add the following reset formula to your Group #1 footer section:

whileprintingrecords;
shared numbervar grtot := 0;

Then create your percentage formulas using the shared variable, e.g.,

whileprintingrecords;
shared numbervar grtot;
{#grp3tot} % grtot;

-LB

 

[ebutter]

I'm stuck. I get a "divide by zero" error when I run the report. It's got nothing to do with the reset because I removed that. I believe it has to do with my link.

Current state:

-Subreport is made and formulas work.
-Subreport is inserted into Grp1 header.
-I'm trying to link on AssetYear.Value in the dialog, but the drop down only has ?PM - AssetYear.Value. Or one blank and other wrong choices.
-I've tried using the Select Data...checkbox and dropdown, but that doesn't seem to work.
-My formula for the Details line is:
WhilePrintingRecords;
shared numbervar grtot;
{@Get Value (Net Equity)} % grtot;
the grtot variable is being read as a 0 when I run it.

Am I not doing something? Thanks!

 

[ebutter]

I'm stuck. I get a "divide by zero" error when I run the report. It's got nothing to do with the reset because I removed that. I believe it has to do with my link.

Current state:

-Subreport is made and formulas work.
-Subreport is inserted into Grp1 header.
-I'm trying to link on AssetYear.Value in the dialog, but the drop down only has ?PM - AssetYear.Value. Or one blank and other wrong choices.
-I've tried using the Select Data...checkbox and dropdown, but that doesn't seem to work.
-My formula for the Details line is:
WhilePrintingRecords;
shared numbervar grtot;
{@Get Value (Net Equity)} % grtot;
the grtot variable is being read as a 0 when I run it. I'm assuming it should have the value established in the subreport in it.

Am I not doing something? Thanks!

ebutter

 

[lbass]

If your group #1 is on {AssetYear.Value} then in the bottom left you would see:

{?pm-AssetYear.Value}

and in the bottom right you should see a check on "Select records based on this field" and then the field "Value" should appear in the box.

It sounds like the shared variable isn't working, but as a check, change your formula to:

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

Did you remember to place the shared variable in the report footer of the subreport? Also, you cannot suppress the section the subreport is in. You can only suppress all sections within the subreport.

-LB

 

[ebutter]

Alright! It works. My Group1 header was suppressed and that was creating the problem.

My values are now right!

I want to thank you very much for all the time you spend helping people like me. This kind of information is invaluable and very difficult to work out without a lot more experience then I have.

ebutter

 

[ebutter]

OK! Now I've got a new layer of problem.

-My report has a parameter called "Window Years" which allows the user to select what years of data to look at.
-Window Years is implemented in Record Selection.
-My Subreport's Shared Variable formula correctly displays any number of successive values for the formula when placed in Grp1 Footer:

WhilePrintingRecords;
shared numbervar grtot := {#SumGroup1};

I am back to getting a "Divide by 0" errors when I try to run.

-Subreport is linked as before on AssetYear.Value (This was working before.)
-Subreport is in unsuppressed Grp1 header. Group 1 is on AssetYear.Value
-Shared Variable is in Grp1 footer of subreport.
-I tried changing my % formulas like:

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

This allows the report to run and returns 0's.

I'm wondering if I have to make some other Sub-Parent link to take into account Window Years?

Thanks,
ebutter

 

[lbass]

Didn't you save the report and use a copy of it as the subreport? If so, you will already have the same record selection formula in the subreport as in the main. Then go to edit->subreport links and select the main report parameter and move it to the right, and then use the dropdown in the bottom left corner to select {?window years}, NOT {?pm-?window years}.

I don't see why you would suddenly get zeros or divide by zero errors. Can you explain what you might have changed? Note that your shared variable formula should be in the subreport report footer. Since it is linked on the group 1 field, the subreport report footer will be the equivalent of the group footer.

-LB

 

[ebutter]

OK. I had to re-insert the subreport. Now I've got my numbers back.

There is one remaining issue. It seems that because we put the Shared Variable formula in the Report Footer that is only pulling the one single value for the last Window Year instead of providing (for example) 3 separate values for 2005, 2006, and 2007. I can tell because the % formulas are generating incorrect values because they're all using the 2007 number for grtot.

Shared Value =
WhilePrintingRecords;
shared numbervar grtot := {#SumGroup1};

Thanks,
ebutter

 

[lbass]

This means your link on year is incorrect. You need to have links both on the {AssetYear.Value} and on {?WindowsYear}. Please verify how you have these set up. If you are grouping on {AssetYear.Value} and have the subreport in the group header for that group, the running total can only have the value for that particular group.

-LB

 

[ebutter]

Links are:

AssetYear.Value to ?PM-AssetYear.Value with checkbox checked and AssetYear.Value selected.

?WindowYears to ?WindowYears

Subreport is located in GH1. Group 1 is on AssetYear.Value

I think I have what you are describing?

ebutter

 

[lbass]

That looks right. Please check the record selection formula in the subreport to see if those links are being incorporated correctly. Maybe you should paste the formula into the thread, as the above should work.

-LB

 

[ebutter]

OK. This record selection is a serious monster. The portion we're currently concerned with is the section on Account Assets.

({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}]))

 

[lbass]

The link on the AssetYear.Value is not appearing in the subreport selection formula. It should appear at the end as:

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

Without this the subreport will run for all selected years in each group header. In the main report, go to edit->subreport link->and double check that you have both parameters set up correctly.

-LB