You can hide columns in the dashboard grid view but still have them in the BAQ to use for filtering.
Again, the OHQ in partbin at runtime of the dashboard may not match what the OHQ will be for the date they select on the dashboard.
That is what I was thinking. And yes, the OH qty will change.
He can do the summing in the dashboard instead.
Youâd have to do that in code though.
Is there an easy way for someone with my current knowledge to get this done? Or can I just create a report with this baq, get it the way I want it, and then put the report in the dashboard?
Well, I canât say I have a handle of what theyâre really looking for to answer that question.
If theyâre wanting the current partbin OHQ no matter what date theyâre selecting then itâs easier to accomplish with a simple BAQ.
Since youâre saying theyâre selecting a date in the dashboard, it sounds like they want a dashboard version of the Time Phased Mtl Requirements report. This is more complex.
Yes. Tell them if they want this, they have to use a parameter. Then walk away. 
1 Like
I must be missing something. The dashboard will only sum whatâs shown in the grid.
No code required.
They want one line per material number, with the sum in each line. Heâs not trying to sum up everything in the grid.
Ugh. Well that changes things.
@Will79, if you feel up to learning some code, the executionParams.ExecutionSetting can be accessed with the where clause from the dashboard inside the getlist from the BAQ.
You could filter out the rows there and fiddle with the calculated field.
I might have some free time later to look.
Iâm always up to learn new things!!
Cab you send me your actual BAQ? Trying to recreate it backwards from the query is not working well for me at the moment lol.
I got it working proof of concept.
Iâll show it tomorrow.
1 Like
For the data you are using you donât need these tables at all with the criteria you selected:
Erp.Part
Erp.JobPart
Erp.OrderHed
Erp.Customer
Erp.JobProd1
For what I tested to make work on my system, I removed everything but
Erp.JobHead
Erp.JobMtl
Erp.PartBin
If you follow this approach, use my tablelist but add back your JobProd & JobOper so you can add your criteria.
I removed your fields and added the following:
Calculated_Filter_GTE_DueDate => JobHead.DueDate [Group By]
Calculated_Filter_LTE_DueDate => JobHead.DueDate [Group By]
Calculated_RequiredQty => sum(JobMtl.RequiredQty)
Calculated_OnHandQty => from SubQuery2 [Group By]
Calculated_PartNum => max(JobMtl.PartNum)
SubQuery2 is
PartBin_Company [Group By]
PartBin_PartNum [Group By]
Calculated_OnHandQty => sum(PartBin.OnhandQty)
I set the BAQ to Updatable.
On the update tab - General, I deselected allow new record.
On the update tab - Update Processing, I selected Advanced BPM Update Only.
I then went to Actions->Run BPM Designer.
I selectected GetList and added a Post-Processing Directive and enabled it.
I chose design, and added a custom code block.
In the Usings and References I added:
using System.Text.RegularExpressions;
The custom code is as follows:
//For Debugging
string retString = "";
string nl = Environment.NewLine;
//Initial Data - Wide Open!
DateTime gteDate = new DateTime();
DateTime lteDate = DateTime.Parse("01/01/2100");
//Test Data
//gteDate = DateTime.Parse("09/01/2022");
//lteDate = DateTime.Parse("10/28/2022");
//This gets the where clause from the dashboard. If you don't use the same names, you will need to modify
try
{
var wRow = (from whereRow in executionParams.ExecutionSetting
where
whereRow.Name == "Where"
select
whereRow).FirstOrDefault();
string[] whereClauseArray = wRow.Value.Split(new[] {"AND"}, StringSplitOptions.None);
foreach (var wClause in whereClauseArray)
{
//retString += wClause + nl;
string fieldName = wClause.Trim().Split(' ')[0].Replace("(","");
string operand = wClause.Trim().Split(' ')[1];
Match match = Regex.Match(wClause, @"'([^']*)");
string fieldValue = match.Groups[1].Value;
gteDate = fieldName == "Calculated_Filter_GTE_DueDate" ? DateTime.Parse(fieldValue) : gteDate;
lteDate = fieldName == "Calculated_Filter_LTE_DueDate" ? DateTime.Parse(fieldValue) : lteDate;
//retString += $"{fieldName} {operand} {fieldValue}" + nl;
}
}
catch (Exception ex)
{
//Set it back to wide open if we have an error (probably don't have where clauses)
gteDate = new DateTime();
lteDate = DateTime.Parse("01/01/2100");
}
//retString += $"{gteDate.ToShortDateString()} {lteDate.ToShortDateString()}" + nl;
//Get our results inside the date criteria
var resultsInDate = from resRow in result.Results
where
resRow.Calculated_Filter_GTE_DueDate >= gteDate &&
resRow.Calculated_Filter_LTE_DueDate <= lteDate
select resRow;
//Get a list of unique parts from the filtered data
var getUniqueParts = (from part in resultsInDate
select
part.Calculated_PartNum).Distinct();
//Our replacement rows will go here to be added back to the results list after we clear it
List<ResultsUbaqRow> newRowsList = new List<ResultsUbaqRow>();
//Loop through each part in the list, create one unique row per part
foreach(var part in getUniqueParts)
{
var partResults = from partRow in resultsInDate
where
partRow.Calculated_PartNum == part
select new {partRow.Calculated_PartNum, partRow.Calculated_OnHandQty, partRow.Calculated_RequiredQty};
decimal sumRQ = 0;
foreach(var pRow in partResults) //Sum rows for this part
{
sumRQ += pRow.Calculated_RequiredQty;
}
var partResultFirst = partResults.First(); //Get the row info for our new row from the first record.
//retString += $"PartNum: {partResultFirst.Calculated_PartNum.PadRight(40)} Required Qty: {sumRQ.ToString().PadRight(40)} {partResultFirst.Calculated_OnHandQty.ToString().PadRight(40)}" + nl;
ResultsUbaqRow newRow = (ResultsUbaqRow)result.Results.NewRow(); //Create a new row
newRow.Calculated_PartNum = partResultFirst.Calculated_PartNum; //Add PartNum
newRow.Calculated_OnHandQty = partResultFirst.Calculated_OnHandQty; //Add OnHand
newRow.Calculated_RequiredQty = sumRQ; //Add Summed of Required Qty Rows
newRow.Calculated_Filter_GTE_DueDate = gteDate; //these are irrelevant when only returning sums, so set to return the range that was filtered
newRow.Calculated_Filter_LTE_DueDate = lteDate; //I would not even show these in the dashboard, just use them as filters.
newRow.RowIdent = DateTime.Now.Ticks.ToString();
newRow.SysRowID = Guid.NewGuid();
newRowsList.Add(newRow); //Add our rows to our list of rows
}
//Clear the original Results
result.Results.Clear();
//Add our new rows
result.Results.AddRange(newRowsList);
//My Debug Message
//InfoMessage.Publish(retString);
That completes the BAQ.
I made a test dashboard with this query. The summary only shows the Part Number, Required Qty, & OnHand Qty.
I added a tracker view with these two fields:
prompt Calculated_Filter_GTE_DueDate = Condition GreaterThanOrEqualTo
prompt Calculated_Filter_LTE_DueDate = Condition LessThanOrEqualTo
I chose Input Prompts Only & criteria required
I tested it and it worked.
Now you can see how this can be done, but it may be a headache to maintain if you need to change it.
It might be just as acceptable to use the same basic query, but use the sums and the group by to get a similar effect.
Or you can clean up that messy query and use a parameter 
3 Likes
I prefer the parameter route myself! However, I gotta follow the boss. And yeah, the baq is crazy messy, but I was asked to use it.
This will work with your existing query with slight modifications, but it would be best to trim it down.