xefil

Hi to all! I've a problem creating a report based on a table I have. It looks like this: mysql> show create table host_historyG *************************** 1. row *************************** Table: host_history Create Table: CREATE TABLE `host_history` ( `entry_datetime` datetime NOT NULL, `host` int(11) NOT NULL, `status` enum('UP','DOWN','UNREACHABLE') default NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 <<skipped some rows and KEYS>>> This table contains records like, in example: +---------------------+------+--------+ | entry_datetime | host | status | +---------------------+------+--------+ | 2014-05-04 05:59:24 | 8123 | DOWN | | 2014-05-04 07:02:16 | 8123 | UP | | 2014-05-04 08:26:50 | 8123 | DOWN | | 2014-05-04 08:27:14 | 8123 | UP | +---------------------+------+--------+ I need to create a query that calculate the percent this host number 8123 was in DOWN state and in UP state, assuming the initial state is UP. In example timerange of give example is 24 hours. DOWN status at 05:59:24, means it was up from 00:00:00 to 05:59:24 = 21564 seconds then, it was DOWN between 05:59:24 and 07:02:16 = 3772 seconds then, it was UP between 07:02:16 and 08:26:50 = 5074 seconds then, it was DOWN between 08:26:50 and 08:27:14 = 24 seconds at the end, assume it was up until end date, because the last state is UP, until 2014-05-04 23:59:59 = 55965 seconds Summing up: DOWN for: 3796 seconds UP for: 82603 seconds UP+DOWN = 86399 seconds (one second missing by 23:59:59) = one day. UP=95.6% DOWN=4.5% Is there a way to obtain these precents in a SQL Query? If so, how? I could in case create a PHP page that calculate these values but how in case tell JasperReports Server these values? A sort of external Data Source based on PHP page. I could image I select a report in JasperServer, then choose a host from a dropdown menu which contains a list of hosts and their id-values. In my example hostA with id '8123'. Then I can choose startdate and enddate and press SUBMIT. These three values are sent to a php page which returns to JasperServer the UP/DOWN values. At the end, JasperServer will draw these values. Is this second approach possible? How in case? Thanks a lot! Simon

Hello! Well, formatting the date in the SQL statement should solve my problem for the output on the Jasper Report. BTW I've just noticed I can select into iReport the output field and into properties select the pattern to apply. This works and this is referred to the output of the date printed on the report. This solves the problem of printing the date on the report in a different format. I still have a problem for formatting the date in the input control. As suggested by ajinkya_c, I've defined the input control locally and not on the server: Properties of the selected input control -> "Input Control Details" --- Type "Single Value" --- "Locate(Datatype) -> "Locally Defined" -> "Edit Local Resource (Datatype)" On General Tab nothing special to notice. On "Data Type Details" TAB: --- Type: Date --- Pattern: ??????????? I then run the report and I have my nice calendar to choose dates. BTW selecting the date, the field is filled in this format: "mm/dd/yyyy" and I need to format it into "yyyy-mm-dd" to validate the field. I've tried it manually changing the field and it works. How can I modify the return format of the calendar? I've tried editing the pattern above setting it to "yyyy-mm-dd" but it's ignored. Do I need a special syntax to setup the pattern? Thank's for the support! Simon NOTE: I would avoid using MySQL function DATE_FORMAT in WHERE clause because, AFAIK it converts all dates in string and then makes the comparsion. This would not an optimized query. I've a very large DB. Post Edited by xefil at 09/10/2012 08:27

Hello! I'm using some imput control to make a query on a Database. Then I print the output of the date simple like follow: $P{date_to} This creates a date based I think on local timezone or whatever. Can I format this field like ("yyyy-mm-dd")? How? Thanks a lot! Simon

Hello! Thank you for your answer. I cannot apply totally your workaround. Let me explain what I've done. I'm working on the JasperServer editing an existing report using iReport connected directly to the JasperServer's repository. My goal is to let persons execute the reports from within the jasperserver java web GUI, *not* using iReport. My report asks the user on the Jasperserver's Tomcat page by a popup about start and stop dates. These are not be accepted anymore, with the error "Specify a valid value for Date.". As descrived by you I've changed the Input Control. I've created on the server a new input control as follow: Type: TEXT Name: date_text Description: empty Format: (date.getYear( )+1900)+"-"+(date.getMonth( )+1)+"-"+date.getDate( ) First of all, saving the above Input Control the "format" field is cutted to "(date.getYear( )+1900)+"... hmmm... Then, changing the input control to the report I obtain two results: 1. The popup doesn't ask for dates using a nice calendar to choose dats, because now it's a text field. This is not so good. 2. Even if I've insert the format of the datatype ((date.getYear( )+1900)+...) the field is still empty I know the point one is a normal result because I've changed the input type. But why I cannot obtain the suggested precompiled dates in the fields? BTW this workaround let me execute the report. It's a step forward :) Thank's for answers and support.

Found a solution? I'm still blocked :(

bump! Sorry, It would be enough if someone could at least point me what look for. I've all web reports unavailable. Can use them only vie iReport.

(pls don't ask me why I was not able to write the msg out from the code tagging, I think it's a browser issue, sorry).

Code:Hello to all! I've a little issue after upgrading JasperServer from version 4.5 to verion 4.7. The first problem was the update. case sensitive tables on mysql wasn't so easy to update, btw I've done it succesfully. Then, via iReport I can browse the Jasperserver repository and execute reports well, without any issue. When I try to execute the same report on the JasperServer it fails always to validate data fields. My report asks for a list of options in drop down window (made by a query) and this works. Then it asks for start and stop date. Using the calendar I can specify a date but then on SUBMIT I obtain always the following error: "Specify a valid value for Date." The Input contro is made using a datatype created on the server and via iReport I've choosen to use it (via repository). The datatype has nothing special set. How can I troubleshoot/debug this problem? Thank's a lot! Simon