SeanDay

We have run the report both in iReports and on Jasper Server and both are slow, we think it may to be linked to the fact the query uses a database link. As we only wanted to provide the users with an option of one or all in the parameter first we re-wrote the likes from: WHERE value LIKE param to: WHERE (param = '%' OR value = param) However, although this appeared to fix the problem on iReports when we uploaded to Jasper Server the query started running very slow again. I ran a trace on the Oracle sessions and the issue appears to be related to the database links (specifically the "SQL*Net message to dblink" and "SQL*Net message from dblink" events. When the query is run in SQL Plus/Toad the session had 834 waits a total of 0.55 seconds. When run in iReports/Jasper the session had 1556893 waits a total of 508.29 seconds... We then created a view in the database that looked at the database link and put this view into the iReport query and the query now appears to be fine on both iReports and Jasper Server.. Never really got to the bottom of what was happening but we have a solution that works. Thanks, Sean

Thanks for the suggestion, I was running the query in both Toad and SQL Plus. I have downloaded SQuirreL and using the same jdbc driver as I use for iReport the query works and returns the data in less than 2 seconds. Unfortunately this looks like an iReport/Jasper Report issue. One thing I noticed when capturing the bind variables in Oracle was that iReport appeared to run the query twice (once using the default values setup on the parameters then a second time using the values I typed in). I cannot see what else is going on in the DB though as the query passed to Oracle looks the same the execution plan is the same and bind variables look OK. I removed the default values from the parameters and the query only runs once but still takes about 8 minutes.. This is very odd behaviour but I have seen it before with another iReport we just removed one of the like clauses (making it an equal) and the query was OK. Fortunately it did not require a like in that case. At least I have discovered SQuirreL which looks pretty impressive. Sean

I have an odd performance problem with an iReport, when I run the query directly on Oracle in sql the results are returned in < 1 second. When I run it in iReports it takes about 8 minutes.. I have found that if I remove one of the LIKE clauses from the SQL below (making it an equals) then the iReport runs in a couple of seconds. It does not appear to matter which LIKE I remove. Has anyone seen anything similar before? I have run out of ideas as to what may be causing this.. Thanks, Sean Code:SELECT DISTINCT stu.stu_code, stu.stu_fnm1, stu.stu_surn, stu.stu_cad1 ||' '|| stu.stu_cad2 ||' '|| stu.stu_cad4 CONTACT_ADDRESS, stu.stu_capc, awf.dwh_admin_pwy_jn subject_code, pwy.pwy_name, awf.attendance_mode, awf.enrolment_status, decode(substr(awf.fee_status,1,1),'H','Home','O','Overseas','E','European', substr(awf.fee_status,1,1)) fee_status, amsl.description, amsl.study_location_key FROM attnd_mon_students mon, attendance_monitor_wkly_fact awf, attnd_mon_study_location amsl, ins_stu@delta_link.uel.ac.uk stu, ins_pwy@delta_link.uel.ac.uk pwy WHERE EXISTS (SELECT student_code FROM attnd_rep_wkly_3non_attnd awn WHERE awn.student_code = awf.student_code) AND awf.xtrct_wk_dt = (SELECT MAX(xtrct_wk_dt) from attendance_monitor_wkly_fact) AND (mon.dwh_admin_pwy_jn like $P{PATHWAY_CODE_P}) AND (awf.attendance_mode LIKE $P{MODE_P}) AND awf.academic_year = $P{ACADEMIC_YEAR_P} AND awf.school_code LIKE $P{SCHOOL_P} AND awf.occurrence_code LIKE $P{OCCURRENCE_P} AND amsl.study_location_key LIKE $P{STUDY_LOCATION_P} AND SUBSTR(awf.fee_status,1,1) LIKE $P{FEE_STATUS_P} AND amsl.join_key = awf.study_location_dkey AND awf.dwh_admin_pwy_jn = pwy.pwy_code AND awf.study_yr_dkey LIKE decode($P{BLOCK_P},'%','%',to_char(to_number('1') + 1)) AND stu.stu_code = mon.student_code AND awf.student_code = stu.stu_code ORDER BY 5, 1

Hi, I have openned a call with JasperSoft regarding this and it is a known defect logged as #14317 I will wait to hear from them as to how it can be fixed. Thanks, Sean

Hi Lucian, IE prompts me to Save or Open the file (see attachment) If I click on Open I get a page saying navigation to the webpage was cancelled, if I click on Save I get a message that IE could not download flow.html from the server. Note the odd filename on the screenshot... Thanks, Sean

Forgot to mention, if I remove the j_username and j_password from the URL then enter these when prompted the report opens in PDF OK.. Sean

I am experiencing a similar problem, if I call a report using the following URL: http://js.domain.com:8080/jasperserver-pro/flow.html?_flowId=viewReportFlow&reportUnit=/Reports/test&decorate=no&output=html&j_username=username&j_password=password The report works, if I use either pdf, rtf or xls for the output parameter the report fails. Again, this problem only occurs with IE (6 and 7) the reports work fine with Firefox. I am using JS Pro 3.5, this was working fine previously on JS community release 2.1. Did you ever get this fixed? Thanks, Sean

Hi, I have JasperServer 3.5 professional edition setup to import users from LDAP but all users are being imported without an organisation. I do not want to use organisations but was unsure if this might cause problems with the system? If so how can I specify an organisation in my LDAP settings? Thanks, Sean

Hi, Was any progress made with this issue, I am also experiencing this and have lots of nvarchar2 columns that need to be displayed in the domain? Thanks Sean

Hi Samed, Yes, JasperServer can recognise 2 nested reports, I have used this before. I think the problem you have may be related to the file/name of the resource you have uploaded. I notice in the error you have that JasperServer is looking for subreport2.jasper. When working with JasperServer you need to uploade .jrxml files (not .jasper files which are what you use with iReports). You need to modify the subreport1.jrxml file subreport element to use subreport2.jrxml. Hope this helps. Regards Sean

I have tried this in version 2.0.5 of iReports and it still does not work, it looks like the style is evaluated before the value is returned from the sub report.. Is this the way iReports/Jasper Reports are designed to work or am I missing a setting (I always have trouble working out what to put for the evaluation time setting. :unsure: Sean

Hi Samed, Did you get this to work OK with the sum (or the count), I can get the sum to work fine but count is a bit more tricky (although possible). Regards, Sean

Hi Samed, Add a new variable and set: Calculation Type = Sum Variable Class Type = Integer In the variable expression put your cirteria e.g. ($F{YEAR}.equals("1997"))?new Integer(1):new Integer(0) The above example will add 1 for each row in the group where the year = 1997. Add this variable to your report group footer. Sean

Hi, I have a subreport that returns a variable to the parent report. The sub report is in the detail section and I want to use the value returned to style the field/row. I have setup a style and an expression to do this but the style is being applied to the line after the one I want changed. e.g. Style expression set to new Boolean($V{TOTAL_V} > 0) and the row below the one greater than zero is being styled.. I can get this to work using a normal field in the report but it appears the style is being applied before the variable is being evaluated. Is this a bug or a setting problem? Thanks, Sean

Ok, I found it (should have spent a bit more time looking before posting..) The file is js.jdbc.properties in the scriptsconfig directory. Sean

Hi Lucian, We are using Oracle for the repository and the js-export script does not appear to work. It looks like it is trying to connect to mysql still. Can this be configured to work with Oracle and if so how? Thanks, Sean

I have found a way of doing what I wanted. I have created a copy of the DefaultParametersForm.jsp file and modified it to add a link next to the dropdown list. This link calls a javascript function that hides the list and displays an edit field. The function also changes the link text/title and swaps the names of the list and text field so the value will be used of whichever one is selected.. The code modifications are show below: Code: function toggleEditable(linkID,listID,textID,fieldName) { var linkObj = document.getElementById(linkID); var listObj = document.getElementById(listID); var textObj = document.getElementById(textID); if(linkObj.firstChild.nodeValue == 'Allow Editing') { linkObj.firstChild.nodeValue = 'Show List'; linkObj.title="Click here for a list of values to select from" ; listObj.style.display = "none"; listObj.name=fieldName+"_HIDE"; textObj.style.display = "inline"; textObj.name=fieldName; textObj.focus(); } else { linkObj.firstChild.nodeValue = 'Allow Editing'; linkObj.title="Click here to enter text manually" ; listObj.style.display = "inline"; listObj.name=fieldName; textObj.style.display = "none"; textObj.name=fieldName+"_HIDE"; } } I then modify any list items I want (e.g. below is the modification to the file for the single-select-query section): Code:[code] <c:when test="${control.type == 4}"> <%-- Single value selected from list created from a query: InputControl.TYPE_SINGLE_SELECT_QUERY --%> <td align="right">${control.label} </td> <td> <select name="${inputName}" id="${inputName}_LIST" class="fnormal" <c:if test="${readOnly}">disabled</c:if> onchange="${onInputChange}"> <c:if test="${!control.mandatory}"><option value="" /></c:if> <c:forEach items="${wrapper.queryResults}" var="item" varStatus="status"> <option value="${item.key}" <c:if test="${item.key == wrapper.value}">selected</c:if>>${item.value[1]}</option> </c:forEach> </select> <input name="${inputName}" class="fnormal" type="text" id="${inputName}_TEXT" style="display:none"> <a id="${inputName}_LINK" title="Click here to enter text manually" target=_self href="javascript:toggleEditable('${inputName}_LINK', '${inputName}_LIST', '${inputName}_TEXT', '${inputName}');">Allow Editing</a> </td> </c:when> I have not done too much testing on this yet but it seems to work OK. It would have been nicer to have been able to add new InputControl types for each type of list item to specify if an editable item was wanted but that might be going beyond my abilities.. Sean

Hi, Is it possible to have a dropdown list for parameters (e.g. single-select query) that the user can also enter free text on? I cannot see this in the system but guess I can maybe do it by modifying the parameter .jsp file. Is this the only way? Thanks, Sean

If you say the query works without the decode then this decode must be causing no data to be returned. Are you sure you have data in the resultset that matches the decode statemtent? Do you need to truncate the dcp072 field (it may have a time value as well as date whereas the decode will have a time of 00:00:00). Sean

Hi, The error message you are getting is an Invalid Credentials message, this means either your password is incorrect or the FQN you are using for the test account is not correct. Can you confirm you have an account in Active Directory in the container CN=test,DC=hapsh,DC=com this appears to be the root of your AD tree are you sure it should not be cn=test,ou=hapsh,dc=hapsh,dc=com? This is the countainer you reference in the second and third sections of your xml. Also, you appear to be getting an error message about connecting as test@hapsh which does not match the configured managerDN information in the xml shown. I think your first section should be using something like below: Code: <bean id="initialDirContextFactory" class="org.acegisecurity.ldap.DefaultInitialDirContextFactory"> <constructor-arg value="ldap://hapsh-if.hapsh.com:389/dc=hapsh,dc=com"/> <!-- You may not need the next properties--> <property name="managerDn"><value>test@hapsh.com</value></property> <property name="managerPassword"><value>********</value></property> <property name="extraEnvVars"> <map> <entry key="java.naming.referral"> <value>follow</value> </entry> </map> </property> </bean> Hope this helps. If not I would suggest downloading LDAPBrowser from: http://www-unix.mcs.anl.gov/~gawor/ldapcommon/bin/Browser282b2.zip and using it to verify your credentials work as you expect them to. Regards, Sean

Hi, In answer to your questions: 1) I think it is possible to configure Jasper not to bring the LDAP groups across but using the settings below you do require the 3rd section. If you don't want it to bring the groups is just change the description=*JASPER* to a description that will never find any groups in your AD schema. 2) Yes, C:program filesjasperserver-2.1apache-tomcatwebappsjasperserverWEB-INFlogs is the correct place to check you should have a log file called jasperserver.log 3) I would use username@domain. If it still does not work a couple of things worth mentioning are: 1) The web.xml file loads any file that matches applicationContext*.xml so if you made a backup of the apapplicationContext-security.xml file it will be loaded if it matches the *.xml pattern above and cause a lot of confusion. (change the extention to .bak). 2) Make sure you have un-commented the LDAPAuthenticationProvider in the authenticationManager section. If it still does not work upload the LDAP section of you .xml file (remove the managerDN password) and I will take a look. Regards, Sean

Hi, I have this working using AD and bringing groups into Jasper whenever a user logs in. I have set it up so Jasper only imports groups with JASPER in the description (I did not want to load all groups from our AD). There are three main sections you have to configure: 1) The credentials for the user Jasper uses to search/read AD. Change server.your.domain.com for your LDAP server and all reference to your.domain.com to your domain.. username username_password to a user who can connect/search AD (this user should have minimal access to AD) Code: <bean id="initialDirContextFactory" class="org.acegisecurity.ldap.DefaultInitialDirContextFactory"> <constructor-arg value="ldap://server.your.domain.com:389/dc=your,dc=domain,dc=com"/> <property name="managerDn"><value>username@your.domain.com</value></property> <property name="managerPassword"><value>username_password</value></property> <property name="extraEnvVars"> <map> <entry key="java.naming.referral"> <value>follow</value> </entry> </map> </property> </bean> 2) Where in AD to search and what to search for, change ou=top_ou_to_search for the top ou that holds your accounts (I don't know if it will work for the root of the domain so try a container first) this does not need to be the FQN ou=users: Code:[code] <bean id="userSearch" class="org.acegisecurity.ldap.search.FilterBasedLdapUserSearch"> <constructor-arg index="0"> <value>ou=top_ou_to_search</value> </constructor-arg> <constructor-arg index="1"> <value>(sAMAccountName={0})</value> </constructor-arg> <constructor-arg index="2"> <ref local="initialDirContextFactory" /> </constructor-arg> <property name="searchSubtree"> <value>true</value> </property> </bean> 3) If you want to bring in groups use the following, this includes the restriction to those with a description of JASPER so you will have to remove that part.. Code:[code] <bean id="ldapAuthenticationProvider" class="org.acegisecurity.providers.ldap.LdapAuthenticationProvider"> <constructor-arg> <bean class="org.acegisecurity.providers.ldap.authenticator.BindAuthenticator"> <constructor-arg><ref local="initialDirContextFactory"/></constructor-arg> <property name="userSearch"> <ref local="userSearch" /> </property> </bean> </constructor-arg> <constructor-arg> <bean class="org.acegisecurity.providers.ldap.populator.DefaultLdapAuthoritiesPopulator"> <constructor-arg> <ref local="initialDirContextFactory" /> </constructor-arg> <constructor-arg> <value>ou=Group_ou</value> </constructor-arg> <property name="convertToUpperCase"> <value>true</value> </property> <property name="rolePrefix"> <value></value> </property> <property name="searchSubtree"> <value>true</value> </property> <property name="groupSearchFilter"> <value>(&(description=*JASPER*)(member={0}))</value> </property> <property name="groupRoleAttribute"> <value>cn</value> </property> </bean> </constructor-arg> </bean> Hope this helps. Sean

Hi Sherman, Thanks for the info, we want most of our reports to go direct to PDF (which is why I replaced the default file as it means we don't have to specify the .jsp for all reports). I have renamed the original DefaultJasperViewer.jsp file so we can still use that for any reports where we require multiple output formats. I will probably also create a specific .jsp that goes direct to excel as well. I am very impressed with JasperReports/JasperServer, I have spent weeks looking at Oracle reports/portal for deployment of reports and was not impressed at all. After spending a few hours (including installing/configuring LDAP etc) I found I could do everything I wanted with JasperServer. Thanks, Sean

Finaly I got around to looking into this again and have found the solution. It was pretty simple I just modified the DefaultJasperViewer.jsp file and removed the part that produces export buttons and html report content. I then manually added the pdf button part and called the javascript function to display the PDF using onload on the PDF icon. I expect there is a better method of doing this but it works fine for what I need. Contents of the modified DefaultJapserViewer.jsp file are below: Code: <%-- Copyright (C) 2005 - 2007 JasperSoft Corporation. All rights reserved. http://www.jaspersoft.com. Unless you have purchased a commercial license agreement from JasperSoft, the following license terms apply: This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License version 2 as published by the Free Software Foundation. This program is distributed WITHOUT ANY WARRANTY; and without the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, see http://www.gnu.org/licenses/gpl.txt or write to: Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA USA 02111-1307 --%> <%-- Default rendering HTML fragment for a JR report called from the JasperViewerTag. Expects attributes: pageIndex: Integer Current page in report lastPageIndex: Integer Greatest page number in report page: String URL for surrounding page exporter: JRHtmlExporter The wrapped JasperPrint pageIndexParameter: String parameter name in URL for paging --%> <%@ taglib prefix="spring" uri="/spring" %> <%@page import="net.sf.jasperreports.engine.export.*" %> <%@page import="net.sf.jasperreports.engine.*" %> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <%@taglib uri="/WEB-INF/jasperserver.tld" prefix="js" %> <%@page errorPage="/WEB-INF/jsp/JSErrorPage.jsp" %> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td colspan="3"> <table width="100%" cellpadding="0" cellspacing="4" border="0"> <tr> <c:choose> <c:when test="${emptyReport}"> <td width="100%" align="left"> </c:when> <c:otherwise> <td width="35%" align="left"> </c:otherwise> </c:choose> <table cellpadding="0" cellspacing="0" border="0"> <tr> <c:if test="${hasInputControls}"> <td width="1" valign="middle"> <a <c:choose> <c:when test="${reportControlsLayout == 2}"> href="javascript:backToInputControlsPage();" </c:when> <c:when test="${reportControlsLayout == 1}"> href="javascript:«»showInputControlsDialog();" </c:when> </c:choose> title="<spring:message code="jasper.report.view.hint.report.options"/>"> <img border="0" src="${pageContext.request.contextPath}/images/report_options.gif" alt="<spring:message code="jasper.report.view.hint.report.options"/>"/> </a> </td> <td width="1"> <c:if test="${!showClose and !emptyReport}"> </c:if> </td> </c:if> <c:if test="${showClose}"> <td width="1" valign="middle"> <a href="javascript:closeViewReport();" title="<spring:message code="jasper.report.view.hint.close"/>"> <img border="0" src="${pageContext.request.contextPath}/images/return_to_repo.gif" alt="<spring:message code="jasper.report.view.hint.close"/>"/> </a> </td> <td width="1"> <c:if test="${!emptyReport}"> </c:if> </td> </c:if> <c:if test="${(showClose || hasInputControls) and !emptyReport}"> <td width="1" style="border-left:1px solid #C0C0C0"> </td> </c:if> <c:if test="${!emptyReport}"> <input type="hidden" name="output"/> <td width="1" valign="middle"><a href="javascript:exportReport('pdf')" title="<spring:message code='jasper.report.view.hint.export.pdf'/>"><img src="${pageContext.request.contextPath}/images/pdf.gif" onload="javascript:exportReport('pdf')" border="0" alt="<spring:message code='jasper.report.view.hint.export.pdf'/>"/></a></td> <td width="1"> </td> </c:if> </tr> </table> </td> </td> </tr> <c:choose> <c:when test="${emptyReport}"> <tr> <td colspan="3"> </td> </tr> <tr> <td nowrap="nowrap" colspan="3" align="center"> <spring:message code="jasper.report.view.empty"/> </td> </tr> </c:when> </c:choose> </table> </td></tr> </table>

Hi Lucian, I have updated the applicationContext.xml file and this is working now. Thanks for your help. Sean