Solved

Crystal Report Selection Criteria

Posted on 2011-03-25
12
406 Views
Last Modified: 2012-05-11
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
Comment
Question by:lancerxe
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 200 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

by:Kurt Reinhardt
Kurt Reinhardt earned 200 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)

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
 

Author Comment

by:lancerxe
ID: 35216083
Thanks guys

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

0
 
LVL 100

Expert Comment

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

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35216249
Kurt could a COMMAND do this?

mlmcc
0
 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 200 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:lancerxe
ID: 35218335
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
 

Author Comment

by:lancerxe
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 100

Assisted Solution

by:mlmcc
mlmcc earned 200 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 34

Assisted Solution

by:James0628
James0628 earned 100 total points
ID: 35221607
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
 

Author Comment

by:lancerxe
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 34

Expert Comment

by:James0628
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now