Crystal Reports SQL criteria question

[ChrisMcKenzie]

Hi! This is my 1st post and obviously I'm here because I need help. I do plan on contributing though.<br>
<br>
On to my question. I am using Crystal Reports 6 to write a simple report containing 1 group. The details section within this group contains a number of fields. My criteria I wish to impose within SQL before the report is run is to filter each group that contains a certain value within one of these fields. <br>
<br>
It looks something like this:<br>
<br>
(Group #1)<br>
xxx xxx xxxxx xxxxxxxxx<br>
xxx xxx xxxxx xxxxxxxxx<br>
xxx aaa xxxxx xxxxxxxxx<br>
<br>
The (aaa)s would be the key, once it is identified, I need the group either suppressed (within CR, but I know it is impossible to surpress an entire group) or filtered out through the SQL query.(my only solution)<br>
<br>
Here is a clip from my SQL query, I've read through every SQL and Crystal Reports book I could get my hands on. SAP_Transports."SAPServer" being the field that the value is contained in.<br>
<br>
SELECT<br>
Help_Desk."Problem #", Help_Desk."Client ID", Help_Desk."Subject ID", ASAPTRAN_."CLIENTNO", SAP_Transports."Transport No", SAP_Transports."Transport Date", SAP_Transports."Pstatus", AP_Transports."SAPServer", SAP_Transports."CurrStatus"<br>
<br>
FROM<br>
"_SMDBA_"."Help Desk" Help_Desk, "_SMDBA_"."_SAPTRAN_" ASAPTRAN_, "_SMDBA_"."SAP Transports" SAP_Transports<br>
<br>
WHERE<br>
Help_Desk."Problem #" = ASAPTRAN_."CASE" AND ASAPTRAN_."TRANSPNO" = SAP_Transports."Transport No" AND ASAPTRAN_."TRANDATE" = SAP_Transports."Transport Date"<br>
<br>
ORDER BY<br>
Help_Desk."Problem #" DESC, ASAPTRAN_."CLIENTNO" ASC, SAP_Transports."Transport No" ASC, Help_Desk."Subject ID" ASC, Help_Desk."Client ID" ASC<br>
<br>
I know I've got to set an Alias and remove it from there, then compare the two(Union), but I can figure it out on my own with the referneces and lack of help my office is providing me with.<br>
<br>
I'd appreciate any help. Thanks.

 

[SirPaladin]

Hi Chris,<br>
<br>
If I'm understanding your question, you just need to filter before calling Crystal. This is how I did it(assumes you have your SQL string in ReportSQL$):<br>
<br>
Dim Report As New PeopleDetail<br>
<br>
Option Explicit<br>
______________________________________________<br>
Private Sub Form_Load()<br>
Dim rs As New ADODB.Recordset<br>
<br>
If Len(ReportSQL$) = 0 Then<br>
MsgBox "Recordset not selected yet. Use the Query form first"<br>
Unload Me<br>
End If<br>
<br>
'Use this SQL statement to select the records to use<br>
rs.Open ReportSQL$, _<br>
"DSN=" & DSN$ & ";", adOpenKeyset<br>
Report.Database.SetDataSource rs<br>
<br>
'CRViewer1.Name = "Test Name"<br>
CRViewer1.DisplayGroupTree = False<br>
CRViewer1.ReportSource = Report<br>
CRViewer1.ViewReport<br>
<br>
End Sub<br>