Where to enter "WHERE" clause in a Crystal Report?

[sjh]

Hi, I am using Crystal Reports (version 9) for Visual Studio.NET.

By using the Expert Wizard, I added a crystal report to a web application project. This is a really dumb question, but I couldn't find where to enter the WHERE clause in the SQL for the report. I found the <Database - Show SQL Query> menu, but it's read-only. I would be very grateful, if anyone can help me out!

And another dumb question: is there anyway to preview the report in the design view?

Thanks in advance!

 

[RTomes]

I have filtered the data before assigning it to a datasource (dataset or XML file).

From looking at the code below is looks like I have done some filtering on the report itself. (Did this last year)

There may be other ways but filtering it first was the easiest approach.

Here is some code I have used a few times with success:

Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.PreRender
				MakeReport("rptServices_Summary.rpt")
			End Sub

Private Sub MakeReport(ByVal rptFile As String)
		'create the report document
		Dim d As DateTime = Now
		Dim dropfile As String = "Services-Summary-" & d.Month & "-" & d.Day & "-" & d.Year & "--" & d.Hour & "-" & d.Minute & "-" & d.Millisecond
		Dim doc As ReportDocument = New ReportDocument
		Dim filename As String = Server.MapPath("~/rpt/" & rptFile)
		doc.Load(filename)

		Dim ds As DataSet = BindData()

		doc.SetDataSource(ds)
		'{RATE.ID_PROJ}=''
		doc.SetParameterValue("custname", customername)
		doc.SetParameterValue("startdate", startdate)
		doc.SetParameterValue("enddate", enddate)
		'doc.RecordSelectionFormula = "{?custname}='" & customername & "'"

		Dim exportOpts As ExportOptions = doc.ExportOptions
		Dim diskOpts As DiskFileDestinationOptions

		'PDF
		exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat
		exportOpts.ExportDestinationType = ExportDestinationType.DiskFile
		exportOpts.DestinationOptions = New DiskFileDestinationOptions

		'Set the disk file options.
		diskOpts = New DiskFileDestinationOptions
		CType(doc.ExportOptions.DestinationOptions, DiskFileDestinationOptions).DiskFileName = Server.MapPath("~/rpt_drop/" & dropfile & ".pdf")

		doc.Export()

		'Excel
		exportOpts.ExportFormatType = ExportFormatType.Excel
		exportOpts.ExportDestinationType = ExportDestinationType.DiskFile
		exportOpts.DestinationOptions = New DiskFileDestinationOptions

		'Set the disk file options.
		diskOpts = New DiskFileDestinationOptions
		CType(doc.ExportOptions.DestinationOptions, DiskFileDestinationOptions).DiskFileName = Server.MapPath("~/rpt_drop/" & dropfile & ".xls")

		doc.Export()

		'Word
		exportOpts.ExportFormatType = ExportFormatType.WordForWindows
		exportOpts.ExportDestinationType = ExportDestinationType.DiskFile
		exportOpts.DestinationOptions = New DiskFileDestinationOptions

		'Set the disk file options.
		diskOpts = New DiskFileDestinationOptions
		CType(doc.ExportOptions.DestinationOptions, DiskFileDestinationOptions).DiskFileName = Server.MapPath("~/rpt_drop/" & dropfile & ".doc")

		doc.Export()



		'Crystal
		exportOpts.ExportFormatType = ExportFormatType.CrystalReport
		exportOpts.ExportDestinationType = ExportDestinationType.DiskFile
		exportOpts.DestinationOptions = New DiskFileDestinationOptions

		'Set the disk file options.
		diskOpts = New DiskFileDestinationOptions
		CType(doc.ExportOptions.DestinationOptions, DiskFileDestinationOptions).DiskFileName = Server.MapPath("~/rpt_drop/" & dropfile & ".rpt")

		doc.Export()

		Response.Write("<a href='rpt_drop/" & dropfile & ".pdf' target='_blank'>PDF Format</a><br>")
		Response.Write("<a href='rpt_drop/" & dropfile & ".xls' target='_blank'>Excel Format</a><br>")
		Response.Write("<a href='rpt_drop/" & dropfile & ".doc' target='_blank'>Word Format</a><br>")
		Response.Write("<a href='rpt_drop/" & dropfile & ".rpt' target='_blank'>Crystal Format</a><br><br><br>")
		Response.Write("<div align=center><a href='#' onclick='window.close()'>Close Window</a><br>")
	End Sub

