eongaro

Link to view our Visualize.js API live samples We have developed a new documentation integration with GitHub for our JSFiddles that we hope developers will find useful. Enjoy! Download to view. Visualize.js is a JavaScript API framework used to embed JasperReport Server reports & visualizations inside web applications. It comes bundled with the commercial editions of JasperReports® Server, specifically Jaspersoft AWS, Enterprise or Professional (and as an add-on with Jaspersoft Reporting). Visualize.js currently does not support JasperReports Server Community Edition or the JasperReports® Library. With Visualize.js, developers can embed interactive HTML5 visualizations, dashboards or multi-element reports using standard JavaScript. Developers can quickly and easily call backend JasperReports Server services using REST calls for common functions such as user authentication, data connectivity, repository services and more. About these Examples The source code for these fiddles are hosted on Github. main_1.html main_2.html

IntroductionThis article applies to iReport Designer v5.6.1 and previous versions. It will not work in any version of Jaspersoft Studio or in iReport Designer v6.0.x. The purpose of this article is to describe how to use parameters in queries when using iReport to design a report from a Domain datasource. The article is written for an advanced user of iReport and JasperReports Server to use, it's meant to be more of a primer than a complete document. QueryDomain based queries use the DomEL language to query data. It's not SQL and therefore you can not use the same syntax for parameters, for example, parameters do not need the $P{syntax}, instead the field name is referenced. In the following example, we have a parameter declared in the JRXML called $P{prev_year}, the way to use that parameter is shown below: <query> <queryfields><queryfield id="total_apps"></queryfield></queryfields> <queryfilterstring>newSet2.Year == prev_year</queryfilterstring></query>[/code]CollectionsCollections or sets can be used as well. You can create a parameter that is a collection and put it in the query with the "in" operator. Refer to section 8.2.3 of the JasperReports Server User Guide Also, multi-select input controls can be populated by a Domain based query Populating from the ServerIt is also possible to populate the values of an import control from a query. Typically I would recommend prototyping this using the ad-hoc editor - create a ad-hoc view with a filter on the column you want to use. Create a report from the view and then in iReport you can see how it's set up. Here's one example: <!--?xml version="1.0" encoding="UTF-8"?--><query xmlns="http://www.jaspersoft.com/2007/SL/XMLSchema"> <grouplist> <group columnname="newSet1.region"></group> </grouplist> <queryfields> <queryfield id="newSet1.region"></queryfield> </queryfields></query>[/code]Here's the example in iReport: The "value" and "name field" would come from newSet1.region in this case See AlsoSection 8.2 of the JasperReports Server User Guide discussed the DomEL language in full detailOther Articles about the Domain Expression Language (DomEL)

