eraskin

I am using Linux (Oracle Linux 7.9) running JasperReports Library 6.18.1. My dev environment is Windows 11 using Jaspersoft Studio 6.20.1. I realize that I need to upgrade the JasperReports library version soon. I would like to modify this report to put formulas in place of the numbers I am currently calculating. The problem is that the formulas reference other columns in the report and I can not guarantee where those numbers will be located. The INDIRECT(ADDRESS(...)) solution is very cumbersome. If I could define Excel names for particular fields instead, then I could reference them in the formulas and it would be much easier. Here is an excerpt from one report - this is my header section: <?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.18.1.final using JasperReports Library version 6.18.1-9d75d1969e774d4f179fb3be8401e98a0e6d1611 --><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="altmlgresponse" pageWidth="1000" pageHeight="1000" columnWidth="996" leftMargin="2" rightMargin="2" topMargin="2" bottomMargin="2" uuid="4f1f795c-8186-44a7-86fa-28aaab31d1fe"> <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="false"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <property name="net.sf.jasperreports.export.xls.collapse.row.span" value="true"/> <property name="net.sf.jasperreports.export.xls.collapse.column.span" value="true"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/> <property name="net.sf.jasperreports.export.xls.ignore.cell.border" value="false"/> <property name="net.sf.jasperreports.export.xls.white.page.background" value="false"/> <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/> <property name="net.sf.jasperreports.export.xls.ignore.graphics" value="false"/> <property name="net.sf.jasperreports.page.break.no.pagination" value="apply"/> <property name="net.sf.jasperreports.print.keep.full.text" value="true"/> <property name="net.sf.jasperreports.exports.xls.font.size.fix.enabled" value="true"/> <property name="net.sf.jasperreports.exports.maximum.rows.per.sheet" value="0"/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="lsp-pg"/> <property name="net.sf.jasperreports.export.xls.font.size.fix.enabled" value="false"/> <textField pattern="#,##0.00%"> <reportElement x="888" y="0" width="60" height="18" uuid="09749a0d-717f-46e3-b304-8eae3085a5c5"> <property name="net.sf.jasperreports.export.xls.definedName" value="myValue"/> <-- this is what I would like to see --> </reportElement> <textElement textAlignment="Right" verticalAlignment="Top"> <font isBold="true"/> </textElement> <textFieldExpression><![CDATA[$F{resppcnt}]]></textFieldExpression> </textField> I would like to be able to give this field an Excel defined name so that I could write the following in a formula: <textField isBlankWhenNull="false"> <reportElement key="textField-3" x="0" y="196" width="280" height="23"> <property name="net.sf.jasperreports.export.xls.formula" value="myValue*100"/> <-- note how this formula references the defined name myValue above --> </reportElement> <box rightPadding="5"> <leftPen lineWidth="4.0" lineColor="#C0C0C0"/> <bottomPen lineWidth="1.0" lineColor="#C0C0C0"/> <rightPen lineWidth="1.0" lineColor="#C0C0C0"/> </box> <textElement textAlignment="Right" verticalAlignment="Middle"/> <textFieldExpression class="java.lang.Integer">0</textFieldExpression> </textField> If we had this capability, then we would no longer need the awkward INDIRECT(ADDRESS(...)) as referenced here: Exporting Crosstab Formula "SUM" To Excel If you look in the code in the JRXlsxExporter.java file: https://github.com/TIBCOSoftware/jasperreports/blob/master/jasperreports/src/net/sf/jasperreports/engine/export/ooxml/JRXlsxExporter.java, you can find this section as an example: protected void insertPageAnchor(int colIndex, int rowIndex) { if(!ignoreAnchors && startPage) { String anchorPage = JR_PAGE_ANCHOR_PREFIX + reportIndex + "_" + (sheetIndex - sheetsBeforeCurrentReport); String ref = "'" + JRStringUtil.xmlEncode(currentSheetName) + "'!$A$1"; // + XlsxCellHelper.getColumIndexLetter(colIndex) + "$" + (rowIndex + 1); definedNames.append("<definedName name="" + getDefinedName(anchorPage) +"">"+ ref +"</definedName>n"); startPage = false; } } As you can see, it generates the definedName for an "anchorPage". I would like the code to generate the same thing for my own defined name. The question is can you actually identify the Excel cell that the data is in? I believe you could use the code in the comment to do it: "'" + JRStringUtil.xmlEncode(currentSheetName) + "'!$" + XlsxCellHelper.getColumIndexLetter(colIndex) + "$" + (rowIndex + 1) + "'"[/code]Hopefully this makes the request clearer. Thank you for considering the feature. Eric Raskin

