Using relative dates in a domain pre-filter

[maddesso]

I have defined a domain that joins a few tables, and I'd like to limit the result set by applying a pre-filter that will only return recent (last 30 days) of records. I am using SQL server, so the SQL syntax would be:

  where MyTable.DateTimeField > getdate() - 30

If I add a pre-filter using an absolute date, I see the following in the exported domain XML:

      <filterString>MyTable.DateTimeField > ts'2014-11-11'</filterString>

 

And this works fine. How can I change the absolute day to TODAY - 30? I have tried many combinations. I have looked at the semanticlayer.xml, but nothing seems to work. Any help would be grately appreciated!

 

-Mark

 

 

 

[calculate.machine]

Seen from your description, I guess you want to join tables from different datasources and then filter the result. If no solution can be figured out with virtual data source, you can try esProc.

 

For example: There is table sales in MySQL and table emp in MSSQL. You are asked to join the two tables, with the condition that the result only includes the orders in the last 30 days or those that belong to certain specified departments (like Marketing and Finance). esProc code for doing this is as follows:

A1=myDB1.query("select * from sales")     // Retrieve data from MySQL

A2=myDB2.query("select * from emp")      // Retrieve data from MSSQL

A3=A1.switch(SellerId,A2:EId)                  //Join tables together

A4=A3.select(OrderDate>=after(date(now()),days*-1)|| depts.array().pos(SellerId.Dept))    // Filter the result of join

 

Jasper can connect esProc using JDBC. It calls the esProc script using the same method as the one with which it calls the stored procedure. See http://blog.raqsoft.com/?p=2546 for more information.