Add extra param fields to Accpac 56 canned Crystal Report 1

[vbdbcoder]

I need to add extra parameter fields to Accpac 56 canned Crystal Report, e.g. OECONF01.rpt. The modified report is pulled and launched from the Accpac screen and the crystal report param screen prompts afterward to allow me to plug in the value. It just does take the value I entered and filter the records. I display the value I entered and display the value from OEORDH. They are not the same, but the record still displays. I have added the VBA code in crystal report to filter the Param value = OEORDH filed vale.

Has anyone tried this kind of approach to modify the canned report by adding more param fields?

Thanks

 

[Jay Converse]

You have to add your own section to OERPT.INI.

 

[vbdbcoder]

Any examples, please?

 

[Jay Converse]

I have a report named "APAGWSUM.RPT". I add this to APRPT.INI:


' For AP Aging reports
[APAGE2]
heading=
crystal=apage2
orientation=portrait
paper size=1
optparams=12
2=PATH COMPANY
3=EXT FILEEXT
4=FISCYEAR STRING
5=FISCPERD STRING
6=ENTFROM STRING
7=ENTTO STRING
8=VENDFROM STRING
9=VENDTO STRING
10=GRPFROM STRING
11=GRPTO STRING
12=DETAILS STRING
13=ENDDATE STRING


And this is how I call it:

Set rptObj = ReportSelect("APAGE2[APAGWSUM.RPT]", " ", " ")

rptObj.SetParam "FISCYEAR", Me.pdPick.Year
rptObj.SetParam "FISCPERD", Format(Me.pdPick.period, "00")
rptObj.SetParam "ENTFROM", (Me.fldEntFrom.Value)
rptObj.SetParam "ENTTO", (Me.fldEntTo.Value)
rptObj.SetParam "VENDFROM", (Me.fldVendFrom.Value)
rptObj.SetParam "VENDTO", (Me.fldVendTo.Value)
rptObj.SetParam "GRPFROM", (Me.fldGroupFrom.Value)
rptObj.SetParam "GRPTO", (Me.fldGroupTo.Value)
rptObj.SetParam "DETAILS", IIf(Me.btnDetail, "YES", "NO")

If Me.btnDueDate Then
rptObj.SetParam "AGEBY", "DUEDATE"
Else
rptObj.SetParam "AGEBY", "INVOICEDATE"
End If

If Me.btnPeriod Then
rptObj.SetParam "DATEPER", "PERIOD"
rptObj.SetParam "ENDDATE", Format(dLastDate, "yyyymmdd")
Else
rptObj.SetParam "DATEPER", "DATE"
rptObj.SetParam "ENDDATE", Format(Me.dtDate, "yyyymmdd")
End If

rptObj.Destination = PD_PREVIEW
rptObj.PrintReport

 

[vbdbcoder]

It seems that you wrote your own custom VBA to launch the custom report. That is a different solution than what I have in mind.

How about only adding a couple paramenters to the current OE1200 (Order Confirmation)?

 

[Jay Converse]

Then your original question isn't clear. You can add parameters to Crystal reports like that, I do it all the time. The user just gets two prompting screens.

So explain again what your second parameter prompt is supposed to do?

 

[vbdbcoder]

Thanks for your prompt feedback...

Objective:

Add 2 extra parameter fields(TERRITORY, EXPSHIPDATE) to Accpac canned crystal report, OECONF01.rpt. When Order Confirmation is printed either from OE screen or OE form screen, the 2 added parameter fields' values should be in the record selection formula to filter the OE record. TERRITORY = OEORDH.TERRITOTRY and EXPSHIPDATE = OEORDH.EXPDATE. The intent is to print only a order confirmation of the territory and an expected ship date entered by user.

The customer has been familiar with OECONF01.rpt format.

I could get the prompt of the extra parameter screens and could display their values, but the record selection part did not seem to work. It prints without taking the values from my parameters.

My Record Selection Formula code. Please look for "20110125", which is the addition\mod to the existing code.

//Selection Criteria Query String now build in UI and passed into @SELECTION_CRITERIA

if {?SWDELMETHOD} = "1" then //Selection partly done in UI - sortto and sortfrom is the same
(
{OEORDH.ORDNUMBER} = {?SORTFROM} and
(
// order is not complete
({OEORDH.COMPLETE} = 1 or {OEORDH.COMPLETE} = 2) or
// order header is complete but details are not
(({OEORDH.COMPLETE} = 3 or {OEORDH.COMPLETE} = 4 or {OEORDH.COMPLETE} = 5) and {OEORDD.COMPLETE} = 0)
)
//Added extra parameter fields 20110125
and
(
Trim ({OEORDH.TERRITORY}) = Trim({?TERRITORY})
)
//End adding
)
else // No selection in UI
(
// order number not null
NOT(ISNULL({OEORDH.ORDNUMBER})) and

// order number range
(
{OEORDH.ORDNUMBER} >= {?SORTFROM} and
{OEORDH.ORDNUMBER} <= {?SORTTO}
) and

//Added extra parameter fields 20110125
(
Trim ({OEORDH.TERRITORY}) = Trim({?TERRITORY})
)
and
//End Adding

// order not on hold
// KRISA90 #141262 Checked for printing of ONHOLD orders
({?SWPRINTONHOLD} = "1" OR {OEORDH.ONHOLD} = 0) and

// order not a quote
{OEORDH.TYPE} <> 4 and

// order is not complete
(
({OEORDH.COMPLETE} = 1 or {OEORDH.COMPLETE} = 2) or
// order header is complete but details are not
(({OEORDH.COMPLETE} = 3 or {OEORDH.COMPLETE} = 4 or {OEORDH.COMPLETE} = 5) and {OEORDD.COMPLETE} = 0)
) and

(
// print previously printed orders
{?PRINTED} = "1" or
// print previously unprinted orders or Internet orders or Eletronic Commerce orders
( {?PRINTED}="0" AND ( {OEORDH.PRINTSTAT} =1 OR {OEORDH.PRINTSTAT} =0 OR {OEORDH.PRINTSTAT} =-1 ) )
) and
// if delivery method is 3, always select customer
// else select customer that have delivery method of mail
(
//Modify criteria 20110125
//{?SWDELMETHOD} = "3"

({?SWDELMETHOD} = "3" and (Trim ({OEORDH.TERRITORY}) = Trim({?TERRITORY}))) or
(
{?SWDELMETHOD} = "2"
and
{ARCUS.DELMETHOD} = 0
)
)
)

 

[vbdbcoder]

The code above has only 1 parameter field, TERRITORY. I am sure you get the idea and direction I am trying to get to.

 

[Jay Converse]

//Selection Criteria Query String now build in UI and passed into @SELECTION_CRITERIA

You need your own UI, or you have to put that selection in a Group selection formula, because the main formula is overridden.

 

[vbdbcoder]

Works like a charm. Thanks!!!