How To: BAQ Parametrised Dashboards

ridgea · June 5, 2023, 2:58pm

Parametrized BAQ Dashboard

BAQ Config

BAQ

Parameter setup

Create Function

Input / Output Variables:

ridgea:

Parametrized BAQ Dashboard

BAQ Config

BAQ

Parameter setup

Create Function

Input / Output Variables:

// Code function:
// 1. Call dynamic query service, execute a query using dynamic parameters.
// 2. Ingnore any paramteres with null or emptry string values.
// 3. Call BAQ using parsed parameters, BAQ will handle skip condition if empty based on validation perfomred on IsEmpty validator: String.IsNullOrEmpty(‘paramter’).
// 4. Return results from BAQ executuion and parse values back into dataset.

CallService<Ice.Contracts.DynamicQuerySvcContract>(dQ => { 

// Create a new instance of QueryExecutionTableset 
Ice.Tablesets.QueryExecutionTableset dsQueryExecution = new QueryExecutionTableset();

//Create a new ExecutionParameterRow for the 'JobNum' parameter 
ExecutionParameterRow drRow = null;
drRow = new ExecutionParameterRow(); 
drRow.ParameterID = "JobNum";
drRow.ParameterValue = this.JobNum;
drRow.ValueType = "nvarchar";
drRow.IsEmpty = String.IsNullOrEmpty(this.JobNum);
drRow.RowMod = "A";
drRow.SysRowID = new Guid();

// Add the 'JobNum' parameter to dsQueryExecution 
dsQueryExecution.ExecutionParameter.Add(drRow); 

// Create a new ExecutionParameterRow for the 'PartNum' parameter 
drRow = new ExecutionParameterRow(); 
drRow.ParameterID = "PartNum";
drRow.ParameterValue = this.PartNum;
drRow.ValueType = "nvarchar";
drRow.IsEmpty = String.IsNullOrEmpty(this.PartNum);
drRow.RowMod = "A";
drRow.SysRowID = new Guid();

// Add the 'PartNum' parameter to dsQueryExecution 
dsQueryExecution.ExecutionParameter.Add(drRow); 

// Create a new ExecutionParameterRow for the 'Desc' parameter 
drRow = new ExecutionParameterRow(); 
drRow.ParameterID = "Desc"; 
drRow.ParameterValue = '%' + this.Desc + '%'; 
drRow.ValueType = "nvarchar"; 
drRow.IsEmpty = String.IsNullOrEmpty(this.Desc);
 drRow.RowMod = "A"; drRow.SysRowID = new Guid(); 

// Add the 'Desc' parameter to dsQueryExecution 
dsQueryExecution.ExecutionParameter.Add(drRow); 

// Create a new ExecutionParameterRow for the 'CustID' parameter 
drRow = new ExecutionParameterRow();
 drRow.ParameterID = "CustID";
 drRow.ParameterValue = this.CustID;
 drRow.ValueType = "nvarchar";
drRow.IsEmpty = String.IsNullOrEmpty(this.CustID); 
drRow.RowMod = "A"; 
drRow.SysRowID = new Guid();

 //Add the 'CustID' parameter to dsQueryExecution 
dsQueryExecution.ExecutionParameter.Add(drRow);

//Create a new ExecutionParameterRow for the 'PONum' parameter
 drRow = new ExecutionParameterRow(); 
drRow.ParameterID = "PONum"; 
drRow.ParameterValue = this.PONum ; 
drRow.ValueType = "nvarchar"; 
drRow.IsEmpty = String.IsNullOrEmpty(this.PONum);
drRow.RowMod = "A"; 
drRow.SysRowID = new Guid(); 

//Add the 'PONum' parameter to dsQueryExecution 
dsQueryExecution.ExecutionParameter.Add(drRow); 

//Execute the query using the BaqID and dsQueryExecution 
this.returnObj = dQ.ExecuteByID(this.BaqID, dsQueryExecution); });

User interface - with search inputs

Dataview setup

Event to call Function

Method parameters

