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