lpeel

Old post but still very helpful. It was working on Windows under SpringSource toolkit but when i went to deploy it on Linux Fedora, it failed with the same error. In my case, it was picking up an old jasper 2.x jar that came with SpringSource. Getting rid of that old jar fixed the problem. I had to add a few other Apache jar dependencies as documented under Maven, but that was trivial. SpringSource had been able to resolve those dependencies under Windows.

I just solved a problem where a simple report was taking 8 to 10 minutes to display 4000 rows of data! It now takes 10 seconds! All I did was uncheck the "ignore pagination" option at IReport report properties in the jrxml. My Jasperserver has been running fine against a MySQL database for quite some time now with no issues. However, I just created a new report that runs against a SQL Server 2005 database that sits on another box. I assumed that the huge performance issues had something to do with the network, database driver, connection, packet size, on and on. I spent 3 days trying to get this thing to run with decent performance against the new SQL Server datasource. And as it turned out, the only issue was that I had that one property checked for that report, guess I copied from some old template. As Harry Caray used to say, "Holy Cow"!

Yes, same scenario only I'm using 2005 versus 2008. But that should not matter. With a local connection your connection string should probably just have localhost in it. Here is everything I ended up having to do to connect to SQL Server. Surely one or more of these must be causing your connection failure. (1) port 1433 TCP/IP was not opened on the remote SQL Server. Adjusted firewall to allow it. (2) In my case it was necessary to specify a named instance as part of the connection string. (3) I had set up login credentials incorrectly on the remote SQL Server database. I had it set up as using Windows Authentication instead of SQL Server authentication. Add a new user account to your SQL server database. Don't use administrator account. Just a simple user (myuser) and then make sure that user has SQL Server authentication - not windows authentication. (4) I had to make sure that the login credential had public and read access to tables (5) Microsoft wouldn't accept my workgroup name so I just used the server's IP address in the connection string. If your server is in a domain and uses DNS, you probably won't run into this issue. This server was not in a domain. (6) The database itself was only set up to use windows authentication. I had to change a property at the database level to enable access by both SQL Server authentication and Windows authentication. (7) And of course restart SQL Server services so that the new settings would take. (8) I had sqljdbc4.jar in the wrong place. It had to be under the lib directory of Apache Tomcat. NOT under the lib under webapps. Put it under the very first lib you see as you expand tomcat root. I'm using sqljdbc4.jar for the JDBC driver. This is the driver to use if you have jdk 1.6 installed on the server. You have to use a different JDBC driver if you have an earlier version of jdk. Make sure there are not 2 conflicting jdks on your server. After I finally got a sucessful connection using Microsoft to Microsoft products, I created a SQL Server connection string like the one below that connected. It defaulted to port 1433 and it worked. Named instance: jdbc:sqlserver://myServerNameOrIpAddress\myNamedInstance;databaseName=myDatabaseName; No Named instance: jdbc:sqlserver://myServerNameOrIpAddress;databaseName=myDatabaseName; Post Edited by lhp at 07/12/2011 14:22

Its not a problem if they are installed on the same machine or different machines. What matters is if you are using Windows authentication or SQL Server authentication. Which one are you using? Are you trying to connect from a client to the Windows Home server? Or are you already logged into the Windows Home server and trying to connect to the SQL Server DB? All my notes are related to using SQL Server authentication. I will share in next post. My notes are not relavant to Windows authentication so I will hold off until you let me know.

Better late than never. Just saw your post and am adding comments for the benefit of others. I had a lot of things I had to do to get connection to work. I first verified that I had connectivity to a remote server using SQL Server Management Studio Express which is free. Once that worked, I created a SQL Server datasource in Jasperserver repository and connected. I used SQL Server Management Studio to identify several problems I had: (1) port 1433 TCP/IP was not opened on the remote SQL Server. Adjusted firewall to allow it. (2) In my case it was necessary to specify a named instance as part of the connection string (3) I had set up login credentials incorrectly on the remote SQL Server database. I had it set up as using Windows Authentication instead of SQL Server authentication. (4) I had to make sure that the login credential had public and read access to tables (5) Microsoft wouldn't accept my workgroup name so I just used the server's IP address. If your server is in a domain and uses DNS, you probably won't run into this issue. This server was not in a domain. (6) The database itself was only set up to use windows authentication. I had to change a property at the database level to enable access by both SQL Server authentication and Windows authentication. (7) And of course restart SQL Server services so that the new settings would take. (8) I had sqljdbc4.jar in the wrong place. It had to be under the lib directory of Apache Tomcat. After I finally got a sucessful connection using Microsoft to Microsoft products, I created a SQL Server connection string like the one below that connected. It defaulted to port 1433 and it worked. jdbc:sqlserver://myServerNameOrIpAddress\myNamedInstance;databaseName=myDatabaseName;

