Solved

Crystal Report Selection Criteria

Posted on 2011-03-25
12
405 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

12 Experts available now in Live!

Get 1:1 Help Now