TOPN Report

[sgd3476]

I have created a report called top 5 equity market values


Class Security market value %0f Portfolio


Bond ustbond 100,000 40%--This should not appear
Equity ibm 31,000 10%
Equity Staples 125,000 25%
Equity Cysco 10,000 7%
Equity AIG 25,000 9%
Equity MRK 50,000 15%

The problem I have is I only want to show equity, but I need to report percent of total Account. So when I select only equity, my % of total account is wrong. When I take out the select, everything is correct except the first line shows a bond.

 

[MJRBIM]

Use the formula SUPRESS SECTION function in the Section Expert on the Detail section, click on X-2 and set a formula like :

{Table.Class} <> 'Equity'

This will not show any Detail records where the Class IS NOT 'Equity', but will retain the count for your percentages.

 

[lbass]

For this to work I think you would have to insert a group on {table.class}, and then do the topN on market value for the class group, along with suppressing the equity group as suggested by MJRBIM. Even though you would be inserting another group level, your percentages could still reflect a percentage of the grand total. This way you won't lose one of your topN groups if equity happens to have a value that allows it to fall within the topN.

-LB

 

[sgd3476]

THAT DID IT!! You all are so great. Here's one more little piece of the puzzle.

How do I get the sum of the % of portfolio. The percentage is: percent of sum of {sp_1;market_value). I tried to just use the subtotal or grandtotal function, but when I selected the percentage of sum field, it said, not an available field to sum.
Thanks.
K

 

[lbass]

You would need to use variables. Create two formulas:

//{@accum} to be placed in the group header containing
{@%ofportfolio}:
whileprintingrecords;
numbervar grpcnt := grpcnt + 1;
numbervar totpercent := totpercent + {@%ofportfolio};

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar totpercent;

If you are suppressing groups that don't meet the topN, then the total percent will equal 100%. To suppress the unwanted groups within the equity group, change your suppression formula for the group to:

whileprintingrecords;
numbervar grpcnt;

{table.class} <> "Equity" or
grpcnt > 5 //substitute your topN figure

If you want to display the subtotal of the topN groups only, then when you set up the topN, uncheck "Include Others with the Name:". Then the above formulas would display only the subtotal.

-LB

 

[sgd3476]

LB---

I'm sorry, I'm not getting this. I may just be too frustrated b/c I've been working on this way too long with bad results.

Is there an easier way?

I unchecked "Include Others with the Name" and now have the subtotal of MV to be all of the equity positions, but I don't want that. I want the original amount that was there, just the subtotal of the top 5 equity market values. I tried to suppress the "others" but I keep getting an error message for the formula. The field I am using to calculate the % of Portfolio is not a formula. It's just the Percentage of Sum of sp_Classified_Position_Report2;1.market_value.

Sorry to be so obtuse, but I'm just not getting it.

-k

 

[lbass]

Please show an example with sample data so we can see what summaries you want.

It is unclear whether you want a subtotal of just the topN Equity values, a subtotal of all Equity values, a total of Equity and Bond values, a subtotal of the percents just for the topN values, etc.

You say that the % of Portfolio is a field, not a formula. Is market value a field also? I have assumed that these were formulas at a group level because you mention using topN. If they are not, then there's probably a much simpler way of doing this.

-LB

 

[sgd3476]

Lb-

Here's what I've got.

ASSETClass Security MV % of Port
Equity Exxon 400,000 .25
Equity EMC 100,000 .10
Equity NOK 125,000 .15
Equity MRK 250,000 .25
Equity ibm 10,000 .5
totalEquity of top 5 mv= 885,000 .80%

I am looking for the percent of total equities. Total of top 5 equity mv is 885,000/4,000,000(represents total equity in account) so 22.13%. I want to show the 22.13% somewhere in report. The market value is just a field also.
Thanks
Kelly

 

[lbass]

I am still confused about whether each of the lines in your example are detail fields or group summaries. From your previous post, it appears that somehow you were able to use a topN. At the same time, you say that market value is a field, not a summary, and yet topN is only possible for group summaries.

What you are trying to do is easily done--one way or another, depending upon your report structure--so please bear with me and explain whether your example shows group header info or detail info and how you have implemented the topN. Did you actually use the topN/group sort function? (Your example doesn't show a sort of any kind.) What are your groups, and are you suppressing certain groups, e.g., bonds? Are you suppressing the non-topN records or discarding them using the topN function. All of this information is necessary.

Thanks.

-LB

 

[sgd3476]

LB--I am so sorry to waste more of your time on this, but if I don't all will be worthless.... so here I go..
I have 2 groups, asset class and security id.

Class Security market value %0f Portfolio
Equity ibm 125,000 25%
Equity Staples 100,000 20%
Equity Cysco 70,000 15%
Equity AIG 55,000 10%
Equity MRK 20,000 5%
subtotal top 5 eq. = 370,000 total of %ofport75%
My report is showing group header info and I've implemented top N by selecting issue group and selecting topN(5) by Market Value. I am sorting by MV, just failed to illustrate. I then discard "other" not in top N. I am suppressing other asset classes by Using the formula SUPRESS SECTION function in the Section Expert on the Detail section, click on X-2 and set a formula asset class <> 'EQUITY'. I just want 370,000/total equity in account.

Is this enough info?

Kelly

 

[lbass]

I think you need to have section suppression on both group headers and footers 1 and 2 using:

{table.assetclass} <> "Equity"

Then without changing anything you've done so far, create three formulas:

//{@accum} to be placed in the Security ID Group Header:
whileprintingrecords;
numbervar topNtot := topNtot + sum({table.marketvalue},{table.securityID});

//{@display} to be placed in the Asset Group Footer:
whileprintingrecords;
numbervar topNtot;

//{@%oftotalequity} to be placed in the Asset Group Footer:
{@display} % Sum({table.marketvalue},{table.asset})

I think I was confused earlier in that it seemed that you wanted to show equity as a percent of a total portfolio that included non-equity classes. If you don't need those other classes, then I would just select them out. If, on the other hand, you DO mean to include other classes in the percentage of total, then we will have to make some adjustments again. Can you verify again which it is?

-LB

 

[sgd3476]

I just want to show the top5 equities mv as a percentage of all the equities mv in the account.

Thanks so much...I'm going to try this now.