Steve Park

UPDATE 5 Apr 2018: I have uploaded a new version of the application to GitHub. This version allows you to rename field ids and labels, in addition to the existing functionality that handles field removals and database column updates. This wiki article has been updated to reflect these changes. UPDATE 7 Jan 2019: Branch updated for JRS 7.1 (with latest hotfix) at https://github.com/kkumlien/Domain-Metadata-Modifier/tree/jrs-7.1 Please note, only a login issue has been fixed (message "Error logging into JasperReports Server: no server found at location"), but this application has not been fully tested on 7.1. Please BACKUP your repository before any operations, as mentioned below in the article. See here for further details: https://community.jaspersoft.com/wiki/rest-v2-login-service Since domain functionality was introduced, it has been mostly suited to a Waterfall development model where requirements are identified and frozen before development begins. The database schema would be developed first and locked down before application or report development proceeded. Many software shops have moved away from the Waterfall model and towards an Agile development model where requirements, database schemas, application and report development occur simultaneously and change frequently. Unfortunately, domain development hasn't been well-suited to the Agile development approach. While it's always been possible to add fields to a domain after dependent objects have been created by turning domain validation off, other changes, like renaming or removing fields, would cause Ad Hoc views, reports or dashboards to break I have developed a Python application in order to make domain changes cascade through these dependent objects. Currently, this application is aimed at two specific use cases: Removing an existing field from a domain and its dependent Ad Hoc views, reports and dashboards Renaming a database column in the domain definition NEW: Renaming domain field ids and labels Since it's possible to add fields to an existing domain as long as domain validation is turned off first, adding fields is beyond the scope of this application. The application is available for download here. IMPORTANT: This is community-contributed content and is not supported by TIBCO Analytics. It is provided as-is with no express or implied warranties of any kind. The source code is included as part of the project and may be modified to meet your business requirements. By doing so, however, the end consumer assumes all liabilities and responsibilities for modifying the code. About The ApplicationThis application is written in Python 3. It calls the REST API to export the entire repository. It downloads the export zip archive to a temporary folder. It extracts the zip archive, iterates over each file looking for matching conditions, and changes the file according to the requested action. It then re-zips the temporary folder and calls the REST API to import the modified archive. Since the REST API requires that the export and import actions require the superuser account, you will need to know the superuser password. Usage on WindowsUPDATE: The first version of this application came with a Windows installer. I have since learned that the NSIS package that was used to build the installer only provides a 32 bit executable that requires Administrator privileges to run. In addition, I have found that in some cases, the JasperReports Server repository export filenames sometimes contain characters that are illegal to use as filenames on Windows. Therefore, I now recommend that you install and execute the application using Cygwin (64 Bit|32 bit installers). Cygwin is a Unix command-line emulator that runs natively on Windows. You can install Python 3 as part of the Cygwin installation. To run the application using Cygwin, follow these instructions: The following video demonstrates how to install and configure Cygwin: After downloading the Cygwin setup program, launch the setup wizard and follow the defaults until you get to the "Package Selection" screen. You'll need to select the python3 and python3-pip packages. They're under the main Python node, but you may need to search for them. After the installer finishes, launch the Cygwin command line application. The Domain Cascade application requires some dependencies that are listed in the requirements.txt file. To install these dependencies using pip, run the following command: pip3 install -r requirements.txt[/code]NOTE: You might see some errors while executing pip. In my experience, these can be ignored. If they prevent you from running the Domain Cascade app, let me know. To remove one or more fields from the domain, run the application using the following syntax: python3 DomainMetadataMod.py <server-url> <superuser-password> <domain-id> <comma-separated-list-of-field-ids-to-remove>[/code] To rename a field id or label in the domain definition, run the application using the following syntax: python3 DomainMetadataMod.py <server-url> <superuser-password> <domain-id> <comma-separated-list-of-original-field-ids> <comma-separated-list-of-new-field-ids>[/code]The default behavior includes renaming the database column name definitions in the domain metadata to the new field ids. If you wish to keep the database column names the same or rename them to something else, then add an additional parameter at the end: python3 DomainMetadataMod.py <server-url> <superuser-password> <domain-id> <comma-separated-list-of-original-field-ids> <comma-separated-list-of-new-field-ids> <comma-separated-list-of-new-or-original-database-columns>[/code] The server-url parameter is the URL you would use to access your JasperReports Server instance from a browser (i.e. http://localhost:8080/jasperserver-pro) without a forward slash on the end. The domain-id parameter is the resource ID for the domain, which you may obtain by right-clicking on the domain in the repository explorer and selecting "Properties". The field ids are available on the display tab of the domain designer. Select a field and use its ID property. For example, to remove the store_country and product_name fields from the supermartDomain domain as well as any associated Ad Hoc Views, reports and dashboards from a JasperReports Server instance running on the same machine using installer defaults, use the following command: python3 DomainMetadataMod.py http://localhost:8080/jasperserver-pro superuser supermartDomain store_country,product_name[/code] Usage on Linux or Mac OSXMac OSX and most flavors of Linux come with Python pre-installed. Since many of these come with both Python 2 and 3, you'll need to ensure you're using the correct version. This application has some dependencies that are available as wheels from PyPI but aren't included in most standard distributions. To get these dependencies, browse to the folder where you downloaded the application and execute the following in a command line window: pip3 install -r requirements.txt[/code]Then run the application using the following syntax: To remove one or more fields from the domain definition and dependent repository objects: python3 DomainMetadataMod.py <server-url> <superuser-password> <domain-id> <comma-separated-list-of-field-ids-to-remove>[/code]To rename one or more field ids and labels in the domain definition and dependent repository objects: python3 DomainMetadataMod.py <server-url> <superuser-password> <domain-id> <comma-separated-list-of-old-field-ids> <comma-separated-list-of-new-field-ids>[/code]To rename one or more field ids and labels and either preserve the existing domain column names or change them to something besides the new field ids: python3 DomainMetadataMod.py <server-url> <superuser-password> <domain-id> <comma-separated-list-of-old-field-ids> <comma-separated-list-of-new-field-ids> <comma-separated-list-of-db-column-names>[/code] Caveats and CautionsThis application is designed to be aggressive. A common use case for SaaS providers is to have multi-tenancy enabled with separate domains in each tenant. Assuming the domains all have the same domain ids, this application will remove the same field from all of them in one pass. This application also accepts partial ID matches. For example, using "store_c" as the field id for the SuperMart domain will cause both the store_country and store_city fields to be removed. BE SURE TO EXPORT THE REPOSITORY TO A ZIP ARCHIVE BEFORE USING THIS APPLICATION!!! Field labels will currently only update if all or part of them match the field ids. The field labels will not currently update if you renamed the labels or are using resource bundles to provide localization. I plan to support updating labels separately in the next release. Please reach out to me if you have any questions or have additional use cases you would like this application to support. I'm also hoping to receive feedback on how the application works for your use case. Thanks for your interest in this application. I hope it helps make working with domains easier. Future EnhancementsHere is a list of proposed enhancements and directions for future work: Sometimes the JIVE interface adds properties that the app doesn't always handle properly, such as sorting. More testing with different JIVE options are needed Port to Java Develop a front end that's friendlier than the command line, such as Angular or React. This could be done in conjunction with the port to Java such that the application can be launched using Spring Boot. To handle larger multi-tenant repositories, change export REST API call to loop over each tenant and download multiple ZIP archives Written by Steve Park on February 22, 2018, last update June 8, 2018

