Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to filter a data report

Posted on 2011-10-31
Medium Priority
Last Modified: 2012-06-27
I have a report that right now display all quote number grouped by a date. In the 9/29/11 field I return quote numbers that are valid in both formats 10001 or 1000-1. The problem I need help with is that if a quote number is 1000-1 then it only returns that single number and not the 1000 number. The "-1" defines it as an updated quote so I would only want to display the most updated quote number.

I tried using an if else and a instr function but that did not do the trip.

Thanks in advance.
Question by:cmdolcet
LVL 101

Expert Comment

ID: 37059566
Are you using a parameter in the filter?

Are you doing any summaries in the report?

LVL 14

Expert Comment

ID: 37059693
You could add a group by quote number (or really a group on a formula that is:

Suppress detail lines, just showing group header.

Insert a summary and choose Minimum, or Maximum of {table.quotefield} into the group header. Either MIN or MAX should should the quotes with -# if two quotes are present. Try both to see which yields your desired result.

LVL 19

Expert Comment

ID: 37061617
Create a formula to extract the quote number part of the field.

LVL 35

Accepted Solution

James0628 earned 500 total points
ID: 37061789
A variation on GJParker's and LinInDenver's suggestions.

 Create a formula like:

 Split ({quote # field}, "-') [ 1 ]

 That's more flexible than using Left ({quote # field}, 5), which assumes that you will always have 5 characters in front of the "-".  If there will always be 5 characters, then using Left is fine.

 Group on the new formula.  Within the group, sort on the quote # field (the field, not the formula).  Suppress the group header (And details?) and put the quote # field in the group footer, and you should see the largest quote # in each group.

 Note that this all assumes that the number after the "-" will always be 1 digit/character.  If you could have numbers like 10000-9, 10000-10, 10000-11, this won't work, because 10000-9 would sort after 10000-10 and 10000-11.  If you could have numbers like those, you'll have to do some more work to find the highest number.


Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question