Maybe in 10.2.400… Maybe…? 
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.
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…
5 Likes
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.
2 Likes
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).
- 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.
- 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.
- Create the Dashboard as usual, include at the tracker the UDCode.CodeID field and deploy as Smart Client Application
-
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.
-
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.
- 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);
}
}
}
}
3 Likes
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.
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.
Yes you’re right. I misread. But the concept remains. If I have to do some coding, I prefer external BAQs.
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.
Still the most requested feature since the inception of ICE Tools 
10.2.600 is already at the door, no sign of feature. 
3 Likes
Yeah @Edge!! this is our most Voted for request!!!
Next time you are looking at Dashboards don’t forget this little guy!!! Pretty please with a cherry on top
2 Likes
EVA Cool, Functions Amazing, CDC Woah, API v2 Hell Yea!.. But what about BAQ Silent Params, actually something we are all hoping for every release 
Its definitely something we will use, abuse and push to the limit.
Indeed, this would be a game changer in dashboard development.
Right now I have a Dashboard and the user would like to search by Period Range… Pretty simple on the Details… But in the SUM() (Head) version… I have to make 52 calculated columns (52 periods) per1, per2, per3… then when they change the filters use C# to hide/show the period range Grid columns among many things… So 1 option is of course to prompt them for params however when you PubSub with Params and Refresh it gets quite annoying being re-prompted over and over and over again.
4 Likes
Ok I hear you all - we will break down and look at options.
11 Likes
Intentional or not, this was kind of punny…
1 Like
Okay, I’m here to relieve the pain caused by dashboard imperfection. I hope I will gain at least one unicorn
I see the requested feature as extending a query filter settings with ability to subscribe published columns from another query to parameters of current query. So, once user clicks a record in “head” query then child query executes with parameters values based on columns from “head” query. No parameters prompt form appears for child query in this case.
User be prompted for child query parameters if these parameter subcribing settings are not specified.
Another, but related question is whether you actually needed in ability to control how to rerun a child query when current “head” row switches. For performance reasons, child query filtering happens on client side.
As I understand, sometimes you want to re-execute the child query parameterized with current “head” row values and filter criteria. You should understand that this may cause a “DDoS” attack to server if user decide to navigate through “head” rows in dashboard quickly.
2 Likes
Well I think we are talking about Parameters (Actual Parameter Prompts) being able to put those parameters in a Dashboard Tracker, what you explained above would be cool too.
However I think the original intent of the quest was just to allow those parameters to be bound to controls in a Dashboard Tracker.
Right now if you put a parameter prompt in a query in BAQ Designer, and you then bring that query into a dashboard then you refresh the dashboard you are prompted (with a popup) for the query parameters. We want to be able to set or pass those parameters in from the query tracker itself, or even default those in or pull them in from a published (header query as you mentioned)
3 Likes
Right now I have to pull in 10,000,000 “Child Rows” in case they do click on 500 rows. But Id rather re-execute the Child and be able to do SUM(), AVG(), MAX(), Grouping over and over again for less rows on-demand as needed. I think I can accomplish that If I can Publish Columns from the Head into the Childs “BAQ Param Prompt” silently. then I can go wild with SQL Aggregates, grouping etc.
Makes it so much easier being able to do
InvcDtlInvoiceNum = @InvoiceNum ... bunch of SubQueries, PIVOTs etc...
And get accurate results with aggregate functions…
Being able to select (normal pub/sub) or (silent param pub/sub) or hybrid of both would be great and of course just like anything at your own risk and yes it could bottleneck SQL if someone doesnt understand how to build it properly.
I am sure you can architect this much better than we are explaining it. If you make it happen, we will do a GoFundMe Page and buy you a real unicorn!
Technically you are almost there… BAQ Prompts work, we just want to publish a column into them and the user never sees the prompt (silent, secret) in addition to re-executing the BAQ with new inputs when Publishers row changes.
1 Like