[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 742
  • Last Modified:

Crystal Reports - Top 20 Rows with Highest Revenue

I am attaching the report file. I just want to see the TOP 20 Rows with HIGHEST TOTAL AMOUNT field. I am not sure how to do it as I do not have any Groups in the report. Please let me know. Thanks
Top-20-Accounts-YTD.rpt
0
rustypoot
Asked:
rustypoot
  • 7
  • 3
  • 3
  • +1
1 Solution
 
MOA81Commented:
Hello



1-right click on the details section and click "section expert"
2-on the sections list, click details sectio
3-leave the check box cleared and click the "X+2" command button next to the supress  (no drill down) the formula editor opens
4-type the formula
remainder(recordnumber,20)<>0
5. save your fuormula
click ok

make sure to sort the record desc to get the highest top 20
0
 
rustypootAuthor Commented:
Thanks for getting back to me. But it did not work. It still returns more than half the records! Can you please look at the file that I attached and let me know.? Thanks
0
 
mlmccCommented:
Is the total amount a calculated field as a summary?

One way os to group on a field then you can use the group sort expert to limit you to the top N if the total is a summary

If it isn't a summary try this
Suppression formula
RecordNumber > 20

mlmcc
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rustypootAuthor Commented:
Thanks very much. Let me try this and test it.
0
 
PCIIainCommented:
Attached is an example of the group solution.

Watch for the grand total at the bottom. I don' t know if that will be the overall total, or the total of the top 20.
Top-20-Accounts-YTD.rpt
0
 
rustypootAuthor Commented:
Thanks ALl for your Help. But I have 2 Companies (OCRC and FSM) and the User wants Top 20 for each Company! How can I do this? Also, the Group Total should be by each Company and Grand Total should be for 40 Top Rows (20 for OCRC and 20 for FSM). I have attached the file. Thanks
Top-20-Accounts-MTD.rpt
0
 
mlmccCommented:
Does this do what you want?

mlmcc
Top-20-Accounts-MTD-1-.rpt
0
 
rustypootAuthor Commented:
Hey There! Thanks for getting back to me. No this is not what I wanted.

I have 2 Companies - OCRC and FSM. I have grouped by these 2 Companies. I want Top 20 Gross Revenue Rows for OCRC and Top 20 Gross Revenue Rows for FSM. The totals should be just for the 20 rows for each Company and Grand Total should be for 40 rows displayed. Thanks
0
 
mlmccCommented:
Since i don't have the data, i can't see what you get.

Are you getting more than 20 records?

Do the totals include morerecords?

mlmcc
0
 
rustypootAuthor Commented:
As u can see in the attached rpt, I am pulling all the records for OCRC and FSM. The rpt is grouped by Company. I want TOP 20 for FSM and TOP 20 for OCRC. Also, the the report should have Totals by Company [Total of only Top 20 rows] and Grand Total [40 Rows displayed on the rpt]. Hope this helps. Thanks so much
Top-20-Accounts-MTD.rpt
0
 
PCIIainCommented:
The attached report should display the top 20 account numbers by revenue but total the entire amount.
Unfortunately, because your original didn't have account number on it (I am assuming account number doesn't have duplicates within a company.) I can't now see the data to fix the totals...

Populate with data and post back?

Top-20-Accounts-MTD.rpt
0
 
rustypootAuthor Commented:
Thanks so much. But what did you do? B/c I have to the same changes to the YTD report. I do not see any filters or formulas! Please let me know so that I can make same changes to YTD rpt. THANKS
0
 
PCIIainCommented:
I grouped by account number, moved all the printing to the group footer, and then used the group sort expert to limit to the top 20.

Note, the summary at the bottom of the report still sums all the data, which is why I asked for a copy with the data in it, so I can 'fix' the summary information.
0
 
rustypootAuthor Commented:
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now