You are not logged in.
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
Offline
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
Offline
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!
Offline
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.
Offline
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)
Offline