Urgent how to apply filter to Crystal report

Question

hello i am implementing Crystal report using c# in my program. I set up the report by using the wizard provided in VS.studio .net 2003. The report is displaying all record in the database i would like to filter to record programmatically  i get an syntax error telling me that the string i type is not valid . CrystalReport1 custReport = new CrystalReport1(); custReport.RecordSelectionFormula ={UserAccount.DateCreated} = 9/28/2004 12:00:00 AM ; crystalReportViewer1.ReportSource = custReport; what should i do to make it work????thanks

shane · Answer

There are essentially two models of using Crystal reports in .NET Push and Pull. Using the pull model the database driver handles all the work in the Crystal report and very little code is required. Using the push model requires you to connect to the database in code load the data into a dataset and pass the data the report. The report should be based on a strongly typed user defined dataset. The more flexible of the two is the push model. When designing the report you need to set the database location of the report to a project dataset. Here are some useful links: http://support.businessobjects.com/communityCS/TechnicalPapers/rtm_reportingoffadonetdatasets.pdf http://support.businessobjects.com/communityCS/TechnicalPapers/crnet_adonet.pdf http://support.businessobjects.com/communityCS/TechnicalPapers/rtm_designerandwinformsviewer.pdf I hope this helps...

keenweng2001 · Answer

I already try the code but it still does not work it still prompt me to ask for the database location and database name  below is my code do i do anyting wrong the data set i just drag and drop to the form do i need to add any table into the data set from the data set properties ?i am using Visual studio .net 2003 : string connString = Provider=Microsoft.Jet.OLEDB.4.0;+Data Source=+Environment.CurrentDirectory.ToString()+\NetEye.mdb ; string sql = select * from UserAccount; OleDbConnection conn= new OleDbConnection(connString); OleDbDataAdapter da= new OleDbDataAdapter(); da.SelectCommand = new OleDbCommand(sql conn); da.Fill(dataSet1); CrystalReport1 Report1 = new CrystalReport1(); Report1.SetDataSource (dataSet1); Report1.RecordSelectionFormula = @{UserAccount.DateCreated} = +Criteria+ @; crystalReportViewer1.ReportSource = Report1;

shane · Answer

I am not sure that you can set the connection string of a report through Report1.Database.Links. I think is is read only. You could try doing the following: string connString = Provider=Microsoft.Jet.OLEDB.4.0;+Data Source=+Environment.CurrentDirectory.ToString()+\NetEye.mdb ; string sql = select * from sometable; OleDbConnection conn= new OleDbConnection(connString); OleDbDataAdapter da= new OleDbDataAdapter(); da.SelectCommand = new OleDbCommand(sql conn); // the dataset should match the reports data schema exactly therefore //it need to be a user defined dataset based on the actual table(s) the report //will be querying MyDataSet ds = new MyDataSet; da.Fill(ds); CrystalReport1 Report1 = new CrystalReport1(); Report1.SetDataSource (dataSet); Report1.RecordSelectionFormula = @{UserAccount.DateCreated} = +Criteria+ @; crystalReportViewer1.ReportSource = Report1; You may want to alter this to suit your situation. I am assuming that CrystalReport1 is an embedded report. You could also put your select (DateCreated) criteria in the sql query and avoid having to pass a RecordSelectionFormula to the report. You could then check the DataSet table and if no rows are returned display a MessageBox instead of loading the report.

keenweng2001 · Answer

thanks for solving the previus problem but now i am facing another problem: When i created my Crystal report  i place my database in a folder location but when i change the location of this database  it will not able to locate the database . How can i set the location of my database programatically ??? Do i need to specify the table as well programatically???Below is my code i get an error when compiling that tellign me that the Database.Links is read only how do i solve this problem????? string connString = Provider=Microsoft.Jet.OLEDB.4.0;+Data Source=+Environment.CurrentDirectory.ToString()+\NetEye.mdb ; CrystalReport1 Report1 = new CrystalReport1(); Report1.Database.Links = connString; Report1.RecordSelectionFormula = @{UserAccount.DateCreated} = +Criteria+ @; crystalReportViewer1.ReportSource = Report1;

shane · Answer

When creating the report in the Crystal IDE your select formula would be: {UserAccount.DateCreated} = 9/28/2004 12:00:00 AM Therefore in code you have to recreate this INCLUDING the double quotes so your code would be as follows: string Criteria = 9/28/2004 12:00:00 AM; Report1.RecordSelectionFormula = @{UserAccount.DateCreated} = +Criteria+ @; or string Criteria = 9/28/2004 12:00:00 AM; string quotes = @; Report1.RecordSelectionFormula = {UserAccount.DateCreated} =  + quotes + Criteria+ quotes;

keenweng2001 · Answer

SOrry  is me again. Now i am storing my string into a string variable string Criteria = 9/28/2004 12:00:00 AM; Report1.RecordSelectionFormula = @{UserAccount.DateCreated} = +Criteria+; and i receive error.The remaining text does not appear to be a part of teh formula.. how should i write the command????thanks

keenweng2001 · Answer

thanks very much i have solve my problem the code is working and now i can reduce one limitation for my system.

shane · Answer

If {UserAccount.DateCreated} is a string field then your filter string in a Crystal Report would look like this {UserAccount.DateCreated} = 9/28/2004 12:00:00 AM As long as the format of {UserAccount.DateCreated} matches exactly the format of the string on the right. However to create this in code the above string would look slightly different as you will have to place quotes within quotes. custReport.RecordSelectionFormula = @{UserAccount.DateCreated} = 9/28/2004 12:00:00 AM You might have to fiddle with the number of quotes. You could also declare a variable to represent the quotes string quotes = @; custReport.RecordSelectionFormula = {UserAccount.DateCreated} =  + quotes + @9/28/2004 12:00:00 AM + quotes Hope this helps...

keenweng2001 · Answer

in my database the DateCreated is a string data field  so the DateTime cannot apply it return an error require string  how should i write to compare the string ???

shane · Answer

Try using this syntax in the string: {UserAccount.DateCreated} = DateTime (2004 09 28 12 00 00)

Urgent, how to apply filter to Crystal report