Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Crystal Report Selection Criteria

Posted on 2011-03-25
Medium Priority
413 Views
Hello experts:
I have a request from a client who wants a report that shows cash transactions
over 10,000 but if there's at least one transaction over 10,000 she wants
to see all cash transactions regardles of amount.
I have started with this but I'm stuck on the 2nd part of the request.
Any help would be greatlty appreciated.
LANCE

(if  (\$ToNumber({HISTORY.Cashin}) >= \$10000) then(\$ToNumber({HISTORY.Cashin}) > \$0))

With the code above, all I get is 10000 amounts
0
Question by:lancerxe
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 4
• 2
• +1

LVL 101

Accepted Solution

mlmcc earned 800 total points
ID: 35215803
Are you trying to do this with selection criteria?
I don't think Crystal can do that at least not through the normal selection when using views and tables.

You could do it with a stored procedure.

Are you looking at the entire dataset for this.

One way to do this would be to conditionally suppress the sections with a formula like
if  (\$ToNumber(Maximum({HISTORY.Cashin})) >= \$10000) then
FALSE
Else
\$ToNumber({HISTORY.Cashin}) < \$10000)

mlmcc
0

LVL 26

Assisted Solution

Kurt Reinhardt earned 800 total points
ID: 35216050
Are there any other wrinkles to the request, such as some sort of grouping within the report?  As an example, you might have 5 groups in the report, you need to check if there are any transactions >= 10k within each group.  If there are, then display all transactions for that group. If there aren't, don't display the group at all.  Out of the 5 groups, only one might have any transaction >= 10k so only one group's data would be displayed.

If you do have groups, you could suppress the groups that don't meet the criteria by using the Group Select Expert using code similar to mlmcc's:

``````(\$ToNumber(Maximum({HISTORY.Cashin},{table.groupfield})) >= \$10000)
``````

This would suppress all groups that don't meet the criteria and display all transactions for all groups that do. While this is a simple approach, it's also not very efficient and won't allow you to display true grand totals.  It's not efficient, because all records would have to be returned and evaluated first to see if they meet the condition.  You could return 100k records to only display 5.  Also, when you use "Insert Summary" and have suppressed groups with the Group Select, the summary still reflects the values from all records, hidden or not. Likewise, you'll still see the hidden groups in the group tree.  Confusing and potentially misleading numbers.  You'd have to substitute the grand total with a running total or formula to get the right numbers

Personally, if possible, I'd right a SQL Expression field that checks for the existence of a transaction >= 10k.  The SQL Expression could then return a "Y" or "N", depending if the condition was met.  You could then use the SQL Expression in the record selection like this:

``````{%10k} = 'Y'
``````

That would efficiently return all transactions when >=10k was identified for the recordset.

~Kurt
0

Author Comment

ID: 35216083
Thanks guys

mlmcc : You mentioned a stored procedure.
How would I go about creating a stored procedure for this.
thanks

0

LVL 101

Expert Comment

ID: 35216245
You would do that in the database and the method depends on the database.

mlmcc
0

LVL 101

Expert Comment

ID: 35216249
Kurt could a COMMAND do this?

mlmcc
0

LVL 26

Assisted Solution

Kurt Reinhardt earned 800 total points
ID: 35216297
You could definitely do it in a command (or a stored proc - essentially the same thing as far as Crystal Reports is concerned), although that would entail changing the entire data source to a command, whereas using a SQL Expression (if possible) would allow the existing structure to remain the same. You could also create a SQL View to do the same thing and a view is treated just like a table as far as Crystal Reports is concerned, so that might be more report designer-friendly.

~Kurt
0

Author Comment

ID: 35218335
Created a SQL Expression called %10k
(
SELECT
COUNT(AccountNumber)
FROM
HISTORY
WHERE
cast (CashInAmountInSplitDeposit as money) > 9999.99
)

if ({%10k} > 1)

then(\$ToNumber({HISTORY.Cashinamountinsplitdeposit}) > \$0))

Got an error message:
%10k field is unknown
0

Author Comment

ID: 35218649
mlmcc:

When I tried this:
if  (\$ToNumber(Maximum({HISTORY.Cashin})) >= \$10000) then
FALSE
Else
\$ToNumber({HISTORY.Cashin}) < \$10000)

got this error message:
This fucntion cannpt be used because it must be evaluated later
((Maximum({HISTORY.Cashin}) is highlighted)

thanks
0

LVL 101

Assisted Solution

mlmcc earned 800 total points
ID: 35219615
That is true.  I forget that Maximum and Minimum are print time formulas and can't be used that way.

You could try this

if   \$ToNumber({HISTORY.Cashinamountinsplitdeposit}) > \$0   then
1
else
0

You could use a SUM summary and then test if it is > 0 and show

mlmcc
0

LVL 35

Assisted Solution

James0628 earned 400 total points
ID: 35221607
When you tried the SQL expression and said:

vvvvvvvvvvvvvvvvvvvv

if ({%10k} > 1)

then(\$ToNumber({HISTORY.Cashinamountinsplitdeposit}) > \$0))

^^^^^^^^^^^^^^^^^^^^

Where did you add that?  What was it supposed to do?

All it says is if there is more than one (> 1) record with 10,000 or more, test the current Cashinamountinsplitdeposit and return True if it's more than 0 or False if it's 0 or less.  I'm not sure what you're trying to do there.

And shouldn't that be {%10k} > 0, to see if there were _any_ records (not more than 1) with at least 10,000?

The "field is unknown" error seems a bit strange, assuming that you didn't mis-type the name of the SQL Expression or something.  Usually I'd expect something more specific, like "a number is expected here", instead of just "I don't know what that is".  That error message might have something to do with where you were trying to use that formula.

Just to clarify, is the field name HISTORY.Cashinamountinsplitdeposit or HISTORY.Cashin ?  Or are those perhaps two different fields?  You've used both names in different places.

Also, what type of field is it?  You're using ToNumber on it, so it's presumably not just a regular number.  I'm guessing that it's probably money or string.  If it happens to be string, that might affect how certain tests should be done, so I just thought I'd check.

For mlmcc's latest formula, that should be >= \$10000, not > \$0.  The idea is that the formula produces a 1 if the amount is ever 10,000 or more.  If the total of that formula is more than 0, there was at least one record with 10,000 or more, so you don't suppress the sections.

James
0

Author Comment

ID: 35232185
HISTORY.Cashinamountinsplitdeposit or HISTORY.Cashin  are the same field.
they are varchar.

Thanks for all your input. I will try all sugestions today.

One thing I noticed is that when I suppressed a section it was on the report as a blank line, so I would get 5 pages of blanks and then a good detail line, then 3 blank pages and another good detail line.
I'm wondering if the reason I get tons of blank line is because I'm using a group selection so if the group has no detials it still prints the group name folowed by a bunch of blank lines.

thanks again
0

LVL 35

Expert Comment

ID: 35239508
I don't know if you've solved the blank line issue that you mentioned, but if you're using group selection (not suppression), the groups that aren't selected won't be visible at all on the report.  Not the group header or footer, or the details in the group.

OTOH, if you're suppressing a section (you mentioned suppressing a section _and_ group selection), that section won't show up on the report as a blank line.  However, if, for example, you suppress the group header and footer sections, but not the detail section, you'll still get the detail lines.  Or, if you suppress the group header section and the detail section, but not the group footer section, you'll still get the group footers.  So, it sounds like you were suppressing some sections, but not everything that you needed to.

James
0

## Featured Post

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirementsā¦
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearlyā¦
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonstā¦
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (httpsā¦
###### Suggested Courses
Course of the Month6 days, 10 hours left to enroll