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

x
Solved

# How to filter a data report

Posted on 2011-10-31
Medium Priority
225 Views
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.

Export.png
0
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?

mlmcc
0

LVL 14

Expert Comment

ID: 37059693
You could add a group by quote number (or really a group on a formula that is:
left({table.quotefield},5)

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.

0

LVL 19

Expert Comment

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

//@QuoteNo
Split({table.Quote},'-')[1]
0

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.

James
0

## Featured Post

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 …