How to add an additional Filter Option value and a Sort By option to the OpenPOReport system report

I’m needing to add an additional Filter Option (new Tab) value and a Sort By (added to the combobox list) option to the OpenPOReport system report. I understand this is a system report that comes out of the box with Epicor.

The value to be added to both the Filter and Sort By is the Buyer value. These look to be added to the OpenPOReportForm where different criteria can be selected that controls the report output. ….if it can be done.

I do know enough about adding a new Buyer Tab to the criteria form. The real issue would be to understand how to pass that value to the system report.

I did see one posting that said to ‘duplicate’ the existing report and add what I need to the customization. If that is the way to go, then can the system report be duplicated? If so, how? Then there’s the issue of connecting and passing values to the report.

I hope this doesn’t mean I have to start from scratch … I see there are a couple posts here that are similar to what I’m asking with no responses. Does this mean it’s not possible?

Do you need the ability to select multiple buyers? If not, you could add a retriever combo to the form to select the buyer. Then bind that to callcontext variable, and pass that to the report. In the report’s RDL, add a filter to hide records of buyers that don’t match the callcontext value.

Hello, Mr. Krusen …

This is actually a two part project; 1) Allow the report to be Grouped by Buyers, and 2) Allow for the selection a Buyer.

So, are you indicating this is possible? The reason I state it that way is because this is a system (old Crystal) report and the BAQ used for this isn’t available. I can see some things within the RDD for this report, but not a whole lot.

I’m very interested in the binding you’ve mentioned and how to hide records.

Thank you for reading my post and responding.

When you say “old crystal report”, you just mean it’s the one Epicor supplies? (which is actually a SSRS report).

Or is it really a crystal report? To be sure, go to Report Style Maint and check the Report Type for the style you’re using.

image

You should copy the report style (select the style to duplicate, then Actions -> Copy Report Style on the menubar). Then you can make changes to the newly created RDL, without affecting the original.

In the new RDL, add a grouping on the Buyer. And it will always group by the Buyers regardless of which sort style is selected. If you want to make that grouping optional, you could add a checkbox to the form, and pass its value via a callcontext variable.

Looking at the Report Style Maintenance, I do see the Standard style is an SSRS, however, there are 3 additional style definitions that are of Report Type Crystal. This is because this system I’m working on was recently converted from E9 to E10 and they were using Crystal Reports in E9. These were not converted to SSRS before converting to E10. So … I’ve been told to disregard the Crystal reports and only focus on the Standard report for right now.

Copying the report style sounds easy enough, but I would like to see the Sort By field include a Buyer option as well as having the ability to Filter By Buyer as well.

I’d like to have a better understanding of what you mean my “pass its value via callcontext variable.” Obviously, I understand that a value is being passed to the report as a criteria. But, by copying the Report Style, does that include the OpenPOReportForm where these options can be selected?

We need to understand how to pass values into the report via the callcontext variable.

Did you already check out this thread?

I hadn’t seen that one. I’ll take a look at it.

Thank you.

To pass a value to a report via the CallContextBpmData variables
(my example images will be for adding a checkbox to the form)

  1. Customize the form that runs the report, by launching in developer mode.

  2. Enter customization mode

  3. Add the control to hold the variable (textbox, checkbox, combo, etc…)

  4. Bind that control to a CallContextBpmData member

  5. Save Cutomization and close

  6. Launch Report Style Maintenance, and load the report ID

  7. Copy the Report Style to a new Style

  8. Download the Report

  9. Open the report you downloaded with Report Builder

  10. Edit the main dataset’s query expression. Adding the field to the select phrase, and a new join phrase. In my example (the OpenPO report), the PoHeader dataset is the main one. So in its query expression, I add the highlighted parts:


    I choose T4 as it was the next available “T#”

  11. Add the query field:


    (You can give it a better name if you’d like).

Now I can use that checkbox’s value in my report. For example, to enable a grouping

image

  1. Save the Report RDL
  2. Upload to server (via Report Style Maint.)

Notes

  1. You can add a control on the form to use as a filter, but it only affects the rendered output. It does not filter the data created by the RDD. So make sure that you use it appropriately.
1 Like

Mr. Krusen …

Wow! This looks great! Thank you!

Our preliminary testing shows this works! My boss, Jim McCoy, who is also posted on here was able to pass a text box value using the steps listed here. We really appreciate it!

Thank you!

Thank you very much! This worked great!

Thanks, this might help in what i am trying to achieve as well, one quick question, the sys revID needs to the one you posted or where to find the correct one??

