Crystal Reports & Record Selection

[mike55]

Hi all,

I am trying to use the crystal Selection Formula for Records. I am selecting all the data stored in a database table, and trying to use the following formula to select the records of interest. Here is the selection formula:

if {@Type} = "1" then
    if {@GID} = "-1" then
        {SentMessages.MessageDate} in(cdate({@DFrom})) to(cdate({@DTo})) And 
        {SentMessages.Org_ID} = {@Org} and {SentMessages.Msg_Type}="Email"
    else
        {SentMessages.MessageDate} in(cdate({@DFrom})) to(cdate({@DTo})) And 
        {SentMessages.Org_ID} = {@Org} and {SentMessages.Manager_ID}= tonumber({@GID})
    and {SentMessages.Msg_Type}="Email"
Else if {@Type} = "2" then
     if {@GID} = "-1" then
        {SentMessages.MessageDate} in(cdate({@DFrom})) to(cdate({@DTo})) And 
        {SentMessages.Org_ID} = {@Org} and {SentMessages.Msg_Type}="SMS" or {SentMessages.Msg_Type}="MO" or {SentMessages.Msg_Type}="WAP"
    else
        {SentMessages.MessageDate} in(cdate({@DFrom})) to(cdate({@DTo})) And 
        {SentMessages.Org_ID} = {@Org} and {SentMessages.Manager_ID}= tonumber({@GID})
    and {SentMessages.Msg_Type}="SMS" or {SentMessages.Msg_Type}="MO" or {SentMessages.Msg_Type}="WAP"
Else if {@Type} = "3" then
     if {@GID} = "-1" then
        {SentMessages.MessageDate} in(cdate({@DFrom})) to(cdate({@DTo})) And 
        {SentMessages.Org_ID} = {@Org} 
    else
        {SentMessages.MessageDate} in(cdate({@DFrom})) to(cdate({@DTo})) And 
        {SentMessages.Org_ID} = {@Org} and {SentMessages.Manager_ID}= tonumber({@GID})

Here is the sql statement that I am using:

SELECT "SentMessages"."Org_ID", "SentMessages"."Manager_ID", "SentMessages"."Msg_Sent", "SentMessages"."Msg_Type", "SentMessages"."Msg_Destination", "SentMessages"."MsgStatus", "SentMessages"."MessageDate", "Managers"."Manager_Name"
 FROM   "SureTxt"."dbo"."SentMessages" "SentMessages" INNER JOIN "SureTxt"."dbo"."Managers" "Managers" ON ("SentMessages"."Org_ID"="Managers"."Org_ID") AND ("SentMessages"."Manager_ID"="Managers"."Manager_ID")
 ORDER BY "SentMessages"."Manager_ID", "SentMessages"."Msg_Type", "SentMessages"."MessageDate" DESC

Here is the code that I am using to load the crystal report:

           oRpt.Load("c:\\\\Inetpub\\\\wwwroot\\\\Crystal1\\\Crystal1_Reports\\\\rptMessages.rpt")
        End If
        oRpt.DataDefinition.FormulaFields("DFrom").Text = "" + Request.QueryString("From") + ""
        oRpt.DataDefinition.FormulaFields("DTo").Text = "" + Request.QueryString("To") + ""
        oRpt.DataDefinition.FormulaFields("Org").Text = "" + Session("OrgID") + ""
        oRpt.DataDefinition.FormulaFields("GID").Text = "" + Request.QueryString("GID") + ""
        oRpt.DataDefinition.FormulaFields("Group").Text = "" + Request.QueryString("Group") + ""
        oRpt.DataDefinition.FormulaFields("Type").Text = "" + Request.QueryString("Type") + ""
        oRpt.DataDefinition.FormulaFields("User").Text = "" + Request.QueryString("by").Replace("", "`") + ""

        Try The following try catch statement is responsible for generating the report in a .pdf format.
            Dim crLogonInfo As CrystalDecisions.Shared.TableLogOnInfo
            crLogonInfo = oRpt.Database.Tables(0).LogOnInfo
            crLogonInfo.ConnectionInfo.ServerName = dbName
            crLogonInfo.ConnectionInfo.DatabaseName = "Suretxt"
            crLogonInfo.ConnectionInfo.UserID = dbUser
            crLogonInfo.ConnectionInfo.Password = dbPass
            oRpt.Database.Tables(0).ApplyLogOnInfo(crLogonInfo)

            Dim myExportOptions As CrystalDecisions.Shared.ExportOptions
            Dim myDiskFilesDestinationOptions As CrystalDecisions.Shared.DiskFileDestinationOptions
            Dim myExportFile As String

            myExportFile = "C:\temp\PDF " & Session.SessionID.ToString & ".pdf"
            myDiskFilesDestinationOptions = New CrystalDecisions.Shared.DiskFileDestinationOptions
            myDiskFilesDestinationOptions.DiskFileName = myExportFile
            myExportOptions = oRpt.ExportOptions

            With myExportOptions
                .DestinationOptions = myDiskFilesDestinationOptions
                .ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile
                .ExportFormatType = CrystalDecisions.Shared.ExportFormatType.PortableDocFormat
            End With

            oRpt.Export()

            Response.ClearContent()
            Response.ClearHeaders()
            Response.ContentType = "application/pdf"
            Response.WriteFile(myExportFile)
            Response.Flush()
            Response.Close()

            oRpt.Dispose()
            If Not oRpt Is Nothing Then
                oRpt = Nothing
            End If

Any suggestions welcome.

Mike55