lisaws

Hi, I am having trouble with a report that has a non-mandatory input which cascades to a mandatory input. In MySQL the report runs perfectly fine, but once I converted it to MS SQL Server and also to Snowflake (which is ANSI SQL), I get the following errors: MS SQL Server: "java.sql.SQLException: Unable to convert between java.util.Collections$EmptyList and JAVA_OBJECT." Snowflake: "net.snowflake.client.jdbc.SnowflakeSQLException: Data type not supported for binding: Object type: class java.util.Collections$EmptyList." For this report the user is required to select a newsletter aka brief, but they can opt to first select an industry. If they do, the list of newsletters should be limited to only that industry. --query for industry input: industry_multi_opt--multi select query--not mandatoryselect distinct categoryfrom dim_brief_subcategory_categorywhere category <> 'Unknown'order by category-- query for brief input-- multi select query-- mandatoryselect brief_key, brief_name from sb_dwh.dim_brief bwhere b.brief_activestatus like 'A%' and b.brief_key <> -999and b.is_main = 1and b.is_dedicated_send = 0and case when $P{industry_multi_opt} is null then 1=1 else b.brief_key in ( select brief_key from link_brief_subcategory_category l inner join dim_brief_subcategory_category c on l.brief_subcategory_category_key = c.brief_subcategory_category_key where $X{IN,c.category, industry_multi_opt}) endorder by b.brief_name[/code]

Hi, I am trying to run a report using Snowflake in iReport. I have the Snowflake JDBC driver as a jar file which I have added to Tools > Options > iReport > Classpath. I have also added it to Window > Services > Databases > Drivers. However, when I try to add Report Datasources via the button at the top of iReport, I do not see the driver listed in the dropdown.

I am having this problem as well. When I click on "Other" and start to type the driver name, I don't see an "Add Driver" button anywhere. Where can I upload my jar?

Sorry for the repost, but I was hoping @hozawa would see my question again since he usually has a lot of good Jasper insight. This is a repost of Limit the amount of data rendered in page? To speed up returning 40,000 rows. I have a report that could potentially return 40k rows, and while the query performs relatively well (~40 seconds), the Jasper interface takes ~6 minutes to render the report. It also makes the browser window very unstable, as 40k rows is a lot to render in the browser. The user does not need to see all 40k rows in the browser - rather, the Excel export is what is important to the user in this case. I googled around and saw an example using SSRS (SQL Server Reporting Services) which has something called Interactive Paging where you can limit the height of the data returned. In this example he/she was able to limit the data rendered in the browser, but upon export to Excel, all of the data is succesfully exported. I'm using iReport version 5.6. (Of course we would like to upgrade to the newest version but that is not an option at the moment.) Is there anything in my version of Jasper that can give similar functionality to the example? To be clear, I want to limit the amount of data rendered in the browser, but include all data ~40k rows in the Excel export.

@Hozawa : I want to retain all 40k rows in the Excel export. I just want to limit the data rendered in the browser.

That looks like a simple report in Jasper. Try reading this wiki: https://community.jaspersoft.com/wiki/basic-report-creation

deleted

I think Jasper schedules are user-specific, unfortunately. For this reason our workaround was to create one user jasper_schedule_user and give everybody the login for it.

I think a quick mock up of what you're trying to achieve would help us understand your question.

On the text element set the property net.sf.jasperreports.export.xls.formula and set its value as the formula you need. Here is an example from one of my reports: <propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["ROUND((A" +($V{PAGE_COUNT}+9) + "/1000)*M" + ($V{PAGE_COUNT}+9) +",2)"]]></propertyExpression>[/code]Then once my report is exported, the formula for that cell in row 1 (for which $V{PAGE_COUNT} = 1) is as follows: =ROUND((A10/1000)*M10,2) Note that for some reason when I tried setting the property through the report designer it didn't work - it only worked for me when I edited the XML directly.

Question is not clear. What are you having trouble with specifically?

What is the layout of your report, i.e. are you using a table, or are theese text elements in the main report? If in the main report, which bands? Are 'Bankovky' and 'Mince' group values? I've never tried having a row label extend down over several rows. However I would think you could achieve this if you set the stretch type on that row label, then put the rows within it as a table element and pass the value for ex. Bankovky into the table datasource. It would require two separate queries - one query in the main report to pull Bankovky and Mince, and one query in the table datasource that pulls the row details.

Pass the field value as a parameter to the subreport.

I have a report that could potentially return 40k rows, and while the query performs relatively well (~40 seconds), the Jasper interface takes ~6 minutes to render the report. At first I thought it was because of grouping/aggregations in Jasper, but once I moved those to the SQL query the report is still just as slow. The user does not need to see all 40k rows in the browser - rather, the Excel export is what is important to the user in this case. I googled around and saw an example using SSRS (SQL Server Reporting Services) which has something called Interactive Paging where you can limit the height of the data returned. I'm using iReport version 5.6. (Of course we would like to upgrade to the newest version but that is not an option at the moment.) Is there anything in my versin of Jasper that can give similar functionality to the example?

