Go Premium for a chance to win a PS4. Enter to Win

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

Crystal Report Selection Criteria

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
lancerxe
Asked:
lancerxe
  • 4
  • 4
  • 2
  • +1
5 Solutions
 
mlmccCommented:
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
 
Kurt ReinhardtCommented:
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)

Open in new window


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'

Open in new window


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

~Kurt
0
 
lancerxeAuthor Commented:
Thanks guys

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

0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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

mlmcc
0
 
mlmccCommented:
Kurt could a COMMAND do this?

mlmcc
0
 
Kurt ReinhardtCommented:
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
 
lancerxeAuthor Commented:
Created a SQL Expression called %10k
(
SELECT
  COUNT(AccountNumber)
FROM
  HISTORY
WHERE
  cast (CashInAmountInSplitDeposit as money) > 9999.99
)



Added this code
if ({%10k} > 1)

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

Got an error message:
%10k field is unknown
0
 
lancerxeAuthor Commented:
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
 
mlmccCommented:
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
 
James0628Commented:
When you tried the SQL expression and said:

 vvvvvvvvvvvvvvvvvvvv

Added this code
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
 
lancerxeAuthor Commented:
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
 
James0628Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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