I'm also connecting to a remote SQL Server (2005) datasource for the first time using Jasperserver. I finally got the connection to work but it was lots of trouble. Is Ireport installed on a Windows machine? Is the Jasperserver also installed on a Windows machine? This may make a difference. I had all kinds of trouble with SQL Server authentication versus Windows Authentication. I can finally connect to SQL Server 2005 via a datasource from both Windows laptop and Linux Fedora machine. However, I am now trying to figure out the cause of error when it executes a query. I'm getting "com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server supports holdability at the connection level only. ". This error did not occur when connecting from Jasperserver 4.01 on my Windows Laptop. The error happens in Linux Jasperserver 4.01. Thats why I ask about which environment IReport is installed in and which environment Jasperserver is installed in. I will open a different thread on my problem if I can't figure it out within a few days. *** 07/11/11 *** My issue above was caused by a setting In jasperreports.properties: net.sf.jasperreports.jdbc.holdability=close I had configured my laptop Jasperserver slightly differently than the production Linux one. Thats why it worked in one environment but not another. MS SQL Server won't allow a query to execute with the holdability setting set to close. It throws an exception with message "SQL Server supports holdability at the connection level only, use the connection.setHoldability() method". Once I commented out the holdability and let it default, everything worked fine. Post Edited by lhp at 07/11/2011 19:14

Hi I also want to thank you for your research into this issue. I established a production jasperserver environment with only a few dozen reports that run at various intervals (daily, weekly ...). I didn't think I had to babysit them, but just now, was astonished to find that several of my dailys have been skipping certain days rather randomly. No errors in the log. All the data is present. The scheduler says last run date was the day before yesterday. Just now I had to run a few of my scheduled reports manually. I will implement your solution and keep an eye on it for a few weeks.

I had the same scenario and ended up implementing a custom solution using Java and SQL to change the contents of the Jasperserver database. The code executes on a cron schedule. The data is always for a prior period of time just like in your scenario. There were 2 goals: 1. dynamically change the input parameter date in preparation for the next run 2. Rename the report output with an appropriate timestamp. //First I selected the reports I was interested in into a collection select id, base_output_name from jireportjob where base_output_name like #baseOutputName# //Depending on the type of report (weekly, monthly, daily) I programatically //determined which date I wanted and appended it to the existing report name update jireportjob set base_output_name = #baseOutputName# where id = #id# //I then used the ids from the collection above to get the parameters for the report: select job_id, parameter_name, parameter_value from jireportjobparameter where job_id = #jobId# (this is from the above collection) and parameter_name = #parameterName# //Then I updated the parameter to it's next date value update jireportjobparameter set parameter_value = #parameterValue# where job_id = #jobId# and parameter_name = #parameterName#

I finally found it and am sharing for others benefit. Its in applicationContext-search.xml under Initial State Resolvers. I changed the sortBy property from "name" to "date". <!-- Initial states resolvers --> <bean id="searchInitialStateResolver" class="com.jaspersoft.jasperserver.search.state.impl.StateImpl"> <property name="text" value=""/> <property name="sortBy" value="date"/> <property name="folderUri" value="/"/> <property name="customFiltersMap"> <util:map> <entry key="accessTypeFilter" value="accessTypeFilter-all"/> <entry key="timeFilter" value="timeFilter-anyTime"/> <entry key="scheduleFilter" value="scheduleFilter-anySchedule"/> <entry key="resourceTypeFilter" value="resourceTypeFilter-all"/> </util:map> </property> <property name="resultIndex" value="0"/> <property name="resultsCount" value="10"/> </bean> <bean id="browseInitialStateResolver" class="com.jaspersoft.jasperserver.search.state.impl.StateImpl"> <property name="text" value=""/> <property name="sortBy" value="date"/> <property name="folderUri" value="/"/> <property name="customFiltersMap"> <util:map> <entry key="accessTypeFilter" value="accessTypeFilter-all"/> <entry key="timeFilter" value="timeFilter-anyTime"/> <entry key="scheduleFilter" value="scheduleFilter-anySchedule"/> <entry key="resourceTypeFilter" value="resourceTypeFilter-all"/> </util:map> </property> <property name="resultIndex" value="0"/> <property name="resultsCount" value="10"/> </bean>

