csbac

Hi! I have the same problem (also firebird). I haven't tried with "one group only", so thanks for pointing in this direction ... I also found the following: http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=&func=view&catid=8&id=5847#5847 It seems the query/statement needs to be kept open even after "commit" ... or after fetching the last result row, so it can be reused? I'll try in this direction ... maybe someone else has an idea? Yours, Sebastian Post edited by: csbac, at: 2007/05/29 11:56

e_kannan wrote: hai! sebastian your swf presentation for, how to execute parameterized queries in iReport is superb. Hey - it's not mine, I just posted the link. I have a doubt. can you tell me, what is the difference between iReport & Jasper reports? what is the purpose of those two elements? iReport is a designer, to create reports, and to run them test-wise. Jasper reports is part of the JasperServer installation, to run these reports in production settings. Yours, Sebastian

Hi! I never used the default JS install, so I can't tell you ... maybe there is a service.bat in the tomcat installation/bin which you can use to register the service? Yours, Sebastian

Hello! JasperServer is usally deployed on a Tomcat ... and the Win32 version of tomcat offers an installation as a windows service. So, it's not JasperServer that needs to be run as service, but the Tomcat container it is deployed on. See http://tomcat.apache.org/tomcat-5.0-doc/setup.html. Yours, Sebastian

Thanks ... for the next time, I know it now ... It's Win XP with ActiveState, yes, otherwise compiling wouldn't have been such a problem ... Should have googled for the right keywords, it seems. Only found the source distribution (on CPAN). Thanx, Sebastian

Hi again! I managed to install XML::LibXML, was quite a job ... libXML2, use Visual Studio, etc. If someone needs it, I can post a guide. Now it works, just the first error message in the UI keeps popping up - obviously, it checks for the existence of XML::Lib, which does not exist, instead of checking of XML::LibXML, which does exist and is installed now. Changing the check in the UI would greatly enhance the searching for the right package ... Yours, Sebastian

Hi! I'm trying to create an XML metadata schema in JasperETL (1.1.2), on Windows. I have a lot of problems there with XML::LibXML and others. Metadata->File xml->Create, give it a name. Pressing "Next", I get the (error) message "The Perl Module XML::Lib is not installed." Unfortunately, I cannot find this module anywhere ... the only matching one is libxml-perl (http://search.cpan.org/dist/libxml-perl-0.08/). Yet even though it is installed, the error msg persists. Going on, the following happens. I can open an xml file, the structure is shown. I set one of the nodes to Xpath loop expression, and one of the sub-nodes or values to Fields to extract. No matter how simple the XML file, not matter what I select, on pressing Preview I get the message "Preview error. Some settings must be changed. Note: Preview errors are generally due to a wrong encoding setting. Output not generated." Going on anyway to the next panel. Error message: "Guess failure Output not generated." Then, I only get "At least one item must exist on Schema". So I just add the (single) schema item myself, save, set to a FileOutputXML, connect to a FileOutputDelimited. Running the stuff, I get "Can't locate XML/LibXML.pm in @INC ..." So, XML::Lib, XML/LibXML _is_ necssary; but in which package do I find it? Are the UI errors related to this, or do they have a different cause? After some searching I found XML::LibXML ... unfortunately, it uses libxml2 ... Well, I'll try to install the stuff, yet maybe someone can give me a hint how to do it? Thanks, Sebastian

Hi! How far do you get? As I explained below, the parametere needs to be defined as a Collection. Then, you should at least be able to print it in some report field (just use $P{param} as field value). Also, $P{param}.getClass() should give you the name of the class (some apache collection implementation). For converting the string list into the format needed for WHERE ... IN ($P!{param}), have a look at http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=&func=view&catid=8&id=23204#23204 Yours, Sebastian Post edited by: csbac, at: 2007/04/12 07:12

Hi! Have a look at http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=215&func=view&catid=10&id=22705#22705 about how to pass login information to a Jasper Report. It can then be used in the query as a parameter. Citing Lucian: "JS always assigns a com.jaspersoft.jasperserver.api.metadata.user.domain.User instane to the "LoggedInUser" parameter, so one would need to declare this type for the parameter and then used getters to extract the user details." Hope it helps, Sebastian

Great - I just like translators that manage to change keywords from English into German, Spanish or whatever language :blink: Just heard about a database book translated into German where "Distinct" in SELECT DISTINCT was translated ... Sebastian

See http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=215&func=view&catid=9&id=23256 Sebastian

Hi! I think you can do this if you remove/clear the first three group header sections and just put the fields of these group headers onto the one that is left ... Of course, the groups (in the query definition) need to be kept, just the section are empty. Yours, Sebastian

Hi! It might be a problem of the Java version ... maybe the format string used in the UI only works with Java 5.0 (aka JRE/JDK 1.5.0)? Mh, I can't find any difference in the documentation of MessageFormat in 1.4.2 and 1.5.0 ... Do you use any {} in text fields, except (cleanly formed) jasper report $P{} or other $X{} statements? Have a try with it, Sebastian

