Any Report Done for CSV Output Has to Be Designed Differently Than a PDF Format

iVOS Reporter Tips

Table of Contents

CSV Report Design

Make Selection Criteria Required

Use a Table not in Data Source->Find…

Days between two dates:

Age when report run:

Add/Subtract days from a Date:

Comparing two dates when it is known that both are not null:

Comparing two dates when one or both could be null:

Comparing two dates, ignore time of day:

Comparing date to a date constant

Remove Org Group from Policy Year

Data Types for Computed Fields in SQL

Boolean vs. boolean

Data Source: GUI vs. Manual

Count of an Item Based on a Condition

Add/Subtract Examples

Multiply Example

Where Clause

Nested Select

Adding a new font to a report

Shifting Field Down if Field Above Expands

Display Report When No Rows Returned

XML jasperReport name =”BaseReport”

Page Break within a Group

DOCTYPE

Steps to Save XML, Edit XML, and Update Report Template in iVOS

Parameters in Select statement of SubReport

Print When for an Entire Band.

Count/Sum based on distinct value

Formatting Dates

Steps to create variable to calculate Check total in Report Summary

How to run a report for a specific division showing only the claims that happened in the Security Department Org2 from all locations.

How to modify Edit Date (Add Date) to show Timestamp format

insured_name_type.name_type_code = 'PN'

claim_total – claimant_reporting_history and claimant_report_period_map

transaction_summary.claimant_total

Using Oracle Stored Procedure from a report

Add a Background/Watermark

CSV Report Design

Any report done for CSV output has to be designed differently than a PDF format.

A separate design should be created as follows:

Put the column headers in the Title band so that they are output only once. They should be in one row.
Put fields to be output into the Detail Band. One row only.
Delete any fields defined in the Page Header band and set its height to 0.
Delete any fields defined in the Column Footer and set its height to 0.
Delete any fields defined in the Page Footer and set its height to 0.
Delete any fields defined in the Summary and set its height to 0.
The field x and y coordinates must be the same - go across every column, make them the same width - make it line up exactly the same for each.
Ensure field width will hold largest data. Data is truncated to fit the width.
Fields in detail band can be variables or columns
Display pattern for date:
MM/dd/yyyyMM-dd-yyyy
yyyy/MM/ddyyyy-MM-dd
yyyyMMdd
Display pattern for numbers:
Without commas: #0 or #0.00
With commas: ###,###,##0.00 or ###,###,##0

Make Selection Criteria Required

To require a user enter a value for a Selection Criteria, follow the following steps. From Data Source, select field and right-click. Select “Set as required”.

Use a Table not in Data Source->Find…

If you want to include a column from a table that is not in the list after selecting Find… and then clicking on the Table tab, you need to edit the SQL statement. Click the SQL… button and add the table to the FROM clause of the SELECT statement. Refer to the documentation provided with the database product or other books on SQL for the syntax required to add the table. When manually adding columns to the SELECT statement, VOS recommends that you follow the syntax:

table_name.column_name as column_name.

SELECT claimant.accepted AS accepted,

claimant.accepted_date AS accepted_date,

Days between two dates:

In this example, days between incident date and when the report is run.

new Integer(com.valleyoak.util.DateUtil.daysAfter($F{incident_date}, com.valleyoak.util.DateUtil.getDate()))

Age when report run:

Note: setScale(0, ROUND_DOWN) truncates so that age of 35.6 years is not displayed as 36.

new BigDecimal(com.valleyoak.util.DateUtil.daysAfter($F{birth_date}, com.valleyoak.util.DateUtil.getDate()) / 365).setScale(1, java.math.BigDecimal.ROUND_DOWN)

Add/Subtract days from a Date:

In this example, display date 10 days prior to the date the report is run through the report run date.

(new java.text.SimpleDateFormat("MM/dd/yyyy")).format(com.valleyoak.util.DateUtil.relativeDate(com.valleyoak.util.DateUtil.getDate(), -10))+" Through "+(new java.text.SimpleDateFormat("MM/dd/yyyy")).format(com.valleyoak.util.DateUtil.getDate())

Comparing two dates when it is known that both are not null:

($F{denied_date}.compareTo($F{delayed_decision_date}) > 0 ?

"Denied before Delayed" :

"Denied after or same as Delayed")

