asimkin

Issue DescriptionCustomer tried to configure TIBCO JasperReports® Server v.7.1.0 to send scheduled job notifications via an MS Exchange server with NTLM authentication. They discovered, that notifications fails with error '530 5.7.1 Client was not authenticated' How to fix it? ResolutionTIBCO JasperReports® Server v.7.1.0 includes JavaMail v.1.4 library which does not support NTLM authentication. NTLM authentication support was added in v.1.4.4 only: https://www.oracle.com/technetwork/java/javamail/javamail144-1562675.html Replacing mail-1.4.jar with mail-1.4.4.jar in WEB-INFlib folderresolved the issue for this specific customer. There is an internal enhancement request to upgrade Javamail library in default installation. Ref. Case 01652462

Use CaseUser has a requirement to create a report which returns a list of user profile attributes and their values. ResolutionTIBCO JasperReports® Server stores profile attributes details in internal database, called repository. In order to retrieve the information, you can create a connection to that database (called jasperserver by default) and use a query like this one: select u.username, u.fullname, t.tenantid, t.tenantname, pa.attrname, pa.attrvalue, pa.descriptionfrom jiprofileattribute pa, jiuser u, jitenant twhere pa.principalobjectclass = 'com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoUser' and pa.principalobjectid = u.id and u.tenantid = t.id[/code]The query returns user details, tenant(organization) information and profile attributes data. Please find attached sample report to demonstrate the idea and the report output: Solution tested with TIBCO JasperReports® Server v.7.1.0 Ref. Case 01660508