I am trying to generate formulas for some of the cells in my XLSX spreadsheet. I need to reference a cell in my formula that is at the top of the page, but not necessarily in a fixed position. It would be great if I could give it a defined name and reference that name in the formula. Excel has that provision, of course. The JRXlsxExporter class does generate definedNames XML, but it only puts page anchors in there. Is there any way to add my own cell defined names? If not, is this a feature that is planned?

Hmmm... You are referencing webapps/jasperserver? I'm not running Jasper Server. I'm running my own application (based on Cuba Platform - http://www.cuba-platform.com) and integrating Jasper Library based reports into that. As you can see from my original post, I can reference the datasource xml file generated by Jasper Studio in my JRXML file, but for some reason it is looking for an encrypted password. How do I tell Jasper Library (NOT Jasper Server) that the password is in plain text?

OK, so if I can make it accessible to the report and NOT accessible to the web, I can use the plain text password. How to I configure the library so it will accept the plain text password?

Hi: I'm building a web application using Tomcat that uses the jasperreports library. My JRXML file contains a reference to a JDBC data adapter like this: <property name="net.sf.jasperreports.data.adapter" value="http://localhost:8080/app-core/Vitalmax.xml"/> The Vitalmax.xml file contains the password in plain text. When I execute the report, I get this error: JRRuntimeException: No secret found for "vitalmaxowner" key in "net.sf.jasperreports.data.adapter" category. How do I encrypt the password WITHOUT JasperReports Server, since I am not running that? I would prefer NOT to deploy with plain text passwords, as the XML has to be visible to the web. Thanks in advance... Eric Raskin eraskin at paslists dot com

XLSX result after patch (PDF is the same as before patch)

Example outputs before patch

Changed Assigned User from @User_306070 to @anonymous Certainly. The attached ZIP file contains the main report and a sub_report that generates individual crosstabs. Let me know if you need database table information as well.

Certainly. Sorry in advance for the long post. You will see that the preview screens show all the crosstabs but the export worksheets do not. There are more pages, but I think these are enough to illustrate the problem. Here goes - iReport 5.0.1 previewer page 1: XLSX Export Tab 1: iReport preview page 2: XLSX Export tab #2: iReport preview page 3: XLSX Export tab 3: iReport preview page 4: XLSX Export tab 4:

Hi: I have a simple report that runs a subreport with a crosstab. The simple outer report sets the Excel sheet name and then runs the subreport passing a parameter from the main report. The goal is to have each crosstab on each own named spreadsheet tab. When I run the report, the iReport internal preview looks fine. I get every crosstab. Exporting to PDF also works. HOWEVER, exporting to any Excel format does not work. I only get some of the crosstabs exported. Does anybody have any idea how to fix -- or at least how to debug what is going wrong? Thanks in advance. Eric Raskin eraskin at paslists dot com

I am going to answer my own post, because I have a solution that works. I did not rewrite anything inside JasperReports library and gave up on a custom data source. Instead I just made may own SQL connection object and passed it to JasperFillManager.fillReport. I believe there are many examples about how to do this, but I can post my code if somebody is interested. I integrated this with a customized version of JRViewer that I can use whenever I need to view/save a report. The customized JRViewer adds XLSX save capability, which is missing from JRViewer for some reason. I may eventually add the ability to stream the report directly to the browser, so that the user doesn't have to save an XLS/XLSX file first but rather have it open directly in Excel. I then wrapped all of this with an Oracle Forms VBean so that it can be included into an Oracle Forms application (based on code available at https://sites.google.com/site/oraont/jasperreports). The Oracle Forms application passes a username/password which is used to create the SQL connection object, which solves my original problem. I can now connect to the database as the user logged in to the form.

Hello: Note: I am not a particularly skilled java developer. Apologies for the long post as well. Please advise for any posting errors (such as posting java at attachments). The problem I have is that I have multiple Oracle users that each need to run a report with their own connections to the database. We have multiple customers using an application. Each customer has their own schema (set of tables/username and password) in our database. The reports they need to run are identical. The only thing that needs to change are the username and password that they connect with when running the reports. The only solution I can figure out is to create a Custom Data Source that takes the username and password from the URL and overrides the settings I have in the Repository for the report. I know this is insecure in that the password is listed in the URL. I expect to modify this with an obfuscated/encrypted password of some kind to increase the security, but first I need to get a proof-of-concept working. My first approach was to try to extend JdbcDataSource somehow, but I could not get that to work. Instead, I have implemented a ReportDataSourceService that creates a JdbcDataSourceService object and then uses that to create a connection. I basically copied a bunch of code from JdbcDataSourceService: package com.paslists; import java.util.Map;import javax.sql.DataSource;import java.sql.Connection;import java.sql.SQLException; import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory; import com.jaspersoft.jasperserver.api.JSException; import com.jaspersoft.jasperserver.api.metadata.jasperreports.domain.ReportDataSource;import com.jaspersoft.jasperserver.api.metadata.jasperreports.service.ReportDataSourceService;import com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService; /** * @author eraskin * */public class pasDataSourceService implements ReportDataSourceService { private static final Log log = LogFactory.getLog(pasDataSourceService.class); private final DataSource dataSource; private String username; private String password; public pasDataSourceService(ReportDataSource dataSource) { this.dataSource = (DataSource) new JdbcDataSourceService((DataSource) dataSource, null); } protected Connection createConnection() { try { Connection c = dataSource.getConnection(username, password); if (log.isDebugEnabled()) { log.debug("CreateConnection successful at com.paslists.pasDataSourceService.createConnection"); } return c; } catch (SQLException e) { if (log.isDebugEnabled()) log.debug("Error creating connection in com.paslists.pasDataSourceService.", e); throw new JSException("jsexception.error.creating.connection", e); } } /* (non-Javadoc) * @see com.jaspersoft.jasperserver.api.metadata.jasperreports.service.ReportDataSourceService#setReportParameterValues(java.util.Map) */ public void setReportParameterValues(Map parameterValues) { if (parameterValues.get("username") != null) { this.username = (String) parameterValues.get("username"); } if (parameterValues.get("password") != null) { this.password = (String) parameterValues.get("password"); } } public void closeConnection() throws JSException { try { if (dataSource != null && !dataSource.getConnection().isClosed()) { dataSource.getConnection().close(); if (log.isDebugEnabled()) { log.debug("closeConnection successful at com.paslists.pasDataSourceService.createConnection"); } } } catch (SQLException e) { if (log.isDebugEnabled()) log.debug("Error closing connection in com.paslists.pasDataSourceService.", e); throw new JSException("jsexception.error.closing.connection", e); } } } I then created a Factory class to create a pasDataSource object when needed: package com.paslists.pasDataSource; import com.paslists.pasDataSourceService; import com.jaspersoft.jasperserver.api.metadata.jasperreports.domain.ReportDataSource; import com.jaspersoft.jasperserver.api.metadata.jasperreports.service.ReportDataSourceService; import com.jaspersoft.jasperserver.api.metadata.jasperreports.service.ReportDataSourceServiceFactory; public class pasDataSourceServiceFactory implements ReportDataSourceServiceFactory { public ReportDataSourceService createService(ReportDataSource dataSource) { return new pasDataSourceService(dataSource); } } I am struggling with how to install this into JasperServer 4.7.0. I know that I have to modify ApplicationContext.xml somehow. I then must configure a Bean Data Source? Or will this somehow appear as a new type of Data Source similar to a JDBC Data Source in the interface (which is preferrable)? I have been struggling with the API documention as well as the User Documentation. I just can't seem to get a single coherent example of all the steps necessary to install a Custom Data Source. I have seen numerous snippets on the web of various pieces and parts, but some seem to be for older JasperServer versions, so I'm not sure if they work or not in 4.7.0. I am open to any suggestions, including to scrap all of this and implement in a different fashion. Thanks in advance. Eric Raskin eraskin AT paslists.com