Dashboard Pass Silent Parameters to BAQ

[hkeric.wci (Haso Keric)]

A Feature asked for ages; never delivered by Epicor yet it seems like a simple one… In a Dashboard allow Passing in Parameters to the BAQ Before Execution so we can stop running wide-open BAQs which hammer the server because Post-Filtering is not the answer.

Example: Click on Parent Grid and have the Child Re-Run BAQ with specific Params. So if you are doing lets say CTEs Recursive or Sub-Queries and SUM() and MAX() you will get “accurate” number with little effort because each time the BAQ will re-run with “params” and viola.

For More Reading:

Has anyone ever figured out how to call a BAQ from a Dashboard with BAQ Parameters (keep reading).

Now you can specify BAQ Params and once the user hits “Refresh” they will be prompted. But if you have Parameters and Dashboard filters, everytime they change a filter and hit refresh they will be re-prompted.

In addition it looks, ugly.

Instead of running a wide-open BAQ that runs for 3 minutes, is there a way to send the BAQ Parameters from the Filters instead (Avoiding the popup).

Further more I would love to see once the user clicks on a Head row, execute a child BAQ with params from the head-row… Let’s say the Details are PartTran records. Its crazy that we grab 1 million PartTran records and the user only wants to analyze 5 of them but by navigating and re-running BAQ Queries upon each click it would make things faster.

Basically:
Click on Head Row → Run Details BAQ with Head Row Parameters. (Repeat as the user iterates through rows) [ON-Demand Data] so we can stop running BAQs wide-open or with a -2 years limit.

I have seen this question asked numerous times, and here I am asking it again. Likewise with BAQ Reports, it seems that the BAQ Report runs this wide-open long Query and then applies the FIlter instead of passing the filters as Parameters.

I am okay if anyone has a way to customize the Dashboard to handle those events. But Epicor oughta make this a new feature.
https://epiusers.help/t/epicor-dashboards-execute-baq-with-params-on-filter/44308

[Chris_Conn]

[josecgomez]

YES!!! This is something we’ve been fighting with for ages. Parameters should be able to be mapped to dashboard tracker so you can pass those in without the extrapoup and pain as well as the publish subscribe functionality

[Banderson (Brandon Anderson)]

#1 vote right here!!! (I’ll take all of my other votes away if this one can happen!)

[Edge (Stephen Edginton)]

Good idea

[hkeric.wci (Haso Keric)]

@Edge Chris_Conn was wondering if its done yet? For his Birthday if he had a choice between a Pony and this, he would choose this.

[DeanMiller]

Yes Yes Yes Yes YES !!

[ridgea (Alex Ridge)]

[hkeric.wci (Haso Keric)]

Maybe in 10.2.400… Maybe…?

[jgiese.wci (Joshua Giese)]

I don’t recall but I don’t believe as of at least 10.1.500 maybe even earlier, that a BAQ actually will post filter from a dashboard. I’m pretty sure @josecgomez and/or I ran sql traces on dashboard BAQs and found that the filters that are passed into the dashboard (not baq params) are used in the direct SQL query. Not the whole query coming back then filtered.

[Banderson (Brandon Anderson)]

@jgiese.wci

I don’t recall but I don’t believe as of at least 10.1.500 maybe even earlier, that a BAQ actually will post filter from a dashboard. I’m pretty sure @josecgomez and/or I ran sql traces on dashboard BAQs and found that the filters that are passed into the dashboard (not baq params) are used in the direct SQL query. Not the whole query coming back then filtered.

Yeah, that good for performance, however, certain things don’t work with that type of filtering. For example, an anchor on a CTE query, or aggregate functions in lower levels on the query. That’s the problems we are having.

[hkeric.wci (Haso Keric)]

@Bart_Elia hows it coming on this?

[josecgomez]

@hkeric.wci I think this is a @Rich request not a @Bart_Elia one lol

[Rich (Richard Riley)]

Yep this is one for me. This is currently not on the list for 400 and I will need to review level of effort as 400 is already past full and we would need to re-prioritize if this is more than a few hours of work.

I will be asking the BAQ team to see how much work it would be to allow the use of BAQData field values in Constraints / Filters. That would require a little code work from you but you should be able to reduce / remove the necessity to have the parameter prompting and that work would provide value into the Kinetic future…

[Bart_Elia]

Agreed this is all @Rich’s. He’s been god of ICE for longer than I was on the team and I am off playing in the cloud these days.

[dkavvouras (DIMITRIS KAVVOURAS)]

Trying to figure out a way to overcome this “temporary” limitation, I have come to the below workaround that works for both Dashboards and BAQ Reports (in a short different way but the key concept is the same).

1. Add some UD Fields to UDCodes table. These will be used to hold the values that your BAQ will use instead of parameters. i.e.

2. Modify your BAQ so that instead of adding a Parameter as Criteria, Join the UDCodes table and add a relation to the field that will contain the value you would normaly use as Parameter. Also select at the Display Fields the CodeID from the UD Codes.

3. Create the Dashboard as usual, include at the tracker the UDCode.CodeID field and deploy as Smart Client Application

4. Now , what we need is to auto-create the UDCode record (before the BAQ is executed) that will store our “Parameter” values. Create a Dashboard Assembly menu item for the Dashboard and enter customization mode.

5. Add any Controls (not need to be bound somewhere) that will hold the “Parameter” values , in my case the InvoiceNum, you can hide the CodeID textbox and label.

6. Add the following code to Create the UDCode Record before the BAQ is executed, also remove the UDcode record just after the Execution.

public class Script
{
	// ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
	// Begin Wizard Added Module Level Variables **

	private DataView V_BAQTestParams_1View_DataView;
	private string codeTypeID = "BAQParams";					
	private string codeID = "";
	private EpiDataView edvV_BAQTestParams_1View;
	// End Wizard Added Module Level Variables **