See http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=215&func=view&catid=9&id=23275 and http://www.jasperforge.org/images/stories/Documentation/ireports/swf/Parameters_viewlet_swf.htm about using parameters. Yours, Sebastian

Hi! The fields you are adding are string fields. To add the numeric value, you need to convert them to integers first, by using Java means. The best way would be to make sure the fields themselves are integers in the database, and are thus assigned to Integer objects. In the case of parameters, set their type to java.lang.Integer. Then, using $P{field1}+$P{field2} should give a class cast exception, as the result is a Integer, and is cast to a String for printing; using ""+($P{field1}+$P{field}) should print the summed value. If you cannot make the fields into Integer, you have to convert them before adding ... ""+(Integer.parseInt($P{field1})+Integer.parseInt($P{field2})) Sebastian

Hi! They way you are creating the border, the white space is expected behaviour ... Can't you just add a horizontal line to the bottom of the page header section, another one to the top of the page footer, and two vertical lines to the right and left of the detail section? Make the vertical lines just as long as your detail section; they should be stretched automatically when needed. Hope this works, haven't tried it ... Yours, Sebastian

Hi! Have a look at http://www.jasperforge.org/images/stories/Documentation/ireports/swf/Parameters_viewlet_swf.htm It describes how to setup parameters in iReport. They can then be set from the Java calling app. Yours, Sebastian

Which database are you using? Sebastian

Hi! This is a general aspect of Java. If you have to Strings y1 = new String("Y"); y2 = new String("Y"); y1==y2 is false, as the object references are compared. y1.equals(y2) is true, as the objects' content is compared. Thus, even using constant strings on one side, you always have to use equals (or equalsIgnoreCase) with strings. Otherwise, you get unexpected "is not equal"s. Thus, new Boolean(!($F{SFPAR_GRADE_EXEMPT_FLG}.equals("Y"))) or new Boolean(!($F{SFPAR_GRADE_EXEMPT_FLG}.equalsIgnoreCase("Y"))) is correct. By the way, using N or Y for boolean database fields is not a good custom. Try to use short integers (smallint, int, etc.), with the values "0" for false and "1" (or any other numeric value) for true. You get much less problems when mapping DB fields to variables, then. Yours, Sebastian

