• 1 reply
  • Latest Post - ‏2012-12-28T18:55:36Z by Harini_Jagannathan
47 Posts

Pinned topic ITM Reporting using SQL in Report Studio using a Date Prompt

‏2012-12-27T13:23:46Z |

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:

where bigint("Linux_Process"."WRITETIME")
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!!
Updated on 2012-12-28T18:55:36Z at 2012-12-28T18:55:36Z by Harini_Jagannathan
  • Harini_Jagannathan
    2 Posts

    Re: ITM Reporting using SQL in Report Studio using a Date Prompt

    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
    Staff Software Engineer, ITM Reporting Solutions