	// Add Custom Module Level Variables Here **

	public void InitializeCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
		// Begin Wizard Added Variable Initialization
		
		this.baseToolbarsManager.ToolClick += new Infragistics.Win.UltraWinToolbars.ToolClickEventHandler(this.baseToolbarsManager_ToolClick);
		this.V_BAQTestParams_1View_DataView = this.V_BAQTestParams_1View_Row.dataView;
		this.edvV_BAQTestParams_1View = ((EpiDataView)(this.oTrans.EpiDataViews["V_BAQTestParams_1View"]));
		this.edvV_BAQTestParams_1View.EpiViewNotification += new EpiViewNotification(this.edvV_BAQTestParams_1View_EpiViewNotification);
		// End Wizard Added Variable Initialization

		// Begin Wizard Added Custom Method Calls

		// End Wizard Added Custom Method Calls
	}

	public void DestroyCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
		// Begin Wizard Added Object Disposal
		
		this.baseToolbarsManager.ToolClick -= new Infragistics.Win.UltraWinToolbars.ToolClickEventHandler(this.baseToolbarsManager_ToolClick);
		this.V_BAQTestParams_1View_DataView = null;
		this.edvV_BAQTestParams_1View.EpiViewNotification -= new EpiViewNotification(this.edvV_BAQTestParams_1View_EpiViewNotification);
		this.edvV_BAQTestParams_1View = null;
		// End Wizard Added Object Disposal

		// Begin Custom Code Disposal

		// End Custom Code Disposal
	}


	private void baseToolbarsManager_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs args)
	{
		if(args.Tool.Key.Equals("RefreshTool"))
			AddUserCodeRecordForBAQ();
	}

	private void AddUserCodeRecordForBAQ()
	{
		try
		{
			// Declare and Initialize EpiDataView Variables
			// Declare and create an instance of the Adapter.
			if(txtInvoiceNum.Text.Equals(""))
				return;

			EpiDataView edvDashBoardParam = ((EpiDataView)(this.oTrans.EpiDataViews["V_BAQTestParams_1View"]));		
			UserCodesAdapter adapterUserCodes = new UserCodesAdapter(this.oTrans);
			adapterUserCodes.BOConnect();
								

			adapterUserCodes.GetByID(codeTypeID);
			if(adapterUserCodes.UserCodesData.UDCodeType.Rows.Count > 0)
			{
				bool result = adapterUserCodes.GetNewUDCodes(codeTypeID);
				if(result)
				{
					DataRow[] DrUD = adapterUserCodes.UserCodesData.UDCodes.Select("RowMod = 'A'");					
					DrUD[0]["CodeDesc"] = txtInvoiceNum.Text;
					DrUD[0]["Char01_c"] = txtInvoiceNum.Text;
					codeID = DrUD[0]["CodeID"].ToString();
				}
				adapterUserCodes.Update();
			}
			// Cleanup Adapter Reference
			adapterUserCodes.Dispose();			
			((EpiTextBox)csm.GetNativeControlReference("ddce9075-b1d1-4166-b354-9bc446498eb1")).Text = codeID;			

		} catch (System.Exception ex)
		{
			ExceptionBox.Show(ex);
		}
	}
	

	private void edvV_BAQTestParams_1View_EpiViewNotification(EpiDataView view, EpiNotifyArgs args)
	{
		// ** Argument Properties and Uses **
		// view.dataView[args.Row]["FieldName"]
		// args.Row, args.Column, args.Sender, args.NotifyType
		// NotifyType.Initialize, NotifyType.AddRow, NotifyType.DeleteRow, NotifyType.InitLastView, NotifyType.InitAndResetTreeNodes
		if ((args.NotifyType == EpiTransaction.NotifyType.Initialize) && codeID != "")
		{		
			try
			{			
			UserCodesAdapter adapterUserCodes = new UserCodesAdapter(this.oTrans);
			adapterUserCodes.BOConnect();
						

			adapterUserCodes.GetByID(codeTypeID);
			if(adapterUserCodes.UserCodesData.UDCodeType.Rows.Count > 0)
			{
				DataRow[] DrUD = adapterUserCodes.UserCodesData.UDCodes.Select("CodeID = '" + codeID + "'");
				if(DrUD.Length > 0)									
				{					
					DrUD[0]["RowMod"] = "D";	
					adapterUserCodes.Delete(DrUD[0]);				
				}
				//adapterUserCodes.Update();
			}
			// Cleanup Adapter Reference
			adapterUserCodes.Dispose();	
			codeID = "";

			} catch (System.Exception ex)
			{
				ExceptionBox.Show(ex);
			}
		}	
	}
}

[louis.fequet]

@dkavvouras

Add some UD Fields to UDCodes table.

Yeah, I did that in the past. It’s takes a whole resource (ud table) for little need…unless that UD table becomes a table of parameters from different sources and then requires a more code to maintain.

I really prefer External BAQs that I call with the DynamicQueryAdapter. It gives me more flexibility over the query.

[Banderson (Brandon Anderson)]

UDcodes is different than a UDtable. UDcodes is one table used for all the codes and has a nice little user interface to maintain said codes already built in.

[louis.fequet]

Yes you’re right. I misread. But the concept remains. If I have to do some coding, I prefer external BAQs.

[dkavvouras (DIMITRIS KAVVOURAS)]

I agree, external BAQs that read Epicor tables, or even calling stored Procedures from an external BAQ is always a good alternative when you need for example performance but don’t care about security or other intermediate business logic.The above approach is a generic solution to imitate, with all its limitations, the missing criteria functionality in any BAQ Report , Dashboard etc and also be lined up with Epicor guidelines.