Private Sub MakeReport(ByVal rptFile As String)
		'create the report document
		Dim d As DateTime = Now
		Dim dropfile As String = "Services-Summary-" & d.Month & "-" & d.Day & "-" & d.Year & "--" & d.Hour & "-" & d.Minute & "-" & d.Millisecond
		Dim doc As ReportDocument = New ReportDocument
		Dim filename As String = Server.MapPath("~/rpt/" & rptFile)
		doc.Load(filename)

		Dim ds As DataSet = BindData()

		doc.SetDataSource(ds)
		'{RATE.ID_PROJ}=''
		doc.SetParameterValue("custname", customername)
		doc.SetParameterValue("startdate", startdate)
		doc.SetParameterValue("enddate", enddate)
		'doc.RecordSelectionFormula = "{?custname}='" & customername & "'"

		Dim exportOpts As ExportOptions = doc.ExportOptions
		Dim diskOpts As DiskFileDestinationOptions

		'PDF
		exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat
		exportOpts.ExportDestinationType = ExportDestinationType.DiskFile
		exportOpts.DestinationOptions = New DiskFileDestinationOptions

		'Set the disk file options.
		diskOpts = New DiskFileDestinationOptions
		CType(doc.ExportOptions.DestinationOptions, DiskFileDestinationOptions).DiskFileName = Server.MapPath("~/rpt_drop/" & dropfile & ".pdf")

		doc.Export()

		'Excel
		exportOpts.ExportFormatType = ExportFormatType.Excel
		exportOpts.ExportDestinationType = ExportDestinationType.DiskFile
		exportOpts.DestinationOptions = New DiskFileDestinationOptions

		'Set the disk file options.
		diskOpts = New DiskFileDestinationOptions
		CType(doc.ExportOptions.DestinationOptions, DiskFileDestinationOptions).DiskFileName = Server.MapPath("~/rpt_drop/" & dropfile & ".xls")

		doc.Export()

		'Word
		exportOpts.ExportFormatType = ExportFormatType.WordForWindows
		exportOpts.ExportDestinationType = ExportDestinationType.DiskFile
		exportOpts.DestinationOptions = New DiskFileDestinationOptions

		'Set the disk file options.
		diskOpts = New DiskFileDestinationOptions
		CType(doc.ExportOptions.DestinationOptions, DiskFileDestinationOptions).DiskFileName = Server.MapPath("~/rpt_drop/" & dropfile & ".doc")

		doc.Export()



		'Crystal
		exportOpts.ExportFormatType = ExportFormatType.CrystalReport
		exportOpts.ExportDestinationType = ExportDestinationType.DiskFile
		exportOpts.DestinationOptions = New DiskFileDestinationOptions

		'Set the disk file options.
		diskOpts = New DiskFileDestinationOptions
		CType(doc.ExportOptions.DestinationOptions, DiskFileDestinationOptions).DiskFileName = Server.MapPath("~/rpt_drop/" & dropfile & ".rpt")

		doc.Export()

		Response.Write("<a href='rpt_drop/" & dropfile & ".pdf' target='_blank'>PDF Format</a><br>")
		Response.Write("<a href='rpt_drop/" & dropfile & ".xls' target='_blank'>Excel Format</a><br>")
		Response.Write("<a href='rpt_drop/" & dropfile & ".doc' target='_blank'>Word Format</a><br>")
		Response.Write("<a href='rpt_drop/" & dropfile & ".rpt' target='_blank'>Crystal Format</a><br><br><br>")
		Response.Write("<div align=center><a href='#' onclick='window.close()'>Close Window</a><br>")
	End Sub