Issue:[toc on_off::hide=1] Your are trying to add calculated fields to a domain. The calculation involves assessment of another field's value based on a condition or set of conditions. Typically (in code), this is done using if/else format, or ternary statements (condition ? 'value if true' : 'value if false'). How can this be achieved in a domain's calculated field? What is the syntax? Resolution:Conditions can be evaluated in calculated fields using the "if(condition,value if true,value if false)" function in DomEL. In the following example, I am determining the value of the field by evaluating another field's value (currentsuboppts.Amount). If currentsuboppts.Amount is less than or equal to 5000, the value of my field is the string '<=$5k'. Otherwise, it is '>$5k'. if(currentsuboppts.Amount <= 5000,'<=$5k','>$5k') another example using dates: if(time_by_day1.the_date == d'2013-01-07', null, time_by_day1.the_date) NOTE: When entering a string value into a calculated field formula, you must use single quotes ('), NOT double quotes ("). Note on Using GroovyAll expressions should be written in the DomEL language. If you want to use Groovy you will need to wrap the code into the groovy() function. But you can't mix DomEL statements and Groovy statements. If so you need to create a separate calculated constant field. Here's an example where we want to show the values of a tenantID and hide the rest. So for that we need to create two fields: Field1 (tenant) - Gets the tenant ID using groovy, expression: groovy('authentication.getPrincipal().getTenantId()') Field2 (anyName) - Based on tenant ID shows one field or another: if((mt_test.tenant == constant_fields_level.tenantID), mt_test.tenant, mt_test.hidden) 21:24, 16 July 2012 (UTC)

SummaryThis article descries how to use xpath2 to retrieve a remote XML web-service and publish it on JasperReports Server. This article assumes you are using JasperReports Server 4.7 or above iReportiReport already contains support for xpath2 and remote XML datasources. To configure a new remote XML datasource just create a new "Remote XML File Datasource" and set the query executor as xpath2. Writing the xpath2 query is beyond the scope of this document, however, you can take a look at this related article: Declare a field for XML datasource JasperReports Server PreparationBy default JasperReports Server does not ship with the xpath2 query executer, for this we'll need a jar file that comes with iReport. Copy jasperreports-extensions-3.5.3.jar from /ireport/modules/ext to JasperReports Server/jasperserver-pro/WEB-INF/lib/jasperreports-extensions-3.5.3.jar (or if using Jaspersoft Studio plugins/com.jaspersoft.studio.data_5.6.0.final.jar and plugins/com.jaspersoft.studio_5.6.0.final.jar)Add the following line to /jasperserver-pro/WEB-INF/classes/jasperreports.properties net.sf.jasperreports.query.executer.factory.xpath2=com.jaspersoft.jrx.query.JRXPathQueryExecuterFactory Restart TomcatReport UnitNow you can upload your report unit containing a valid xpath2 query. The key is uploading the report and setting it to Do not link a data source - the remote XML data source is easy enough to use, you simply need a parameter called $P{XML_URL} which contains the URL of an XML feed, for example: https://api.twitter.com/1/statuses/user_timeline.xml?include_entities=true&include_rts=true&screen_name=jaspersoft&count=200" ParametersYou can parametrize the xpath2 query as you wish, this is simple within iReport. If you want to parametrize the URL that you are calling, for example, changing the screen_name then you need to declare two parameters (note that the order in which they are declared is critical) Parameter 1: $P{twitter_account} Used for asking the user, in my example asking them the user name Parameter2: $P{XML_URL} Used for passing the URL to the report, in my example the default value expression is set to concatenate $P{twitter_account} to the URL like this: https://api.twitter.com/1/statuses/user_timeline.xml?include_entities=true&include_rts=true&screen_name=" + $P{twitter_account} + "&count=200 SubReportThis example contains a sub-report as well. You can upload the sub-report as usual and keep this in mind: You will want to "Use a datasource expression" that looks like this one: ((net.sf.jasperreports.engine.data.JRXmlDataSource)$P{REPORT_DATA_SOURCE}).dataSource("//statuses/status[id=" + $F{id} + "]/entities/user_mentions/user_mention") No need to declare any special parameters in the sub-report, it just works! DebuggingYou can add net.sf.jasperreports.engine as DEBUG (as superuser) to http://localhost:8080/jasperserver-pro/log_settings.html - you should get some stats about the datasource if it fails. Sample ReportsIMPORTANT NOTE: Twitter has deprecated their 1.0 API which this sample was built upon, unfortunately the sample will need to be re-written to work with the 1.1+ API and this report does not currently work. (written February 2014)ThisJRXML_Files.zip contains two JRXML files, you will need to create Input Controls to get them to work, I suggest getting the Complete Sample instead Note that the sub-report expression is set to repo: report67.jrxml - change this as you wishComplete Sample (repository export):This is arepo_output.zip and uses parametrization. The sample should execute upon being imported with the js-import.sh tool, for example, run the command: ./js-import.sh --input-zip /path/to/remote_xml_sample.zip - remember to install the xpath2 query executor first! The sample will be found in /Reports/Samples/Remote XML Sample ScreenshotWhat's a sample without a of good screenshot?

The Jaspersoft MongoDB ConnectorThe Jaspersoft MongoDB Connector works with iReport, JasperReports, and JasperReports Server. In previous versions (4.7 and below) the MongoDB driver needed to be installed seprately, you can find instructions on installation for older versions are here: MongoDB Install Target AudienceUsers who want to integrate Jaspersoft's JasperReports Server with MongoDB. This document assumes that the user already has familiarity with MongoDB and already has data in MongoDB collections. Getting StartedAdd a new datasource of type "MongoDB Connection" Set an appropriate url and test the connection 3. Create a reportThe simplest way for a new user to create a new report is with the Report Wizard. File → New...Choose any template and click "Launch Report Wizard".Set the report name and location.Create a query to retrieve data.There is no visual query editor, so the button "Design query" is inactive.The Jaspersoft query language for MongoDB is documented here: Jaspersoft MongoDB Query Language Sample queries Minimal: { 'collectionName' : 'accounts' }[/code]Specify the fields returned and sort the results: { 'collectionName' : 'accounts', 'findFields' : {'name':1,'phone_office':1,'billing_address_city':1,'billing_address_street':1,'billing_address_country':1}, 'sort' : {'billing_address_country':-1,'billing_address_city':1}}[/code]Filtered and parameterized: { 'collectionName' : 'accounts', 'findQuery' : { 'status_date' : { '$gte' : $P{StartDate} }, 'name' : { '$regex' : '^N', '$options' : '' } }}[/code]MapReduce job: { 'collectionName' : 'zips', 'sort' : { 'value.population' : -1, }, 'mapReduce' : { 'map' : 'function () { emit (this.state, {population : this.pop}); }', 'reduce' : 'function (key, values) { var total = 0; for( var index = 0; index < values.length; index ++) { total += valuesindex.population; } return {population : total} }', 'out' : 'totalPopulation' }}[/code]4. Deploy the query executer to JasperReports ServerImport note: These steps are only required if you have JasperReports Server version 4.5.1 or below Copy the files in WEB-INF from step 1 into /webapps/jasperserver-pro/WEB-INF Be sure to keep the folder structure when copying the files.Start or restart JasperReports Server.5. Deploy the report to JasperReports ServerCreate the Data SourceDefine the data source in JasperReports Server Right-click a folder, add a Data Source Set the Data Source properties Create and Run the ReportUse either the JasperReports Server web interface or the iReport Repository Navigator to deploy the report. Deploying from iReport is simpler in most cases. Run the report Search for the report or browse to the report in the JasperReports Server repository. Related LinksRead the query referenceDownload JasperReports Server: Community Edition or Commercial Edition.Download the Jaspersoft MongoDB Connector.MongoDB ResourcesMongoDB Website: http://www.mongodb.org/MongoDB Documentation: http://docs.mongodb.org/manual/MongoDB Training: http://education.mongodb.orgThe Jaspersoft MongoDB Query LanguageGeospatial Queries Example

In Ad-Hoc editor mode, there's a button for Sample Data and one for Full Data. Traditionally these would only limit the amount of data loaded into the server's memory, however the full query would always be executed against the database. Starting in Version 4.7 of JasperReports Server there are options, to issue LIMIT BY, ROWNUM or TOP queries against the database when using Domains. In Version 5.0 this behavior is supported for more database vendors and the limiting behavior is enabled by default. The configuration file controlling this behavior is tomcat/webapps/jasperserver-pro/applicationContext-semanticLayer.xml. In Version 5.0 and above, to peruse the keywords and positions of the limiting clauses, first find your database vendor's sqlGenerator 'ref' within serverTypes of the dbServerTypeConfigFactory bean. Second, within your specific sqlGenerator bean, like oracleSQLGenerator, inspect the limitSQLMap property. In 4.7.0, to add this behavior for Postgres and MS Sql Server only, you will need to edit tomcat/webapps/jasperserver-pro/applicationContext-semanticLayer.xml - make sure to restart Tomcat after this change. Using the pre-existing mysqlSQLGenerator bean as your example, find the bean specific to your database, like postgreSQLGenerator and modify the entries corresponding to the limitPosition and limitKeyword in the table below. IMPORTANT NOTE: This feature is still considered experimental in 4.7.0, make sure to test your database before rolling this into production MySQL Bean ID mysqlSQLGenerator limitPosition AT_END limitKeyword limit Postgres Bean ID postgreSQLGenerator limitPosition AT_END limitKeyword limit Microsoft SQL Server Bean ID sqlserverGenerator limitPosition BEFORE_FIELD_LIST limitKeyword top

Problem[toc on_off::hide=1] You get an error when launching Jaspersoft ETL 4.2.3 on a Mac The JETL_Express-macosx-carbon executable launcher was unable to locate its companion shared library SolutionFind the file <JETL Install Path>/JETL_Express-macosx-carbon.app/Contents/MacOS/JETL_Express-macosx-carbon.ini Replace the contents of that file with this one: -startup ../../../plugins/org.eclipse.equinox.launcher_1.1.0.v20100507.jar --launcher.library ../../../plugins/org.eclipse.equinox.launcher.carbon.macosx_1.1.0.v20100503 --launcher.XXMaxPermSize 256m -vmargs -Xms40m -Xmx256m -Xdock:icon=../Resources/Eclipse.icns -XstartOnFirstThread -Dorg.eclipse.swt.internal.carbon.smallFonts -Dosgi.instance.area.default=../../../workspace

When defining subreports that use a domain datasource within iReport, there are a few parameters that must be defined to pass the appropriate connection details to the subreport. Parameters to add to main report and subreport filesIn main report and every subreport JRXML file, declare the following parameters: [toc]<parameter class="com.jaspersoft.commons.semantic.Schema" isforprompting="false" name="slSchema" /><parameter class="com.jaspersoft.commons.semantic.datasource.SemanticLayerFactory" isforprompting="false" name="slFactory" />[/code]Declaring paramters to pass from between the main report and subreportIn main report, declare the above parameters to pass to subreport (This is done within the tag): <subreportparameter name="slSchema"> <subreportparameterexpression> <!--[CDATA[$P{slSchema}]]--> </subreportparameterexpression></subreportparameter><subreportparameter name="slFactory"> <subreportparameterexpression> <!--[CDATA[$P{slFactory}]]--> </subreportparameterexpression></subreportparameter>[/code]Note: Note that these parameters can be added through the iReport UI even though they are represented as the resulting xml. How To: Domain based Sub-Reports with JasperReports Server

Here are the steps for adding "Standard Deviation" to the Adhoc Crosstab Designer Open "actionModel-adhocCrosstab.xml". Add the following into the measureColumn and measureRow sections: <option action="selectFunction" actionargs="StandardDeviation" clienttest="isSelectedMeasureNumeric" isselectedtest="isSelectedSummaryFunction" isselectedtestargs="StandardDeviation" labelkey="Standard Deviation" /> Then open "applicationContext-adhoc.xml". And add the following lines in aggregatedConfigs section: <bean class="com.jaspersoft.ji.adhoc.strategy.AggregateConfig"> <property name="name" value="StandardDeviation" /> <property name="functionName" value="stddev" /> <property name="calcMethod" value="sqlUnionAll" /></bean>

Configuring JasperReports Server with the Cassandra ConnectorSummaryDeploy the connectorDefine a connectionDeploy reportsDetailsDeploy the connectorDownload the Cassandra Connector. Unzip it. Copy the files in WEB-INF into your JasperReports Server instance. Be sure to keep the folder structure when copying the files. In connector version 0.5.1 these are the files: Special fix: remove the Hive libraries which ship with JasperReports Server since these conflict with Cassandra (clashing Thrift versions). Here are the scripted changes for a Linux instance, but you can just delete (or rename) the files manually: Restart JasperReports Server. Define a connectionRight-click on a folder in the Repository View in JasperReports Server. Use the context menu to add a data source. The data source definion should follow the model shown below. Deploy reportsUse either the JasperReports Server web interface or iReport to deploy the report to JasperReports Server.

You can design standard reports to use column based security with the following features: Parameters: LoggedInUsername and/or LoggedInUserRolesJasperReports element: Print When ExpressionJasperReports element: Remove Line When Blank[toc]Run-Time Examples using TIBCO JasperReports® ServerIn the example below, four text fields and labels are configured to display only when the logged-in user's roles include ROLE_ADMINISTRATOR: Here is the same report run by a user whose roles do not include ROLE_ADMINISTRATOR: Quick View of the Layout in iReportThis is a brief look at a some of the field attributes in the iReport Designer. Column based security will be enforced at runtime when JasperServer matches the logged-in user's credentials with the LoggedInUsername and/or LoggedInUserRoles parameters. Define the Parameter(s)TIBCO JasperReports Server 3.7 and above include parameters that reference the logged-in user's credentials. The two most commonly used for column based security are: LoggedInUsername (class = java.lang.String)LoggedInUserRoles(class = java.util.Collection)These parameters are built-in and known to JasperServer at runtime. However, you need to define them in the JRXML using iReport) in order to reference them in expressions.Note that case is important: the n is lower case in LoggedInUsername and the R is uppercase in LoggedInUserRoles. Set Print When Expression ElementsPosition the field you want to optionally display onto the report layout. With the field select click the ... button to the right of the Print When Expression element in the Properties box. Enter an expression that tests for a match on username or role. If the expression results in "true" the field will display; otherwise it will not display. Here are two examples: Security based on user name (these are for Groovy as the report language): ($P{LoggedInUsername}.contains("jasperadmin")) Security based on role: $P{LoggedInUserRoles}.contains("ROLE_ADMINISTRATOR") Do this for every field you wish to protect. Removing Blank SpaceRemoving blank space only eliminates vertical space. Refer to "Removing Blank Space" in the JasperReports Ultimate Guide for specifics. To take advantage of this feature, select the field and click Remove Line When Blank in the Properties panel. Report JRXMLJasperReports Server Ultimate Guide - this is the finished JRXML file for this tutorialSee AlsoDesigning Row-Based Security into Standard Reports - similar concept but using Attributes vs Roles and Rows vs ColumnsBuilt-in_Parameters_for_Logged_In_User - A list of all built-in paramters for Logged in UsersAccessing_the_Logged_In_User_within_a_ Query-based_Input_Control - Query Based input controls with LoggedInUserSection 4.11.4 of the JasperReports Server Ultimate Guide: Built-in Parameters for Query-based Input Controls (pp 63-64, version 0311-JUG40-8)

[toc on_off::hide=1] Session timeouts dictate how long a user is allowed to be inactive before being automatically logged out of JasperReports Server. TomcatEdit <tomcat>webappsjasperserver-proWEB-INFweb.xml and change the sesssion-timeout. The default is 20 minutes. <session-config> <session-timeout>20</session-timeout> </session-config> Restart the application server to make the setting take effect. TipsIf you're trying to run a very slow report, make sure that the database timeout is shorter than the session timeout. See AlsoJasperReports Server Configuration File - Web.xml

OverviewThis how-to will explain how to create a report that displays data for stores that the currently logged in user has access to. The concept has been simplified using the SuperMart sample data that ships with JasperReports Server. The scenario is that you have two managers: NameTitleStoresMax FlowersCA DirectorAlameda,Beverly Hills,Los Angeles,San Francisco,San DiegoJoe MartsStore DirectorLos Angeles[toc]The most robust way to handle row and column level data security in JasperReports Server is to utilize a Domain and a security file. This is covered in Chapter 6 of the JasperReports Ultimate Guide (SECURING DATA IN A DOMAIN). The how-to in this page utilizes some of the same techniques but at the SQL level without the need for a domain. Again, the recommended way to achieve data security is via a domain, this is another less-robust method. That said, let's get started with the example. Creating UsersLet's create our two users: Max and Joe. Log in as the jasperadmin and create the two users. Once the users are created, add some attributes to the JiProfileAttribute table. This is covered in depth in the JasperReports Server Ultimate Guide, Section 6.2.5.5 "Inserting Attributes into the Profile Attribute Table in SQL". Note that in version 5.0 and above, you can now manage these in the UI, as in the example below: Note: the example above is for PostgresSQL syntax For older version of the product or if you prefer to do it programatically (or through API): For Max (in my case user id #10): INSERT INTO JIProfileAttribute( attrName ,attrValue ,principalobjectclass ,principalobjectid) values( "Cities" ,"'Alameda','Beverly Hills','Los Angeles','San Francisco','San Diego'" ,"com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoUser" ,10);[/code]For Joe (in my case user id #11): INSERT INTO JIProfileAttribute( attrName ,attrValue ,principalobjectclass ,principalobjectid) values( "Cities" ,"'Los Angeles'" ,"com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoUser" ,11);[/code]NOTE: this is for MySQL syntax You should see the Profile Attributes in JasperReports Server's User Interface now: Creating the ReportNow a report can be created. There's a few special things needed to take advantage of the Profile Attributes. QueryIn this example the query is structured something like this: SELECT sum(store_sales) store_city FROM stores WHERE store_city IN ($P!{LoggedInUserAttribute_Cities})[/code]So for example, what JasperReports Server will do when Max runs this report is to expand the WHERE clause to: ... WHERE store_city IN ( 'Alameda&' ,'Beverly Hills&' ,'Los Angeles' ,'San Francisco' ,'San Diego&')[/code]ParameterIn order for the report to be aware of the Logged In User Attributes you will need to declare them in the form of Parameters in your report. NOTE: Make sure there is no default value for the parameter, sometimes iReport will fill in a blank default value, clear it out in the XML file ResultsSo when Joe runs the report he only sees the cities he's allowed to see: And when Max runs the report he sees the cities he's allowed to see: Other ConsiderationsInput ControlsThe same logic can be applied to Query based Input Controls so that the user only sees applicable fiels. An example Query for an Input Control might look like this: SELECT store_city FROM stores WHERE store_city IN ($P!{LoggedInUserAttribute_Cities})[/code]LimitationsNot every user has the Profile Attribute of Cities, for example running this report as jasperadmin caused errors. An additional parameter was added to the example report. The parameter LoggedInCheck was added to generate valid SQL even when $P{LoggedInUserAttribute_Cities} is empty. This was acheived via the default value expression of LoggedInCheck, like so: $P{ LoggedInUserAttribute_Cities}.isEmpty() ? "'dummy'" : $P{LoggedInUserAttribute_Cities }[/code]In addition the chart in the example report is dependant on getting data back from SQL so there's a Print When Expression on the chart to only print if the $P{LoggedInUserAttribute_Cities} is not empty, here is that expression: $P{LoggedInUserAttribute_Cities}.length() > 0[/code]Example ReportSample.zip - this file contains the JRXML necessary to deploy to Jasperserver. It is not necessary to create input controls on the JasperReports Server side.Choose /analysis/datasources/FoodmartDataSourceJNDI as this report's data sourceSee AlsoDesigning_Column-Based_Security_into_Standard_Reports - similar concept but using Roles vs Attributes and Columns vs Rows.Built-in_Parameters_for_Logged_In_User - A list of all built-in paramters for Logged in UsersAccessing_the_Logged_In_User_within_a_Query-based_Input_Control - Query Based input controls with LoggedInUserJasperReports Server Administration Guide: Built-in Parameters for Query-based Input Controls

This article provides information about calling stored procedures in iReport Designer with Microsoft SQL. Create a Stored Procedure on Micrsoft SQL-- =============================================-- Author: author name-- Create date: created-- Description: description-- =============================================CREATE PROCEDURE ireport @parameter1 INTAS BEGIN SELECT 125 * @parameter1 AS somefield ENDGO[/code]This particular dummy stored procedure simply takes a parameter and multiplies it by 125. Not very useful but a good proof of concept. Chose plsql as the query language Create Parameters and Simply call the Stored Procedure by Name In this case, the name of the stored procedure is 'ireport' and it takes exactly one parameter. The parameter was defined with a default value expression of "2" which simply multiplies 125 by 2. That's it, simply use the fields in your report and the user will be prompted via parameter, the parameters might also come from LoggedinUser/role/attribute which helps tie into security models. It is also possible to save this report in the Topics folder of JasperReports server to take advantage of the Ad-Hoc engine and stored procedures. Note: You should disable the rows() affected message by running SET NOCOUNT ON on the SQL server It may be necessary to add an exclamation point after the $P, for example $P!{parameter1}.

iReport - Calling Stored ProceduresThis article provides information about calling stored procedures in iReport Designer with different databases. Various query languages are supported: MySQL Choose SQL as query language. Call your procedure like below: call P_YOUR_PROCEDURE( $P{param_1}, $P{param_2},..., $P{param_X} ) Oracle10g/XEImportant: Calling an Oracle stored procedure requires the use of a Reference Cursor as an OUT parameter. The screen shot below shows a simple stored procedure that includes a reference cursor as an OUT parameter. In iReport, define a Report Datasource to connect to your Oracle database. If the name of your JDBC driver displays in red (rather than black) in the dropdown list, then the driver cannot be located in the iReport classpath. Refer to the iReport Ultimate Guide, section 2.9: Creating a JDBC Connection. Create a new report. Choose a template and click Open this Template and complete the steps. (The Launch Report Wizard only supports SQL queries.) Select the datasource you created above. Edit the report Query. Choose plsql as query language. Choosing plsql creates a built-in parameter named ORACLE_REF_CURSOR with java.sql.ResultSet as its parameter class. Note: The plsql query executer was introduced in iReport 3.6. It is not yet integrated into JasperServer as of version 3.7. A separate article explains how to deploy the plsql query executer to JasperServer. Call your procedure like below. The parameter $P{ORACLE_REF_CURSOR} represents the oracle reference cursor and it is MANDATORY. Also, don't forget the curly brackets {} around your call. {call P_YOUR_PROCEDURE( $P{param_1}, $P{param_2},..., $P{param_n}, $P{ORACLE_REF_CURSOR} )} Create your fields to match what your procedure returns. Example: if your ref cursor returns a column named FIRST_NAME, then create a java.lang.String field named FIRST_NAME ( $F{FIRST_NAME} ). Drag fields into your report design and run (Preview) your report!PostgreSQLNote: this article will not cover all the possibilities of calling PostgreSQL functions. PostgreSQL allows you to create 4 types of stored functions (or procedures): query language functions (SQL) procedural language functions (PL/pgSQL or PL/Tcl) internal functions C-language functionsFor further information, please visit PostgreSQL online manual, chapter User-Defined Functions. Usually, when you call your function in pgAdmin III, you can use this same call in iReport. Take a look at the example below. SQL Functions Returning TABLE Choose SQL as query language. Call your procedure like below: SELECT *FROM your_sql_procedure( $P{param_1}, $P{param_2},..., $P{param_X} ) Microsoft SQLCalling Microsoft SQL Stored Procedures from iReport See also...JasperServer - Calling Oracle Stored Procedures

Relational databasesMicrosoft SQL databaseConnecting iReport to a MicrosoftSQL datasource Oracle databaseOracle Datasource Pervasive PSQL databaseConnecting iReport to a Pervasive datasource

[toc on_off::hide=1] The following are steps that can be followed to set up a data source within iReport to connect to an Oracle database: Note:These instructions will work for iReport version 3.1 and greater. Download the JDBC driver (ojdbc-[your version].jar) from Oracle @ http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html in iReport, go to the following menu Window =>Services Expand the Database tree that shows in the Services window and right-click on the Drivers folder and select "New Driver..." Add the jar file; Give it a name i.e. "Oracle Thin" and click OK Right Click on the new "Oracle Thin" driver that appears in the Services window and selected "Connect Using..." Set up the database connection - as an example, you might use "jdbc:oracle:thin:@remotehost:1521:orcl", your username and password - and click ok Add a new datasource using the NetBeans Database JDBC connection - the new connection defined should be in the dropdown Open the report wizard and you are on your way.... See Also JasperReports Server Datasources

[toc on_off::hide=1] You will need to enter the username as username|organization_id

NOTE: This functionality was replaced by Relative Date functionality. Find more here and hereDynamic Dates in ReportsThis article covers a commonly asked question of: How do I create a report with dynamic dates? This is important for reports that are scheduled to run on a regular basis, but for which you want the time date range of the data to change based on the date the report is run. Some example solutions follow: Creating a report that shows activity in the last 10 days...You may want to simply create a report that pulls activities for the past 10 days, regardless of the date that it is run. There is 2 ways to achieve this. There is the possibility in iReport to create a parameter -let's call it today- which returns today's Date with the default expression new Date() [/code](Disable prompt and give it class java.util.Date) Then from that parameter -today- you can generate any date passing parameters to the method Date(). You can for example create a parameter returning the date of 10 days ago with this default expression new Date($P{today}.year, $P{today}.month, $P{today}.date-10) [/code](same, disable prompt and give it class java.util.Date) In the same way you could generate parameter: year to date:new Date($P{today}.year, 0, 1) [/code]month to date:new Date($P{today}.year, $P{today}.month, 1) [/code]beginning of last month:new Date($P{today}.year, $P{today}.month - 1, 1) [/code]end of last month:new Date($P{today}.year, $P{today}.month, 0) [/code]Those parameters should be used in the SQL query to select the right data which you want to be displayed in your report. Or this can also be done with Scriptlets by using date parameters with a default value expression initialized with a calculated date - the current date or the current date minus 10 days. By doing this, the value of these parameters will be determined at run time based on the current date and can be used to filter your query as required. You could also add a parameter and input control to allow you to specify the number of days back you wish to retrieve data for at report run time. Note: Subtracting 10 days from the current date, using a single expression may not be easy. You may need to create a helper class to deploy with your report or in your environment. The Apache Commons Lang library is one option for calculating dates in a single expression. For example, you may have one parameter "TODAY" of type java.util.Date with a default expression of: new java.util.Date() [/code]and another parameter "TENDAYSAGO" (also of type java.util.Date) with a default expression of: org.apache.commons.lang.time.DateUtils.addDays($P{TODAY},-10) [/code]This leverages the DateUtils.addDays method from the Apache Commons Lang package. The addDays method was introduced in version 2.2 of the Apache Commons Lang library. You may need to download and add commons-lang-2.2.jar (or newer) to the Jaspersoft classpath. Creating Reports with Input Controls with Dynamic DatesPerhaps you would like your users to be able to select from predetermined calculated dates, for example: TodayOne Month AgoBeginning of Current MonthEnd of Current MonthBeginning of Current YearBeginning of Current Weeketc.Achieving this is also possible, yet takes a little more effort. To start, create a parameter of String type with a matching single-select input control with the predefined String values you would like to have available to the user. For this example, we will call this "DATELITERAL"Next, create a class with a method that converts the string values to the equivalent Date. For example, something like: public static Date getDate(String dateLiteral) { switch(dateLiteral) { case "Today": return new Date(); break; case "One Month Ago": Date today = new Date(); Calendar cal = Calendar.getInstance(); cal.setTime(today); cal.add(Calendar.Month, -1); return cal.getTime(); break; .... default: //convert MM-DD-YYYY into Date and return } } [/code]Now, create a parameter of Date type with a default expression like: myClass.getDate($P{DATELITERAL}) [/code]

Issue:[toc on_off::hide=1] How do I hide the back button on the report viewer? (viewReportFlow) Resolution:JasperReports Server >= 4.0In the file webapps/jasperserver-pro/WEB-INF/jsp/modules/viewReport/ViewReport.jsp You can hide the back button here by commenting out some text. Note that the Back button is class "button capsule up first" so you'll also need to consider changing the "Options" button to this class so that it's rounded off. The thing there is that the Options button is not always shown so you'll have to edit a little bit more JSP, this should be a reasonable start though. <div class="toolbar"> <ul class="list buttonSet"> <!-- <li class="leaf"> <button id="back" class="button capsule up first"> <span class="wrap"> <spring:message code="button.back"/> <span class=""></span> </span> </button></li> --> JasperServer versions < 4.0In ../WEB-INF/jsp/viewReport.jsp: Find (around line 42) <body style="background-color:#ffffff"> <c:set var="showClose" value="${isSubflow}" scope="request"/> <% if (("true".equals(request.getParameter("standAlone"))) || ("true".equals((String)request.getAttribute("standAlone")))) { %> <c:set var="showClose" value='false' scope="request"/> <% } %> Replace: <c:set var="showClose" value='true' scope="request"/> with: <c:set var="showClose" value='false' scope="request"/> No need to restart JasperServer Ref. Case #00020273 -- 17:28, 19 May 2011 (UTC)

IntroductionThis document is an example about integrating a CAS server running on Ubuntu with a JasperReports Server running on Windows. It's divided into two sections: Installing CAS on UbuntuWe'll install a sample CAS application in a Ubuntu instance, which will run on top of a Tomcat 6 server. (CAS does not provide user contexts, like roles and organizations). Configuring JasperReports Server to use CASThis article assumes you have a running JasperReports Server. To get the integration going, we'll need to modify the Tomcat startup script, and two JasperReports Server Spring configuration files: applicationContext-security.xml, and, if you're using multi-tenancy, you'll also need to edit applicationContext-multiTenancy-security.xml. Install CAS on UbuntuWe're assuming that: You are using at least Ubuntu 10.04You have Java set up in your environment - you'll need the full JDKYou have Tomcat installed - the example below uses Tomcat6In this example, we're doing everything as the root user; in a real environment you would have to adapt it to your needs. You can download the CAS application from here: http://www.jasig.org/cas/download, make sure to download the .tar.gz version. Once you've downloaded it to your Ubuntu environment, un-zip and un-tar the file. Then find the WAR file in the expanded CAS application directory structure; it should be under the modules subdirectory, and named something like cas-server-webapp-.war. Copy that WAR file as cas.war to Tomcat's webapps directory. Use keytool utility to self-author a server certificateThis operation we will perform on the machine that's hosting the CAS server. Remove the .keystore file from your home directory, if it exists. In this example, we set it up in root's home directory: $ sudo rm /root/.keystore Go to your JDK's bin directory, and run the command below. (To find out where the JDK is installed, just execute this command: sudo update-java-alternatives - l ) $ sudo keytool -genkey -alias tomcat -keypass changeit -keyalg RSA - validity 365 This will take you through a set of questions that you can answer. For the second question (the one about your first and last name), please answer with the name of the machine that's hosting the CAS server. Enter keystore password: changeit What is your first and last name? [unknown]: '''ubuntu''' What is the name of your organizational unit? [unknown]: Information Systems What is the name of your organization? [unknown]: Pacific Disaster Center What is the name of your City or Locality? [unknown]: Kihei What is the name of your State or Province? [unknown]: HI What is the two-letter country code for this unit? [unknown]: US Is CN=localhost, OU=Information Systems, O=Pacific Disaster Center, L=Kihei, ST=HI, C=US correct? [no]: yes Of course, you can substitute in your own values. $ sudo keytool -export -alias tomcat -keypass changeit -file server.crt $ sudo keytool -import -file server.crt -keypass changeit -keystore ../jre/lib/security/cacerts This last step will result in a message that says that "Certificate was added to keystore." Configure Tomcat's server.xml fileNext, we'll need to configure Tomcat's server.xml file to enable SSL and add parameters related to the keystore. uncomment connector element for port 8443 (SSL)add the parameters for keystoreFile, keystorePass, truststoreFile as shown belowbounce tomcat<!-- Define a SSL HTTP/1.1 Connector on port 8443 --> <connector acceptcount="100" br="" clientauth="false" connector="" disableuploadtimeout="true" enablelookups="false" keystorefile="/root/.keystore" keystorepass="changeit" maxhttpheadersize="8192" maxsparethreads="75" maxthreads="150" minsparethreads="25" port="8443" protocol="HTTP/1.1" scheme="https" secure="true" sslenabled="true" sslprotocol="TLS" truststorefile="/usr/lib/jvm/java-6-openjdk/jre/lib/security/cacerts" /> The values for the keystoreFile and truststoreFile parameters will no doubt be different in your machine; you'll just have to adapt them to your environment. Copy the cas-server-webapp-3.4.5.war to the /opt/apache-tomcat-6.0.29/webapps, renaming it to cas.war in the target directory. Then restart the tomcat server. If you go the CAS web app (http://ubuntu:8080/cas/login), you will get a CAS login screen. Modify the cas.properties fileYou can find the cas.properties file in the /cas/WEB-INF directory. You will need to change some settings there: Open CAS_HOME/cas-server-webapp/src/main/webapp/WEB-INF/cas.properties. It should look something like this: cas.securityContext.serviceProperties.service=https://localhost:8443/cas/services/j_acegi_cas_security_check cas.securityContext.casProcessingFilterEntryPoint.loginUrl=https://localhost:8443/cas/login cas.securityContext.casProxyTicketValidator.casValidate=https://localhost:8443/cas/proxyValidate You will need to change those URLs (most likely only by changing the hostname and port) to your CAS application's URLs. Configuring JasperReports Server to use CASThe modifications we're talking about in the rest of this article will be on the machine that's hosting JasperReports Server. To carry out these changes, we'll first have to stop JasperReports Server. Once we're done with our configuration changes, you'll need to restart JasperReports Server. From a high-level point of view, this is what we're going to do: Add CAS client jar filescatalina.sh (or catalina.bat if you're working with Windows to startup the JasperReports Server application)the applicationContext-security.xml Spring configuration filethe applicationContext-multiTenancy-security.xml Spring configuration file, if you are using the Professional or Enterprise version of JasperReports ServerAdd CAS client jar filesAdd these JAR files to JasperReports Server's WEB-INF/lib directory: cas-2.0.12.jar casclient-2.0.11.jar casclient-2.1.1.jar You can find it here: http://mirrors.ibiblio.org/maven/cas/jars/ Configuring Java to trust the CAS CertificateWe'll need to modify how we start up JasperReports Server, so that it trusts the CAS server's certificate. You can modify the startup script (catalina.sh or catalina.bat depending on your OS). If you have Windows 7 and you have set up Tomcat as a service, then you'll have to open up the tomcat6w application and edit the Java tab, which maintains the values that would otherwise go into the JAVA_OPTS variable. Copy the .keystore file from the CAS server host and put it somewhere in your JasperReports Server's host machineAdd the following value to the end of your JAVA_OPTS parameter in your Tomcat server startup script:-Djavax.net.ssl.trustStore=C:TEMP2011-02-14keystore Of course, the path to the keystore file will depend on where you decide to store it in your host machine. Modify the Spring Security Filter ChainIn the applicationContext-security.xml (applicationContext-security-web.xml in JRS 4.01 and above) Spring configuration file, find the filterChainProxy bean and add the following name-value pair right below /services: /j_spring_cas_security_check=httpSessionContextIntegrationFilter,authenticationProcessingFilter,anonymousProcessingFilter,exceptionTranslationFilter,filterInvocationInterceptor In the same bean, for the /** pattern, locate the${bean.authenticationProcessingFilter} and replace it with the authenticationProcessingFilter. Add the casAuthenticationProviderAgain in the applicationContext-security.xml Spring configuration file, add the casAuthenticationProvider to the authenticationManager bean's list of references. Make sure it appears first, followed by the anonymousAuthenticationProvider. Comment out the rest. <bean class="org.springframework.security.providers.ProviderManager" id="authenticationManager"> <property name="providers"> <list> <!-- not on by default <ref local="ldapAuthenticationProvider" /> --> <!-- <ref local="${bean.daoAuthenticationProvider}" /> --> <ref local="casAuthenticationProvider" /> <ref local="anonymousAuthenticationProvider" /> <!-- <ref local="jaasAuthenticationProvider"/> --> </list> </property> </bean> Configure the CAS Authentication BehaviorThis applies to the applicationContext-security.xml Spring configuration file. In the applicationContext-security.xml file, below the authenticationManager, add the casAuthenticationProvider bean and its helper beans. In particular, the ticketValidator property is configured with an unnamed bean initialized with the HTTPS URL where tickets are validated on the CAS server. Note that it is important to configure the URL with the exact hostname of the CAS server entered when creating the security certificate, otherwise it will return a "CertificateException: No subject alternative names present" error. There is a slight difference in configuring the authentication behaviror starting with version 4.7.0; both configurations are listed below. <!-- BEGIN: Added for CAS authentication for 4.5.x and earlier --> <bean class="org.springframework.security.providers.cas.CasAuthenticationProvider" id="casAuthenticationProvider"> <property name="userDetailsService"> <ref local="casUserAuthorityService" /> </property> <property name="serviceProperties"> <ref local="authenticationServiceProperties" /> </property> <property name="ticketValidator"> <bean class="org.jasig.cas.client.validation.Cas20ServiceTicketValidator"> <constructor-arg index="0" value="https://ubuntu:8443/cas" /> </bean> </property> <property name="statelessTicketCache"> <bean class="org.springframework.security.providers.cas.cache.EhCacheBasedTicketCache"> <property name="cache"> <ref local="ticketCache" /> </property> </bean> </property> <property name="key"> <value>lam_or_lame</value> </property> </bean> <bean class="org.springframework.security.ui.cas.ServiceProperties" id="authenticationServiceProperties"> <property name="service"> <value>http://localhost:8080/jasperserver-pro/j_spring_cas_security_check</value> </property> <property name="sendRenew"> <value>false</value> </property> </bean> <bean class="org.springframework.cache.ehcache.EhCacheManagerFactoryBean" id="ticketCacheManager"> <property name="configLocation"> <value>classpath:/ehcache-failsafe.xml</value> </property> </bean> <bean class="org.springframework.cache.ehcache.EhCacheFactoryBean" id="ticketCache"> <property name="cacheManager"> <ref local="ticketCacheManager" /> </property> <property name="cacheName"> <value>casTicketCache</value> </property> </bean> <!-- END: Added for CAS authentication --> <!-- BEGIN: Added for CAS authentication for 4.7.x and later --> <bean class="org.springframework.security.providers.cas.CasAuthenticationProvider" id="casAuthenticationProvider"> <property name="userDetailsService"> <ref local="casUserAuthorityService" /> </property> <property name="serviceProperties"> <ref local="authenticationServiceProperties" /> </property> <property name="ticketValidator"> <bean class="org.jasig.cas.client.validation.Cas20ServiceTicketValidator"> <constructor-arg index="0" value="https://ubuntu:8443/cas" /> </bean> </property> <property name="statelessTicketCache"> <bean class="org.springframework.security.providers.cas.cache.EhCacheBasedTicketCache"> <property name="cache"> <ref local="ticketCache" /> </property> </bean> </property> <property name="key"> <value>lam_or_lame</value> </property> </bean> <bean class="org.springframework.security.ui.cas.ServiceProperties" id="authenticationServiceProperties"> <property name="service"> <value>http://localhost:8080/jasperserver-pro/j_spring_cas_security_check</value> </property> <property name="sendRenew"> <value>false</value> </property> </bean> <bean class="org.springframework.cache.ehcache.EhCacheFactoryBean" id="ticketCache"> <property name="cacheManager"> <ref local="cacheManager" /> </property> <property name="cacheName"> <value>casTicketCache</value> </property> </bean> <!-- END: Added for CAS authentication --> Setting the User RolesAfter the helper beans for the casAuthenticationProvider in the applicationContext-security.xml file, add the casUserAuthorityService bean that defines roles for CAS users: <!-- BEGIN : Setting the User Roles --> <bean class="com.jaspersoft.jasperserver.api.metadata.user.service.impl.UserDetailsServiceImpl" id="casUserAuthorityService"> <property name="adminUsers"> <list> <value>myorgadmin</value> </list> </property> <property name="defaultAdminRoles"> <list> <value>ROLE_USER</value> <value>ROLE_ADMINISTRATOR</value> </list> </property> <property name="defaultInternalRoles"> <list> <value>ROLE_USER</value> </list> </property> </bean> <!-- END : Setting the User Roles --> Configuring the Filters for CASThis also involves editing the applicationContext-security.xml (applicationContext-security-web.xml in JRS 4.01 and above) Spring configuration file. Finally, we need to replace the authenticationProcessingFilter and the authenticationProcessingFilterEntryPoint for CAS. Notice that we keep the same bean names in the id attribute and substitute a different class with its specific properties. Because these beans are referenced by name in the beans that call them, the beans with the new classes are automatically used instead. Alternatively, you could give the new beans new names, but then you would also need to re-configure the beans that call them. Note that the documentation for Spring Security takes the latter approach. Locate and replace the existing entries in the applicationContext-security.xml (applicationContext-security-web.xml in JasperReports Server v4.01 and above) file with the following beans: <bean class="org.springframework.security.ui.cas.CasProcessingFilterEntryPoint" id="authenticationProcessingFilterEntryPoint"> <property name="loginUrl"> <value>https://ubuntu:8443/cas/login</value> </property> <property name="serviceProperties"> <ref bean="authenticationServiceProperties" /> </property> </bean> <bean class="org.springframework.security.ui.cas.CasProcessingFilter" id="authenticationProcessingFilter"> <property name="authenticationManager"> <ref bean="authenticationManager" /> </property> <property name="authenticationFailureUrl"> <value>/loginerror.html</value> </property> <property name="defaultTargetUrl"> <value>/loginsuccess.html</value> </property> <property name="filterProcessesUrl"> <value>/j_spring_cas_security_check</value> </property> </bean> The service property of the authenticationServiceProperties bean specifies the JasperReports Server URL that receives the ticket when the CAS server performs service validation. The loginUrl property of the authenticationProcessingFilterEntryPoint bean specifies the login URL for the CAS server, to which JasperReports Server redirects all users for authentication. The authenticationManager property of the authenticationProcessingFilter bean specifies the authenticationManager bean that we previously configured. Setting the User OrganizationOrganizations are a feature of JasperReports Server 3.7 Enterprise Edition. Skip this section if you have JasperReports Server 3.7 Community or Professional Editions. You can also skip this section if you're just using the default organization_1 value with your multi-tenancy enabled JasperReports Server. Built-in support for organizations with CAS is limited to mapping all users authenticated by CAS to a single organization. If you use the single default organization, the default mapping will place all CAS-authenticated users there. If you have multiple organizations, you must choose the one in which users will be placed. The following example shows the defaultExternalUserProcessor bean in the applicationContext-multiTenancy-security.xml file that performs this function: <bean br="" id="mtUserAuthorityServiceTarget"> ... <property name="userProcessors"> <list> <!-- For LDAP authentication --> <!-- ref bean="ldapExternalUserProcessor"/ --> <ref bean="defaultExternalUserProcessor" /> </list> </property> </bean> <bean class="com.jaspersoft.jasperserver.multipleTenancy.DefaultExternalUserProcessor" id="defaultExternalUserProcessor"> <property name="multiTenancyService"> <ref bean="internalMultiTenancyService" /> </property> <property name="defaultOrganizationIfNotDetected" value="organization_1" /> </bean> TestingTo test, go to a page within JasperReports Server that's not the login page, such as: http://localhost:8080/jasperserver-pro/flow.html?_flowId=homeFlow If you're not authenticated, then it will re-direct you to a login screen - it will look like the CAS login screen. Try logging in as jasperadmin, with first an incorrect password, and then the correct password. See AlsoHow to configure JasperReports Server 4.5.1, 4.7.1, 5.0.1 and CAS.Cannot log in with internal users after configuring CAS authentication in 5.1, 5.2, 5.5ResourcesQuick SSO demo using CAS:https://wiki.jasig.org/display/CASUM/Demo Dual Authentication with CAS and LDAP:https://wiki.jasig.org/display/CASUM/HOWTO+Setup+Dual+Authentication+in+CAS+-+SSL+Client+Auth+and+LDAP

Issue:[toc on_off::hide=1] You'd like to remove the sections shown below from the login page: Resolution: This only applies to JasperReports Server versions 4.0 and above 'Make sure to apply this to the root theme, as it affects the login screen. You will upload the theme as the superuser, not jasperadmin'' (jasperadmin is organization specific)In overrides_custom.css add the following snippet: #loginPage #buttons { display: none; } #loginPage #rotating { display: none; } #loginPage #primary { display: none; } #loginPage #secondary { display: none; } #loginPage #help { display: none; } Result Ref. Case #00020288 --

Issue:[toc on_off::hide=1] You'd like to remove the help link from the main menu via theme. Resolution:This only applies to JasperReports Server versions 4.0 and above This CSS code, applied to the theme, will hide the Help Link. #metaLinks li#help { display: none; } See Also: Wiki Search of Theme Related ContentRef. Case #00020288 --

Issue:[toc on_off::hide=1] I'd like to remove "search" menu from subsequent menu after logon. Resolution:This only applies to JasperReports Server versions 4.0 and aboveIn overrides_custom.css add the following snippet: globalSearch{ display:none; } secondarySearchBox{ display:none; } Ref. Case #00020288 -- 19:51, 19 May 2011 (UTC)