crystal report formula

[EyeTalion]

anyone know why I can't pass this parameter to my report?

oRpt = New CrystalReport1() ' create new instance of the report object
oRpt.RecordSelectionFormula = "begindate ='" & Format(tranPeriod, "MM/dd/yyyy") & "'"

[EyeTalion]

I have another question...is there a way to add to the sql query in Crystal? When I right click the DataBase Field, I can get to Show Query, but can't edit it...

[Lunatic3]

Either pass the formula to the CrystalReportViewer or pass it to the report object. In case you want to pass it to the report object it should be:

oRpt.[b]DataDefinition[/b].RecordSelectionFormula = "begindate ='" & Format(tranPeriod, "MM/dd/yyyy") & "'"

And if you want to pass it to the report viewer

crystalReportViewer1.SelectionFormula = ....

[EyeTalion]

I'm getting an error:

Error in formula <record_Selection>
"begindate='05/05/2003"
The remaining text does not appear to be part of the formula

I created a formula in Crystal and named it begindate and placed it on my report.

Dim oRpt As CrystalReport1 ' create new report object 
oRpt = New CrystalReport1() ' create new instance of the report object
oRpt.DataDefinition.RecordSelectionFormula = "begindate ='" & Format(tranPeriod, "MM/dd/yyyy") & "'"
CrystalReportViewer1.ReportSource = oRpt

I have this code in the page_load of the form...

[Lunatic3]

I think you have to use # around the date.

[EyeTalion]

the date time literal not understood

CrystalReportViewer1.SelectionFormula = "#begindate#"

[Lunatic3]

you have to build your formula string in a way that when it's parsed it reads like
begindate=#05/05/2003#

[EyeTalion]

attached

[Lunatic3]

It seems your formula puts an extra ' before the #02/05/2003#

[EyeTalion]

Crystal puts in the single quote at the front and back..I took out my single quotes, but still get the same error msg...I'll deal with it tomaro...thanks for all the input Luna...

oRpt.DataDefinition.RecordSelectionFormula = "begindate =#" & Format(tranPeriod, "MM/dd/yyyy") & "#"

[Lunatic3]

I think you need {} around the field names too in the formula text:

oRpt.DataDefinition.RecordSelectionFormula = "{begindate} =#" & Format(tranPeriod, "MM/dd/yyyy") & "#"

Try it, if still getting problem i think the # and ' issue should be considered again. I am too lazy to check it

[EyeTalion]

hey lun, I don't think thats the issue. I replaced the date with a string and I still get the same error...

oRpt.DataDefinition.RecordSelectionFormula = "begindate='" & work_unit & "'"

[Lunatic3]

and putting the field name in {} still produced error?
Try a working formula at design time and the copy paste the text to code.

[EyeTalion]

yup

[EyeTalion]

I'm going to create a Dataset instead and pass my paramaters in the DS first then run my report

[Lunatic3]

ok, I think I found the final soultion:
You have to mention the table name in the formula.
so that will be:

oRpt.DataDefinition.RecordSelectionFormula = "{mytablename.begindate}='" & work_unit & "'"

[EyeTalion]

hey Lun, I decided to create a Dataset and use that to get the query I need for the report...I'm using the code below, and I'm not getting any errors, but the output on the report is the same as before, it's giving me all the data in the DB. I think it's using the query I created when I made my report...do you know how Ic an kill that query so it only uses the Dataset to fill the report?

Dim cmdView As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("select purchase_date,credit_card_no,vendor_name,transaction_amt,sales_tax_amt,status_ind from pcms.pcms_pcard_transaction_header where purchase_date between TO_DATE('" & tranPeriod & "','mm/dd/yyyy') and TO_DATE('" & tranPeriodEnd & "','mm/dd/yyyy') ", objCon)
        ds = New DataSet()
        cmdView.Fill(ds, "cards")
 
        Dim oRpt As New CrystalReport1() ' create new report object 
        oRpt.SetDataSource(ds)
        CrystalReportViewer1.ReportSource = oRpt

[Lunatic3]

1- Give the solution i gave you a try. It should work.
2- If you decide to base your report on dataset instead of Database, you should add the dataset at desing time to your project. Then at desing time you should use the dataset file,lets say its mydataset.XSD, as the datasourse of your report and drag the fields from there to your report.

[EyeTalion]

with your solution I wasn't getting the value from a table, it was coming from my app. I wanted to place the transaction period on the top of my report, so I was grabbing that value from a combobox on my form and then calling it by the formula name on the top of the report.

[Lunatic3]

???

In your selection formula you should provide both the table name and filed name. So if the table in your database is called mytable then you should use {mytablename.begindate}=yourdesiredvalue
in your formula. It's quite clear.

[EyeTalion]

this is how I was doing it in VB6...it worked fine.

CrystalReport1.Formulas(0) = "begindate ='" & Format(mdate, "mm/dd/yyyy") & "'"
      CrystalReport1.Formulas(1) = "enddate = '" & Format(mdate2, "mm/dd/yyyy") & "'"

[Lunatic3]

oRpt.DataDefinition.RecordSelectionFormula = "[b]{tablenamehere.[/b]begindate[b]}[/b] ='" & Format(tranPeriod, "MM/dd/yyyy") & "'"

This works fine in you should take care of {} and tablename before the field name.

help://MS.VSCC/MS.MSDNVS/crystlmn/html/crtskselectionformulasruntimecustomization.htm