numbering the top 20 items in a group (1, 2, 3, 4,...)

[germsnan]

(upon previewing this post it displayed (programmer)
after my user name) (don't know why because I am not
a programmer, but an accountant)

I am using Crystal 7.0
My knowledge is rusty as I only get to use Crystal
in between accounting periods.

My problem:

I need to get the top 20 bond issuer listings to SHOW on my report the number for each one, 1 to 20.
ex: 1) issuer 1
2) issuer 2
3) issuer 3 and so on

I can get each of the 20 grouped line items to show a
" 1) " in front of each item by putting in a StringVar formula in the header but I can't figure out the
forumla to sequentially number each of those 20 items.

I tried using "nth largest" function in my formula
but kept getting errors.
I would appreciate any assistance.


This site has been great for individuals like myself
who are not programmers but need some ideas and tips
in using Crystal to create some decent looking accounting
reports from a database.

 

[synapsevampire]

You may have various options:

Use a Top N report (if CR 7 supports that, I can't recall)

Or set a counter:

In the preceding group, or the beginning of the report, create and place a suppressed formula like:

whileprintingrecords;
Global numbervar MyCounter:=0;
Global booleanvar ShowMe:=1

In your Group section
create and place a formula like:
whileprintingrecords;
Global numbervar MyCounter;
Global booleanvar ShowMe:=1
MyCounter := MyCounter+1;
If MyCounter = 20 then
ShowMe := 0;
If ShowMe <> 0 then
MyCounter

I haven't tested this, and you may have meant that you want to display this in the details, rather than the group, but this should give you the theory and a proper example.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Hello germsnan,

The reason you have a programmer designation is that you may have inadvertantly selected it when registering.

As to your question, the easiest thing to do if this is in the details section is to simply place the recordnumber (located in Special Fields) on the report canvass.

This numbers the retrieved records so even if you do a topn, it will still number sequentially.

If this is Grouped with data underneat, create a RunningTotal. I believe 7.0 had that, do a Count, on Change of Group (Select your Group), Reset Never.

Hope that solves the problem if not let me know and I will be happy to help you,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

germsnan: Ro's solutions sound best, if you're counting the detail rows, I thought that you were numbering the first 20 groups, not the detail within the groups.

&quot;I can get each of the 20 grouped line items to show a&quot;

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[germsnan]

I need to number the groups displayed from a
top N.
20 groups in all, but each group needs to be
numbered 1, 2 and so on.
I need to put together some sort of formula
to display a string &quot;1).&quot;, &quot;2).&quot;, etc.
I've already got one to display &quot;1).&quot;, but need
something to display sequentially.
I must have the ability to change the look of those
strings, (cosmetics) to appease mgt. if they change
their minds about the way the report looks.

After that gets resolved, I need to figure out a way
to list the top 20 bonds on one side of the page of the
report and list the top 20 stocks on the other side
of the page. But that is another hurdle for another day.

In the past, I have been able to place whatever I want
by creating a singular formula for whatever bond
category I wanted displayed, even showing zero in a
category that had nothing. Lots of formulas for
certain reports, but it gets the job done.
Creating this formula is probably, easy, but it somehow
has me stumped.

 

[rosemaryl]

Dear Germsman,

You have choices here.

You can create the running total using the Running Total expert and then another formula for formatting. Or, you can do this with a manual running total formula. Let's try it first using Crystal's tool.

As far as displaying the second dataset you would need a subreport or a crosstab to do that.

Here are some formulas.

Design your report with the grouping you desire.

Go to Report Topn Expert and set it up the way you need it to display.

If you create the running total with the Running Total Expert, let's call it GrpNum. Create the running total as:

Count (your field upon which you are producing the Group)
On change of Your Group field
Reset - Never.

Now, create the following formula and place in in the Group Header before your group name field:

ToText({#GrpNum},0) & &quot;.&quot; & &quot&quot;

That should do it.

Let me know how this works out or if you need more assistance.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[germsnan]

Dear Rosemary,

Tried your suggestion, but I get an error message
for the formula which states

The remaining text does not appear to be part of the formula

with the curser stopping just before the 1st ampersand.

Thought maybe I miskeyed something, but I keyed your
formula exactly.

 

[germsnan]

Dear Rosemary,

I looked into the help section for some examples for
that totext function, and substituted a &quot;+&quot; instead of
&quot;&&quot; and Whoala!
It now works!
Now if I can only get my stock subreport to not overlay
the left side of the page of my bond report,
I'm in business.

Thanks, Rosemary!
Nancy Ceglarski

 

[rosemaryl]

Dear Germsman,

Sorry, I just realized that you are using 7.0. The & was introduced in 8.0.

Change the & to a + sign and the formula will work fine.

Sorry again.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rosemaryl]

We were posting at the same time.

You should be able to place your subreport and make the left margin anywhere you want.

What exact problem are you having?
ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[germsnan]

Dear Rosemary,

I am linking to another report (stocks) because
that report uses CUSIP and not ISSUER for grouping
like the bond report does.
I tried making the margins smaller in the stock
report but it still does not look right. Maybe I need to
hide the headers and such, I don't know.

I don't want to have to create a subreport since
my knowledge with them is very limited.
I thought linking to another report would have been the best way to go in this situation.

And now I just find out some of the formatting has
been changed on this report to summarize US Treas.
as a separate number apart from the top 20.

bookv mktv bookv mktv
us govt tr xxx xxx

top 20 bonds top 20 stks
1 xxx xxx 1 xxx xxx
2 xxx xxx 2 xxx xxx
3...

this is basically what I'm trying for in the layout of
this report

 

[rosemaryl]

Dear Nancy,

Save your report with data and email it to me to get a quick look. I will respond write back.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.