I've looked on the web and the application but cannot find a way to make the application sort by Modified Date. I have a list of reports which are generated daily and I want the most current report to automatically appear first. Currently the user has to click "Sort by Modified" or scroll way down to bring the most current report to the top. I looked in applicationContext-search.xml and see these 2 sorters but am unable to find a way to make the application use ByDateSorter as the default. <!-- Custom sorters --> <util:list id="customSorters"> <bean class="com.jaspersoft.jasperserver.search.common.CustomSorter"> <property name="id" value="date"/> <property name="labelId" value="SEARCH_SORT_BY_DATE"/> <property name="searchSorter"> <bean class="com.jaspersoft.jasperserver.search.sorter.ByDateSorter"/> </property> <property name="roleAccessList" ref="${bean.byDateSorter.RoleAccessList}"/> </bean> <bean class="com.jaspersoft.jasperserver.search.common.CustomSorter"> <property name="id" value="name"/> <property name="labelId" value="SEARCH_SORT_BY_NAME"/> <property name="searchSorter"> <bean class="com.jaspersoft.jasperserver.search.sorter.ByLabelSorter"/> </property> <property name="roleAccessList" ref="${bean.byNameSorter.RoleAccessList}"/> </bean>

I would like to see this implemented as well. My end users should not see developer components but if I try to hide them, the users cannot execute the report.

I've noticed that too. What I usually do is compile the report, then replace the actual jrxml document. If I try to replace the jrxml document within a report run unit, it won't work. I'm doing my update directly from IReport repository view though, so not sure if this will help.

What happens if you change the language from Groovy to Java? I ran into a scenario where Groovy was the cause of a problem when I was evaluating against a null numeric field.

Hi, Just ran into this and posted my work around. Look for Line chart Ireport bug post dated 05/05/2011. The line not only breaks, but legitimate values that should be there are sometimes not displayed at all!

Hi, I came across what appears to be an IReport 4.01 bug. To prove that it is a bug, I created as simple of a test chart as possible and a test database table, see attached. My test chart displays total fees by day by type of pet that a vetenarian sees. There are 7 days of data numbered 01 thru 07. There are 4 types of pets, cat, dog, bird, ferret. On days 1 and 2, the vetenarian sees at least 1 of each type of pet. On day 3, he only sees a bird and a cat. On day 4, he sees a bird, dog, cat, & ferret. However, the linechart misses the value for ferret entirely even though it is there. On day 5 and 6, he only sees cats and dogs. On day 7 he sees at least 1 dog, cat, bird and ferret. However, on day 7 the line chart will not display value expression (total fees) for bird and ferret. Please confirm that this is indeed an Ireport bug. Post Edited by lhp at 05/05/2011 16:22 The only work-around I know of is to develop a "fake" reference table that can be used as part of a left outer join. The reference table2 has every combination of day and pet. However the problem still persists if the totalFees is null. So, by setting totalFees to 0 if it is null will cause all the values to display on the line chart like they should. This is not an optimal solution and not everyone will have the flexability I have to create place-holder database tables to represent missing data points. I appreciate any alternative solutions. SELECT DATE_FORMAT(DATE(table2.date_seen),'%d') AS day_seen,table2.pet_type, IFNULL(SUM(fee),0) AS totalFees FROM table2 LEFT JOIN table1 ON table2.pet_type = table1.pet_type AND table2.date_seen = DATE(table1.date_seen) GROUP BY DATE(table2.date_seen), table2.pet_type ORDER BY DATE(table2.date_seen),table2.pet_type Post Edited by lhp at 05/05/2011 17:38 Post Edited by lhp at 05/05/2011 17:46 Post Edited by lhp at 05/05/2011 18:44 Post Edited by lhp at 05/05/2011 18:49