Comparing two dates when one or both could be null:

$F{denied_date} != null & $F{delayed_decision_date} != null ?

($F{denied_date}.compareTo($F{delayed_decision_date}) == 0 ?

"Denied and Delayed same date" :

($F{denied_date}.compareTo($F{delayed_decision_date}) > 0 ?

"Denied before Delayed" :

"Denied after Delayed")

) :

"Denied or Delayed date not entered"

Comparing two dates, ignore time of day:

$F{accepted_date} != null & $F{delayed_decision_date} != null ?

((new java.text.SimpleDateFormat("yyyyMMdd")).format($F{delayed_decision_date}).compareTo((new java.text.SimpleDateFormat("yyyyMMdd")).format($F{accepted_date})) > 0?

"Delayed after Accepted" :

"Delayed before Accepted"

) :

"Accepted or Delayed date not entered"

Comparing date to a date constant

com.valleyoak.util.DateUtil.parseDate("20060630","yyyyMMdd")

used in a comparison statement.

($F{insurance_type}.equals("3") || $F{insurance_type}.equals("5") || $F{insurance_type}.equals("6") || $F{insurance_type}.equals("7")|| $F{insurance_type}.equals("8")) & com.valleyoak.util.DateUtil.parseDate("20060630","yyyyMMdd").compareTo($F{incident_date}) ==-1 ? new BigDecimal(0) : new BigDecimal(9)

Remove Org Group from Policy Year

iVOS 4.1 displays the Org Group, Client Code, Insurance Type, and Occupation Group when a Selection Criteria’s list of values is displayed. In some cases, this is not the desired result. The example below shows how to remove Org Group from the Policy Year Selection Criteria. Click on the Edit button, then select the policy_period_desc column in the claim table. Replace the syntax with the following:

Select distinct field_value=policy_period_desc, field_desc=policy_period_desc, field_name='policy_period_desc' From (select policy_period_desc from policy_period) as policy_period

Data Types for Computed Fields in SQL

When the Select statement contains a formula, Reporter sometimes does not correctly identify the data type of the field. Use the cast function available in Oracle and MS SQL to force the date type.

Select max(amount) as max_amount

From claim…

Select cast(max(amount) as numeric(16, 4)) as max_amount

From claim…

Select count(*) as count_sample

From claim…

Select cast(count(*) as int) as count_sample

From claim…

Boolean vs. boolean

Comparisons return the Java built-in type of boolean with values true or false. iVOS Reporter requires the Java object Boolean. When the element requires the result of a comparison such as the Print When Expression, define as Boolean:

new Boolean($V{PAGE_COUNT}.intValue() > 0)

Data Source: GUI vs. Manual

iVOS Report Designer looks at the Select to determine if the SQL is being maintained by the GUI or in manual mode. If ‘SeLeCT’, SQL is being maintainable using

Count of an Item Based on a Condition

Create a variable with Calculation set to Sum. If the condition is true, the row is counted.

$F{claimant_type_code}.intValue() == 2? new Integer(1): new Integer(0)

Add/Subtract Examples

$F{payment_amount}.subtract($F{payment_no_reserve})

$F{payment_amount1}.add($F{payment_amount2}).add($F{payment_amount3})

Multiply Example

$F{payment_no_reserve1}.multiply (new BigDecimal(-1))

Where Clause

iVOS Reporter modifies the first Where clause found in the select to apply the Selection Criteria that has been selected. By adding ‘(2=2)’ to the Where clause in the primary select, iVOS will place the Selection Criteria at that location. This is necessary when the Query contains multiple selects because of the use of Union or a nested select.

Nested Select

In the example below, a column returned in the select is actually another select. For iVOS Reporter to correctly handle this case, a carriage return must immediately follow the FROM in the select. No spaces can be between the FROM and carriage return. Note in the example that the where clause of the nested select also contains a select. Every instance of the FROM must be immediately followed by the carriage return. (This example is returning the most recently edited notepad entry for a given notepad_type_code. The syntax is MS SQL specific because of the convert)

SELECT claim.incident_date as incident_date,

claim.claim_number as claim_number,

claim.claim_id as claim_id,(select max(convert(varchar(8000), notepad.body)) from

notepad where claimant.claimant_id = notepad.claimant_id and notepad.notepad_type_code = 3

and notepad.edit_date =(select max(np.edit_date) from

notepad np where claimant.claimant_id = np.claimant_id and notepad_type_code = 3))