Use CaseUser has a requirement to create a JRXML report with HTML5 Time Series Area Spline. He needs to display the data which is categorized using last 12 months i.e. if he runs the report on 20-DEC-2018, he should see data categorized monthly from January 2018 to December 2018, regardless if dataset contains data or not for each period. Also, the chart should always display each month on the axis. ResolutionIn order to display 12 months period in xAxis, xAxis.min and xAxis.max Highcharts properties should be specified as follows: <hc:chartProperty name="xAxis.min"> <hc:propertyExpression><![CDATA[DATEVALUE(org.apache.commons.lang.time.DateUtils.addMonths(new Date(),-12))]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="xAxis.max"> <hc:propertyExpression><![CDATA[DATEVALUE(org.apache.commons.lang.time.DateUtils.addMonths(new Date(),0))]]></hc:propertyExpression> </hc:chartProperty> In order to make each month to be displayed in the axis, you can set xAxis.units property to have ticks for each month: <hc:chartProperty name="xAxis.units"> <hc:propertyExpression><![CDATA[new Object[]{new Object[]{"month", new Object[]{1}}}]]></hc:propertyExpression> </hc:chartProperty> Find attached a sample report which demonstrates the solution. Although, the chart data contain only a few months, 12 months period displayed in the chart: Solution tested with TIBCO JasperReports® Server v.7.1.0 Ref. Case 01491078 s_lms_coursecompletion.jrxml

Issue DescriptionTIBCO JasperReports® Server installation script js-install fails with ORA-65096: invalid common user or role name error. What does the error mean and how to fix it? ResolutionInstallation script log showed that the process failed at Oracle Database user creation stage: [advanced-sql] Failed to execute: create user jasperserver identified by xxx ..... [Oracle]ORA-65096: invalid common user or role name This error is specific to Oracle 12c with CDB/PDB feature (a container database which includes zero,one or many customer-created pluggable databases). As stated in TIBCO JasperReports® Server Installation guide, in such case Oracle username should be specified as c##username in default_master.properties configuration file: https://community.jaspersoft.com/documentation/tibco-jasperreports-server-installation-guide/v71/installing-war-file-using-js-insta-0 Set parameter dbUsername=c##jasperserver in default_master.properties file and re-run install script. Solution tested with TIBCO JasperReports® Server v.7.1.0 Ref. Case 01659342

Issue Descriptionjs-export script fails with error java.io.FileNotFoundException: class path resource [js.jdbc.properties] cannot be opened because it does not exist[/code]How to fix it? ResolutionThe export script is looking for file js.jdbc.properties located in folder .../buildomatic/conf_source/default It is possible that the file or the entire folder was removed. To re-generate the folder content, execute the following script in buildomatic: js-ant refresh-config[/code]The script re-builds configuration settings based on default_master.properties. Ref. Case 01657218

The following SQL query will help user to identify all report units which have assigned an Input Control stored in TIBCO JasperReports® Server repository: SELECT ic.name as InputControlName, rf.uri as ReportURI, rep.name as ReportNameFROM jireportunitinputcontrol ru, jiresource rep, jiresource ic, jiresourcefolder rfwhere ru.input_control_id = ic.id and ru.report_unit_id = rep.id and rep.parent_folder = rf.id and ic.resourceType = 'com.jaspersoft.jasperserver.api.metadata.common.domain.InputControl' and ic.name = 'test_ic' -- specify required input control name here[/code]This query can be used to assist in the effort to determine related reports if a user needs to delete an input control from the repository but the operation fails with message The selected items cannot be deleted because they are referenced by other objects. The query tested with TIBCO JasperReports® Server v.7.1.0 and PostgreSQL repository database Ref. Case 01656654

Issue DescriptionI created and Ad Hoc View and then Ad Hoc Report. When I run the report in Report Viewer, there is icon to get the latest data 'Refresh Report With Latest Data'. But if I click on the icon, old cached data returned, but not the latest one. ResolutionAs of TIBCO JasperReports® Server v.7.1.0, 'Refresh Report With Latest Data' functionality works with regular JRXML reports only with 'Data Snapshots' enabled. With enabled Data Snapshot feature, the first time a report runs, it queries the data source and stores a copy of the data with the report in the repository. Users who view the report later see the data from the saved snapshot, not from querying the data source. For more information about Data Snapshots, please review TIBCO JasperReports® Server Administrator Guide, 'Enabling Data Snapshots' chapter In case of running Ad Hoc Reports, 'Refresh Report With Latest Data' pulls data from Ad Hoc Cache instead of data source. There is an internal defect to make 'Refresh Report With Latest Data' work with Ad Hoc Reports as well. Ref. Case 01583199

Use caseCustomer embedded TIBCO JasperReports® Server into a custom application via HTTP API passing username and passsword in URL like http://<host>:<port>/<context>/flow.html?_flowId=viewReportFlow&reportUnit=/reports/test&j_username=xxx&j_password=yyy In order to encrypt password passed in the URL, they enabled static key encryption and encrypt the password as described in TIBCO JasperReports® Server Security Guide. To ensure more security, customer would like to encrypt username, passed in the URL, as well. ResolutionIn order to encrypt username in the URL, a parameter encryption.param.uname=j_usernameshould be added to WEB-INF/classes/esapi/securtiy-config.properties configuration file. Then, restart the server, login into JasperReports® Server and open URL http://localhost:8080/jasperserver-pro/encrypt.htmlEncrypt username in the same way it is done for password. Please note if there is more than 1 organization created in the server, login should be specified like and this entire string should be encrypted. Solution tested with JasperReports Server v.7.1.0 Ref. Case 01642953

Issue DescriptionCustomer created an Ad Hoc View Chart with a filter. In case if applied filter returns no data, default message displayed: 'The chart contains no data. Adjust your data levels or filters.' Customer would like to customize that message. [toc]ResolutionText for empty Ad Hoc View chart defined in bundle files, property ADH_1214_ICHARTS_NO_CHART_DATA[/code]The property value can be changed to required No Data message. It supports a subset of HTML Text Formatting tags, like <b>, <font color=> Please refer to printscreen with default mesage and customized one ADH_1214_ICHARTS_NO_CHART_DATA=<b><font color="red">The chart contains no data</font></b><br>Please adjust filters[/code] Please note that ADH_1214_ICHARTS_NO_CHART_DATA property applied to Ad Hoc View Designer only. A report, created from that Ad Hoc View is not affected, it displays another default No Data message.

Use caseCustomer created a report from Ad Hoc View without any parameters/filters. They noticed that if the report executed by different users, data retrieved from underlying database instead of Ad Hoc Cache. Customer would like to change this behavior and use cached data regardless of user who runs the report. ResolutionBy default, datasets for each user are cached separately. A parameter in the cache key identifies the user. This per-user caching can result in duplicate datasets and impaired performance when different users run the same query. You can configure JasperReports Server to share cached datasets across users by editing configuration file /WEB-INF/applicationContext-datarator.xml[/code]The following code configures the cacheKeyInterceptor bean to ignore logged-in users' credentials when creating the cache keys: <value>LoggedInUsername</value><value>LoggedInUser</value>[/code]Theses parameters should be uncommented Please refer to the printcreen of the Ad Hoc Cache view with default properties and commented out ones Solution tested with TIBCO JasperReports® Server v.7.1.0

Use caseCustomer created a report with HTML5 Chart based on a parameterized subdataset. In case the subdataset returns no records, an empty chart displayed which is not user-friendly and might lead to misunderstanding. Customer would like to automatically add a message on top of the empty chart saying that there was no result from the query. ResolutionA possible solution it to use Highcharts' load event to specify a custom Javascript function which will display a message over the chart: https://api.highcharts.com/highcharts/chart.events.load In order to utilize the event, you need to go to 'Edit Chart Properties' context menu, 'Chart Formatting' tab, click on 'Show Advanced Properties' button. Locate chart > events > load property, modify it by checking 'Use Expression' option and specify expression value like: "function(){if (typeof this.series[0].data[0] == 'undefined'){this.renderer.label('Chart Has No Data', 350, 120).attr({fill:'red',padding: 10,r: 5}).css({color: '#FFFFFF'}).add();}}"[/code]Please refer to printscreen The function uses Highcharts' renderer.label function to draw the message: https://api.highcharts.com/class-reference/Highcharts.SVGRenderer#label Please note, the solution required property com.jaspersoft.jasperreports.highcharts.function.properties.allowed[/code]to be set to true as described in the article Using functions in HTML5 Charts / HighCharts Printscreen to compare default behavior (empty chart) and customized one: Solution tested with TIBCO JasperReports® Server v.7.1.0 Ref. Case 01649298

Issue DescriptionCustomer created an Ad Hoc Crosstab with 1 measure and 1 field in Columns and 1 fields in Rows. If he tries to change the Ad Hoc type from Crosstab to Chart, he gets message "The server can't render the chart because a row or column group summary was deleted. Restore all summaries before switching to a chart" Please refer to printscreen. How to fix the issue? ResolutionIssue investigation revealed that the user manually removed Row Totals (for shipcountry row in provided printscreen) which leads to the message. As of v.7.1.0, TIBCO JasperReports® Server requires all Rows and Columns Totals to be displayed in order to swtich to Chart mode. Once the Row Totals is added back to the crosstab, it is possible to change to Chart mode. Please find attached printscreen: Ref. Case 01647121

Use caseCustomer created an Ad Hoc Chart and wants to utilize plotLines feature available in Highcharts library. ResolutionHighcharts' plotLines is an array of objects represinting plot lines on a axis: https://api.highcharts.com/highcharts/yAxis.plotLines In order to utilize plotLines in Ad Hoc Chart designer, you need to go to 'Chart Format' menu, 'Advanced' tab and manually type yAxis.plotLines property name and array of objects escaped in double-quotes as a value, e.g.: [ { "color":"red", "value":"2000", "width":"2", "dashStyle":"longdashdot" }, { "color":"yellow", "value":"5000", "width":"2", "dashStyle":"longdashdot" }, { "color":"green", "value":"7000", "width":"4", "dashStyle":"solid" }][/code]Please refer to printscreen Please note that "width" property must be specified explicitly otherwise default value (undefined) is applied and the line is not visible Solution tested with TIBCO JasperReports® Server v.7.1.0 Ref. Case 01637993

Issue DescriptionCustomer configured TIBCO JasperReports® Server to send job notifications with checked 'Include HTML report in email body' option. He noticed that some of reports are aligned to the right side of page when he viewed the report in GMail, while the reports designed centered [toc] ResolutionThis is default behavior of GMail. If an email exceed 102kb size (GMail limit), GMail just clips the message and add [Message clipped] View entire message text and hyperlink and the page bottom, on the left side and causes the shift. If an email has less then 102kb, the report displays as expected. This behavior out of TIBCO JasperReports® Server control. Please review an article that explains GMail behavior: https://epicfizz.com/gmail-message-clipped-why-and-fix/ What you could try is to install 'Trimless for Google Mail™' Chrome extension which expands trimmed parts, removes "Show trimmed content" button and as a result, the report displayed centered: https://chrome.google.com/webstore/detail/trimless-for-google-mail/niepjjjfafhadmfdminbckmciijcaagc Chrome restart may required to make the extension work. Ref. Case 01638281

Use caseCustomer created an Ad Hoc Chart and wants to utilize plotBands feature available in Highcharts library. ResolutionHighcharts' plotBands is an array of objects defining plot bands on a axis: https://api.highcharts.com/highcharts/yAxis.plotBands In order to utilize plotBands in Ad Hoc Chart designer, you need to go to 'Chart Format' menu, 'Advanced' tab and manually type yAxis.plotBands property name and array of objects escaped in double-quotes as a value, e.g.: [{"color":"red","from":"0","to":"1000"},{"color":"yellow","from":"2000","to":"4000"},{"color":"green","from":"6000","to":"8000"}] Please refer to printscreen Solution tested with TIBCO JasperReports® Server v.7.1.0 Ref. Case 01637993

Issue DescriptionCustomer developed a JRXML report with Charts Pro component. When the report runs in JasperReports Server, output looks good, per requirements. However, when the report exported to PDF format, it shows some extra axis lines, please refer to printscreen. How to fix the issue? ResolutionThe lines, displayed in PDF output, are so-called divisional lines: https://www.fusioncharts.com/dev/chart-guide/chart-configurations/div-lines-and-grids In order to remove/hide them in PDF, try to specify the following settings in 'Divisional Lines and Grids' menu of the chart settings: Num = 0 Color = #FFFFFF Alpha = 0 Please find attached printscreen: It might be possible that the properties seem to be already set. In that case try to change them to another value and then back to the provided one, to make sure the values are really saved, or manually modify report JRXML file and add required properties: <fc:chartProperty name="divLineColor"> <fc:propertyExpression><![CDATA[new java.awt.Color(-1)]]></fc:propertyExpression> </fc:chartProperty> <fc:chartProperty name="numDivLines"> <fc:propertyExpression><![CDATA[new Integer(0)]]></fc:propertyExpression> </fc:chartProperty> <fc:chartProperty name="divLineAlpha"> <fc:propertyExpression><![CDATA[new Integer(0)]]></fc:propertyExpression> </fc:chartProperty>Ref. Case 01597755

Issue DescriptionCustomer developed a crosstab report in Jaspersoft Studio. Per report specifications, the crosstab totals should be calculated as custom weighted average function. ResolutionLet's review a report based on SugarCRM sample database, ORDERS table. The main idea is to manually add new measures, like: WeightSum - to calculate Summary on required fieldWeightedSum - to calculate average weighted by ShipVia with expression$F{ShipVia} * $F{Freight} The engine automatically creates variables for new measures which can be added to Crosstab Total columns. Attached is a sample report that displays both a standard average of order freight and an average weighted by ShipVia. Ref. Case 01466303 lateordersreport.jrxml

Issue DescriptionCustomer requested for a way to change source domain for already created Ad Hoc View, to another one with the same content/structure ResolutionUnfortunately, there is no way to change domain source of ad hoc view in JasperReports Server UI. There is an internal enhancement request to implement such feature. But, as a workaround, it can be done using Jaspersoft Studio. Steps to achieve it: create a connection to JasperReports Server repository in the Studio, 'Repository Explorer' tablocate the ad hoc view in question in the reporight-click on the view and select Properties context menugo to 'Data Source' tabselect new domain from the repository The same result can be achieved using REST API: use GET callhttp://<host>:<port>/jasperserver[-pro]/rest_v2/resources/path/to/resourceto get existing Ad Hoc view details, like http://localhost:8710/jasperserver-pro/rest_v2/resources/public/Cases/01594596/01__Geographic_Results_by_Segment got Ad Hoc View details in XML format<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <adhocDataView> <creationDate>2018-06-05T13:31:42</creationDate> <description> Sample HTML5 multi-axis column chart from Domain showing Sales, Units, and $ Per Square Foot by Country and Store Type with various filters </description> <label>01. Geographic Results by Segment</label> <permissionMask>1</permissionMask> <updateDate>2018-06-05T13:33:01</updateDate> <uri>/public/Cases/01594596/01__Geographic_Results_by_Segment</uri> <version>1</version> <dataSourceReference> <uri>/public/Samples/Domains/supermartDomain</uri> </dataSourceReference> </adhocDataView> as you can see, there is <dataSourceReference></dataSourceReference> tag which defines ad hoc view datasource modify the XML to specify a new source domainuse PUT method to update the ad hoc view, as described in REST API: /documentation/tibco-jasperreports-server-rest-api-reference/v710/resources-service#Modifying_a_Resource Content-Type is application/repository.adhocDataView+xml Solution tested with TIBCO JasperReports® Server v.6.4.0 Ref. Case 01594596

Issue DescriptionCustomer configured TIBCO JasperReports® Server to send shceduled jobs notification via GMail. He specified report.scheduler.mail.sender.from property in WEB-INFjs.quartz.properties file to a value, different from one set as mail.sender.username. But received emails have From Header as set in report.scheduler.mail.sender.username. ResolutionEmail detailed review (with complete header) showed that custom email (specified as report.scheduler.mail.sender.from) mentioned in X-Google-Original-From header property. As it turned out, Google does not allow sending mail from your account saying you are somebody you're not (other domain). So it overwrites Sender with account you authorized with saving what you've specified in X-google-original-from header. https://stackoverflow.com/a/27420030 A possible solution in this specific case is to manually add custom email account in GMail service as described in article: https://support.google.com/mail/answer/22370?hl=en Solution tested with TIBCO JasperReports® Server v.6.4.0 Ref. Case 01580137

Issue DescriptionCustomer has a requirement to create a query Input Control based on calling Oracle Stored Procedure. How to achieve it? ResolutionTIBCO JasperReports® Server does not support using Oracle Stored Procedure which returns ref_cursor as Input Controls datasource, out-of-the-box. But it might be possible to achieve the requirements by creating a function in Oracle database which 'processes' cursor returned by stored procedure and returns data as a table. Let's say, we have a stored procedure without IN parameters, like create or replace procedure "AS_SP" (p_cursor OUT SYS_REFCURSOR) as begin open p_cursor for select distinct shipcountry from orders order by shipcountry; end AS_SP; which returns list of countries from a table. The idea is: Create a custom Oracle typecreate or replace type myTable as table of VARCHAR2(100); Create a function which fetches cursor records and creates myTable object:create or replace function as_function RETURN myTable as i int; l_var VARCHAR2(100); l_data myTable := myTable(); p_rc sys_refcursor; begin i:=0; as_SP(p_rc); loop FETCH p_rc INTO l_var; EXIT WHEN p_rc%NOTFOUND; i:=i+1; l_data.extend; l_data(i) := l_var; end loop; return l_data; end; Use that function to retrieve data for input control:SELECT COLUMN_VALUE from TABLE(cast(as_function as myTable)) Solution tested with TIBCO JasperReports® Server v.6.4.0 Ref. Case 01578476

Issue DescriptionCustomer is running a report via JasperReports Server HTTP API and specifying &viewAsDashboardFrame=true parameter in URL to remove Report Viewer toolbars: [toc]<iframe src="http://localhost:8710/jasperserver-pro/flow.html?_flowId=viewReportFlow&_flowId=viewReportFlow&ParentFolderUri=%2Fpublic%2FCases%2F01576058&reportUnit=%2Fpublic%2FCases%2F01576058%2FChart_Report&standAlone=true&viewAsDashboardFrame=true&j_username=XXX&j_password=YYY"> The report contains a Chart with drilldown hyperlink to call a child report. The problem is that he clicks on Chart hyperlink, the child report opens without viewAsDashboardFrame property, with Report Viewer Toolbars Customer needs a way to hide toolbars for the child report as well ResolutionA possible solution is to manually add one more parameter to the Chart hyperlink details: viewAsDashboardFrame with value constant value true. As a result, option "&viewAsDashboardFrame=true" will be automatically added to the URL and you get no toolbars. Solution tested with TIBCO JasperReports® Server v.6.4.0, v.7.1.0 Ref. Case 01576058

Issue DescriptionCustomer has a requirement to create a Dual Level Doughnut (Donut) HTML5 chart based on 2 independent series. Attached are sample data for the chart and desired output: ResolutionA simple (one-level) Doughnut chart can be easily implemented by specifying plotOptions.pie.innerSize chart property. In order to create a Dual Level Doughnut chart, you can create a column chart with 2 measures and change it's type to pie value via chart.type property. Attached is a sample JRXML report based on dummy query. Generated output is Solution tested with TIBCO JasperReports® Server v.6.4.0 Ref. Case 01567088 highcharts_doughnutchart.jrxml

Issue DescriptionCustomer created Ad Hoc View Crosstab based on Oracle datasource. Then, they created a Calculated Field using expression like Contains("field_name",'string_value') and made it available in Measures panel ('Use as Measure' context menu). If they try to add that measure into Crosstab Rows or Columns, it failes with error: "An error occured while performing the previous request"[/code]ResolutionFor any measure, added to Ad Hoc Crosstab, the engine tries to perform Summary Calculation. By default, for that custom created calculated measure, CountAll Summary Calculation is used, it can be checked in 'Edit Calculated Measure' window, 'Summary Calculation' tab. As a result, generated SQL is incorrect from Oracle database point of view: select * from ( select count( regexp_like("product1"."BRAND_NAME",'Best', 'i')) as "CountAll_Contains_product1_BRA",..... ) where ROWNUM <= 1000[/code]and failes with error Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "REGEXP_LIKE": invalid identifier[/code]as REGEXP_LIKE is a conditional operator which returns TRUE or FALSE and can't be used in Count() aggregation. A possible solution here is to manually specify custom Summary Calculation expression, for example: CountAll(IF(Contains("Brand", 'Best'), 1, 0))[/code]This expression returns count of records where Brand field contains Best. As a result, a correct SQL is generated: select * from ( select count(case when ( regexp_like("product1"."BRAND_NAME",'Best', 'i')) then (1) else (0) end) as "CountAll_IF_Contains_product1_", Sum("sales_fact_1998_1"."STORE_SALES") as "Sum_sales_fact_1998_1_STORE_SA", "product1"."BRAND_NAME" as "product1_BRAND_NAME" from "FOODMART"."SALES_FACT_1998" "sales_fact_1998_1" inner join "FOODMART"."PRODUCT" "product1" on (("product1"."PRODUCT_ID" = "sales_fact_1998_1"."PRODUCT_ID")) group by "product1"."BRAND_NAME" order by "product1_BRAND_NAME" ) where ROWNUM <= 1000[/code] Solution tested with TIBCO JasperReports® Server v.6.4.0, 6.4.2 Ref. Case 01567069

Issue DescriptionCustomer created an Ad Hoc View Crosstab, enabled Crosstab Column Header per article https://community.jaspersoft.com/wiki/missing-column-titles-when-exporting-ad-hoc-crosstab-view and exported the view to MS Excel. As a result, the second column is partially merged with the first column: End users usually don't want this column (the seconds one), so they need to delete it every time after they do the export. How to avoide such the behavior? ResolutionCrosstab report generator creates an extra Frame element with incorrect size/position which leads to creating an extra column. A possible solution/workaround is to create a report from the affected Ad Hoc View, modify it in Studio and correct Frame element size or position: Solution tested with TIBCO JasperReports® Server v.6.4.0 Ref. Case 01560446

Issue DescriptionWe created Ad Hoc View from a topic. The View works without any issues but when we try to export it from the repository (right-click and select Export context menu), we get 'Export failed. The server has encountered an unexpected error' message in JasperReports Server UI.[/code]TIBCO JasperReports® Server log file contains exception like ' Export failed:com.jaspersoft.jasperserver.export.service.ExportFailedException: URI /organizations/organization_1/adhoc/topics/OrdersTopic_files/OrdersTopic_files was not found in the repository'[/code]What is the reason of the error and how to fix it? ResolutionAs it turned out, the Topic in question used so-called 'Local Datasource' instead of a link to a datasource stored in the repository: Topic Local datasource details are stored inside the topic internal definition. As a result, when a user tries to export Ad Hoc View based on such topic, export process can't locate datasource details (in topicName_files folder) and failes. While the issue is not fixed, a workaround is to use a datasource stored in the repository. Solution tested with TIBCO JasperReports® Server v.6.4.0, 6.4.2 Ref. Case 01563565