Nevermind! I realized that my workaround #1 actually does work (keeping Detail cells but making their height = 0 px).

I am trying to use a table element in my report (iReport Designer Professional 5.2.0) and use zero cells in the Detail section - and instead only use a Group Header. (In case you're wondering why - it is to make use of Jasper's variables and aggregations rather than relying solely on the SQL query). Is it possible to use zero Detail cells? When I try to delete all of the detail cells, I get the following errors: org.xml.sax.SAXParseException; lineNumber: 213; columnNumber: 18; cvc-complex-type.2.4.b: The content of element 'jr:column' is not complete. One of '{"http://jasperreports.sourceforge.net/jasperreports/components":detailCell}' is expected.<br>Error Trace[/code]net.sf.jasperreports.engine.JRException: org.xml.sax.SAXParseException; lineNumber: 213; columnNumber: 18; cvc-complex-type.2.4.b: The content of element 'jr:column' is not complete. One of '{"http://jasperreports.sourceforge.net/jasperreports/components":detailCell}' is expected.[/code]org.xml.sax.SAXParseException; lineNumber: 213; columnNumber: 18; cvc-complex-type.2.4.b: The content of element 'jr:column' is not complete. One of '{"http://jasperreports.sourceforge.net/jasperreports/components":detailCell}' is expected.[/code]I have tried such workarounds as 1) keeping Detail cells but making their height = 0 px, and 2) having a Detail cell with a blank static text element in it and setting Remove Line When Blank to true on that element.

I want to increase the max length of the group_concat function for my report. Normally (not in Jasper) I would do the following: SET SESSION group_concat_max_len = 10000;[/code]But if I add that to the beginning of my Jasper report query, I get an error when running the report ("An error has occurred..."). Is there any way to set SQL system variable such as this in Jasper iReport? I am using Jasper iReport 5.2.0 and MySQL db. Thanks. EDIT: also, I've tried a workaround to avoid using group_concat which uses user-defined variables, for example: SELECT resultFROM (SELECT @result := '', (SELECT result FROM (SELECT @result := CONCAT_WS(';', @result, field2) AS result, LENGTH(@result) AS blength FROM some_table ORDER BY blength DESC LIMIT 1) AS sub1) AS result) AS sub2; [/code]But I am getting the same error, " An error has occurred. Please contact your system administrator. (6632)". I am guessing this is because Jasper doesn't allow SQL variables?

@sadakar - No it isn't. However I realized that I could solve my problem by using subreports and referencing an external jrxml file resource located in the repository which I mention here: http://community.jaspersoft.com//questions/1049611/setting-subreport-path-another-jrxml-located-elsewhere-jasper-repository

I figured out the problem. After reading http://community.jaspersoft.com/documentation/jasperreports-server-administration-guide/v550/jasperreport-structure and knowing that what I am trying to do is definitely possible, I looked closer into my main report, and saw that for the subreport element I needed to pass a data connection along to the subreport. I set Connection Type to "Use a connection expression" and se tthe connection expression to $P{REPORT_CONNECTION}.

Hi, I'm trying to create a report that includes a subreport that is located elsewhere in the Jasper repository (and not under the main report's Resources folder). The file I want to use for the subreport is a jrxml resource located at /public/DEV/test/test_subreport.jrxml. In the main report, which is located at /public/DEV/test, I have a subreport element. I have set the subreport expression to "repo:/public/DEV/test/test_subreport.jrxml". When I try to run the main report, I get no errors and only a blank page. I'm pretty sure that the main report is able to find the test_subreport.jrxml file because when I change the subreport expression to something else (for ex. adding an extra character to the path), it errors as expected "Error opening input stream from URL: repo:/public/DEV/test/test_subreportX.jrxml". Both the main report and subreport have appropriate dummy queries in them so that is not the issue. Any help would be appreciated! I'm using iReport 5.2.

There is an equivalent property for setting the header called net.sf.jasperreports.export.xls.sheet.header.center (http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.sheet.header.center). To set a dynamic value - in the Add/modify property window, select the 'Use an expression' checkbox and then you can use whatever expression you want.

How about: $V{AMOUNT_MEASURE}.equals(0)? "":$V{AMOUNT_MEASURE}

I'm not sure because I haven't actually dealt with .jasper files. I only see .jrxml files in my reporisotry explorer. However I wonder if this helps: http://community.jaspersoft.com/questions/849170/difference-between-jrxml-and-jasper-files

deleted

You can use an expression: $F{field} == 0 ? "" : $F{field} Which reads "If value is zero, print blank cell. Otherwise print the value"