[Adding WITH statements to Security Validator]

TIBCO JasperReports® Server
Security Guide
Software Release 8.0

section 4.4.3 Customizing Query Validation

If you wish to use a different validator expression for queries, always create a new validator expression with a
new name in validation.properties, then substitute that name in the validation rule in security.properties. For
example, if you wish to forbid queries from running stored procedures in your database, you can add the
following validator expression in validation.properties:

#Validator.ValidSQL=(?is)^\s*(select|call)\b((?!\binto\b)[^;])*;?\s*$
Validator.ValidSQLnoProc=(?is)^\s*(select)\b((?!\binto\b)[^;])*;?\s*$

Then you would uncomment and modify the validation rule in security.properties as follows:

# Main SQL execution point
sqlQueryExecutor=Alpha,ValidSQLnoProc,500000,true,SQL_Query_Executor_context

It is also possible to have two or more validation rules that will be applied sequentially (logical AND) until one
fails. The rules must have the same names but with a numerical suffix, for example:

# Main SQL execution point
sqlQueryExecutor=Alpha,ValidSQL,500000,true,SQL_Query_Executor_context
sqlQueryExecutor2=Alpha,ValidSQLCustom,500000,true,SQL_Custom_Executor_context

With multiple rules for query validation, each rule is applied in the order listed until one fails. When one
rule fails, the whole validation fails.

 

[Scheduling a job on the last day of every month]

1) Refer to https://docs.oracle.com/cd/E12058_01/doc/doc.1014/e12030/cron_expressions.htm to set the correct cron expression for the last day of month report execution. for example:

0 15 10 L * ?

Fire at 10:15 AM on the last day of every month

2) Create a Calendar Recurrence type job with any settings such as Every Month Every Day 0 hour and 0 minute from JRS scheduler web UI.

3) After the job is created, find the job ID, such as 20318 and use it in the following SQL to update this job's cron expression to run it on the last day of the month.

update qrtz_cron_triggers set cron_expression = '0 15 10 L * ?' where trigger_name = (select trigger_name from qrtz_triggers where job_name = 'job_20318')

[How to disable inserting rows into JIAccessEvent table]

This table provides JasperReports Server (JRS) access logging information for the past 60 days (default) to tell who did what, how and when. The information is used for JRS search function. If disabled, users will not be able to find any access information for repository resource search.

[How to remove "Need help logging in" from login page?]

Add the following to overrides_custom.css file should meet the requirement:

 

#loginPage a#needHelp {display: none;}

 

We prefer to use stylesheet for web UI customization over the server page modification for easy maintenance and deployment migration and upgrade.  That is the reason JRS puts this overrides_custom.css file in the deployment in the first place. Hope this helps to clarify and explain the situation.

[Ad hoc template header&footer]

<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="BlahBlahBlah" pageWidth="792" pageHeight="612" orientation="Landscape" columnWidth="752" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isSummaryWithPageHeaderAndFooter="true" uuid="bbcb539b-1bc4-4aae-b4e0-xx123456dxyz">
 

[Virtualizers in JasperReports]

This is a JRL core feature not involving professional/commercial components.

[Ad Hoc filter invalid date or timestamp]

jasperserver_config.properties:

date.format=MM/dd/yyyy
datetime.format=MM/dd/yyyy HH:mm:ss
time.format=HH:mm:ss
 

calendar.date.format=mm/dd/yy
calendar.datetime.format=mm/dd/yy HH:mm:ss
calendar.time.format=HH:mm:ss

 

calendar.datetime.separator=u0020
 

validation.date.pattern=/^d{2}/d{2}/d{4}$/
validation.datetime.pattern=/^d{2}/d{2}/d{4}sd{2}:d{2}:d{2}$/
validation.time.pattern=/^d{2}:d{2}:d{2}$/
 

repository.date.format=M/d/yyyy
repository.current.year.date.format=MMMMM d
repository.datetime.format=M/d/yyyy hh:mmaaa
repository.time.format=hh:mmaaa

report.scheduling.output.format.1=pdf
report.scheduling.output.format.2=html
report.scheduling.output.format.3=xls
report.scheduling.output.format.4=rtf
report.scheduling.output.format.5=csv
report.scheduling.output.format.6=odt
report.scheduling.output.format.7=txt
report.scheduling.output.format.8=docx
report.scheduling.output.format.9=ods
report.scheduling.output.format.10=xlsx
report.scheduling.output.format.14=pptx
 

input.password.substitution=~value~subst~

client.delimiters.thousands = ,
client.delimiters.decimal = .
client.currency.symbol = $

====================================================

 

 

See screenshot........

 

/sites/default/files/user_uploads/tchen/screenshot_6053.png

[Uncommenting is required to modify the validation.properties file's Validator.ValidSQL]

The information is in our official document TIBCO JasperReports® Server Security Guide Software Release 7.5, scetion 4.4.3 "Customizing Query Validation". An copy of the document is available at community site:

https://community.jaspersoft.com/documentation/tibco-jasperreports-server-security-guide/v7/protecting-against-sql-injection

"Customizing Query Validation"

 

[Getting "Invalid Credential" When Trying to Login to TIBCO JasperReports®​​​​​​​ Server 7.5 in a Two Nodes Server Farm Deployment]

You need to make sure the keystore files in the directory are accessible by the user running the web app process. Or you need to move the keystore to a location that permits such access. Please refer to the following wiki article on how to update keystore files to make the keystore relocation work.

https://community.jaspersoft.com/wiki/encryption-jasperreports-server-75#Updating_Keystore_files

 

[Sample Report to Create a Monthly Calendar]

https://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql

[Sample Report to Create a Monthly Calendar]

You can use a similar logic as shown for Sunday to designate a different color for a specific date - just use equal to instead the modulo operation to meet your needs. As for a relative date holiday such as US Thanksgiving Day for every fourth Thursday of November, you will have to make a decision based on month and date ranging from 23rd to 28th of November to see which one has remainder of 4 when divided by 7 with weekday offset on the first day of the month.  

[Where Can I Find JasperReports Ultimate Guide For Release Version 7.5.0?]

TIBCO JasperReports Server Ultimate Guide is different from JasperReports Library Ultimate Guide. One is for JasperReports Server  product and the other is for JasperReports Library "report engine" APIs.

This wiki article only covers JasperReports Library "report engine" APIs document. Hope this clarifies the issue. Thanks

[Creating domains based on AWS Athena]

Added EXTERNAL_TABLE, tested the connection, created Domain, and selected a schema, but tables are no where to be found. Did a browser debug and found object Error in domaindesigner.html page:

Numeric character reference does not resolve to a valid character

I am going to file a bug report on this issue most likely stemming from the new domain designer introduced in JRS 7.2.0 that broke the code to support this non-officially-supported fearture. :(

 

[Sample of JSON report with sub report]

Please find report templates for main and sub reports, their PDF outputs, and json data in the attached zip file.