Tweaking the where clause on a dashboard refresh

(Brandon Anderson) #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="">
      <Value>(JobHead_JobNum = N'402047-1-1')</Value>

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?

(Jose C Gomez) #2

Not sure I follow. Explain further plz


(Brandon Anderson) #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?

(Jose C Gomez) #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…

(Bernie Walker) #5

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

(Brandon Anderson) #6

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

(Brandon Anderson) #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…

(Jose C Gomez) #8

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

(Jose C Gomez) #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)

(Chris Conn) #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

(Brandon Anderson) #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…