Filter supporting UDF Functions

[edgarf]

As discussed, we would like to provide an ability to have filters which can be used as a UDF function params. This means that those filters should not be applied in WHERE $filters clause, but should only be in places where $filter.FilterFieldName is written.

Generally the task would be: $filters by default applies all conditions. If $filters.parameter is found in the SQL text, then it removes it from the list and applies it where it is found in the SQL.

If @regiscamimura, you have something more to add - let us know.

[regiscamimura]

@edgarf What do you think of adding an option to the filter type for "Parameter". So "Parameter" filters would NOT be displayed in the form and eliminates possible confusions with being able to select different types of filters for something that has an exact value to be used as a parameter. Thoughts?

[regiscamimura]

@edgarf I just got the feature done. It's pushed to branch 266. I move forward and added "Parameter" as a filter type. It needs to have a "Default" value, or the passed value will be blank, so if you do "SELECT * FROM Students WHERE LastSurname LIKE '$filter.LastSurname%'", the resulting query will be "SELECT * FROM Students WHERE LastSurname LIKE '%'"

[edgarf]

@regiscamimura, thank you.

I have found a few issues, which are blocking us from testing it properly: 1) The most important one - we can't have it instead of current filter types, because in that way we are not able to create dynamic or static list of options. I think it's better to be some kind of checkbox, which will tell whether it should present in the WHERE $FILTERS part. However, it might be easier to do it otherwise, the way like proposed in the beginning, just not to add the value into WHERE $FILTERS if it's noticed somewhere before as $filters.FilterName before in the command text

2) Currently, something strange is happening with this. The report with "param" kind of filter is getting saved, but the filter row (in filters section) disappear when you try opening it again. You can look into this example http://easol-dev.azurewebsites.net/reports/edit/68

3) Now I also noticed another issue. http://easol-dev.azurewebsites.net/dashboard in the dashboard the top two charts are represented incorrectly, you can see that both titles are different, but the charts themselves are the same. If you tap on the chart title on the left (CHECK BIRTHDAY Aggregation), you will get into the correct chart details view.

[regiscamimura]

@edgarf I'm working on 2 and 3, but I need some feedback on 1:

• First of, it's not hard to just skip any filter that appears as a parameter. Also, please note that we can actually have dynamic or static lists, regardless the type being "parameter". That's because the usage of the parameters are really simple, and it will be just replaced with whatever you type in the "Default" column. For example, you could write a query in the command text like that:

SELECT * FROM Students WHERE YEAR(BirthDate) IN ($filter.Year)

From there, you can create a filter with field name being "Year", and "Default" being "2008,2009,2010". Then that value will simply replace the $filter.Year and the query would get:

SELECT * FROM Students WHERE YEAR(BirthDate) IN (2008,2009,2010)

If the "Year" filter is a dynamic list, i.e, a query, no problem, it will work just the same. If the filter for "Year" value is "SELECT YEAR(BirthDate) FROM Students WHERE LastSurname LIKE 'M%'", the query would be

SELECT * FROM Students WHERE YEAR(BirthDate) IN (SELECT YEAR(BirthDate) FROM Students WHERE LastSurname LIKE 'M%')

And that works just fine. So it's really on the user's hand to create good queries and make good use of the filters as parameters.

Said that, be aware and note that there is absolutelly no technical difficulty with just remove the filters if they are used as parameters, or having a filter type being "parameter". It's all about what will be more clear for the user. Maybe we should create a new section for "Parameters", instead of putting such parameters inside the "Filters" section, it's not hard neither.

Well, I'll be working on the other issues while I wait for a feedback on that. You might get a better idea on how to make more clear and intuitive how the filters/parameters work. If not, just please confirm that I should remove the filter type for "Parameter" and just skip it from the filter form if it's found as a parameter in the command text, it's really not easier or harder to take one or other approach, so please just let me know what and the users would prefer, ok?

