Simple Filtering
All filters are built up using a combination of:
Changes to the WHERE clause of the SQL query
CustomFilterField XML elements which describe the type of the field and other UI-specific properties
Let's start by allowing the user to filter on Title. Modify your SQL query as per the example below, and also insert the Filter section prior to your Retrieval section:
<SqlDef Name="BookstoreReportTutorial" GroupName="BookstoreReportTutorial"> select SYS_BOOK_ID, TITLE, ISBN, PUBLISHED_DATE, QUANTITY_SOLD, PROMOTIONAL_DISCOUNT, STATE, PUBLISHER_ADDRESS_COUNTRY,PUBLISHER_ADDRESS_COMP1, PUBLISHER_ADDRESS_COMP2, PUBLISHER_ADDRESS_COMPS, PROMOTIONAL_PERIOD_START_DATE, PROMOTIONAL_PERIOD_END_DATE from ZBKS_BOOK where TITLE like $TITLE$</SqlDef><Filters> <CustomFilterField> <FieldRef category="PDF" levelType="ZBKS.Book"> <FieldName>Title</FieldName> </FieldRef> <Type>STRING</Type> <SimpleMapping sqlName="TITLE"/> </CustomFilterField></Filters> <Retrieval> ... A few things to understand about the entries above:
When we surround a SQL identifier with the dollar-sign ($) symbol, such as $TITLE$, we are designating it as a parameter. This means we will "plug in" the value for that parameter based on a filter value.
The CustomFilterField is used to provide a UI filter called "Title" of type STRING, and map that to the $TITLE$ parameter in your SQL query.
After submitting this report and reloading the UI, the first thing you will notice is that when the report is auto-executed, it gets no results:
This is happening because it's applying a filter value of NULL for $TITLE$. Since you probably want most of your filters to be optional, you should change your query as follows:
where ${filterIfNotNull:TITLE, TITLE like $TITLE$}This tells the report engine to add the clause "TITLE like $TITLE$" to the SQL query, but only when the user provides a value for TITLE. Otherwise, it is effectively removed from the query. Once you make this change, you should see all books when you first execute the report:
Then you can try filtering: If I click Filters and enter the string "Learning Platform", I should see only the matching book.
What if I don't match case exactly though? Try typing "LEARNING PLATFORM" instead. You will see that it doesn't match:
This is because the database SQL comparison is case-sensitive by default. To search case-insensitively, you can use the UPPER() function on both the user-provided and database values:
where ${filterIfNotNull:TITLE, UPPER(TITLE) like UPPER($TITLE$)}
As an added twist, we frequently want to do inexact or wild card searches. For example, what if the user types only "Learning"? Again, you can modify the query to accommodate this by appending the SQL wild card (%) to the user-provided value:
where ${filterIfNotNull:TITLE, UPPER(TITLE) like UPPER($TITLE$) || '%'}
You're encouraged to try some other filter types, like DATE, FLOAT, INTEGER, etc. They should work similar to STRING, except you should use equality and comparison operators in your SQL such as = < > <= >= instead of the LIKE keyword.