Steps to Manually Upgrade the Java and Tomcat versions from 7 to 8 on the Amazon AWS Marketplace CloudFormation Template AMIsSteve Park, CISSP, Principal Customer Success Engineer, TIBCO Jaspersoft Written on November 14th, 2017 The AMIs that are included with the hourly and BYOL Cloud Formation Templates (CFT) available on the Amazon Marketplace use Tomcat 7 and Java 7. In addition to being no longer officially supported, both our internal testing and feedback from customers indicate that Tomcat 8 and Java 8 offer superior performance. In order to upgrade the Tomcat and Java versions, please follow the following steps. These steps were tested using JasperReports Server 6.3.0, but I'm unaware of any issues when using either version 6.3.2 or version 6.4. Manual Upgrade StepsLaunch either an hourly or BYOL instance on the Marketplace and SSH into it using the ec2-user@<amazon-public-hostname-or-ip> account Become the root user: sudo su - [/code]Update all of the packages on the instance: yum update [/code]Remove the Tomcat 7 server: yum erase tomcat7 [/code]Remove OpenJDK7: yum erase java-1.7.0-openjdk.x86_64 [/code]Install OpenJDK8: yum install java-1.8.0-openjdk.x86_64 [/code]Edit the /etc/profile file and add the following line right above the EXPORT command: JAVA_HOME=/usr/lib/jvm/jre-1.8.0 [/code]Add JAVA_HOME to the list of variables set by the EXPORT command: export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL JAVA_HOME [/code]Execute the source command on the command line to set the JAVA_HOME environment variable: source /etc/profile [/code]Run the echo command on the command line to verify the JAVA_HOME environment variable has been properly set: echo $JAVA_HOME [/code]Install Tomcat 8: yum install tomcat8 [/code]Change the tomcat user's home folder by executing the following command on the command line: usermod -d /usr/share/tomcat8 tomcat [/code]Navigate to the /etc/jasperserver folder: cd /etc/jasperserver [/code]Navigate to the /etc/jasperserver folder: cd /etc/jasperserver [/code]Rename the tomcat7.config file to tomcat8.config: mv tomcat7.config tomcat8.config [/code]Open the tomcat8.config file in a text editor like vi, and change the second CATALINA_OPTS= line to: CATALINA_OPTS="$CATALINA_OPTS -XX:+UseG1GC" [/code]Save and close the file On the command line, replace all occurrences of tomcat7 with tomcat8 in the files in the current directory: sed -i 's/tomcat7/tomcat8/g' [/code]Edit the memory_settings.sh file in a text editor like vi and replace its contents with the following: #!/bin/sh rm -f /tmp/instance-type wget -q http://169.254.169.254/latest/meta-data/instance-type -O /tmp/instance-type itype=`cat /tmp/instance-type` case $itype in ("m1.medium") echo "-Xms2048m -Xmx2048m -XX:MetaspaceSize=512m -XX:MaxMetaspaceSize=512m -Xss2m";; ("m3.medium") echo "-Xms2048m -Xmx2048m -XX:MetaspaceSize=512m -XX:MaxMetaspaceSize=512m -Xss2m";; ("m1.large") echo "-Xms5500m -Xmx5500m -XX:MetaspaceSize=1024m -XX:MaxMetaspaceSize=1024m -Xss2m";; ("m3.large") echo "-Xms5500m -Xmx5500m -XX:MetaspaceSize=1024m -XX:MaxMetaspaceSize=1024m -Xss2m";; ("m1.xlarge") echo "-Xms13300m -Xmx13300m -XX:MetaspaceSize=1024m -XX:MaxMetaspaceSize=1024m -Xss2m";; ("m3.xlarge") echo "-Xms13000m -Xmx13000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("m3.2xlarge") echo "-Xms28000m -Xmx28000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("c1.xlarge") echo "-Xms5500m -Xmx5500m -XX:MetaspaceSize=1024m -XX:MaxMetaspaceSize=1024m -Xss2m";; ("m2.xlarge") echo "-Xms15000m -Xmx15000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("m2.2xlarge") echo "-Xms30000m -Xmx30000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("m2.4xlarge") echo "-Xms62000m -Xmx62000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("hi1.4xlarge") echo "-Xms560000m -Xmx560000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("hs1.8xlarge") echo "-Xms107000m -Xmx107000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("cc1.4xlarge") echo "-Xms20000m -Xmx20000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("cc2.8xlarge") echo "-Xms56000m -Xmx56000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("cg1.4xlarge") echo "-Xms20000m -Xmx20000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("m4.large") echo "-Xms5500m -Xmx5500m -XX:MetaspaceSize=1024m -XX:MaxMetaspaceSize=1024m -Xss2m";; ("m4.xlarge") echo "-Xms13000m -Xmx13000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("m4.2xlarge") echo "-Xms28000m -Xmx28000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("m4.4xlarge") echo "-Xms60000m -Xmx60000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("m4.10xlarge") echo "-Xms150000m -Xmx150000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("r3.xlarge") echo "-Xms280000m -Xmx280000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("r3.2xlarge") echo "-Xms56000m -Xmx56000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("c4.xlarge") echo "-Xms5500m -Xmx5500m -XX:MetaspaceSize=1024m -XX:MaxMetaspaceSize=1024m -Xss2m";; ("c4.2xlarge") echo "-Xms13000m -Xmx13000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("c4.4xlarge") echo "-Xms280000m -Xmx280000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; ("c4.8xlarge") echo "-Xms56000m -Xmx56000m -XX:MetaspaceSize=2048m -XX:MaxMetaspaceSize=2048m -Xss2m";; (*) echo "-Xms2048m -Xmx2048m -XX:MetaspaceSize=512m -XX:MaxMetaspaceSize=512m -Xss2m";; esac [/code]Save the file and exit the text editor On the command line, navigate to the /etc/tomcat8 folder: cd /etc/tomcat8 [/code]Edit the tomcat8.conf file and change the JAVA_HOME variable to: /usr/lib/jvm/jre-1.8.0 [/code]Save the file and exit the text editor On the command line, use the cat command to append the contents of the /etc/jasperserver/tomcat8.config file to the /etc/tomcat8/tomcat8.conf file: cat /etc/jasperserver/tomcat8.config >> /etc/tomcat8/tomcat8.conf [/code]Copy the /etc/jasperserver jrs_* files to their respective locations in the /etc/init.d folder: cp /etc/jasperserver/jrs_boot_settings.sh /etc/init.d/jrs_boot_settings cp /etc/jasperserver/jrs_init.sh /etc/init.d/jrs_init [/code]Navigate to the /etc/init.d folder: cd /etc/init.d [/code]Enable the jrs_init script to run on startup: chkconfig jrs_init on [/code]Change ownership of the /etc/init.d/jrs* scripts to ec2-user: chown ec2-user:ec2-user /etc/init.d/jrs* [/code]Edit the /etc/tomcat8/server.xml file and change the port from 8080 to 80: <Connector port="80" protocol="HTTP/1.1" connectionTimeout="20000" redirectPort="8443" />[/code]Edit the /etc/yum/post-actions/allowTomcatToBindPort80.action file and change all occurrences of tomcat7 to tomcat8 Save the file and exit the text editor On the command line, navigate to the /usr/share/jrs_dist/jasperreports-server-bin folder: cd /usr/share/jrs_dist/jasperreports-server-bin[/code]Recursively change the tomcat7 references to tomcat8 in all files: find ./ -type f -exec sed -i -e 's/tomcat7/tomcat8/g' {} ;[/code]On the command line, navigate to the buildomatic folder that's under the current folder: cd buildomatic [/code]Deploy the JasperReports Server web application: ./js-ant deploy-webapp-pro [/code]Copy the password-changer-1.0.jar file from /etc/jasperserver to /usr/share/tomcat8/lib: cp /etc/jasperserver/password-changer-1.0.jar /usr/share/tomcat8/lib [/code]Copy the ROOT application from tomcat7 to tomcat8: cp -R /var/lib/tomcat7/webapps/ROOT /var/lib/tomcat8/webapps [/code]Change ownership of /var/lib/tomcat8 to tomcat: chown -R tomcat:tomcat /var/lib/tomcat8 [/code]Change ownership of /usr/share/tomcat8 to tomcat: chown -R tomcat:tomcat /usr/share/tomcat8 [/code]Exit out of the SSH session In the web browser, assuming you're still logged in to AWS, click Services -> EC2 Click the Running Instances link Click the checkbox next to the running instance, then click Actions -> Image -> Create Image Provide an image name and description, then click the Create Image button Click the Services menu item, then under Management Tools, click the CloudFormation link Click the name of the running stack Mouse over the Other Actions button in the upper-right corner and select the View/Edit template in Designer option In the template code section in the lower half of the page, scroll until you find the section that begins with Mappings and contains AWSRegionAMI In the desired region, change the name of the AMI id to the one you just saved Mouse over the File menu button in the upper-left corner (it looks like a page with the upper-right corner folded over) and select Save If you don't have an S3 bucket to save the template to, you may need to save it as a local file until you can create an S3 bucket later Close the CloudFormation stack editor and return to the Running Instances page Click the name of the running stack again Click the Update Stack button in the upper-right corner Specify either the S3 template URL or upload the saved template file to S3 Continue through the wizard until the stack restarts ValidationYou can validate the steps taken by SSH'ing into the new instance and running the following commands: sudo su - java -version # should show Java 8 ps -eaf | grep tomcat # should show the server using /var/lib/tomcat8 file locations[/code]

