I'm trying to generate reports on an ITM by using sql directly in report studio. Since ITM tables have a WRITETIME field I need to filter with in order to filter by date. Write time is something like: 1121001000000000 where the first is century, then the last two digits of the year, then the month, then day, etc.
I can do it if I write the query like "where WRITETIME > hardcoded value" but if I want to filter it using a date prompt (in a dinamic way) I need to format one of them. Is there a way to do this? I tried something like this:
bigint( '1' || substr(cast( #prompt('Fecha Desde', 'date')# as char(10)),3,2)
|| substr(cast( #prompt('Fecha Desde', 'date')# as char(10)),6,2)
|| substr(cast( #prompt('Fecha Desde', 'date')# as char(10)),9,2) )
but it ends up failing: UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.UDA-TBL-0004 There was a Write error while processing a temporary file.RSV-SRV-0042
Any help regarding how to solve this filtering with date prompt? Thanks!!
Pinned topic ITM Reporting using SQL in Report Studio using a Date Prompt
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-12-28T18:55:36Z at 2012-12-28T18:55:36Z by Harini_Jagannathan
Harini_Jagannathan 270004KU7K2 Posts
Re: ITM Reporting using SQL in Report Studio using a Date Prompt2012-12-28T18:55:36ZThis is the accepted answer. This is the accepted answer.If you expect to create reports for the ITM OS agents, you can have a look at some of the existing reports and the data model provided out of the box with ITM. It will give you an idea of how timestamps can be formatted. Under TCR Shared dimensions, there should be a Time query subject. You should be able to find fields like "Hour", "Day", "Year", "Month", etc. and obtain WRITETIME in the format you need. In out-of-the-box reports, we usually use Standard Timestamp or Date fields to do time filtering.
- Harini Jagannathan