AS notepad_claim_status

FROM claim

LEFT OUTER JOIN organization1 ON claim.org_group_code = organization1.org_group_code

…

Adding a new font to a report

If you go to a font on a new report and add a new font it merely creates a new style of a font you already have – even if you give it the name of the font fine you want.
So to really add a new font:

Put your .TTF font file in …\WEB-INF\classes folder.
Go to the reports folder (ie; C:\Tomcat55\webapps\ivos\reports), find and open the XML file of the report.
Add the font definition (manually) to the xml file using the MICRE13B.TTF or BARCODE39.TTF entries as examples. The standard iVOS font definitions are at the top of the xml. Sample line to copy:

Reopen your report in Report Designer. You should now see the font listed.

Notes:

pdfFontName is the most critical parameter. It must match the name of the font file.
/GARABD.TTF is the true-type font file that exists in the the WEB-INF/classes folder. Notice that GARABD stands for Garabond Bold, so the isBold attribute is true. If you need bold, normal, and italics, you’ll need to copy all three font files and create three separate reportFont tags.
Advanced note: I recommend that you keep the isDefault property set to false.If you set it to true, then jasper (or Adobe, not sure which) assumes that the font is already loaded into memory; usually this won’t be a problem in a Window environment, but it might behave on a different OS.

Shifting Field Down if Field Above Expands

If a field is placed on the report below an element whose size can increase based on the data returned, it is possible to configure the fields below to shift down automatically. This is done by adding positionType="Float" to the reportElement as in the example below:

</textElement>

<text<![CDATA[How Injury Occurred:]]</text>

</staticText>

Display Report When No Rows Returned

If you want a report to display the headings and other static elements when the select returns no rows, enter the whenNoDataType=AllSectionsNoDetail" parameter as shown below. The data in all sections is displayed except for elements in the detail band.:

A use of the feature is to show that no rows were retrieved. Below is an example on placing a field in the summary band that is only printed when zero rows are returned.

XML jasperReport name =”BaseReport”

The value in name is displayed in error messages. If the report contains sub reports, it is recommended that you edit the XML file and change the default name of BaseReport to a name unique to each sub report. This helps when an error is logged because it then identifies which report generated the exception.

Page Break within a Group

If the report requires a page break within a group, a possible solution is to create two groups with the same Group Expression and check Start New Page for both. Use a sub report if multiple rows are to be displayed before the page break.

DOCTYPE

iVOS Reporter modifies the path in DOCTYPE in certain situations. When you use the Report Designer Save As to get a copy of the XML, the path is changes. This will cause an error to be displayed when you try to open or run the report later. Replace the modified line with the following to correct the problem:

<!DOCTYPE jasperReport PUBLIC "-//JasperReports//DTD Report Design//EN" "

Variables

Count value in Column

Accepted date field

($F{accepted}==null || $F{accepted}.equals("0"))?new Integer(0):new Integer(1)

Denied date field

($F{denied}==null || $F{denied}.equals("0"))?new Integer(0):new Integer(1)

Grand Total

$V{REPORT_COUNT}

Repeat offender report

ssn in(select ssn from claimant group by ssn having count(*)>3)

Steps to Save XML, Edit XML, and Update Report Template in iVOS

From Reporter Layout tab, click Export XML Source.
From XML source view, select File->Save As to get XML saved into location available from workstation
Make changes to XML.
Replace DOCTYPE line in the XML file with line above in DOCTYPE section and save
On Reporter layout tab, click Select User Design and select the update XML.

Parameters in Select statement of SubReport

When specifying a parameter to be placed into the Select statement, you need to specify in format $P!{} instead of usual $P{}. The subreport will run fine from Report Designer when testing but when you have the main report invoke the subreport, you will receive an error such as below or error stating parameter n is invalid:

<SEVERE> java.sql.SQLException: An expression of non-boolean type specified in a context where a condition is expected, near '@P0'.

Example:

WHERE (2=2) and insured.insured_id = $P!{insured_id}

There are two possible ways to use parameters in the query:

1. The parameters are used like normal java.sql.PreparedStatement parameters using the following syntax:

SELECT * FROM Orders WHERE CustomerID = $P{OrderCustomer}

2. Sometimes is useful to use parameters to dynamically modify portions of the SQL query or to pass the entire SQL query as a parameter to the report filling routines. In such a case, the syntax differs a little, like in the following example:

SELECT * FROM Orders ORDER BY $P!{OrderByClause}

For more detail how to use parameters:

Print When for an Entire Band.

The sample report, ClaimLogExample.xml shows how to add the Print When to an entire band. The XML needs to be directly edited to do this, iVOS Report Designer does not display the Print When expression for the entire band.

Create a variable that will evaluate to new Integer(1) or new Integer(0). The band prints if a 1, does not print if a 0.
Edit the XML to put the print when expression at the band level. In the sample file, the print when has been places at the detail band using the variable print_detail.

Count/Sum based on distinct value

The sample report, ClaimLogExample.xml, show how to count based on a distinct value for claim_id. The samples also show how to define the expression when using the Print When for an entire band to not count the rows that are not printed..

Formatting Dates

Below is a complete listing of date formats. These formats work with both Date and Timestamp fields.

The format logic can be entered in the design tool as show below or as part of a string as shown in the following example:

"This claim is set to close on " + (new java.text.SimpleDateFormat("MM/dd/yyyy")).format($F{insured_coordinator_date})

A specific example is to display the date and time with the time in the AM/PM format. The specific example is: MM/dd/yyyy hh:mm aa. Note the lower case hh that displays in 12 hour format compared to HH that displays the 24 hour format.

In the reports XML file, the field that you want to display in AM/PM needs to be defined as a Timestamp. From Data Source as shown, highlight the date field and right click. Select Change to Timestamp.

The following pattern letters are defined (all other characters from 'A' to 'Z' and from 'a' to 'z' are reserved):

Letter / Date or Time Component / Presentation / Examples
G / Era designator / Text / AD
y / Year / Year / 1996; 96
M / Month in year / Month / July; Jul; 07
w / Week in year / Number / 27
W / Week in month / Number / 2
D / Day in year / Number / 189
d / Day in month / Number / 10
F / Day of week in month / Number / 2
E / Day in week / Text / Tuesday; Tue
a / Am/pm marker / Text / PM
H / Hour in day (0-23) / Number / 0
k / Hour in day (1-24) / Number / 24
K / Hour in am/pm (0-11) / Number / 0
h / Hour in am/pm (1-12) / Number / 12
m / Minute in hour / Number / 30
s / Second in minute / Number / 55
S / Millisecond / Number / 978
z / Time zone / General time zone / Pacific Standard Time; PST; GMT-08:00
Z / Time zone / RFC 822 time zone / -0800

Pattern letters are usually repeated, as their number determines the exact presentation:

Text: For formatting, if the number of pattern letters is 4 or more, the full form is used; otherwise a short or abbreviated form is used if available. For parsing, both forms are accepted, independent of the number of pattern letters.
Number: For formatting, the number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount. For parsing, the number of pattern letters is ignored unless it's needed to separate two adjacent fields.
Year: For formatting, if the number of pattern letters is 2, the year is truncated to 2 digits; otherwise it is interpreted as a number.

For parsing, if the number of pattern letters is more than 2, the year is interpreted literally, regardless of the number of digits. So using the pattern "MM/dd/yyyy", "01/11/12" parses to Jan 11, 12 A.D.

For parsing with the abbreviated year pattern ("y" or "yy"), SimpleDateFormat must interpret the abbreviated year relative to some century. It does this by adjusting dates to be within 80 years before and 20 years after the time the SimpleDateFormat instance is created. For example, using a pattern of "MM/dd/yy" and a SimpleDateFormat instance created on Jan 1, 1997, the string "01/11/12" would be interpreted as Jan 11, 2012 while the string "05/04/64" would be interpreted as May 4, 1964. During parsing, only strings consisting of exactly two digits, as defined by Character.isDigit(char), will be parsed into the default century. Any other numeric string, such as a one digit string, a three or more digit string, or a two digit string that isn't all digits (for example, "-1"), is interpreted literally. So "01/02/3" or "01/02/003" are parsed, using the same pattern, as Jan 2, 3 AD. Likewise, "01/02/-3" is parsed as Jan 2, 4 BC.