[edgarf]

Hi Regis,

Thanks for the reply.

So the issue is that those filter values will also be provided by the user, the same way like all other filter types (except of System Variable).

So imagine this situation -

SELECT UDF.SchoolId , StudentAttributes.* , UDF.CountAbsences , UDF.MaxInstructionalDays , UDF.AttendanceRate FROM [EASOL].[StudentAttendanceRate_UDF]($filters.date_start, $filters.date_end) AS UDF INNER JOIN EASOL.StudentAttributes ON UDF.StudentUSI = StudentAttributes.StudentUSI ORDER BY UDF.SchoolId , UDF.StudentUSI

As you can see we have 2 variables which are used as params. The problem is that users will be able to change those dates dynamically, while they are looking at the report and we don’t want to provide a text-field, but a drop-down instead. And drop-down values would be generated dynamically using “Dynamic List” type of filters: | DISPLAY NAME | FIELD NAME | TYPE | VALUE | DEFAULT VALUE | | From | date_start | Dynamic List (param) | SELECT day FROM SCHOOL_DAYS | 01-Sep-13 | | To | date_end | Dynamic List (param) | SELECT day FROM SCHOOL_DAYS | 31-MAY-14 |

Let me know if it’s more understandable right now. I can provide more examples if needed.

Regards, Edgar

On 07 Mar 2016, at 19:45, Regis Camimura notifications@github.com wrote:

@edgarf https://github.com/edgarf I'm working on 2 and 3, but I need some feedback on 1:

First of, it's not hard to just skip any filter that appears as a parameter. Also, please note that we can actually have dynamic or static lists, regardless the type being "parameter". That's because the usage of the parameters are really simple, and it will be just replaced with whatever you type in the "Default" column. For example, you could write a query in the command text like that: SELECT * FROM Students WHERE YEAR(BirthDate) IN ($filter.Year)

From there, you can create a filter with field name being "Year", and "Default" being "2008,2009,2010". Then that value will simply replace the $filter.Year and the query would get:

SELECT * FROM Students WHERE YEAR(BirthDate) IN (2008,2009,2010)

If the "Year" filter is a dynamic list, i.e, a query, no problem, it will work just the same. If the filter for "Year" value is "SELECT YEAR(BirthDate) FROM Students WHERE LastSurname LIKE 'M%'", the query would be

SELECT * FROM Students WHERE YEAR(BirthDate) IN (SELECT YEAR(BirthDate) FROM Students WHERE LastSurname LIKE 'M%')

And that works just fine. So it's really on the user's hand to create good queries and make good use of the filters as parameters.

Said that, be aware and note that there is absolutelly no technical difficulty with just remove the filters if they are used as parameters, or having a filter type being "parameter". It's all about what will be more clear for the user. Maybe we should create a new section for "Parameters", instead of putting such parameters inside the "Filters" section, it's not hard neither.

Well, I'll be working on the other issues while I wait for a feedback on that. You might get a better idea on how to make more clear and intuitive how the filters/parameters work. If not, just please confirm that I should remove the filter type for "Parameter" and just skip it from the filter form if it's found as a parameter in the command text, it's really not easier or harder to take one or other approach, so please just let me know what and the users would prefer, ok?

— Reply to this email directly or view it on GitHub https://github.com/EASOL/easol/issues/273#issuecomment-193366308.

[regiscamimura]

@edgarf ah yeah, it makes sense. I wasn't aware the user would provide those values. I understand now and will implement accordingly.

[edgarf]

Thanks! Great. And I was thinking it would be easier to find out whether that field was already in the command text, because otherwise we'd need to amend the database schema

[regiscamimura]

@edgarf I just got the param filters implemented as requested. I also fixed the issues with charts in the dashboard being actually the same.

[edgarf]

@regiscamimura, thank you. I will check it out tonight.

[edgarf]

Closing this issue. Thank you Regis!