Does this work in Kinetic Public Cloud? I’ve been trying to replicate this in the Scheduled Shipments report with no luck. I created 5 radio buttons on the Scheduled Shipments Report screen, set them all to the CallContextBpmData.Character01 field for the EpBinding, added the LEFT OUTER JOIN onto the SSRS OMR50 Dataset query (per your instructions), and added the field T6.Character01 to the Query Fields and the main report. I am unable to get the value to show up and if I try changing the LEFT OUTER JOIN → ON value to anything else (e.g. T1.Company = T6.Company), it then errors the report on the OMR50 dataset. Any help on this would be greatly appreciated.

I have the report working now with the below Dataset query, but the CallContextBpmData.Character01 field will not come through no matter what I try.

="	SELECT DISTINCT
		T1.Company,
		T1.CustID,
		T1.CustName,
		T1.FirmRelease,
		T1.IUM,
		T1.JobNum,
		T1.OrderHeld,
		T1.OrderLine,
		T1.OrderNum,
		T1.OrderRelNum,
		T1.PartDesc,
		T1.PartNum,
		T1.Qty,
		T1.ReqDate as ReqDate,
		T1.ShipToName,
		T1.ShipToNum,
		T1.ShipVia,
		T1.StagedQty,
		T1.WhseCode,
		T2.JobNum as JobProd_JobNum,
		T2.ProdQty,
		T3.CustNum_AgingCreditHold AS OrderDtl_CustNum_AgingCreditHold,
		T3.CustNum_CreditHold AS OrderDtl_CustNum_CreditHold,
		T3.MktgCampaignID,
		T3.MktgEvntSeq,
		T3.NeedByDate,
		T3.OrderNum_FOB AS OrderDtl_OrderNum_FOB,
		T3.ProdCode,
		T3.ShortChar01,
		T4.Candidate,
		T4.CheckOff4,
		T4.DueDate AS JobHead_DueDate,
		T4.ProductHeight_c,
		T4.ProductLength_c,
		T4.ProductOversized_c,
		T4.ProductWidth_c,
		T4.UserDecimal1,
		T4.ScaledWeight_c,
		(Select Distinct City 
			FROM ShipTo_" + Parameters!TableGuid.Value + " As Tmp 
			WHERE Tmp.Company = T5.Company AND Tmp.ShipToNum = T5.ShipToNum AND Tmp.CustNum = T5.ShipToCustNum ) as City,
		(Select Distinct State 
			FROM ShipTo_" + Parameters!TableGuid.Value + " As Tmp 
			WHERE Tmp.Company = T5.Company AND Tmp.ShipToNum = T5.ShipToNum AND Tmp.CustNum = T5.ShipToCustNum ) as State,
		T6.Character01 AS CCBD_Character01

	FROM

		OMR50_" + Parameters!TableGuid.Value + " T1


	LEFT OUTER JOIN

		JobProd_" + Parameters!TableGuid.Value + " T2
	ON
		T1.Company = T2.Company AND T1.OrderLine = T2.OrderLine AND T1.OrderNum = T2.OrderNum AND T1.OrderRelNum = T2.OrderRelNum


	LEFT OUTER JOIN

		OrderDtl_" + Parameters!TableGuid.Value + " T3
	ON
		T1.Company = T3.Company AND T1.OrderLine = T3.OrderLine AND T1.OrderNum = T3.OrderNum


	LEFT OUTER JOIN

		JobHead_" + Parameters!TableGuid.Value + " T4
	ON
		T2.Company = T4.Company AND T2.JobNum = T4.JobNum


	INNER JOIN

		OrderRel_" + Parameters!TableGuid.Value + " T5
	ON
		T3.Company = T5.Company AND T3.OrderLine = T5.OrderLine AND T3.OrderNum = T5.OrderNum


	LEFT OUTER JOIN

		CallContextBpmData_" + Parameters!TableGuid.Value + " T6
	ON
		T6.SysRowID = '00000000-0000-0000-0000-000000000000'


	WHERE

		NOT T3.ProdCode IN ('BADD', 'CCF', 'EXWR', 'GUAR', 'LFRT', 'MOVE', 'OTH', 'PART', 'PARTENTK', 'RAWM', 'REPR', 'RFKT', 'RTXWARR', 'RTXWARRX', 'SC', 'SHOW', 'SUSP', 'TACM', 'UD', 'WARR', 'WARX') AND
		NOT T3.ProdCode LIKE 'CLY-%' AND
		NOT T3.ProdCode LIKE 'CQR-%' AND
		NOT T1.PartNum LIKE 'RECON-%-INSTALL'
"

Here are screenshots of my screen customization:

image
Each radio button has a different ID and Data values, but they are all set to the CallContextBpmData.Character01 EpBinding.

I’m pulling my hair out trying to figure out what I’m doing wrong…all while hoping it is just something stupid with Kinetic!

Try setting each radio button to a unique Checkbox (Checkbox01, Checkbox02, …)