NAB APP filtering on fields not in datasource SQL statement

[petersioufi]

We have a file that contains Dimensions and Values. It looks like this:

Dim1, Dim2, Dim3, Month, Value1, Value2

In a bar chart widget we group by Month and show Value1 and Value2 for each month as the bars. However, we also have a filter widget which alllows users to select specific Dim1, Dim2, Dim3.

The problem is the data source has to be grouped by month only otherwise you get too many bars because of all the dimensions. In APP behaviours we don't get the option to set the filters using Dim1, Dim2 or Dim3 because they are not in the grouped result. We found a workaround which is very cumbersome:-
1. Assume the "group by" in datasource includes all the dimensions. That corrupts the result set but we need that for the next step.
2. Now you will see all the dimensions and can filter by them from the selection form in the APP.
3. After you save the APP, go back and fix the datasource to remove the Dimensions so you can get a "total summary" or individual filtered results dependant on the selection widget.

Our App has 7 of these Widgets and each time we need to maintain it we have to corrupt all the datasources then fix them back again after building the APP.

It would be ideal if we could move a widget also. We needed to create multiple vertical alignment widgets to hold the other widgets properly but we couldn't move widgets between alignment widgets. We deleted the widgets and added them again in their proper position but that lost the filter button behaviours so we had to corrupt our data surces again to re-instate the filters then fix the data sources again.

I suggest the filter selections should show all the fields possible in the underlying data source tables (not just based on the SQL statement produced) because it is valid to filter on columns that are not grouped result set.

Thanks,

Peter

[Johnny Major]

Hi Peter,

Can you give us the SQL behind your data source that drives your bar chart?

Thanks

[petersioufi]

Hi Johnny,

Sorry, my request was missleading. The point is that there is no SQL statement. Our developer used "behaviours" to filter widgets. The filter behaviour looks like this attachment.

You see the filter widget has selection fields that are not shown as on primary table at all (WZBR, WZBG, WZMA, WZAGTCOD do not appear on the left side as choices to pick from). That is because we can't have those columns in the data source otherwise the Bar Chart Widget shows detailed bars instead of summarised by month (called WZMTHSEQ in the table).

So, to define this behaviour, our user had to insert those columns as "group by" fields in the data source, define this behaviour, then re-define the data source to remove those columns from the data source. This is not ideal.

I see the option exists in behaviours to define the filter by program code instead of user-friendly selections (and that's a really good option) but it's hard to teach our users how to write code.

Peter