I am needing to modify report parameters for a standard epicor report to include a date parameter to include between two selected dates. I have duplicated the standard report and created a new report style and data definition for the duplicated report. I just need guidance on modifying the report window when running the report to include new date parameters.
Which built-in report are you trying to modify?
Credit Card Transaction
I need to have the parameters be a date in between two dates instead of the default greater than date and/or dynamic selection parameters.
Is it something you need to do regularly? Or only rarely?
I ask because if its rarely, there may be easier ways to get the data you want.
Making a BAQ report from scratch would give you the most control. The best you’d be able to do by adding a field to the form would be to pass that field’s value on to the report generator, and then filter the data inside the report. And even that is going to be tricky.
Another way (kind of a hack), would be to make a BAQ the gets the CC trans, with columns for the CustID and TranDate. And make a Quick Search with two Criteria for the dates.
Then you would just use the Customer filter on the CC Report to select all customers that had CC transactions via the Quick Search.
The report will be ran weekly/daily. But the needed feature of selecting single dates or multiple dates is needed. This baq is a default epicor baq so there is no way to view how the original calculations were done. That is why I have duplicated the report and created a new data definition and report style.
Try the following:
- Customize the Credit Card Report form, adding a date control. Bind it to
CallContextBPMData.Date01 - Nothing need to be done in the RDD
- In the copy of your CC Report RDL, edit the Query expression for dataset CreditTran. Add the highlighted parts below:
The RDD will use the native date from the form to limit the lower (oldest date), and the additions to the Query expression limit the upper end date.
I haven’t tried the above - as we don’t process credit cards. But a similar approach works on another built-in form and report.
edit
I re-uploaded the pict after fixing a missing space : (...+ " T2 )
I appreciate the help, so I’ve added the parameter code and updated the rdd file, uploaded the new report into the report style, and now I’m customizing the report form and binding the new parameter such as below. Is that correct? New to Epicor so appreciate the help. Running the report is resulting in a blank report.
That control.you added to the customization needs to be a date editor, not a time editor.
Other than that it looks good so far.
One note, you will get a blank report if you don’t enter a date in that field when it’s run.
I’ve adjusted the control to a date editor, however, I am getting a blank report.
Have you tried other dates in the custom field (like far in the future, and far in the past)? Same thing if it is blank?
Can you post the query expression from the RDL?
Yes, still getting blanks. Below is the query expression.
="SELECT
T1.Ordernum,
T1.AuthCode,
T1.CardNumber,
T1.PNRef,
T1.ResponseMsg,
T1.Result,
T1.TranDate,
T1.TranTotal,
T1.Calc_CardTypeDesc,
T1.Calc_CustomerID,
T1.Calc_CustomerName,
T1.Calc_Expiration,
T1.Calc_UnMaskedCard,
T1.ReferencePNRef,
T1.TranType,
T1.TranTypeDesc,
T1.Calc_EffAmount,
T1.CurrencyCode,
T1.DocTranTotal,
T1.Calc_DocEffAmount,
T2.Date01
FROM CreditTran_" + Parameters!TableGuid.Value + " T1"
- " JOIN CallContextBPMData_" + Parameters!TableGuid.Value + " T2
ON T1.TranDate <= T2.Date01"
The FROM part of the expression should be:
FROM CreditTran_" + Parameters!TableGuid.Value + " T1"
+ " JOIN CallContextBPMData_" + Parameters!TableGuid.Value + " T2
ON T1.TranDate <= T2.Date01"
I think you have an * where a + should be
Try changing " JOIN CallContextBPMData_" to " INNER JOIN CallContextBPMData_".
If you get the same thing (no results), then try
" LEFT JOIN CallContextBPMData_"
That should make it so that the BPM data doesn’t limit the data used by the RDL. The report should look like it normally would (just using the Trans after Date parameter). If that renders okay, add field for T2.Date01 to the report body, to see what appears when it runs.
Looks like the Left Join was the key! Your help on this is very much appreciated!
The LEFT JOIN won’t limit the records based on your extra date parameter.
Show the Date01 field on the report to make sure your value is being passed to the report.
It looks like the Date01 value is not being passed to the report as it is blank after adding a column for the Date01 in the report to view.
Make sure that after you added the new control for the Date Editor, that you set the binding.
When I bind Date01 to the date filter the report does not run/return results.