ReportServer Forum

monteillin

Member

Registered: 2020-07-16

Can not create date filter in Dynamic List

I have a classic date filter report use case where I have a dynamic list report and I want to filter a specific date/time column for all dates from the last week, ie: current day - 6. I can then schedule this report to run weekly and it would always return the latest week's data. The specific column I want to filter on is showing as a TIMESTAMP type in the "Configure List" view. I should mention that, if I hard code a date range in the query itself, it works fine, eg: WHERE column > '2020-07-17' or WHERE column BETWEEN ' 2020-07-17' AND '2020-07-23'. However, I have been unable to set up this filter in either the column filter or pre-filter section.

Based on your documentation, the proper way to do this is set the INCLUDE RANGE to FROM ${today.addDays(-6)} TO ${today}. However, when I tried this I get:

"Query could not be executed: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: No matching signature for operator IN for argument types DATETIME and {DATE} at [50:43]"

I've tried various iterations, including adding format() to the code but can't get it to work. Also, even if I pick one of the existing values from the returned value list, and search for that discreet value, I get the same error.

Any idea what's going on? this is pretty basic functionality for automated reports. Thanks

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Can not create date filter in Dynamic List

Hi monteillin,

can you please post some screenshots of a basic testcase, so we can reproduce the problem ? Please make the testcase as simple as possible, so delete anything unnecessary (unnecessary columns, unnecessary filters, etc).
You can use http://imgbb.com for the screenshots.

Regards,
Eduardo

monteillin

Member

Registered: 2020-07-16

Re: Can not create date filter in Dynamic List

Here's the SQL in the Dynamic List

/*<rs:cte>*/

with NA as
( select pc.Serial_Number, i.full_name Item, c.FULL_NAME Customer
  from DB.parcel_content pc
  inner join DB.items i on pc.ITEM_ID = i.ITEM_ID
  inner join DB.parcel p on pc.Parcel_id = p.PARCEL_ID
  inner join DB.transactions t on p.SALES_ORDER_ID = t.transaction_id
  inner join DB.customers c on t.entity_ID = c.CUSTOMER_ID
  where i.full_name in (……..)
  )
 
/*</rs:cte>*/

select    t.Identifier
,         n.Item
,         n.Customer
,         ut.username
,         a.accountid
,         t.Registered
,         ts.firstconnected
from       DB2.devices t
inner join DB2.device_state ts on t.Identifier = ts.identifier
inner join NA n on t.Identifier = n.Serial_Number
left join  DB2.user_device ut on t.Identifier = ut.Identifier
left join  DB2.accounts a on ut.username = a.username

here are the screenshots

https://ibb.co/tJtVCQ5
https://ibb.co/Ypsq51F
https://ibb.co/Zh7GtL8

Thanks in advance for all your help!

monteillin

Member

Registered: 2020-07-16

Re: Can not create date filter in Dynamic List

I thought it may be due to the TIMESTAMP type of the firstconnected column, so I thought about creating a computed column called firstconnecteddate and convert it to a DATE type. I tried using the code: TO_DATE(firstconnected) both with or without the additional format parameter, ie:'yyyy-MM-dd' but for some reason, it just keeps spinning "Validating input" forever.

monteillin

Member

Registered: 2020-07-16

Re: Can not create date filter in Dynamic List

Ok I got it to work.. but I had to convert the ts.firstconnected field (which is listed as a TIMESTAMP) in the SQL to a date type by encapsulating it in DATE(). Once I do that, I can apply the same dynamic date range to it and it works fine.  Strange that I can't do that post-SQL within ReportServer.

Last edited by monteillin (2020-07-28 17:22:22)