Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Crystal Reports SQL criteria question

Crystal Reports SQL criteria question

Crystal Reports SQL criteria question

Hi! This is my 1st post and obviously I'm here because I need help. I do plan on contributing though.

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.

It looks something like this:

(Group #1)
xxx xxx xxxxx xxxxxxxxx
xxx xxx xxxxx xxxxxxxxx
xxx aaa xxxxx xxxxxxxxx

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)

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.

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"

"_SMDBA_"."Help Desk" Help_Desk, "_SMDBA_"."_SAPTRAN_" ASAPTRAN_, "_SMDBA_"."SAP Transports" SAP_Transports

Help_Desk."Problem #" = ASAPTRAN_."CASE" AND ASAPTRAN_."TRANSPNO" = SAP_Transports."Transport No" AND ASAPTRAN_."TRANDATE" = SAP_Transports."Transport Date"

Help_Desk."Problem #" DESC, ASAPTRAN_."CLIENTNO" ASC, SAP_Transports."Transport No" ASC, Help_Desk."Subject ID" ASC, Help_Desk."Client ID" ASC

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.

I'd appreciate any help. Thanks.
Replies continue below

Recommended for you

RE: Crystal Reports SQL criteria question

Hi Chris,

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$):

Dim Report As New PeopleDetail

Option Explicit
Private Sub Form_Load()
Dim rs As New ADODB.Recordset

If Len(ReportSQL$) = 0 Then
MsgBox "Recordset not selected yet. Use the Query form first"
Unload Me
End If

'Use this SQL statement to select the records to use
rs.Open ReportSQL$, _
"DSN=" & DSN$ & ";", adOpenKeyset
Report.Database.SetDataSource rs

'CRViewer1.Name = "Test Name"
CRViewer1.DisplayGroupTree = False
CRViewer1.ReportSource = Report

End Sub

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close