When creating domain-driven Ad Hoc views and reports, TIBCO JasperReports Server offers administrators the ability to restrict access at the row and column level based on some criteria about the currently logged in user via the domain security file. However, the default behavior is to filter either the entire record or the column. This may not be desirable in situations where only part of the record or column should be hidden. Among other problems, this can lead to inaccurate aggregations. The solution is to display redacted individual field values using a static string mask such as "XXXXXX". This can be accomplished without requiring customizations by implementing the following instructions: PrerequisitesJasperReports Server Professional - any version One or more database tables that map user IDs to the field values they are allowed access Basic knowledge of how to create user accounts NOTE: These steps use the Foodmart sample database on PostgreSQL 9.4 and assumes one is starting from a brand new install. In many cases, not all of these setup steps will be necessary. Example Setup StepsOpen a SQL client connection to the Foodmart database. Create the user lookup table using the following syntax: create table user_state_lookup(username varchar(30), state_province varchar(30));Using insert SQL statements, populate the table with these values: usernamestate_provincebradGuerrerobradYucatanbradBCbradCAbradJaliscocoletteDFcoletteSinaloacoletteWAcoletteORjohnVeracruzjohnZacatecasjohnOaxaca Using a web browser, log into JasperReports Server as superuser. Create 3 non-privileged user accounts (under the organization_1 tenant if multi-tenancy is enabled) named brad, colette, and john. Create a test domain (this example uses the name Masking Test Domain and stores it in the /Public/Samples/Domain folder in the repository. On the Add New Domain screen, supply the following values: Name (required): Masking Test Domain Resource ID (required): Masking_Test_Domain Save Location: /public/Samples/Domains Data Source: /public/Samples/Data_Sources/FoodmartDataSourceJNDI Domain Design: Click the Upload radio button and upload masking_test_schema.zip. Click the Submit button to save the domain. Right-click on the new Masking Test Domain in the Repository Explorer and click Edit. On the Edit Domain screen, click the Edit with Domain Designer... link to launch the domain designer. How The Example WorksIn this section, we will examine how this example domain selectively redacts data depending on the logged in user. This example works by defining three calculated fields. They are: logged_in_username: Uses a Groovy binding to obtain the currently logged in user user_state_check: Checks to see if the username in the user_state_lookup table matches the currently logged in user filtered_state_province: Uses the CaseWhen() DomEL function to determine whether to show the data or the redaction To examine how the example works, perform the following steps: Click the Calculated Fields tab in the domain designer. Expand the Constants and JoinTree_1 nodes. Under the JoinTree_1 node, expand the public_user_state_lookup node. Double-click the logged_in_username node. This calculated field is used to retrieve the logged in username. Observe the following properties: Field Name: logged_in_username Type: String Expression: groovy('authentication.principal.username') Click the Cancel button next to the Save Field button. Next, double-click the user_state_check node under the JoinTree_1 node. This calculated field checks to see if the public_user_state_lookup.username from the domain data set matches the currently logged in user. Observe the following properties: Field Name: user_state_check Type: boolean Expression: public_user_state_lookup.username == constant_fields_level.logged_in_username Click the Cancel button next to the Save Field button. Next, double-click the filtered_state_province node under the JoinTree_1 node. This calculated field handles the displaying or masking of the public_region.sales_state_province field data. Observe the following properties: Field Name: filtered_state_province Type: String Expression: CaseWhen(public_user_state_lookup.user_state_check, public_region.sales_state_province, 'XXXXXX') Click the Cancel button next to the Save Field button. Finally, click the Display tab in the domain designer In the Sets and Items section, expand the Store and Region nodes and click the State node. In the Properties section, notice how the State node uses the JoinTree_1.filtered_state_province calculated field rather than the public_region.sales_state_province field. This is the only one of the three calculated fields that will be displayed to the end user. Click the Cancel button next to the OK button in the lower-left corner to exit the domain designer. Testing The Example DomainTo test the domain, create a new Ad Hoc view, save it to the repository and run it using each of the three end user accounts we created at the beginning. Perform the following steps: In the menu bar at the top of the screen, click Create -> Ad Hoc View to launch the Ad Hoc editor.On the Select Data dialog, select the Masking Test Domain domain and click Choose Data.... On the Choose Data wizard, click the >> button to move all of the fields to the right, then click OK. In the Ad Hoc editor in the Measures section, select and drag the Store Sales, Store Cost, and Unit Sales measures to the Columns box. In the Fields section, expand the Store and Region nodes and select and drag the Country, State, and City fields to the Rows box. Click the expansion button next to the State group in the crosstab to see all of the groups. RESULT: All of the state group values are masked with "XXXXXX": Mouse over the Save icon in the Ad Hoc toolbar, select Save Ad Hoc View, call the view Masking Test View and save it in the root > Organizations > Organization > Ad Hoc Components > Ad Hoc Reports folder. In the menu bar at the top of the screen, select Manage > Users. Select brad and click the Login As User button. As brad, click the View > Repository menu item to launch the Repository Explorer. Expand the Ad Hoc Components and Ad Hoc Reports nodes and click the Masking Test View resource to launch it in the Ad Hoc editor. RESULT: In the State group, the BC, Guerrero, Jalisco, Yucatan, and CA fields are visible while the rest are masked:

Using the REST API to Add Report Data to a MS Word Document using Native TablesTIBCO JasperReports® Server can export reports to DOC and DOCX formats, however some end users would prefer to inject report data or objects into existing MS Word documents or create reports that allow for a layout that is not limited by a typical banded report structure. This wiki article will demonstrate how to add report content to an existing MS Word document containing native tables using the REST API. Future versions of this document will also show how to create native Excel charts using reporting data. We are assuming that the reader is familiar with .NET programming. The code snippets are written in the C# programming language. Conversion to Visual Basic or another .NET-compatible language is left as an exercise for the reader. IMPORTANT: This is community-contributed content and is not supported by TIBCO Analytics. It is provided as-is with no express or implied warranties of any kind. The source code is included in the WordDocument3.zip file and may be modified to meet your business requirements. By doing so, however, the end consumer assumes all liabilities and responsibilities for modifying the code. PrerequisitesMicrosoft Visual Studio 2010 (newer versions might work, but will likely require API changes) .NET 4.0 Microsoft Office 2010 (newer versions might work, but will likely require API changes) Visual Studio Tools for Office runtime, available here RestSharp REST client for .NET, available here (I used the .NET 4.0 client DLL in version 105.2.3 downloaded using NuGet) JasperReports Server with SugarCRM sample data (I used version 6.2 Professional, but Community and older versions should work with little or no adaptation) Configuration StepsYou can download the entire source code solution WordDocument3.zip or you can create a new Visual Studio solution. To create a new solution: Open Visual Studio and select File -> New -> Project Expand the Visual C# and Office nodes Select 2010 and Word 2010 Document Specify the name and location of the project, solution name, ensure the Create new solution option is selected and the Create directory for solution checkbox is checked Click OK When the solution opens, a new MS Word document will appear. This solution will assume the MS Word document has at least one empty table already created. For this proof-of-concept, I created a MS Word document that looks like this: This solution will call the REST API to execute a report, export it in XML format, and then add rows to the existing table in the MS Word document. This example will use the EmployeeAccounts report that was included in the old report samples prior to the 5.5 release. The repository export of this report is available EmployeeAccounts-repo-export.zip. You will need to import this report unit to your repository by logging in as superuser, clicking Manage -> Server Settings, clicking the Import menu option on the left side, and importing the EmployeeAccounts-repo-export.zip file. The EmployeeAccounts report contains a single input control that allows you to choose from a list of 14 employees. In this proof-of-concept, we will execute a REST API call to retrieve a list of all employees, then we will loop through this list, copy the first page of the MS Word document, replace the <> placeholder in the salutation with the employee name, execute a REST API call that executes the report on the server, export it to XML format, and create rows on the table for the report data for each employee. The first task we need to do is authenticate to JasperReports Server. There are many ways we could do this, but for this exercise we will create a simple Windows form that will pop-up when the MS Word document is opened and prompt for credentials. To create a Windows form for retrieving JasperReports Server credentials: In the Toolbox window, expand the All Windows Forms node In the Solution Explorer window, right-click on the WordDocument3 project node and select Add -> Windows Form... The Add New Item dialog appears and the Windows Form option should be selected Specify a filename for the Windows form (this example uses JSCredentials.js) Click the Add button Drag labels, textboxes and buttons to the new Windows form until it looks like this: The source code samples below assume you've named the textfield and Connect button objects according to the following table: Textfield/ButtonNameServerserverTBUsernameUsernameTBPasswordPasswordTBOrganizationOrgTBConnect ButtonOKOnce you've named the textfield objects and Connect button, you'll need to write some code for the JSCredentials Windows form so that the main application can retrieve the values. First, select the Connect button, and on the Properties window, click the Events button. On the Click property, select the OK_Click option. Then to add the source code, right-click on the Windows form and select View Code. Finally, in the CSharp editor, modify the source code so that it looks like this: public partial class JSCredentials : Form{ private String serverURL; private String username; private String password; private String org; public JSCredentials() { InitializeComponent(); } private void OK_Click(object sender, EventArgs e) { this.ServerLabel.ForeColor = Color.Black; this.UsernameLabel.ForeColor = Color.Black; this.PasswordLabel.ForeColor = Color.Black; this.OrgLabel.ForeColor = Color.Black; if (this.serverTB.Text == null || this.serverTB.Text.Length == 0) { this.ServerLabel.ForeColor = Color.Red; } if (this.UsernameTB.Text == null || this.UsernameTB.Text.Length == 0) { this.UsernameLabel.ForeColor = Color.Red; } if (this.PasswordTB.Text == null || this.PasswordTB.Text.Length == 0) { this.PasswordLabel.ForeColor = Color.Red; } if (this.OrgTB.Text == null || this.OrgTB.Text.Length == 0) { this.OrgLabel.ForeColor = Color.Red; } if (this.ServerLabel.ForeColor == Color.Black && this.UsernameLabel.ForeColor == Color.Black && this.PasswordLabel.ForeColor == Color.Black && this.OrgLabel.ForeColor == Color.Black) { serverURL = this.serverTB.Text; username = this.UsernameTB.Text; password = this.PasswordTB.Text; org = this.OrgTB.Text; this.DialogResult = DialogResult.OK; this.Close(); } else { MessageBox.Show("One or more required fields was blank", "Validation Error", MessageBoxButtons.OK); } } public String getServerURL() { return this.serverURL; } public String getUsername() { return this.username; } public String getPassword() { return this.password; } public String getOrg() { return this.org; }}[/code]NOTE: This example assumes you have the Professional version of JasperReports Server with multi-tenancy enabled and have more than one organization existing on the repository. If this is not the case in your environment, you'll need to remove the Organization textbox and label, remove the validation checks for OrgTB in the OK_Click method and remove the getOrg() method. Save the source code and close the JSCredentials.cs file If you've not already done so, open the ThisDocument.cs file associated with the MS Word document. Now that we have a way to connect to the server, we'll need to open this Windows form from the MS Word document. For the sake of simplicity, we will always open this JSCredentials dialog box every time the MS Word document opens. To do this, create the following method: private RestClient restClient;private void initServerConnection(){ // Get server credentials JSCredentials credentials = new JSCredentials(); credentials.Margin = Padding.Empty; credentials.Padding = Padding.Empty; credentials.StartPosition = FormStartPosition.CenterParent; NativeWindow wordMain = new NativeWindow(); wordMain.AssignHandle(Process.GetCurrentProcess().MainWindowHandle); DialogResult result = credentials.ShowDialog(wordMain); wordMain.ReleaseHandle(); if (result != DialogResult.OK) this.Close(ref missing, ref missing, ref missing); string url = "http://" + credentials.getServerURL() + "/jasperserver-pro/rest_v2/reports/reports/samples"; restClient = new RestClient(url); // Multi-tenant version (comment out for single tenant or community) String credentialsStr = String.Format("{0}|{1}", credentials.getUsername(), credentials.getOrg()); restClient.Authenticator = new HttpBasicAuthenticator(credentialsStr, credentials.getPassword()); // Single-tenant or community version (comment out for multi-tenancy) //restClient.Authenticator = new HttpBasicAuthenticator(credentials.getUsername(), credentials.getPassword());}[/code]NOTE: Be sure to comment and uncomment the last four lines of code properly depending on whether you have the Professional version of JasperReports Server with multi-tenancy enabled or not. Also, note that if you have multi-tenancy enabled but only one organization exists in your repository, you'll either need to uncomment the single-tenant code or create another tenant in your repository. Add a call to the initServerConnection() method in the ThisDocument_Startup() method. You'll need to surround this method with a try/catch block and catch a possible WebException object. As a further enhancement, we can automatically prompt the user again for valid credentials and give them up to five attempts to provide valid credentials before exiting. To do this, modify the ThisDocument_Startup method as follows: private const int MAX_ATTEMPT_COUNT = 5;private void ThisDocument_Startup(object sender, System.EventArgs e){ object paramMissing = System.Type.Missing; int connAttempt = 0; bool success = false; while (connAttempt < MAX_ATTEMPT_COUNT && !success) { try { initServerConnection(); success = true; } catch (WebException we) { String errorMsg = we.GetBaseException().Message + ". Please try again."; if (we.GetBaseException().Message.Contains("401")) { errorMsg = "Invalid JasperReports Server credentials. Please double-check your credentials and try again."; } DialogResult result = MessageBox.Show(errorMsg, "Error connecting to JasperReports Server", MessageBoxButtons.RetryCancel); if (result == DialogResult.Cancel) { this.Close(ref paramMissing, ref paramMissing, ref paramMissing); } connAttempt++; } } if (!success) { MessageBox.Show("Maximum server attempt count exceeded. Please ensure your JasperReports Server instance is running and try again later.", "Max Connection Attempts", MessageBoxButtons.OK); this.Close(ref paramMissing, ref paramMissing, ref paramMissing); }}[/code]The EmployeeAccounts report contains a single option query-based input control that lets end users choose from a list of employees. As our plan is to iterate over all the employees and create a separate letter for each, we need to make a REST API call to retrieve the list of values from the input control. Once we have those values, we store them in a KeyValuePair class variable for access later by other methods. We can leverage the JSON deserializer from the RestSharp library to help parse the response from the server. This is done in the following code: private List> employeeIDMap;private JsonDeserializer deserial = new JsonDeserializer();private void processInputControlValues(){ employeeIDMap = new List>(); // Create web request RestRequest icRequest = new RestRequest("/EmployeeAccounts/inputControls/values"); // Get response IRestResponse res = restClient.Execute(icRequest); if (res.StatusCode == HttpStatusCode.OK) { InputControlResponse icRes = deserial.Deserialize(res); // Since there's only one IC, we can hard-code the inputControlState array reference without looping foreach (InputControlOptions option in icRes.inputControlState[0].options) { employeeIDMap.Add(new KeyValuePair(option.value, option.label)); } } else { throw new WebException("Status code: " + res.StatusCode); }}[/code]The JSONDeserializer depends on helper classes to store the parsed result. Based on the guidance given in section 14.2 of the JasperReports Server REST API Reference, we will need to create the following helper classes to parse the response: private class InputControlOptions{ public string label { get; set; } public Boolean selected { get; set; } public string value { get; set; }}private class InputControlState{ public string uri { get; set; } public string value { get; set; } public List options { get; set; }}private class InputControlResponse{ public List inputControlState { get; set; }}[/code]Next, we need to add this method call to the ThisDocument_Startup method. For brevity, we will only show the containing try block, however the rest of the code should remain as shown above: try{ initServerConnection(); processInputControlValues(); success = true;}[/code]Now that we have a list of employees, it's time to execute the report and pass in each employee ID as a parameter. We'll use the following code to loop over each employee and make a REST API call to execute the report and return the output in XML format: private void execReports(){ int empCount = 1; foreach (KeyValuePair mapEntry in employeeIDMap) { String empId = mapEntry.Key; // Create web request RestRequest icRequest = new RestRequest("/EmployeeAccounts.xml"); icRequest.AddParameter("EmployeeID", empId); // Get response IRestResponse res = restClient.Execute(icRequest); empCount++; }}[/code]Now that we have the report output in XML format for each employee, we need to process it and begin adding the data to the MS Word table. This part is fairly complex, so we'll develop it in several stages. First, so that we don't lose the template that we're adding reporting to, we need to copy the first page of the MS Word document and keep the first page as the template that we'll use for the rest of the document. Also, since the MS Word document doesn't have a native implementation of pages as containers at the API level as of Word 2010, we'll need to manually insert a page break. To do this, we'll add the following code: private void copyPage(){ insertPageBreak(); object start = this.Content.Start; if (templateEnd == null) { templateEnd = this.Content.End / 2 - 1; } object docStart = this.Content.End - 1; object docEnd = this.Content.End; this.Range(ref start, ref templateEnd).Copy(); Microsoft.Office.Interop.Word.Range rng = this.Range(ref docStart, ref docEnd); rng.Paste();}private void insertPageBreak(){ Object objBreak = Word.WdBreakType.wdPageBreak; this.Characters.Last.InsertBreak(ref objBreak);}[/code]Before we move on to the next stage, we need to add a call to the copyPage() method in the execReports method. We'll insert this in the beginning of the foreach loop: private void execReports(){ int empCount = 1; foreach (KeyValuePair mapEntry in employeeIDMap) { copyPage(); String empId = mapEntry.Key; ... }}[/code]Next, let's begin processing the report output. We'll begin by checking to see if the REST API call returned valid report output or not. If not, we'll display an error message and shut down the process gracefully: private void processReportOutput(IRestResponse res, int empCount){ // Check to see if the report ran successfully on the server if (res.StatusCode != HttpStatusCode.OK) { MessageBox.Show("There was an error executing the report. Please ensure a valid report exists on the server.", "Report Execution Error", MessageBoxButtons.OK); this.Close(ref missing, ref missing, ref missing); } else { string reportStr = res.Content; int row = writeToWordDoc(reportStr, empCount); cleanUpTable(row, empCount); }}[/code]Next, we need to define the body of the writeToWordDoc() method. The algorithm for this method is: private int writeToWordDoc(String reportStr, int empCount){ // 1. replace <> placeholder with actual employee name // 2. parse XML output // 3. begin looping through the frame nodes in the XML output that denote a row of the report // 4. process group headers first // 5. process individual fields}[/code]First, we need to replace the <<employee>> placeholder: // Replace salutation object docEnd = this.Content.End;Word.Range tmpRange = this.Range(ref templateEnd, ref docEnd);tmpRange.Find.ClearFormatting();tmpRange.Find.Text = "<>";tmpRange.Find.Replacement.ClearFormatting();tmpRange.Find.Replacement.Text = employeeIDMap[empCount - 1].Value.Substring(0, 1).ToUpper() + employeeIDMap[empCount - 1].Value.Substring(1);tmpRange.Find.Wrap = Word.WdFindWrap.wdFindContinue;object replaceOne = Word.WdReplace.wdReplaceOne;tmpRange.Find.Execute(ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref replaceOne, ref missing, ref missing, ref missing, ref missing);[/code]Next, we need to get a handle to the Word table, parse the XML output, and begin looping over the frames. Since we've already made a copy of the first page and keeping it as a template, we need to get a handle to the second table in the report, which is why the Tables reference uses empCount + 1 as its index. // Get a handle to the Word tableWord.Table docTable = this.Tables[empCount + 1];// parse report outputXmlDocument doc = new XmlDocument();doc.LoadXml(reportStr);XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);nsManager.AddNamespace("js", "http://jasperreports.sourceforge.net/jasperreports/print");// loop thru framesXmlNodeList frameList = doc.SelectNodes("//js:frame", nsManager);int row = 2; // skip the header rowforeach (XmlNode frame in frameList){ // process each row, discussed in next section}return row;[/code]Next, we need to process the rows. We're going to treat the group headers separately so that we can format the MS Word table accordingly. Since the group headers in the report output use a bold font, we can take advantage of this knowledge to determine which rows contain the group headers. The code to format the group headers is: if (node.Attributes["style"] != null && node.Attributes["style"].InnerText.Equals("Sans_Bold")){ // Add group name to the table String groupName = frame.SelectSingleNode("js:text/js:textContent", nsManager).InnerText; docTable.Cell(row, 1).Range.Text = groupName; docTable.Cell(row, 1).Range.Font.Color = Word.WdColor.wdColorWhite; docTable.Cell(row, 1).Shading.BackgroundPatternColor = Word.WdColor.wdColorBlueGray; docTable.Cell(row, 1).Range.Font.Bold = 1; docTable.Cell(row, 2).Shading.BackgroundPatternColor = Word.WdColor.wdColorBlueGray; docTable.Cell(row, 3).Shading.BackgroundPatternColor = Word.WdColor.wdColorBlueGray;}else{ // process regular rows, described below}[/code]Next, we need to process the data for each regular row. Since the report contains three fields, we'll need to create three table cells for each row. The code to process the regular rows is: String accountName = frame.SelectSingleNode("js:text[2]/js:textContent", nsManager).InnerText;docTable.Cell(row, 1).Range.Text = accountName;docTable.Cell(row, 1).Range.Font.Color = Word.WdColor.wdColorBlack;docTable.Cell(row, 1).Shading.BackgroundPatternColor = Word.WdColor.wdColorWhite;docTable.Cell(row, 1).Range.Font.Bold = 0;String phone = frame.SelectSingleNode("js:text[3]/js:textContent", nsManager).InnerText;docTable.Cell(row, 2).Range.Text = phone;docTable.Cell(row, 2).Range.Font.Color = Word.WdColor.wdColorBlack;docTable.Cell(row, 2).Shading.BackgroundPatternColor = Word.WdColor.wdColorWhite;docTable.Cell(row, 2).Range.Font.Bold = 0;String address = frame.SelectSingleNode("js:text[4]/js:textContent", nsManager).InnerText;docTable.Cell(row, 3).Range.Text = address;docTable.Cell(row, 3).Range.Font.Color = Word.WdColor.wdColorBlack;docTable.Cell(row, 3).Shading.BackgroundPatternColor = Word.WdColor.wdColorWhite;docTable.Cell(row, 3).Range.Font.Bold = 0;[/code]Finally, we may have an extra row on the MS Word table that we will need to remove. The code to handle this is: private void cleanUpTable(int row, int empCount){ // Get a handle to the Word table Word.Table docTable = this.Tables[empCount + 1]; while (docTable.Rows.Count > row) { docTable.Rows[row].Delete(); }}[/code]That's all. Now we're ready to build and execute the project. For reference, here's the complete code for ThisDocument.cs file: using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Xml;using System.Xml.Linq;using System.Diagnostics;using System.IO;using System.Net;using System.Windows.Forms;using Microsoft.Office.Tools.Word;using Microsoft.VisualStudio.Tools.Applications.Runtime;using Office = Microsoft.Office.Core;using Word = Microsoft.Office.Interop.Word;using RestSharp;using RestSharp.Deserializers;using RestSharp.Authenticators;namespace WordDocument3{ public partial class ThisDocument { private const int MAX_ATTEMPT_COUNT = 5; private RestClient restClient; private List> employeeIDMap; private JsonDeserializer deserial = new JsonDeserializer(); private object templateEnd = null; private void ThisDocument_Startup(object sender, System.EventArgs e) { object paramMissing = System.Type.Missing; int connAttempt = 0; bool success = false; while (connAttempt < MAX_ATTEMPT_COUNT && !success) { try { initServerConnection(); processInputControlValues(); execReports(); success = true; } catch (WebException we) { String errorMsg = we.GetBaseException().Message + ". Please try again."; if (we.GetBaseException().Message.Contains("401")) { errorMsg = "Invalid JasperReports Server credentials. Please double-check your credentials and try again."; } DialogResult result = MessageBox.Show(errorMsg, "Error connecting to JasperReports Server", MessageBoxButtons.RetryCancel); if (result == DialogResult.Cancel) { this.Close(ref paramMissing, ref paramMissing, ref paramMissing); } connAttempt++; } } if (!success) { MessageBox.Show("Maximum server attempt count exceeded. Please ensure your JasperReports Server instance is running and try again later.", "Max Connection Attempts", MessageBoxButtons.OK); this.Close(ref paramMissing, ref paramMissing, ref paramMissing); } } private void ThisDocument_Shutdown(object sender, System.EventArgs e) { } private void initServerConnection() { // Get server credentials JSCredentials credentials = new JSCredentials(); credentials.Margin = Padding.Empty; credentials.Padding = Padding.Empty; credentials.StartPosition = FormStartPosition.CenterParent; NativeWindow wordMain = new NativeWindow(); wordMain.AssignHandle(Process.GetCurrentProcess().MainWindowHandle); DialogResult result = credentials.ShowDialog(wordMain); wordMain.ReleaseHandle(); if (result != DialogResult.OK) this.Close(ref missing, ref missing, ref missing); string url = "http://" + credentials.getServerURL() + "/jasperserver-pro/rest_v2/reports/reports/samples"; restClient = new RestClient(url); // Multi-tenant version (comment out for single tenant or community) String credentialsStr = String.Format("{0}|{1}", credentials.getUsername(), credentials.getOrg()); restClient.Authenticator = new HttpBasicAuthenticator(credentialsStr, credentials.getPassword()); // Single-tenant or community version (comment out for multi-tenancy) //restClient.Authenticator = new HttpBasicAuthenticator(credentials.getUsername(), credentials.getPassword()); } private void processInputControlValues() { employeeIDMap = new List>(); // Create web request RestRequest icRequest = new RestRequest("/EmployeeAccounts/inputControls/values"); // Get response IRestResponse res = restClient.Execute(icRequest); if (res.StatusCode == HttpStatusCode.OK) { InputControlResponse icRes = deserial.Deserialize(res); // Since there's only one IC, we can hard-code the inputControlState array reference without looping foreach (InputControlOptions option in icRes.inputControlState[0].options) { employeeIDMap.Add(new KeyValuePair(option.value, option.label)); } } else { throw new WebException("Status code: " + res.StatusCode); } } private void execReports() { int empCount = 1; foreach (KeyValuePair mapEntry in employeeIDMap) { copyPage(); String empId = mapEntry.Key; // Create web request RestRequest icRequest = new RestRequest("/EmployeeAccounts.xml"); icRequest.AddParameter("EmployeeID", empId); // Get response IRestResponse res = restClient.Execute(icRequest); processReportOutput(res, empCount); empCount++; } } private void copyPage() { insertPageBreak(); object start = this.Content.Start; if (templateEnd == null) { templateEnd = this.Content.End / 2 - 1; } object docStart = this.Content.End - 1; object docEnd = this.Content.End; this.Range(ref start, ref templateEnd).Copy(); Microsoft.Office.Interop.Word.Range rng = this.Range(ref docStart, ref docEnd); rng.Paste(); } private void insertPageBreak() { Object objBreak = Word.WdBreakType.wdPageBreak; this.Characters.Last.InsertBreak(ref objBreak); } private void processReportOutput(IRestResponse res, int empCount) { // Check to see if the report ran successfully on the server if (res.StatusCode != HttpStatusCode.OK) { MessageBox.Show("There was an error executing the report. Please ensure a valid report exists on the server.", "Report Execution Error", MessageBoxButtons.OK); this.Close(ref missing, ref missing, ref missing); } else { string reportStr = res.Content; int row = writeToWordDoc(reportStr, empCount); cleanUpTable(row, empCount); } } private int writeToWordDoc(String reportStr, int empCount) { // Replace salutation object docEnd = this.Content.End; Word.Range tmpRange = this.Range(ref templateEnd, ref docEnd); tmpRange.Find.ClearFormatting(); tmpRange.Find.Text = "<>"; tmpRange.Find.Replacement.ClearFormatting(); tmpRange.Find.Replacement.Text = employeeIDMap[empCount - 1].Value.Substring(0, 1).ToUpper() + employeeIDMap[empCount - 1].Value.Substring(1); tmpRange.Find.Wrap = Word.WdFindWrap.wdFindContinue; object replaceOne = Word.WdReplace.wdReplaceOne; tmpRange.Find.Execute(ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref replaceOne, ref missing, ref missing, ref missing, ref missing); // Get a handle to the Word table Word.Table docTable = this.Tables[empCount + 1]; // parse report output XmlDocument doc = new XmlDocument(); doc.LoadXml(reportStr); XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable); nsManager.AddNamespace("js", "http://jasperreports.sourceforge.net/jasperreports/print"); // loop thru frames XmlNodeList frameList = doc.SelectNodes("//js:frame", nsManager); int row = 2; // skip the header row foreach (XmlNode frame in frameList) { XmlNode node = frame.SelectSingleNode("js:text/js:reportElement", nsManager); if (node.Attributes["style"] != null && node.Attributes["style"].InnerText.Equals("PageHeader")) { // ignore page headers continue; } else { Object beforeRow = System.Type.Missing; docTable.Rows.Add(ref beforeRow); if (node.Attributes["style"] != null && node.Attributes["style"].InnerText.Equals("Sans_Bold")) { // Add group name to the table String groupName = frame.SelectSingleNode("js:text/js:textContent", nsManager).InnerText; docTable.Cell(row, 1).Range.Text = groupName; docTable.Cell(row, 1).Range.Font.Color = Word.WdColor.wdColorWhite; docTable.Cell(row, 1).Shading.BackgroundPatternColor = Word.WdColor.wdColorBlueGray; docTable.Cell(row, 1).Range.Font.Bold = 1; docTable.Cell(row, 2).Shading.BackgroundPatternColor = Word.WdColor.wdColorBlueGray; docTable.Cell(row, 3).Shading.BackgroundPatternColor = Word.WdColor.wdColorBlueGray; } else { String accountName = frame.SelectSingleNode("js:text[2]/js:textContent", nsManager).InnerText; docTable.Cell(row, 1).Range.Text = accountName; docTable.Cell(row, 1).Range.Font.Color = Word.WdColor.wdColorBlack; docTable.Cell(row, 1).Shading.BackgroundPatternColor = Word.WdColor.wdColorWhite; docTable.Cell(row, 1).Range.Font.Bold = 0; String phone = frame.SelectSingleNode("js:text[3]/js:textContent", nsManager).InnerText; docTable.Cell(row, 2).Range.Text = phone; docTable.Cell(row, 2).Range.Font.Color = Word.WdColor.wdColorBlack; docTable.Cell(row, 2).Shading.BackgroundPatternColor = Word.WdColor.wdColorWhite; docTable.Cell(row, 2).Range.Font.Bold = 0; String address = frame.SelectSingleNode("js:text[4]/js:textContent", nsManager).InnerText; docTable.Cell(row, 3).Range.Text = address; docTable.Cell(row, 3).Range.Font.Color = Word.WdColor.wdColorBlack; docTable.Cell(row, 3).Shading.BackgroundPatternColor = Word.WdColor.wdColorWhite; docTable.Cell(row, 3).Range.Font.Bold = 0; } } row++; } return row; } private void cleanUpTable(int row, int empCount) { // Get a handle to the Word table Word.Table docTable = this.Tables[empCount + 1]; while (docTable.Rows.Count > row) { docTable.Rows[row].Delete(); } } private class InputControlOptions { public string label { get; set; } public Boolean selected { get; set; } public string value { get; set; } } private class InputControlState { public string uri { get; set; } public string value { get; set; } public List options { get; set; } } private class InputControlResponse { public List inputControlState { get; set; } } }}[/code]

IntroductionJasperReports Server bundled installers install bundled Apache Tomcat, PostgreSQL servers, and PhantomJS. Normally, these installers run in either graphical or text-based menu mode. However, it's possible to install JasperReports Server silently using an options file. This install mode can be particularly helpful when used as part of another product installation. Step-by-Step instructionsOpen a text editor and add the following lines: mode=unattended installer-language=en jasperLicenseAccepted=yes prefix={path to install location e.g. C:\jrs63 or /opt/jrs63} Save the file as options.txt or whatever file you wish and close the text editor Open a command line window and browse to where the installer has been saved Run the following command: Windows: jasperreports-server-{version}-windows-x64-installer.exe --optionfile options.txt Linux: ./jasperreports-server-{version}-linux-x64-installer.run --optionfile options.txt ResultJasperReports Server will install silently then exit. The server will need to be started separately. To do so, execute the ctlscript.sh or ctlscript.bin file with the start option. Additional InformationTo see a list of available options, run the installer from the command line with the --help option. An example with additional options may look as follows: mode=unattended installer-language=en jasperLicenseAccepted=yes prefix={path to install location e.g. C:\jrs63 or /opt/jrs63} postgres_installation_type=bundled postgres_locale=en postgres_port=5432 postgres_password=<postgres password> tomcat_installation_type=bundled tomcat_server_port=8080 tomcat_server_shutdown_port=8005 tomcat_server_ajp_port=8009 phantomjs_installation_type=bundled jasperserver_install_sampledata=1

How To Access Localized Data Via A DomainTIBCO JasperReports Server offers users the ability to localize domain labels by leveraging the Java platform's built-in internationalization support using resource bundles. But accessing and analyzing localized data isn't as straightforward a proposition. The usual recommendation for this use case is to create multiple domains, one for each locale, store the localized data in separate database tables, and limit user access to the domains via roles. While this approach is fine in limited situations, some customers have reported performance and scalability issues and others have found this approach to be inflexible and difficult to maintain. There is an alternate approach that allows for localized data to be stored inline in a single table and uses a single domain to access it. The purpose of this wiki article is to present step-by-step guidance to implement this approach. PrerequisitesJasperReports Server Professional (any version) One or more database tables with localized inline data Configuration StepsCreate a new domain or edit an existing one and launch the Domain Designer Click the Calculated Fields tab Enter the following values in the text fields to create a new calculated field: Field Name: any descriptive name (this example uses "user_locale") Type: String Expression: groovy('org.springframework.context.i18n.LocaleContextHolder.getLocale()') Click the Save Field button Create another calculated field by populating the text fields with the following values: Field Name: any descriptive name (this example uses "Locale_Check") Type: Boolean Expression: <Database locale column name> == constant_fields_level.user_locale Click the Save Field button Click the Pre-filters tab Find the second calculated field created in the previous step (called "Locale_Check" in this example) and double-click it In the new filter that appears, leave the default equals in the combo box and enter true in the text field to the right of the combo box Click OK and Submit to save the domain TestingIf you have the sample data installed and you're using PostgreSQL as your database, perform the following steps to test the solution: Download, unzip and execute the following SQL script against the SugarCRM sample database: simple_domain_test_data_sql.zip Modify the /organizations/organization_1/domains/simple_domain using the steps described above Create an Ad Hoc view using the Simple Domain as a data source. Select the Opportunities set, drag or click it into the Selected Fields list, and click OK In the Ad Hoc editor, expand the Opportunities node Drag or click the Opportunity and Sales Stage fields to add them to the report Save the Ad Hoc view Log out of JasperReports Server On the login screen, click the Show locale and timezone link Change the Locale combo box setting from en_US to es Provide your credentials and re-login to JasperReports Server Click Ver -> Repositorio and navigate to the Ad Hoc view created in the previous steps Upon executing the Ad Hoc view, you should see the constrained data set with the locale filter automatically applied Known IssuesDue to Ad Hoc caching, you might still see the non-localized data the first couple of times you execute the Ad Hoc view. It may be necessary to log in as Superuser and manually clear the Ad Hoc cache from the Manage -> Server Settings page.

[toc on_off::hide=1] IMPORTANT: This is community-contributed content and is not supported by TIBCO Analytics. It is provided as-is with no express or implied warranties of any kind. The source code is included in the ji-ldap-sso-mt-60.zip file and may be modified to meet your business requirements. By doing so, however, the end consumer assumes all liabilities and responsibilities for modifying the code. IMPORTANT: This configuration has been tested and verified on TIBCO JasperReports® Server versions 6.x beginning with 6.0.1. If you're planning to use JasperReports® Server 6.0.0 or earlier, the original version of this page is available here. Normally, one would use the sample-applicationContext-externalAuth-ldap[-mt].xml as the basis to configure LDAP integration with JasperReports Server. Once configured, end users would be able to login to JasperReports Server using their LDAP credentials, but they would still see the login screen even if they were being redirected from a third party application. In order to bypass the login screen and authenticate via HTTP headers, some additional beans need to be defined and configured in the sample-applicationContext-externalAuth-ldap[-mt].xml file. A sample configuration for these beans are as follows: <!-- This pre-authentication header will handle LDAP authentication using a request header --> <bean id="preAuthProvider" class="org.springframework.security.web.authentication.preauth.PreAuthenticatedAuthenticationProvider"> <property name="preAuthenticatedUserDetailsService"><ref local="wrappedUserDetailsService"/></property> <property name="throwExceptionWhenTokenRejected" value="true"/> </bean> <!-- This wrapped user details service is used by the preauth provider defined above and provides a hook into the LdapUserDetails Service --> <bean id="wrappedUserDetailsService" class="org.springframework.security.core.userdetails.UserDetailsByNameServiceWrapper"> <property name="userDetailsService"><ref local="ldapUserDetailsService"/></property> </bean> <!-- This LdapUserDetailsService creates UserDetails objects using the userSearch and ldapAuthoritiesPopulator beans --> <bean id="ldapUserDetailsService" class="org.springframework.security.ldap.userdetails.LdapUserDetailsService"> <constructor-arg index="0"> <ref local="userSearch" /> </constructor-arg> <constructor-arg index="1"> <ref local="ldapAuthPopulator" /> </constructor-arg> </bean> <!-- This authorities populator bean retrieves roles automatically from the LDAP server --> <bean id="ldapAuthPopulator" class="org.springframework.security.ldap.userdetails.DefaultLdapAuthoritiesPopulator"> <constructor-arg index="0"> <ref local="ldapContextSource" /> </constructor-arg> <constructor-arg index="1"> <value></value> </constructor-arg> <property name="groupRoleAttribute"> <value>cn</value> </property> <property name="searchSubtree"> <value>true</value> </property> </bean> <!-- This filter will be added to the wildcard filter chain to intercept the request headers and pass them to the authentication manager --> <bean id="requestAuthFilter" class="org.springframework.security.web.authentication.preauth.RequestHeaderAuthenticationFilter"> <property name="principalRequestHeader" value="uname"/> <property name="credentialsRequestHeader" value="upass"/> <property name="authenticationManager" ref="ldapAuthenticationManager"/> </bean> Additionally, you'll need to add the preAuthProvider bean to the list of provider beans in the ldapAuthenticationManager bean: <bean id="ldapAuthenticationManager" class="org.springframework.security.providers.ProviderManager"> <property name="providers"> <list> <ref local="preAuthProvider"/> <ref local="ldapAuthenticationProvider"/> <ref bean="${bean.daoAuthenticationProvider}"/> <!--anonymousAuthenticationProvider only needed if filterInvocationInterceptor.alwaysReauthenticate is set to true <ref bean="anonymousAuthenticationProvider"/>--> </list> </property> </bean>Once these changes are made, copy the sample-applicationContext-externalAuth-LDAP[-mt].xml file to the <jasperserver[-pro]>/WEB-INF folder and rename the file to applicationContext-externalAuth-LDAP[-mt].xml. Additionally, the requestAuthFilter bean will need to be defined and configured in the WEB-INF/applicationContext-security-web.xml file. This bean will need to be added to the wildcard (/**) filter chain in the filterChainProxy bean immediately after the httpSessionContextIntegrationFilter bean: <bean id="filterChainProxy" class="org.springframework.security.util.FilterChainProxy"> <security:filter-chain-map path-type="ant" request-matcher="ant"> <security:filter-chain pattern="/xmla" filters="httpSessionContextIntegrationFilter,${bean.loggingFilter},${bean.basicProcessingFilter},JIAuthenticationSynchronizer,${bean.diagnosticLoggingFilter},anonymousProcessingFilter,basicAuthExceptionTranslationFilter,filterInvocationInterceptor"/> <security:filter-chain pattern="/services/**" filters="httpSessionContextIntegrationFilter,${bean.loggingFilter},delegatingCASSingleSignOutFilter,delegatingPreAuthenticatedFilter,${bean.portletAuthenticationProcessingFilter},delegatingBasicProcessingFilter,${bean.passwordExpirationProcessingFilter},JIAuthenticationSynchronizer,${bean.diagnosticLoggingFilter},anonymousProcessingFilter,wsBasicAuthExceptionTranslationFilter,filterInvocationInterceptor"/> <security:filter-chain pattern="/rest/login" filters="httpSessionContextIntegrationFilter,${bean.loggingFilter}, encryptionFilter,delegatingCASSingleSignOutFilter,delegatingPreAuthenticatedFilter,delegatingAuthenticationRestProcessingFilter,JIAuthenticationSynchronizer,${bean.diagnosticLoggingFilter},anonymousProcessingFilter,filterInvocationInterceptor"/> <security:filter-chain pattern="/rest/**" filters="httpSessionContextIntegrationFilter,${bean.loggingFilter},delegatingCASSingleSignOutFilter,delegatingPreAuthenticatedFilter,${bean.portletAuthenticationProcessingFilter},delegatingBasicProcessingFilter,${bean.passwordExpirationProcessingFilter},JIAuthenticationSynchronizer,${bean.diagnosticLoggingFilter},anonymousProcessingFilter,wsBasicAuthExceptionTranslationFilter,filterInvocationInterceptor"/> <security:filter-chain pattern="/rest_v2/**" filters="httpSessionContextIntegrationFilter,encryptionFilter,delegatingCASSingleSignOutFilter,textOnlyResponseWebAppSecurityFilter,jsCsrfGuardFilter,${bean.loggingFilter},${bean.userPreferencesFilter},delegatingPreAuthenticatedFilter,${bean.authenticationProcessingFilter},${bean.userPreferencesFilter},delegatingBasicProcessingFilter,delegatingRequestParameterAuthenticationFilter,JIAuthenticationSynchronizer,${bean.diagnosticLoggingFilter},anonymousProcessingFilter,restExceptionTranslationFilter,filterInvocationInterceptor"/> <security:filter-chain pattern="/**" filters="httpSessionContextIntegrationFilter,requestAuthFilter,encryptionFilter,delegatingCASSingleSignOutFilter,multipartRequestWrapperFilter,webAppSecurityFilter,jsCsrfGuardFilter,${bean.loggingFilter},${bean.userPreferencesFilter},delegatingPreAuthenticatedFilter,delegatingAuthenticationProcessingFilter,${bean.userPreferencesFilter},delegatingBasicProcessingFilter,delegatingRequestParameterAuthenticationFilter,JIAuthenticationSynchronizer,${bean.diagnosticLoggingFilter},anonymousProcessingFilter,delegatingExceptionTranslationFilter,filterInvocationInterceptor,switchUserProcessingFilter,iPadSupportFilter"/> </security:filter-chain-map> </bean> The principalRequestHeader and credentialsRequestHeader property values can be renamed to match any existing HTTP headers generated by your application. Multi-tenancy OnlyTo use these filters in a multi-tenant environment, you will need the following jar file: ji-ldap-sso-mt-60.zip Download and unzip this jar file and copy it to your <jasperserver-pro>/WEB-INF/lib folder. Then make the following changes to the following beans as shown below: <!-- This wrapped user details service is used by the preauth provider defined above and provides a hook into the LdapUserDetails Service --> <bean id="wrappedUserDetailsService" class="com.jaspersoft.jasperserver.multipleTenancy.MTUserDetailsByNameServiceWrapper"> <property name="userDetailsService"><ref bean="ldapUserDetailsService"/></property> <property name="tenantService"><ref bean="${bean.hibernateTenantService}"/></property> </bean> <!-- This LdapUserDetailsService creates UserDetails objects using the userSearch and ldapAuthoritiesPopulator beans --> <bean id="ldapUserDetailsService" class="com.jaspersoft.jasperserver.multipleTenancy.MTLdapUserDetailsService"> <constructor-arg index="0"> <ref local="userSearch" /> </constructor-arg> <constructor-arg index="1"> <ref local="ldapAuthPopulator" /> </constructor-arg> <property name="ldapExternalTenantProcessor"><ref local="ldapExternalTenantProcessor"/></property> </bean> <!-- This filter will be added to the wildcard filter chain to intercept the request headers and pass them to the authentication manager --> <bean id="requestAuthFilter" class="com.jaspersoft.jasperserver.multipleTenancy.security.externalAuth.processors.ldap.MTRequestHeaderPreAuthenticatedProcessingFilter"> <property name="principalRequestHeader" value="uname"/> <property name="credentialsRequestHeader" value="upass"/> <property name="authenticationManager" ref="ldapAuthenticationManager"/> <property name="externalDataSynchronizer" ref="externalDataSynchronizer"/> </bean> TestingTo test this setup, install the Mozilla Firefox browser and install the Modify Headers plugin. To install this plugin, click the Tools > Add-ons menu, click the Get Add-ons tab, and search for the Modify Headers plugin. Once the plugin is installed, perform the following steps: Click the Firefox menu and select Web Developer > Modify Headers.Click the Select Action button at the top of the dialog box and choose Add.In the Header Name text field, type uname.In the Header Value text field, type a username in your LDAP directory.Click the Add button.Click the Select Action button again and choose Add.In the Header Name text field, type upass.In the Header Value text field, type the password for the user specified in step 4.Click the Add button.Click OK to close the dialog box.In Firefox, navigate to http://localhost:8080/jasperserver-pro.Result: The standard landing screen appears instead of the login screen. NOTE - Since headers are not passed for Studio connections, the initial primary login attempt via Studio will fail and the backup mechanism of SOAP will be used. But in JasperReports Server version 7.1.0 SOAP is removed so Studio connections will no longer work with JasperReports Server 7.1.0 using the above approach. ji-ldap-sso-mt-60.zip

Issue DescriptionIn JasperReports Server v6.x, when first creating a new Ad Hoc view, the data chooser dialog box appears. This dialog box has two modes: Tree and List view. List view is the default. How can we change this to default to Tree? ResolutionYou can modify the following file to set the default data source selection mode from list to tree mode: Look for the following line (it should be on or around line 52: var LIST_TAB = "list"; Add a TREE_TAB variable, like: var LIST_TAB = "list"; var TREE_TAB = "tree"; Look for the following function: render: function () { Dialog.prototype.render.apply(this, arguments); // connect search form to dialog header this._dfdRenderSerachFormTo.resolve(this.$tabHeaderContainer); this.openTab(LIST_TAB); return this; }, Change it to: render: function () { Dialog.prototype.render.apply(this, arguments); // connect search form to dialog header this._dfdRenderSerachFormTo.resolve(this.$tabHeaderContainer); this.openTab(TREE_TAB); // <== Change the openTab parameter from LIST_TAB to TREE_TAB return this; }, Set the javascript.optimize property to false in the file, jasperserver-proWEB-INFjs.config.properties. # Control default value of javascript optimization in runtime javascript.optimize=falseShut down JasperReports Server web application. Delete the application server cache. Restart the server. Review section "Customizing JavaScript Files" in the JasperReports Server Ultimate Guide and consider making your change in the optimized files.

IMPORTANT: This is not the supported upgrade path. The supported upgrade path is to export the JRS repository as superuser, start a new AWS instance of the desired version, then re-import the repository as superuser. To upgrade JasperReports Server on an AWS instance using the overlay installer, do the following: If you haven't already done so, follow these instructions to export your license key to a format that can be used with a secure FTP/SCP client: http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-connect-to-instance-linux.html Using a secure FTP/SCP client like FileZilla, connect to your instance using the instance's public IP address and ec2-user credentials: Upload the overlay installer zip archive to the root directory of the AWS instance using the secure FTP/SCP client. Connect to the instance using an SSH client like PuTTY: Unzip the overlay installer using the following command: unzip jasperreports-server-x.x.x-overlay.zip Change to the buildomatic folder under the newly created overlay folder, copy the sample_conf/postgresql_master.properties and rename it to default_master.properties: cd overlay/buildomatic cp sample_conf/postgresql_master.properties default_master.properties Edit the default_master.properties file using vi and make the following changes: Line 42: appServerDir = /var/lib/tomcat7 Line 45: CATALINA_HOME = /usr/share/tomcat7 Line 46: CATALINA_BASE = /var/lib/tomcat7 Shut down the Apache Tomcat service: sudo service tomcat7 stop Change to the parent overlay folder and back up the jasperserver PostgreSQL database:: cd .. pg_dump -U postgres -W jasperserver > jasperserver.dmp (at the password prompt, type postgres) Run the overlay install script: sudo ./overlay install (at the prompts, do the following: Accept the default overlayWorkspace folder location Type y when asked if you backed up your jasperserver database Type y when asked if you shut down your Tomcat server instance At the prompt asking for the location of your master.properties file, type /home/ec2-user/overlay/buildomatic/default_master.properties Accept the default location for the deployed application folder Applying customizations is beyond the scope of this article, so unless you have customizations, type y when prompted) Make the following file system permission changes: sudo chown -R tomcat:tomcat /var/log/tomcat7 sudo chown -R tomcat:tomcat /tmp/adhocCache sudo chown -R tomcat:tomcat /var/lib/tomcat7/webapps Start the tomcat service: sudo service tomcat7 start RESULT: You should now see the new JasperReports Server home screen when you navigate to the login screen in your browser OPTIONAL: Take a snapshot of the volume associated with your AWS instance by doing the following: Log into the AWS console Navigate to the Instances page Look up the Instance ID Browse to the Volumes page and search for the volume based on the instance ID Select the volume associated with the instance ID, click the Actions button and select Create Snapshot: Provide a name and description of your snapshot, then click the Take Snapshot button. Click the Close button when finished.