Tweaking the where clause on a dashboard refresh

Epicor ERP 10
1

So I’m fiddling around trying to figure out how I could filter a grid with multiple values shown on a tracker. In the trace I see this. 402047-1-1 is the value that I have in my tracker.

<parameter name="executionParams" type="Ice.BO.QueryExecutionDataSet">
  <QueryExecutionDataSet xmlns="http://www.epicor.com/Ice/300/BO/DynamicQuery/QueryExecution">
    <ExecutionSetting>
      <Name>Where</Name>
      <Value>(JobHead_JobNum = N'402047-1-1')</Value>
      <SysRowID>00000000-0000-0000-0000-000000000000</SysRowID>
    </ExecutionSetting>
  </QueryExecutionDataSet>
</parameter>

Can I hijack that dataset and add a where clause with an “or” in it? Then populate that with values from more than one text field?

2 Likes
2

Not sure I follow. Explain further plz

-Jose

3

So when you make a dashboard, you can put in the tracker and make filters for fields. It’s really nice because it parameterizes the query and the more filters you have populated, the faster the query returns the results. The only problem is, I can only put one value in that field. I can make a little less specific by using starts with, or greater than or something, but I don’t know of a good way to make it so I can put two (or more) totally unique values in to filter the results. There are a couple of ways of doing it that I don’t like much.

One is to run the query wide open and then use the grid filters to filter after the query is run. The problem with that, like I mentioned before, is with a large data set, this can take a long time to run. The other is to add a list in the parameters of the BAQ. In certain contexts, with the right people using it, that works pretty good. However, what I am doing requires large volumes of transactions with shop floor workers, and that extra step will end up being a problem. What I would like to do, is to be able to have more than one text field, and then have the query run with multiple filters. Hence, see if I can use the Dynamic query adapter to filter on what’s in the text boxes as the query is run that would be the best solution. The trace is from the refresh button, but maybe it would be better to call my own method instead built in refresh?

image
image.png1267×450 50 KB

4

That’s tough to do, you may be able to do it if you run the BAQ manually (in code) and dynamically pass a “LIST” using the “IN” whereClause… but UGH…

5

when you build the BAQ, can you define a list parameter that will get prompted for when you refresh the dashboard?

6

Yes, but like I mentioned, that extra step will be problematic in the environment that it’s going to be used in.

1 Like
7

on my BAQ, can I do an advanced and make a pre-processing BPM on get list that sets some of the BPM parameters, then at the dashboard calculation tie those to the text fields? Does that make sense? Obviously I will need some logic to handle nulls…

8

Pre Processing GetList runs AFTER parameters… HMm let me think some more.lol

9

Ok I tried every hack in the book… the only way I can see doing this is to invoke the BAQ view code (not in a normal dashboard)

10

If it’s more about the filtering on the view, as opposed to the dataset, you can filter the grid programmatically.

      private void myGrid_InitializeLayout(object sender, Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs e)
        {       
            e.Layout.Bands[0].ColumnFilters["JobNum"].FilterConditions.Add(FilterComparisionOperator.Equals, "12345");
      //add more as needed
        }
1 Like
11

Yeah, I know how to do that. The problem is, if you have to return all of the data before filtering, performance really suffers, and shop floor personnel are not patient…

1 Like
12

Hi @Banderson,

Did you find any solution of this problem? I also need to where clause some of BAQ when dashboard refresh.

13

I did not. The best I can get is the “starts with”. I did figure out how to add an index to the SQL database that significantly sped up this query, so running more wide open isn’t an issue any more. Then they can use the normal grid filters.

14

@josecgomez do you have an example of this? I have used parameters, but this sounds like setting a where clause like the search function. or am I overthinking this.