Using Filter Conditions in SQL Queries for Reports

Posted by Rollbase User on 03-Mar-2010 11:38

When generating a report, I would like to use the filter conditions in an SQL query. Is this possible? For example, in the Chp 6 - Server Side documentation you give the example: Example of HTML report which displays total number of users in the system: Total users: #EVAL[ rbv_api.selectValue("SELECT COUNT(1) FROM USER") ] What if I wanted to get the count of Users that were created between the dates selected in the filter? I've tried: #EVAL[rbv_api.selectValue("SELECT COUNT(1) FROM USER WHERE createdAt BETWEEN '" + "{!#FILTER}".substring(44,48) + "-" + "{!#FILTER}".substring(38,40) + "-" + "{!#FILTER}".substring(41,43) + "' AND '" + "{!#FILTER}".substring(94,98) + "-" + "{!#FILTER}".substring(88,90) + "-" + "{!#FILTER}".substring(91,93) + "'")] and tried nested EVALs: #EVAL[rbv_api.selectValue("SELECT COUNT(1) FROM USER WHERE createdAt BETWEEN '" + EVAL["{!#FILTER}".substring(44,48)] + "-" + EVAL["{!#FILTER}".substr

Rollbase - Forum

All Replies

Posted by Admin on 03-Mar-2010 13:11

It looks like you're extracting portion of report's filter and trying to use it to run SELECT query (which is cool, BTW). To use given string as SQL Date please try MySQL function



STR_TO_DATE(str,format)

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

The second example will not work since we do not support EVAL[] blocks inside EVAL[].

However you should be able to use more than one JavaScript operator inside single EVAL[] In this case add return statement at the end.

Posted by Admin on 03-Mar-2010 13:14

I just found the real problem: field license__ cannot be resolved (either does not exist or does not have DB column). Please fix this issue first.

Posted by Admin on 03-Mar-2010 14:58

Pavel,

You're right, I am extracting the start/end dates from the filter. I tried the STR_TO_DATE MySQL function you suggested, but it's also not working.

The problem appears to be that template tokens (in this case: {!#FILTER}) are not being translated until after the EVAL executes. This may be the expected behavior?


#EVAL[ var filter= "{!#FILTER}"; return filter; ]

After Running the Report, Output:
License Date greater or equal 02/01/2010 AND License Date less than 02/28/2010


#EVAL[ var filter= "{!#FILTER}"; return filter.substring(0,10); ]

After Running the Report, Output:
{!#FILTER}

Posted by Admin on 23-Mar-2010 09:15

I need to obtain the first and last (min/max) license numbers from a given date range specified by the user in the Filter fields for a monthly report.

Since it appears that I can not use data from the filter fields to dynamically generate a SQL query, do you have any other suggestions of how I could go about this?

Thanks,
Mike

Posted by Admin on 23-Mar-2010 12:41

This should work now, please try again. FILTER token should be resolved.

Posted by Admin on 15-Apr-2010 08:51

Thanks, works like a charm.

This thread is closed