JobNum (EpBinding: Search.JobNum)
BAQ ID (Your BAQ ID)
PartNum (EpBinding: Search.PartNum)
PartDesc (EpBinding: Search.PartDesc)
CustID (EpBinding: Search.CustID)
PONum (EpBinding: Search.PONum)

Response Parameters:

Now your grid is ready to be bound with the appropriate columns produced with the dataset

User interface - with search inputs

Dataview setup

Event to call Function

Method parameters

JobNum (EpBinding: Search.JobNum)
BAQ ID (Your BAQ ID)
PartNum (EpBinding: Search.PartNum)
PartDesc (EpBinding: Search.PartDesc)
CustID (EpBinding: Search.CustID)
PONum (EpBinding: Search.PONum)

Response Parameters:

Now your grid is ready to be bound with the appropriate columns produced with the dataset

hmwillett · June 5, 2023, 3:11pm

This is a great solution for the parameter issue; it gets around the problem of the hard paging that just calling the REST service presents.

Thanks for sharing!

ridgea · June 5, 2023, 3:18pm

Thanks @hmwillett , really appreciated the feedback

klincecum · June 5, 2023, 3:20pm

You beat me to it.

angry monty python GIF

Nice.

ridgea · June 5, 2023, 3:34pm

@klincecum @hmwillett
This Is The Way GIF by Shibetoshi Nakamoto

estm8ben · June 5, 2023, 5:11pm

Is there some formatting issue with this post?
I can’t click on any images to get them big enough to actually read.
(I’m old and have bad eyes)

ridgea · June 6, 2023, 10:51am

Apologies @estm8ben , its my awful formatting in the dev software we use and hold our documentation.

Here is a more user friendly PDF:
parametrizedbaq dashboard.pdf (1.6 MB)

Hope this helps.

jrich2624 · January 8, 2024, 5:07pm

Thank you so much for this topic! I used these instructions with success but had to bind my grid to a dataview called ‘actionResult’ which I am not able to see in App Studio and now I’m not sure how to edit the columns in the grid. I tried creating columns and then binding them to the ‘actionResult’ dataview but that did not work. Do you have any advice?

hmwillett · January 8, 2024, 5:09pm

You can’t edit this. It’s a runtime dataview used to hold results returned from things like functions and REST calls.
You would be better off using a dataview-copy event widget to copy actionResults to a dataview you created which would then allow you to add new columns.

jrich2624 · January 9, 2024, 2:03pm

Thank you so much for your help! I created an event to copy the dataview and then bound it to my grid and got results

But when I try to define my columns under Grid Model, I don’t get data anymore:

Any idea why? I’m really only trying to rename my columns

ridgea · January 9, 2024, 8:54pm

Hi @jrich2624 ,

Would it be possible to screenshot the setup on the grid in the provider model and also the data grid column example ?

Glad to know the guide got you 90% of the was there.

jrich2624 · January 9, 2024, 9:05pm

Sure, I’m only referencing the same grid in both

hmwillett · January 9, 2024, 9:10pm

Would you send a screenshot of the Grid Model > Columns as well?

LarsonSolutions · January 9, 2024, 10:51pm

Interesting approach.

How I have worked this out was to take a user defined table - UD11 for example
Create an updatable BAQ that allows you to add records.
When I create a new record, Key1 is assigned the current UserID
The BAQ allows any one of the fields to be updated.
Date1 and Date2 for example.

I then create a BAQ for orders where I have UD11 as a CTE filtered to current userID
The next subquery is OrderHed filtered by the results of UD11 Date1 and Date2 compared to OrderDate.

On the dashboard I pull in both queries and place the UD11 at the top of the screen allowing you to enter in your date range.

This worked for me since I am not a programmer using functions

jrich2624 · January 10, 2024, 1:59pm

Sure, here is the first column:

And here is the second:

hmwillett · January 10, 2024, 2:05pm

Remove the Ep Bindings and change the Field to be Calculated_MINumRev and Calculated_PartNum.

jrich2624 · January 10, 2024, 2:04pm

Also, here is my view:

jrich2624 · January 10, 2024, 2:12pm

It worked! Thank you so much!