Hi! I use a second parameter which is calculated from the first one ... The first on is a Collection of Integers that are entered by the user via an input control (SQL Multi-Select), called MultiTaskInput. Type is java.util.Collection. It has default value of Code: Arrays.asList(new Integer[] { Integer.valueOf(1), Integer.valueOf(2) }) The other parameter is of type String and calculated (defaultValueExpression) via Code:[code] $P{MultiTaskInput}.toString().replaceAll("[\[\]]", ""«») (there are two backslashes before the [ and two again before the ]). This is for integer, of course ... in case of Strings, the following might work (I can't test it now): Code:[code] $P{MultiTaskInput}.toString().replaceAll("[\[\]]", "'"«»).replaceAll(", ", "', '"«»); (replace the leading [ and trailing ] with ', and all , with ', ' - not very robust, of course, so it can only be done if one can rely on the string values provided (no direct user entry). On the other hand, in SQL, using IDs (integer) and a lookup-table for this kind of select is usually cleaner. Hope this helps, Sebastian Post edited by: csbac, at: 2007/03/29 05:02

Hi! The beginning of a Schema file has to look Code: <?xml version="1.0"?> <!DOCTYPE mondrian.dtd> <Schema name="SchemaName"> <Cube name="CubeName"> <Table .... You're probably missing the name="SchemaName" attribute tag ... This is about the second error message. Though I have to admit I have no idea what tool you "create a schema and save it as a .data file" with ... I only know writing the mondrian schema .xml by hand, probably you're using the Professional version? Hope this helps you, Sebastian

Hello! I already got JS up and running on a FirebirdSQL database (the meta-data repository). Integrating them into reports was no problem. But, we want to use JasperETL to consolidate our existing databases ... which are in FirebirdSQL, of course. Before I start looking into JasperETL and how to define another DB driver to read the schema and the data, maybe someone can give me a hint whether this is a "put the lib into the path, add a statement to some config file" problem, or a "implement complete database-specific wrapper code" kind of problem?[/ul] There is also the problem of the Perl drivers ... there is a DBD::Interbase at sourceforge, but it surely has to be integrated into the JasperETL configs as well. For the moment, we are using ODBC, and (on windows) it works without problems. Still, direct access would be nicer ... Maybe someone was already there and can give me a hint ... otherwise, I'll just see what I can do. Thanks in advance, Sebastian

Hello, out there, as the title says, I managed to deploy JasperServer on a FirebirdSQL database. This refers to the jasperserver meta-data, not the sugarcrm/foodmart sample data. There are many reasons to use or not to use MySQL; mainly, we did not want to deploy, backup and maintain another database server in our project. Short Overview A short overview on what I had to do: 1. patch the ddl script to work with FB data types 2. change the column names "password" and "value" to non-reserved words 3. adjust the createDefaultSecurity-script 4. patch two of the hbm.xml-files to use the new column namens 5. configure the data source for FB, including the hibernate dialect 6. set up the server I used FirebirdSQL 2.0, though I'm quite sure it will also work with 1.5.x, as I will find out in a few days. The JasperServer is 1.2.0. The installation has been done on JBoss 4.0.4, not Tomcat; this should not change anything important in this description. Afterwards, importing the "default" repository, and my own extended one, did work, so does running analyses and reports. The long version 1. Patch the ddl types in jasperserverCreate.ddl longblob, mediumblob, tinyblob -> blob text -> blob sub_type text tinyint -> smallint bit -> numeric(1) datetime -> timestamp bigint(#) -> numeric(#) smallint(#) -> smallint removed all "null" (of course, "not null"s were kept) removed all auto_increment (auto_increment seems only to be used for the createDefaultSecurity?) removed the Type=InnoDB statements removed the index (...) statements from create table removed the add index FK... (...) statements alter table (the indices are still created, as they are used as foreign keys) 2. Change reserved words The words "password", "value" and "message" are reserved in FB and thus not allowed for column names. (Strange - I remember "value" being rejected as column name in MySQL3 as well). They could be used and reference as "PASSWORD", etc., yet I did not find out how to configure this in the .hbm.xml files (xml does not like 'backslash"') Thus, I renamed password -> passwor (JIUser, JIJdbcDatasource, JIXMLAConnection) value -> valu (JIListOfValuesItem) message -> messag (JILogEvent) (The "messag" I haven't completed yet, no log events have been needed). After (1.)&(2.), the jasperserverCreate.ddl runs through smoothly. I know it's not a good thing to change column names for a widely distributed project, yet I don't know any other solution if reserved words from at least on existing rdbms are used ... Also, hibernate requires a generator hibernate_sequence. Because of the insert statements in (3.), I made it to start with 100. create generator hibernate_sequence; set generator hibernate_sequence to 100; 3. adjust jasperserverCreateDefaultSecurity.sql Removed all backticks from `identifier`s. The same column name changes had to be done here as well. Added "id" column name and value(s) to all insert statements that used one of the former auto_increment pk's. I did not find any need to define triggers on the auto_inc, as hibernate uses a different mechanism anyway. Split the first insert statement into 3. Changed "now()" to current_timestamp (no ()) 4. Patch RepoUser.hbm.xml and RepoResource.hbm.xml I have to admit I was too lazy to compile from the source code; thus, I just unzipped jasperserver-repository-hibernate-1.2.0.jar, patched the files, and re-jarred. Luckily, JS does not use annotations (yet?). com/jaspersoft/jasperserver/api/metadata/user/domain/impl/hibernate/RepoUser.hbm.xml in JIUser, change column="password" to column="passwor". com/jaspersoft/jasperserver/api/metadata/common/service/impl/hibernate/persistent/RepoResource.hbm.xml in JIJdbcDatasource and JIXMLAConnection, also changed to column="passwor" in JIListOfValuesItem, added column="valu" to the name="value" entry. As exercise to the reader: change the hbm.xml that contains JILogEvent.message. Re-created the .jar from the patched files. 5. data source for JS on FB Datasource-ds.xml for JBoss: Code:<datasources> <local-tx-datasource> <jndi-name>jasperserver</jndi-name> <datasource-mapping>Firebird</datasource-mapping> <connection-url>jdbc:firebirdsql:host/3050:database</connection-url> <driver-class>org.firebirdsql.jdbc.FBDriver</driver-class> <user-name>user</user-name> <password>password</password> </local-tx-datasource> </datasources> Changed WEB-INF/applicationContext.xml, in hibernateProperties <prop key="hibernate.dialect">org.hibernate.dialect.FirebirdDialect</prop> instead of MySQLDialect 6. Server Setup Put firebirdsql.jar (JayBird) into the server-lib. Deployed jasperserver.war (as directory). Worked (well, not on the first try ;-)) To import an existing repository (e.g., the sample one distributed with the server), the patched jasperserver-repository-hibernate-1.2.0.jar needs to be placed into scripts/ji-export-util/lib. Also, firebirdsql-full.jar needs to be put there. (If the "non-full" JayBird is used, an exception will be thrown that the ResourceException class can't be found). ji-export-util/js.jdbc.properties needs to be modified accordingly (see (5.)). Important is (again) metadata.hibernate.dialect=org.hibernate.dialect.FirebirdDialect Then, the import (and export) can be done as usual. That's it, I believe. I hope I did not forget to document any of my steps. Otherwise, just ask. Yours, Sebastian

Also, you neeed to delete the log4j.properties in the WEB-INF. Otherwise, it just hung on deployment ... Yours, Sebastian

Hi! One of the JS samples does this ... SalesByMonth. Accesses $P{LoggedInUser}.getFullName(). LoggedInUser is defined as a com.jaspersoft.jasperserver.api.metadata.user.domain.User, "Use in prompt". There is no corresponding input control; thus, I presume JS just knows from the class type what to assign to it? Yours, Sebastian