Solved

SELECT WHERE Formula in Crystal ReportsXI

Posted on 2011-03-09
27
406 Views
Last Modified: 2012-05-11
In Crystal Report XI,  I am trying to find an equivalent for a SQL command to put into a formula field.  THis is what I want to do:

SELECT {TABLE_B.FILE_NO}
      WHERE {TABLE_B.FILE_NO}<>(SELECT {TABLE_A.FILE_NO} WHERE {TABLE_A.INFO_CODE} = "BINGO")
0
Comment
Question by:PorterConsultingLLC
  • 12
  • 11
  • 4
27 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 35085208
Does that return a single value?
If so you could use a SQL expression

mlmcc
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35085335
Please explain in more detail what you are tyring to achieve as this is unclear,
0
 

Author Comment

by:PorterConsultingLLC
ID: 35085639
This should return a list of values.  I am working from a flat-file legacy database and am trying to create a list of files that do not have the value "BINGO" in any of their sub-table records.  Most files have multiple records, one or more of which may have the "BINGO" value in this field.  It is like an invoicing db, with one table for the invoice header (Inv_no, client name, address, etc.) and another table with all of the invoice lines on it, including code fields like "BINGO."  

The problem arises in that you cannot simply program it to disallow any records with the "BINGO" field -- you then get the invoice returned without BINGO on it.  I need any invoice with BINGO on it NOT to appear at all.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35085799
You can't use SQL in a formula field unless it is a SQL expression and that must return just 1 value not a list of values.

How do you intend to use the list?
You could use the SQL as the source for a subreport and show the list of value sthere.

mlmcc
0
 

Author Comment

by:PorterConsultingLLC
ID: 35085814
In the SQL statement above, I can create a subset listing ANY invoice with "BINGO" in it and SELECT only the Invoice file numbers NOT in that list.

Because my legacy database is flat-file and NOT SQL, i have to do all of this in Crystal, rather than creating a view in SQL and choosing from a limited subset in the report.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35085838
Do you want this as a list for parameters?
If so just create a COMMAND and include it in your report then base the parameter on the COMMAND

mlmcc
0
 

Author Comment

by:PorterConsultingLLC
ID: 35085872
I am trying to create a report listing information regarding  the clients who did not have "BINGO" in the specific field in the subtable.

I'm hoping that you know of a way to create a formula field or group that will allow me to do this.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35085899
You can use the SQL above assuming it works in a query analyzer and then use the SQL as the source for the report through a COMMAND.

mlmcc
0
 

Author Comment

by:PorterConsultingLLC
ID: 35085904
I'm sorry, I don't know what you mean by create a COMMAND.  Where, and in what language?
0
 

Author Comment

by:PorterConsultingLLC
ID: 35086520
Because I am working with a non-SQL-based database, I am not certain where I could create the command.  Is there somewhere in a Parameter field or a criteria field I could put this?  I was trying to create a formula field that would return only the file numbers of the records that had BINGO so that I could exclude those file numbers.  I cannot seem to get that to happen.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35087133
Can you create a report against the database?

How do you select the tables?

DO you see ADD COMMAND as an option?

mlmcc
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35087176
you should still be able to select tables, join them abd add filters using ta selection formula.
0
 

Author Comment

by:PorterConsultingLLC
ID: 35087198
how about if I concatenate all the values in each invoice's records then do a suppress if LIKE *BINGO*?  How could I go about concatenating all of those records, each invoice having only one concatenated field instead of a variable number of records?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 35087374
There is a way to do it without all that trouble but you need to have a working report first.  By that I mean you need to have a report that shows all the records then we can get the other.

Make sure the report has a group on
   {TABLE_B.FILE_NO}

You can then add a formula
If {TABLE_A.INFO_CODE} = "BINGO" then
   1
Else
    0

You can the use a Group Selection formula
Sum({@BingoFormula},   {TABLE_B.FILE_NO}) = 0

mlmcc
0
 

Author Comment

by:PorterConsultingLLC
ID: 35087442
I do have a working report, so I will try this.  THANK YOU!!
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35093265
Can you not just sjoin the 2 tables together in the report ?

