Parameter between two dates

[cathiec]

i have the following formula field called date:

{ma_asset.TRANS_DATE} > {?FromDate} and {ma_asset.TRANS_DATE} < {?ToDate}

 

date is stored in the db as an integer in the format 20040309. i have two parameter fields called ?FromDate and ?ToDate. They are of type number. i use a view in my asp.net form and then use the parameters to return the rows between the dates entered. here is my code:

 

private void Button1_Click(object sender, System.EventArgs e)

{

this.sqlDataAdapter1.Fill(this.dataSet31);

this.DataGrid1.DataBind();

ExportReport();

}

 

private void ExportReport()

{

 

 

 

rptASSETSBYDATE crReportDocument = new rptASSETSBYDATE();

//this.sqlDataAdapter1.Fill(this.dstAUTHSHEETS1);

crReportDocument.SetDataSource(this.dataSet31);

 

ParameterValues myparameterValues = new ParameterValues();

ParameterDiscreteValue myparamDiscreteValue = new ParameterDiscreteValue();

myparamDiscreteValue.Value = TextBox1.Text;

myparameterValues.Add(myparamDiscreteValue);

crReportDocument.DataDefinition.ParameterFields["FromDate"].ApplyCurrentValues(myparameterValues);

 

ParameterValues myparameterValues2 = new ParameterValues();

ParameterDiscreteValue myparamDiscreteValue2 = new ParameterDiscreteValue();

myparamDiscreteValue2.Value = TextBox2.Text;

myparameterValues2.Add(myparamDiscreteValue2);

crReportDocument.DataDefinition.ParameterFields["ToDate"].ApplyCurrentValues(myparameterValues2);

 

 

string ExportPath;

ExportPath = Request.PhysicalApplicationPath;

 

crDiskFileDestinationOptions = new DiskFileDestinationOptions();

crExportOptions = crReportDocument.ExportOptions;

 

string fName = Session.SessionID.ToString() + ".pdf";

 

crDiskFileDestinationOptions.DiskFileName = ExportPath + fName;

 

crExportOptions.DestinationOptions = crDiskFileDestinationOptions;

crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;

crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;

 

crReportDocument.Export();

Response.ClearContent();

Response.ClearHeaders();

Response.ContentType = "application/pdf";

Response.WriteFile(fName);

Response.Flush();

Response.Close();

}

 

 

the report displays all rows regardless of dates entered. any suggestions please?

[Joe Mamma]

i have the following formula field called date:

{ma_asset.TRANS_DATE} > {?FromDate} and {ma_asset.TRANS_DATE} < {?ToDate}

 

date is stored in the db as an integer in the format 20040309.

Do not take this personally, I only say this to help you and I do you a disservice not to tell you: I would not hire the database designer who defined this schema.

Dates are dates, they are not numbers. They should NEVER be treated as such.

There is no reason to do this. Doing so builds in huge development and maintenance costs.

 

 

Consider the following scenario -

Client: 'I really like the app, but I want to use a calender control to enter dates and not type them in a text box'

 

 

That being said,

 

I am not familiar with the Crystal Reports component so I might be missing something but I dont see where the filtering is taking place on your formula field. After applying the parameters Where do you say only give me the data that meets the criteria of formula field = true?

 

Joe Mamma

[Nerseus]

I'd try this first (haven't worked with Crystal in awhile):

Run the report in the Crystal Designer (if you can). It should allow for entering the params manually. Try entering "good" values for FromDate and ToDate - values that SHOULD return a limited set of records. Let's assume that works... if it doesn't then something is wrong in the report.

 

Next would be to verify that SQL Server is getting the right params, if Crystal passes those values on to SQL Server. If Crystal is only filtering locally then I'm not sure how to debug it. Assuming SQL Server is being filtered (in a WHERE clause somehow), then you can turn on tracing in SQL Profiler and check the SQL being sent to the DB to see what the WHERE clause shows.

 

-ner

[cathiec]

sorry, i forgot to mention that i have a formula field called @Date

 

{ma_asset.TRANS_DATE} > {?FromDate} and {ma_asset.TRANS_DATE} < {?ToDate}

 

i think that this is where my problem lies but i just cant get the syntax right to say: select all rows from the view where the dates are between the ?FromDate and the ?ToDate