In this document we are going to cover the usage of dynamic view with one of the sample example. Also we will discuss about what all are must things for creating the dynamic view.
1. What is dynamic view and how it is different from normal view ?
– In normal view, you cannot pass selected filter value from report (Front End) within the view. You can only filter whole view (where condition applied on top of view sql). But this can be achieved using Dynamic view. Using dynamic view you can pass selected filter value from front end within the sql query of view.
– In normal view in order to apply any column as filter it is must that column should be part of selection. Anything which is not part of view sql, you will not able to use it as filter. But in dynamic view if anything is not part of selection then also we can use it as filter.
2. How to create dynamic view ?
– With Helical Insight application version 4.0 and higher, you can find a separate tab for Dynamic view in metadata -> views section. You can add your sql within this. Reference image shown below :
MUST THINGS :
– SQL you add within dynamic view must be in single string format, you need to remove all the spaces in between sql and need to bring it in single line string format.
– There must be at least 1 if and 1 else statement within dynamic view. Below we have added sample dynamic view sql which we have explained in detail.
if (check("${filter}.label" ,"travel_date"))
{
traveldateFilter = findFilterByLabel("travel_date")
traveldateFilterValue = traveldateFilter.value
traveldateFilter_1 = findFilterByLabel("travel_date_1")
traveldateFilterValue_1 = traveldateFilter_1.value
//You can write your business logic here.
return "select * from \"travel_details\" T1 where \"travel_date\" between "+ traveldateFilterValue +" and "+traveldateFilterValue_1;
}
else
{
return "select * from \"travel_details\""
}
Explanation of above sample :
– Within if condition in above sample code, we have tried to check that if any filter with label / name “travel_date” exists or not at report level. If while creating report any filter is not added then it will simply gets out from if condition and directly executes else condition. (check(“${filter}.label” ,”travel_date”),this is syntax to check the label of filter if it exists or not. If it returns true then it will execute if condition and the values selected at report level will be passed as variables within the view sql.
– To get the actual selected value while run time from report, we need to use below kind of syntax :
traveldateFilter = findFilterByLabel(“travel_date”)
traveldateFilterValue = traveldateFilter.value
This will fetch the latest run time value of travel_date filter and stores it in traveldateFilterValue variable.
– It is must that you put same SELECTION of columns in sql in if as well as else condition because while executing the view sql (retrieving columns) and saving it within metadata, always ELSE condition gets executed. Because while you are creating view it will never check for filter label which is IF condition. So, ELSE condition executes and based on that column names will be retrieved. Columns within select clause of ELSE statement get saved as part of metadata, if your IF statement doesn’t have same columns in select clause then at report level your sql will always returns error and you will never be able to create a report.
– In ELSE condition you can avoid having any filters (where clause), keep just select and from clause. In your IF condition have actual filters (where clause), put all filters here and assign them respective variable which will be assigned the value based on run time selection.
– Columns from ELSE condition helps in populating drop down values. When no filter is applied, ELSE gets executed and provides actual drop down values for filters.
– You can have as many filters as you want, which you can pass dynamically as variable.
– If you want to by pass the filter OR you want to select all data (passing _all_ to the filter), you need to take care below thing :
When ’ _ all _ ’ is passed you have to add an additional OR condition in your dynamic view so that when this value comes it will bypass the filter. You can refer to the below code wherein we have added one more OR condition and checking that if the filter value is selected _ all _ then_ all _ becomes equal to _ all _ and thus that filter gets bypassed.
if (check("${filter}.label" ,"travel_type"))
{
traveltypeFilter = findFilterByLabel("travel_type")
traveltypeFilterValue = traveltypeFilter.value
//You can write your business logic here.
return "select * from \"travel_details\" T1 where \"travel_type\" = "+ traveltypeFilterValue+” OR “_all_” = “+ traveltypeFilterValue;
}
else
{
return "select * from \"travel_details\""
}
– You can apply metadata security conditions on the created dynamic view the way you apply on any table or on any normal view.
– A report with dynamic view gives better performance as in normal view filter gets applied on top of whole sql (fetch all result set first and them limit it by applying filter), where as in dynamic view filter gets applied within sql and limit the data itself.