Your original SQL
SELECT {TABLE_B.FILE_NO}
      WHERE {TABLE_B.FILE_NO}<>(SELECT {TABLE_A.FILE_NO} WHERE {TABLE_A.INFO_CODE} = "BINGO")

could also be represented as

SELECT TABLE_B.FILE_NO
FROM TABLE_B INNER JOIN
TABLE_A ON TABLE_B.FILE_NO = TABLE_A.FILE_NO
WHERE TABLE_A.INFO_CODE <> "BINGO"

If you can do this then this would be the more efficient way of producing teh correct recordset for teh report.

Could you uploada copy of your report on to here I would be intersted to see how the report is designed.
0
 

Author Comment

by:PorterConsultingLLC
ID: 35095939
Because the company I work for does some sensitive government work, I cannot upload a copy of the report.  The tools in Crystal are the only tools available to me for report creation and data manipulation.  
The tables have been joined in the report, but when the report is run, requiring that all Invoice records with "BINGO" in an invoice line be suppressed, only the invoice LINES with "BINGO are suppressed, NOT the Invoice itself, which is the desired end-result.  This happens because we are dealing with a flat-file database, not a related one.  
There is no place to use the SQL code as I creted it or as you created it.  I'm trying to find a way to put a Basic version of the Select Where statement into Crystal Reports' designing.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35096013
You can only use SQL with SQL databases.

Did you try my idea?

mlmcc
0
 

Author Comment

by:PorterConsultingLLC
ID: 35096547
mlmcc:
The problem with your solution is that there are other values in that field in most of the invoice line records, so that the sum would not be "0" or "1" when the calculating was done.  It would be something like 13 or 22.  This is the frustration of working with a flat-file.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35096713
Unless there is something else you haven't explained then this should work

If {TABLE_A.INFO_CODE} = "BINGO" Then 1 Else 0

If you are seeing values of 13 or 22 then this should mean that you have that number of invoice lines with a code of BINGO in the same invoice group. If the field holds any other value then the result will be zero, so as long as none of the invoice lines in a group contain the code of BINGO then the sum of this formula should = 0



0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35096786
If there are no "BINGO" records, then the sum of that formula will be 0 for that group and that group will be selected.  If there is 1 or more "BINGO" records the sum will not = 0 and thus that group won't be selected for the report.

It doesn't matter what values are iin the records for that field.  You are testing for BINGO records.  The group selection selects all the records for the group or none of the records for that group.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35097048
The SUM summary function only works on the specified field.  In this case the {@BingoFormula} formula.  The sum is also restricted to the group

The SUM summary does not add all the values in the record together.

mlmcc
0
 

Author Comment

by:PorterConsultingLLC
ID: 35097859
How do I tie in table A's file number with Table B's?  this is where I want the "Select Where" clause, but cannot get it.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35097973
How are you selecting the files for the report?

mlmcc
0
 

Author Comment

by:PorterConsultingLLC
ID: 35132221
The 3 tables needed have four fields in common if they are regarding the same file/invoice.  Using the Database Expert, I have chosen and linked using Left Outer Joins so that the Invoice Header is the Left-most table, with the tables holding the invoice lines and invoice remarks as equal next-tier tables.  The criteria (Select Expert) include the account number and the date field.  
I tried to group with a Select Distinct on the Invoice Number and exclude any Invoice Numbers that had a remark field of "BINGO", but what happened is that a distinct instance was created for each of the Invoice lines that did not include "BINGO" and the Invoice was included in the report.  

Do you think that we could use a Shared or Global Variable to get around this?  I.e.:  Create a Shared Variable that lists all Invoice Numbers that have a BINGO field and exclude it in the list of invoices?  If so, Can you help me with it?  I am not good at doing the variables.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35133820
no.  A shared variable is evaluated well after the selection of records is done.

mlmcc
0
 

Author Closing Comment

by:PorterConsultingLLC
ID: 35166131
I was blanking out on what was being explained, but when I was away from it a bit and went back I realized what mlmcc was trying to tell me and GOT IT!

Thanks for your patience.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

705 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

19 Experts available now in Live!

Get 1:1 Help Now