jburczyk

I'm still getting an empty dataset. I think the issue I'm having is you'll notice that the management groups column is a result of a LISTAGG operation - it's made up of multiple individuals management groups, with the primary management group denoted with "(P)" and all groups separated by a comma. Do I need to do a separate CTE to identify the protocols that have the selected management groups and then move forward from there? I made an attempt along these lines in the attached document, though this is running much too long for users to wait.

Thank you so much! I always forget that there's additional options within the windows. I do all my coding in the Source tab. Ok. Let me try.

jgust, Thanks so much for your help! I think I understand the first part of your suggestion, because, as you noted, I did that for my OrganizationalUnit and VisitStatus parameters. I don't understand the part about changing the nested type...are you referring to TypeGroup? Or ChooseGroup? I don't think so, but I just haven't ever done a multi-select parameter this way.

I've made multiple attempts to turn the Single Select Management Group Parameter in the attached JRXML file to a Multi-Select Parameter, but I keep returning an empty data set. Please help! Jennifer J. Burczyk-Brown, MA | OnCore Report Writer UAB Center for Clinical & Translational Science (CCTS) JT1608 | 625 19th Street South | Birmingham, AL 35233 P: 205.996.3971 | M: 205.612.7990 | JBurczyk@uabmc.edu

I have been working on a report in Jaspersoft Studio, but now when I try to load it into OnCore (the application we use that requires JasperSoft), I'm getting an error that javascript is not supported. I have been working on this report (mostly just the SQL portion), and because I do not know JAVA or javascript, I have no idea what part of the code is causing issues. Could someone take a look and let me know what I'm doing wrong?

Hi, I have written the program below, and I'm struggling with a "Parameter type not suppoprted in query" error. Any suggestions about what I'm doing wrong? I've used this type of parameter before, without any issues... <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 6.9.0.final using JasperReports Library version 6.9.0-cb8f9004be492ccc537180b49c026951f4220bf3 --> <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="invoiceable_items" pageWidth="1690" pageHeight="954" orientation="Landscape" columnWidth="1690" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" whenResourceMissingType="Empty" uuid="7f369dea-094c-458f-97fb-6f6d351dea7f"> <property name="net.sf.jasperreports.print.keep.full.text" value="true"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="OnCore Test2"/> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.unit." value="pixel"/> <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/> <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/> <parameter name="fromDate" class="java.util.Date"/> <parameter name="thruDate" class="java.util.Date"/> <parameter name="ChoosePatientType" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="userContactId" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[]]></parameterDescription> </parameter> <queryString> <![CDATA[(select * from (select summ.PROTOCOL_ID , summ.PROTOCOL_NO , summ.CONSENTED_COUNT , summ.CONSENT_TOTAL_COUNT , summ.NOT_ELIGIBLE_COUNT , summ.ON_STUDY_COUNT , summ.ON_TREATMENT_COUNT , summ.OFF_TREATMENT_COUNT , summ.OFF_STUDY_COUNT , summ.EXPIRED_COUNT , summ.ON_FOLLOWUP_COUNT , summ.ON_LTFU_COUNT , case $P{ChoosePatientType} when 'Consented' then CONSENT_TOTAL_COUNT when 'On Study' then ON_STUDY_COUNT when 'On Treatment' then ON_TREATMENT_COUNT end as PATIENT_TYPE_COUNT , hist.STATUS , hist.START_DATE , hist.END_DATE , row_number() over (partition by summ.PROTOCOL_NO, hist.START_DATE order by END_DATE desc) as rn from sv_pcl_accrual_summary summ left outer join sv_pcl_status_history hist on summ.PROTOCOL_NO = hist.PROTOCOL_NO where hist.START_DATE between $P{fromDate} and $P{thruDate} and hist.STATUS in ('TERMINATED','CLOSED TO ACCRUAL','ABANDONED') ) where rn = 1 and PATIENT_TYPE_COUNT = 0) step1 --protocol privileges LEFT JOIN ( SELECT protocol_id ,( CASE WHEN EXISTS ( SELECT 1 FROM sv_user_pcl_permission WHERE function_group = 'PROTOCOL-SEARCH' AND protocol_id = pcl.protocol_id AND contact_id = $P{userContactId} ) THEN 'Y' ELSE 'N' END ) has_protocol_search FROM smrs_protocol pcl ) pcl_privileges ON step1.protocol_id = pcl_privileges.protocol_id]]> </queryString> <field name="PROTOCOL_ID" class="java.math.BigDecimal"/> <field name="PROTOCOL_NO" class="java.lang.String"/> <field name="CONSENTED_COUNT" class="java.math.BigDecimal"/> <field name="CONSENT_TOTAL_COUNT" class="java.math.BigDecimal"/> <field name="NOT_ELIGIBLE_COUNT" class="java.math.BigDecimal"/> <field name="ON_STUDY_COUNT" class="java.math.BigDecimal"/> <field name="ON_TREATMENT_COUNT" class="java.math.BigDecimal"/> <field name="OFF_TREATMENT_COUNT" class="java.math.BigDecimal"/> <field name="OFF_STUDY_COUNT" class="java.math.BigDecimal"/> <field name="EXPIRED_COUNT" class="java.math.BigDecimal"/> <field name="ON_FOLLOWUP_COUNT" class="java.math.BigDecimal"/> <field name="ON_LTFU_COUNT" class="java.math.BigDecimal"/> <field name="PATIENT_TYPE_COUNT" class="java.math.BigDecimal"/> <field name="STATUS" class="java.lang.String"/> <field name="START_DATE" class="java.sql.Timestamp"/> <field name="END_DATE" class="java.sql.Timestamp"/> <field name="RN" class="java.math.BigDecimal"/> <field name="HAS_PROTOCOL_SEARCH" class="java.lang.String"/> <title> <band height="102"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <frame> <reportElement mode="Opaque" x="0" y="0" width="1690" height="72" backcolor="#006699" uuid="2f9e325f-5726-45f7-980d-45df3aefc4ea"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <staticText> <reportElement x="0" y="0" width="946" height="72" forecolor="#FFFFFF" uuid="c8271c97-a3f6-4dc1-83e0-f40231bdab3f"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textElement verticalAlignment="Middle"> <font size="34" isBold="true"/> </textElement> <text><![CDATA[Ended Protocols]]></text> </staticText> <staticText> <reportElement x="1221" y="0" width="465" height="72" forecolor="#FFFFFF" uuid="c89de18d-0f2f-4fe9-a2d2-6cbee0c1c1e7"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Right" verticalAlignment="Middle"> <font size="14" isBold="false"/> </textElement> <text><![CDATA[All protocols, by item type]]></text> </staticText> </frame> <frame> <reportElement stretchType="RelativeToTallestObject" mode="Opaque" x="0" y="72" width="1690" height="30" backcolor="#006699" uuid="a9a52697-082e-42e1-89ec-68dd0b049098"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <staticText> <reportElement stretchType="RelativeToTallestObject" x="0" y="0" width="88" height="30" forecolor="#FFFFFF" uuid="3afa32d5-2f7d-4eb8-a131-28fdda4bc83b"/> <textElement textAlignment="Right"/> <text><![CDATA[start Date:]]></text> </staticText> <staticText> <reportElement stretchType="RelativeToTallestObject" x="167" y="0" width="106" height="30" forecolor="#FFFFFF" uuid="2d71ca4b-6729-46eb-a0a4-ab7decdab5c6"/> <textElement textAlignment="Right"/> <text><![CDATA[End Date:]]></text> </staticText> <textField pattern="MM/dd/yyyy"> <reportElement stretchType="RelativeToTallestObject" x="88" y="0" width="79" height="30" forecolor="#FFFFFF" uuid="55296cab-b55c-4bfa-95de-7c268febe10e"/> <textFieldExpression><![CDATA[$P{fromDate}]]></textFieldExpression> </textField> <textField pattern="MM/dd/yyyy"> <reportElement stretchType="RelativeToTallestObject" x="273" y="0" width="72" height="30" forecolor="#FFFFFF" uuid="c091402d-3826-4336-a1f3-95cdc021eaaa"/> <textFieldExpression><![CDATA[$P{thruDate}]]></textFieldExpression> </textField> </frame> </band> </title> <columnHeader> <band height="22" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement mode="Opaque" x="0" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="b1dcfa37-fe41-4dd3-ba8c-1132cca71ed2"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Protocol No.]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="100" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="1928f95a-28f0-4497-995c-e008308bff8c"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Protocol Status]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="200" y="0" width="72" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="9e8369e1-45fe-4a67-845f-0a4fe441169e"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[start Date]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="272" y="0" width="72" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="fa7b5671-3185-4328-ba28-fa3f57d38c95"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[End Date]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="344" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="8448a272-89ca-4966-8527-f2c38d4d6fa0"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Consented Subjects]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="430" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5e7fd45d-792f-499e-83b9-e2075c938224"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Total Consented Subjects]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="516" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="4a6ebe9b-a072-4f48-bf3b-a8347dc047b9"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Not Eligible Subjects]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="602" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="c591bc04-dc74-41d8-94d7-e2aab81e21fc"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[On Study Subjects]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="688" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5c8d0443-f637-4202-9259-38a4122fc2b7"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[On Treatment Subjects]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="774" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="2c3fed66-2675-4f75-96f9-d67194495e1d"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Off Treatment Subjects]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="860" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="6822b1bc-3d29-4e50-a96a-0be70ea18855"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Off Study Subjects]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="946" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="f21fa8bd-0e0d-4f38-a6f7-dc98632918e1"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Expired Subjects]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1032" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="0cd2dcaf-09a6-4d3d-a927-1ccb6eea7daf"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[On Followup Subjects]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1118" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="84e996e6-c9a7-427e-85da-56127c21d313"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Lost to Followup Subjects]]></text> </staticText> </band> </columnHeader> <detail> <band height="27" splitType="Prevent"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" x="0" y="0" width="100" height="27" uuid="3f984aa3-f917-4330-85e8-5a07cc66650a"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{PROTOCOL_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="100" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="da3929a7-d752-4ba9-830c-fe93ef1cdc22"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{STATUS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="200" y="0" width="72" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="1257da62-9027-4101-b32c-504e606b701a"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{START_DATE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="272" y="0" width="72" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="dea22672-42b8-4c4e-bca6-d0eebfcd4181"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{END_DATE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="344" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="e5b65c06-b8c8-47fb-b1fa-a1052e4a2111"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{CONSENTED_COUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="430" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="9a905988-f100-45d0-9b3b-0fac589372a3"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <textFieldExpression><![CDATA[$F{CONSENT_TOTAL_COUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="516" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="ccf720d2-779b-4bc4-b1b4-3fa253629c5d"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <textFieldExpression><![CDATA[$F{NOT_ELIGIBLE_COUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="602" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="14dcbcda-6498-412f-b21e-a3fcb5018502"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{ON_STUDY_COUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="688" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="613bbd4e-6774-4cce-ad88-7e7fa8b3077d"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{ON_TREATMENT_COUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="774" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="c9073fec-8b08-4620-bf9d-82060c93f0e4"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{OFF_TREATMENT_COUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="860" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="a7830fc3-d9b8-4790-85d8-7810a9591f0d"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{OFF_STUDY_COUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="946" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="b654a9a5-7e1e-4197-9107-577e91b277f4"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{EXPIRED_COUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1032" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="6f1b1463-21fb-4cb3-92a5-e29639debbc5"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{ON_FOLLOWUP_COUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1118" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="17078554-b751-42c3-9b9d-7cf920281e08"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{ON_LTFU_COUNT}]]></textFieldExpression> </textField> </band> </detail> <pageFooter> <band height="25" splitType="Prevent"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement mode="Opaque" x="0" y="0" width="89" height="25" backcolor="#E6E6E6" uuid="00b71e79-08b5-44a3-af35-e114fc18433b"> <property name="local_mesure_unity" value="pixel"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textElement textAlignment="Right" verticalAlignment="Middle"/> <text><![CDATA[Report Date:]]></text> </staticText> <textField pattern="MM/dd/yyyy"> <reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="89" y="0" width="1411" height="25" backcolor="#E6E6E6" uuid="4306bff6-d9b7-498f-8124-8c583de632a9"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Left" verticalAlignment="Middle"> <paragraph firstLineIndent="20"/> </textElement> <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression> </textField> <textField isBlankWhenNull="false"> <reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="1501" y="0" width="100" height="25" forecolor="#000000" backcolor="#E6E6E6" uuid="16866726-e0e9-4b0b-86b7-17528b27e5e7"> <property name="com.jaspersoft.studio.unit.x" value="px"/> </reportElement> <textElement textAlignment="Right" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of "]]></textFieldExpression> </textField> <textField evaluationTime="Report" isBlankWhenNull="false"> <reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="1601" y="0" width="90" height="25" forecolor="#000000" backcolor="#E6E6E6" uuid="43c13917-328f-49cc-84e0-fd0ca42186ba"> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> </reportElement> <box leftPadding="2"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression> </textField> </band> </pageFooter> <noData> <band height="20"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement x="0" y="0" width="1690" height="20" uuid="29dde353-6dcd-4817-a15e-599cff469095"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <text><![CDATA[No invoiceable items found for the parameters entered and user's access.]]></text> </staticText> </band> </noData> </jasperReport>

I have a couple of parameters with the definitions below: <parameter name="mgmtGroup" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="TypeGroup" class="java.lang.String"/> <parameter name="ChooseGroup" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["Primary Management Group".equals($P{TypeGroup}) ? "pcl_info.primary_group" : "pcl_info.management_groups"]]></defaultValueExpression> </parameter> The issue is that the datafields that the parameters need to refer to are set up as such: Management Groups select mga.* from (select distinct mg.protocol_id ,LISTAGG((case when mg.primary='Y' then mg.mgmt_group_description || ' (P)' else mg.mgmt_group_description end), '; ') within group (order by primary DESC, mgmt_group_description) management_groups from SV_PCL_MGMT_MGMTGROUP mg GROUP BY mg.protocol_id)mga inner join (select distinct protocol_id from SV_PCL_MGMT_MGMTGROUP mg where $X{IN,mg.mgmt_group_description,mgmtGroup} on mga.protocol_id = mgb.protocol_id Primary Management Group select mg.protocol_id ,case when mg.primary='Y' then mg.mgmt_group_description end as primary_group from SV_PCL_MGMT_MGMTGROUP mg where $X{IN,mg.mgmt_group_description,mgmtGroup} So, management_groups appears as ManagementGroup1; ManagementGroup2;...ManagementGroupn Primary_group appears as PrimaryManagementGroup I've tried a number of different arrangements, and if I only want to find any row where the selected management groups appear, the report works fine. It's when I added the join to the Primary Management Group where things began to fail. When I tried to compare the mgmtGroup parameter to the ChooseGroup parameter, I got an error about column index - I'm thinking it was because the two parameters aren't the same type. I can't refer to ChooseGroup instead of mgmtGroup in the $X statement, since I have that set up as a String parameter. I just can't seem to figure out what I need to do.

Thank you so much for your help. Between the time I posted the question and when you responded, I kept plugging away at it, moving some stuff around, and ended up getting to a report that produced data. I just had gotten stuck and couldn't figure out how/why the report I had wasn't working as expected.

Thank you both for your suggestions. The issue that I'm having is that when I select values for the parameter, I'm getting those values in the report + the null values, and I only want those values, not the nulls. Perhaps I just don't have my parameter set up properly.

Somewhat related to this question, I have a couple of parameters with the definitions below: <parameter name="mgmtGroup" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="TypeGroup" class="java.lang.String"/> <parameter name="ChooseGroup" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["Primary Management Group".equals($P{TypeGroup}) ? "pcl_info.primary_group" : "pcl_info.management_groups"]]></defaultValueExpression> </parameter> The issue is that the datafields that the parameters need to refer to are set up as such: Management Groups select mga.* from (select distinct mg.protocol_id ,LISTAGG((case when mg.primary='Y' then mg.mgmt_group_description || ' (P)' else mg.mgmt_group_description end), '; ') within group (order by primary DESC, mgmt_group_description) management_groups from SV_PCL_MGMT_MGMTGROUP mg GROUP BY mg.protocol_id)mga inner join (select distinct protocol_id from SV_PCL_MGMT_MGMTGROUP mg where $X{IN,mg.mgmt_group_description,mgmtGroup} on mga.protocol_id = mgb.protocol_id Primary Management Group select mg.protocol_id ,case when mg.primary='Y' then mg.mgmt_group_description end as primary_group from SV_PCL_MGMT_MGMTGROUP mg where $X{IN,mg.mgmt_group_description,mgmtGroup} So, management_groups appears as ManagementGroup1; ManagementGroup2;...ManagementGroupn Primary_group appears as PrimaryManagementGroup I've tried a number of different arrangements, and if I only want to find any row where the selected management groups appear, the report works fine. It's when I added the join to the Primary Management Group where things began to fail. When I tried to compare the mgmtGroup parameter to the ChooseGroup parameter, I got an error about column index - I'm thinking it was because the two parameters aren't the same type. I can't refer to ChooseGroup instead of mgmtGroup in the $X statement, since I have that set up as a String parameter. I just can't seem to figure out what I need to do.

I'm working on a report, and I have several parameters that are Multi-Select type parameters that are set up as $X{IN,column_name,parameter_name}, but in my testing when I select a value for the parameter, I'm also getting rows where column_name is null. Is there a way to exclude those values IF the parameter is not empty?

I keep getting the "Document is Empty" message when I try to run the report below, and when I run it in OnCore (which is where I'm trying to load this report), I get an error message that says:"com.percipenz.oncore.reporting.jasper.JasperRunServiceException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : invoiceable_items" I can't figure out what the issue is. Can anyone see any errors? When I comment out the lines with parameters and run the SQL code in SQL Developer, the report completes. <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 6.9.0.final using JasperReports Library version 6.9.0-cb8f9004be492ccc537180b49c026951f4220bf3 --> <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="invoiceable_items" pageWidth="1434" pageHeight="954" orientation="Landscape" columnWidth="1434" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" whenResourceMissingType="Empty" uuid="7f369dea-094c-458f-97fb-6f6d351dea7f"> <property name="net.sf.jasperreports.print.keep.full.text" value="true"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="OnCore Test2"/> <property name="com.jaspersoft.studio.unit." value="pixel"/> <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/> <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/> <parameter name="fromDate" class="java.util.Date"/> <parameter name="thruDate" class="java.util.Date"/> <parameter name="mgmtGroup" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="oncologyGroup" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="department" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="protocol" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="ProtocolInclude" class="java.lang.String"> <parameterDescription><![CDATA[For Protocol Related: "Include" or "Exclude"]]></parameterDescription> </parameter> <parameter name="ProtocolStatus" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="Unplanned" class="java.lang.String"> <parameterDescription><![CDATA[For Visit Variations: Unplanned - "Y" or "N"]]></parameterDescription> </parameter> <parameter name="Additional" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="VisitStatus" class="java.lang.String"> <parameterDescription><![CDATA[For Visit Variations: Status - "Missed" or "NA"]]></parameterDescription> </parameter> <parameter name="userContactId" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[]]></parameterDescription> </parameter> <queryString> <![CDATA[sELECT event_data.type ,event_data.protocol_id ,event_data.item ,event_data.milestone ,event_data.sequence_number ,event_data.initials ,event_data.exclusion ,event_data.occurred_date ,event_data.direct ,event_data.total_cost ,event_data.withhold ,event_data.withholding_pct ,event_data.sponsor_name ,event_data.indirect_flag ,event_data.trigger_type ,event_data.reoccurring ,event_data.visit_status ,event_data.comments ,NVL(event_data.comments, event_data.eval_notes) AS more_info ,event_data.STATUS ,event_data.unplanned_visit ,event_data.additional ,event_data.eval_notes ,pcl_info.protocol_no ,pcl_info.departments ,pcl_info.management_groups ,pcl_info.oncology_groups ,pcl_info.primary_sponsor ,pcl_info.secondary_sponsors ,pcl_info.indirect_cumulative ,pcl_info.protocol_indirect ,pcl_info.subject_indirect ,pcl_info.milestone_indirect ,pcl_info.not_indirect_multiplier ,pcl_info.indirect_multiplier ,pcl_privileges.has_invoiceable_items ,pcl_privileges.has_visit_variations ,pcl_privileges.has_subject_identification FROM (select pr.* from (SELECT type ,protocol_id ,item ,NULL milestone ,NULL sequence_number ,NULL initials ,NULL visit_status ,occurred_date ,direct ,total_cost ,withhold ,withholding_pct ,sponsor_name ,indirect_flag ,trigger_type ,reoccurring ,CASE WHEN trigger_type IS NOT NULL AND reoccurring = 'Y' THEN 'Include' WHEN ( reoccurring IS NULL OR reoccurring = 'N' ) THEN 'Include' WHEN trigger_type IS NULL AND reoccurring = 'Y' THEN 'Exclude' END AS exclusion ,comments ,STATUS ,NULL unplanned_visit ,NULL additional ,NULL eval_notes FROM ( SELECT 'Protocol Related' type ,a.protocol_id ,a.budget_event_description item ,a.occurred_date ,NVL(a.sponsor_cost, 0) direct ,NULL total_cost ,a.withhold ,nvl(a.withholding_pct, 0) withholding_pct ,( SELECT sponsor_name FROM smrs_pcl_sponsor sps ,smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.sponsor AND sps.sponsor = ss.sponsor ) sponsor_name ,a.indirect_flag ,a.trigger_type ,b.reoccurring ,b.comments ,c.STATUS ,row_number() OVER ( PARTITION BY b.pcl_budget_event_id ORDER BY c.status_date DESC ,c.created_date DESC ) rn FROM sv_pcl_invoicables a LEFT OUTER JOIN sv_fin_pcl_events b ON a.protocol_id = b.protocol_id AND a.budget_event_id = b.budget_event_id AND a.pcl_budget_event_id = b.pcl_budget_event_id LEFT OUTER JOIN sv_pcl_status c ON a.protocol_id = c.protocol_id WHERE 1 = 1 AND a.na_for_invoice IS NULL OR a.na_for_invoice = 0 ) WHERE (rn = 1)) pr UNION ALL SELECT 'Subject Milestone' type ,protocol_id ,NULL item ,(visit_description) milestone ,sequence_number ,initials ,visit_status ,TO_CHAR(visit_date, 'MM/DD/YYYY') occurred_date ,NVL(sponsor_cost, 0) direct ,NULL total_cost ,withhold ,NVL(withholding_pct, 0) withholding_pct ,sponsor_name ,'N' indirect_flag ,NULL trigger_type ,NULL reoccurring ,NULL exclusion ,NULL comments ,NULL STATUS ,NULL unplanned_visit ,NULL additional ,NULL eval_notes FROM ( SELECT protocol_id ,pcl_milestone_id ,( CASE WHEN visit_description IS NULL THEN NULL WHEN ( SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no ) IS NOT NULL THEN 'Arm ' || ( SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no ) || ': ' || visit_description ELSE visit_description END ) visit_description ,na_for_invoice ,withhold ,withholding_pct ,visit_status ,visit_date ,cost ,sponsor ,sponsor_cost ,( SELECT sponsor_name FROM smrs_pcl_sponsor sps ,smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.sponsor AND sps.sponsor = ss.sponsor ) sponsor_name ,protocol_subject_id ,sequence_number ,initials ,TO_CHAR(date_submitted, 'MM/DD/YYYY') submitted_date FROM ( SELECT vst.protocol_id ,vst.pcl_milestone_id ,vst.arm_no ,vst.visit_id ,vst.visit_description ,CASE WHEN cal.missed_flag = 'Y' THEN 'Missed' WHEN vst.visit_date IS NULL THEN 'Missed' WHEN cal.na_flag = 'Y' THEN 'N/A' WHEN cal.visit_status = 'Planned' THEN 'Planned' WHEN vst.visit_date IS NOT NULL THEN 'Occurred' END AS visit_status ,vst.visit_date ,vst.cost ,vst.sd_study_spec_id ,vst.budget_version_no ,vst.na_for_invoice ,vst.withhold ,vst.withholding_pct ,vst.sponsor ,vst.sponsor_cost ,NULL sponsor_due ,vst.protocol_subject_id ,vst.sequence_number ,vst.initials ,vst.uncertified_item ,vst.date_completed ,vst.date_monitored ,vst.date_submitted FROM sv_pcs_visit_invoicables vst LEFT OUTER JOIN sv_sub_calendar cal ON vst.visit_id = cal.sd_pcs_tracking_id AND vst.protocol_subject_id = cal.protocol_subject_id UNION SELECT ext.protocol_id ,ext.pcl_milestone_id ,ext.arm_no ,ext.visit_id ,ext.visit_string visit_description ,CASE WHEN cal.missed_flag = 'Y' THEN 'Missed' WHEN ext.visit_date IS NULL THEN 'Missed' WHEN cal.na_flag = 'Y' THEN 'N/A' WHEN cal.visit_status = 'Planned' THEN 'Planned' WHEN ext.visit_date IS NOT NULL THEN 'Occurred' END AS visit_status ,ext.visit_date ,ext.cost ,ext.sd_study_spec_id ,ext.budget_version_no ,ext.na_for_invoice ,ext.withhold ,ext.withholding_pct ,ext.sponsor ,ext.sponsor_cost ,NULL sponsor_due ,ext.protocol_subject_id ,ext.sequence_number ,get_subject_name(ext.protocol_subject_id, 'Y') initials ,ext.uncertified_item ,ext.date_completed ,ext.date_monitored ,ext.date_submitted FROM sv_pcs_extended_invoicables ext LEFT OUTER JOIN sv_sub_calendar cal ON ext.visit_id = cal.sd_pcs_tracking_id AND ext.protocol_subject_id = cal.protocol_subject_id UNION SELECT ext.protocol_id ,ext.pcl_milestone_id ,ext.arm_no ,ext.visit_id ,ext.visit_string visit_description ,CASE WHEN cal.missed_flag = 'Y' THEN 'Missed' WHEN ext.visit_date IS NULL THEN 'Missed' WHEN cal.na_flag = 'Y' THEN 'N/A' WHEN cal.visit_status = 'Planned' THEN 'Planned' WHEN ext.visit_date IS NOT NULL THEN 'Occurred' END AS visit_status ,ext.visit_date ,ext.cost ,ext.sd_study_spec_id ,ext.budget_version_no ,ext.na_for_invoice ,ext.withhold ,ext.withholding_pct ,ext.sponsor ,ext.sponsor_cost ,NULL sponsor_due ,ext.protocol_subject_id ,ext.sequence_number ,get_subject_name(ext.protocol_subject_id, 'Y') initials ,ext.uncertified_item ,ext.date_completed ,ext.date_monitored ,ext.date_submitted FROM sv_pcs_oem_invoicables ext LEFT OUTER JOIN sv_sub_calendar cal ON ext.visit_id = cal.sd_pcs_tracking_id AND ext.protocol_subject_id = cal.protocol_subject_id UNION SELECT rev.protocol_id ,rev.milestone_id ,rev.arm_no ,rev.visit_id ,rev.visit_description ,CASE WHEN cal.missed_flag = 'Y' THEN 'Missed' WHEN rev.visit_date IS NULL THEN 'Missed' WHEN cal.na_flag = 'Y' THEN 'N/A' WHEN cal.visit_status = 'Planned' THEN 'Planned' WHEN rev.visit_date IS NOT NULL THEN 'Occurred' END AS visit_status ,rev.visit_date ,rev.cost ,rev.sd_study_spec_id ,rev.budget_version_no ,rev.na_for_invoice ,rev.withhold ,rev.withholding_pct ,rev.sponsor ,rev.sponsor_cost ,rev.sponsor_due ,rev.protocol_subject_id ,rev.sequence_number ,rev.initials ,rev.uncertified_item ,rev.date_completed ,rev.date_monitored ,rev.date_submitted FROM sv_invoice_reversals rev LEFT OUTER JOIN sv_sub_calendar cal ON rev.visit_id = cal.sd_pcs_tracking_id AND rev.protocol_subject_id = cal.protocol_subject_id WHERE milestone_id IS NOT NULL ) a ,( SELECT NULL spon ,NULL milestone_id ,'Y' include_na FROM dual ) b WHERE 1 = 1 AND ( b.spon IS NULL OR NVL(a.sponsor, b.spon) = b.spon ) AND ( b.milestone_id IS NULL OR a.pcl_milestone_id IN ( SELECT pcl_milestone_id FROM sv_pcs_milestone_ref WHERE milestone_id_ref = b.milestone_id ) ) AND ( NVL(b.include_na, 'N') = 'Y' OR NVL(a.na_for_invoice, 0) = 0 ) AND (NVL(visit_description, ' ') != 'Screen Failed') UNION ALL SELECT protocol_id ,pcl_milestone_id ,visit_description ,na_for_invoice ,withhold ,withholding_pct ,visit_status ,visit_date ,cost ,sponsor ,sponsor_cost ,( SELECT sponsor_name FROM smrs_pcl_sponsor sps ,smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.sponsor AND sps.sponsor = ss.sponsor ) sponsor_name ,protocol_subject_id ,sequence_number ,initials ,submitted_date FROM ( SELECT evt.protocol_id ,pcl_milestone_id ,visit_description ,CASE WHEN cal.missed_flag = 'Y' THEN 'Missed' WHEN evt.visit_date IS NULL THEN 'Missed' WHEN cal.na_flag = 'Y' THEN 'N/A' WHEN cal.visit_status = 'Planned' THEN 'Planned' WHEN evt.visit_date IS NOT NULL THEN 'Occurred' END AS visit_status ,evt.visit_date ,NULL sae_no ,NULL osr_id ,cost ,na_for_invoice ,withhold ,withholding_pct ,sponsor ,sponsor_cost ,evt.protocol_subject_id ,sequence_number ,initials ,NULL submitted_date FROM sv_pcs_event_invoicables evt LEFT OUTER JOIN sv_sub_calendar cal ON evt.protocol_subject_id = cal.protocol_subject_id AND evt.visit_date = cal.visit_date WHERE evt.visit_description != 'Screen Failed' AND TRUNC(evt.billed_date) < TRUNC(NVL(( SELECT MIN(start_date) FROM onc_pcl_invoicing_rule rl WHERE rl.protocol_id = evt.protocol_id AND rl.rule_type = 'NB' AND ( rl.sponsor IS NULL OR rl.sponsor = evt.sponsor ) ), evt.billed_date + 1)) UNION SELECT evt.protocol_id ,pcl_milestone_id ,visit_description ,CASE WHEN cal.missed_flag = 'Y' THEN 'Missed' WHEN evt.visit_date IS NULL THEN 'Missed' WHEN cal.na_flag = 'Y' THEN 'N/A' WHEN cal.visit_status = 'Planned' THEN 'Planned' WHEN evt.visit_date IS NOT NULL THEN 'Occurred' END AS visit_status ,evt.visit_date ,NULL sae_no ,NULL osr_id ,cost ,na_for_invoice ,withhold ,withholding_pct ,sponsor ,sponsor_cost ,evt.protocol_subject_id ,sequence_number ,get_subject_name(evt.protocol_subject_id, 'Y') initials ,NULL submitted_date FROM sv_invoicable_screen_failures evt LEFT OUTER JOIN sv_sub_calendar cal ON evt.protocol_subject_id = cal.protocol_subject_id AND evt.visit_date = cal.visit_date WHERE TRUNC(evt.billed_date) < TRUNC(NVL(( SELECT MIN(start_date) FROM onc_pcl_invoicing_rule rl WHERE rl.protocol_id = evt.protocol_id AND rl.rule_type = 'NB' AND ( rl.sponsor IS NULL OR rl.sponsor = evt.sponsor ) ), evt.billed_date + 1)) UNION SELECT protocol_id ,pcl_milestone_id ,visit_description ,CASE WHEN cal.missed_flag = 'Y' THEN 'Missed' WHEN saes.visit_date IS NULL THEN 'Missed' WHEN cal.na_flag = 'Y' THEN 'N/A' WHEN cal.visit_status = 'Planned' THEN 'Planned' WHEN saes.visit_date IS NOT NULL THEN 'Occurred' END AS visit_status ,saes.visit_date ,sae_no ,NULL osr_id ,cost ,na_for_invoice ,withhold ,withholding_pct ,sponsor ,sponsor_cost ,saes.protocol_subject_id ,sequence_number ,initials ,NULL submitted_date FROM sv_invoicable_saes saes LEFT OUTER JOIN sv_sub_calendar cal ON saes.protocol_subject_id = cal.protocol_subject_id AND saes.visit_date = cal.visit_date UNION SELECT protocol_id ,pcl_milestone_id ,milestone ,CASE WHEN cal.missed_flag = 'Y' THEN 'Missed' WHEN osrs.visit_date IS NULL THEN 'Missed' WHEN cal.na_flag = 'Y' THEN 'N/A' WHEN cal.visit_status = 'Planned' THEN 'Planned' WHEN osrs.visit_date IS NOT NULL THEN 'Occurred' END AS visit_status ,osrs.visit_date ,NULL sae_no ,osr_id ,cost ,na_for_invoice ,withhold ,withholding_pct ,sponsor ,sponsor_cost ,osrs.protocol_subject_id ,sequence_number ,initials ,NULL submitted_date FROM sv_invoicable_osrs osrs LEFT OUTER JOIN sv_sub_calendar cal ON osrs.protocol_subject_id = cal.protocol_subject_id AND osrs.visit_date = cal.visit_date ) a WHERE nvl(na_for_invoice, 0) = 0 ) UNION ALL SELECT 'Pass Through' type ,a.protocol_id protocol_id ,budget_event_description item ,( CASE WHEN ( SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no ) IS NOT NULL THEN 'Arm ' || ( SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no ) || ': ' || visit_description ELSE visit_description END ) milestone ,sequence_number ,initials ,NULL visit_status ,tracking_date occurred_date ,NVL(ptec.visit_direct, 0) direct ,NVL(ptec.visit_total, 0) total_cost , --sponsor,--id of the sponsor withhold ,NVL(withholding_pct, 0) withholding_pct , --na_for_invoice, ( SELECT sponsor_name FROM smrs_pcl_sponsor sps ,smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.sponsor AND sps.sponsor = ss.sponsor ) sponsor_name ,'N' indirect_flag ,NULL trigger_type ,NULL reoccurring ,NULL exclusion ,NULL comments ,NULL STATUS ,NULL unplanned_visit ,NULL additional ,NULL eval_notes FROM ( SELECT DISTINCT pcl_evaluation_id ,pcl_budget_event_id ,protocol_id ,tracking_date eval_date ,TO_CHAR(tracking_date, 'MM/DD/YYYY') tracking_date ,tracking_id ,a.sd_ss_tx_cycle_visit_id ,visit_eval_id ,a.arm_no ,arm_code arm ,na_for_invoice ,a.sd_study_spec_id ,a.budget_version_no ,( SELECT version_no FROM sd_study_spec WHERE sd_study_spec_id = a.sd_study_spec_id ) calendar_version_no ,( SELECT TO_CHAR(version_date, 'MM/DD/YYYY') FROM onc_pcl_budget_version WHERE sd_study_spec_id = a.sd_study_spec_id AND budget_version_no = a.budget_version_no ) budget_version_date ,NULL sponsor_cost ,NULL sponsor_due ,sponsor ,withhold ,withholding_pct ,( SELECT sponsor_name FROM smrs_pcl_sponsor sps ,smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.sponsor AND sps.sponsor = ss.sponsor ) sponsor_name ,NVL(budget_event_description, clinical_procedure) budget_event_description ,visit_description ,protocol_subject_id ,sequence_number ,get_subject_name(a.protocol_subject_id, 'Y') initials ,to_number(NULL) uncertified_item FROM sv_pcs_procedure_invoicables a UNION ALL SELECT DISTINCT a.pcl_evaluation_id ,a.pcl_budget_event_id ,a.protocol_id ,visit_date eval_date ,TO_CHAR(a.visit_date, 'MM/DD/YYYY') tracking_date ,a.visit_id tracking_id ,NULL sd_ss_tx_cycle_visit_id ,visit_eval_id ,a.arm_no ,( SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no ) arm ,na_for_invoice ,a.sd_study_spec_id ,a.budget_version_no ,( SELECT version_no FROM sd_study_spec WHERE sd_study_spec_id = a.sd_study_spec_id ) calendar_version_no ,( SELECT TO_CHAR(version_date, 'MM/DD/YYYY') FROM onc_pcl_budget_version WHERE sd_study_spec_id = a.sd_study_spec_id AND budget_version_no = a.budget_version_no ) budget_version_date ,a.sponsor_cost ,a.sponsor_due ,a.sponsor ,a.withhold ,a.withholding_pct ,( SELECT sponsor_name FROM smrs_pcl_sponsor sps ,smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.sponsor AND sps.sponsor = ss.sponsor ) sponsor_name ,DECODE(a.pcl_budget_event_id, NULL, NVL(c.clinical_procedure, d.event_description), ( SELECT budget_event_description FROM onc_budget_event WHERE budget_event_id = e.budget_event_id )) budget_event_description ,a.visit_description ,a.protocol_subject_id ,a.sequence_number ,a.initials ,uncertified_item FROM sv_invoice_reversals a ,onc_pcl_evaluation b ,sd_study_spec_evaluation c ,pf_lab_category d ,onc_pcl_budget_event e WHERE a.milestone_id IS NULL AND a.pcl_evaluation_id = b.pcl_evaluation_id AND b.sd_study_spec_eval_id = c.sd_study_spec_eval_id AND c.lab_category_id = d.lab_category_id(+) AND a.pcl_budget_event_id = e.pcl_budget_event_id(+) ) a LEFT JOIN ( SELECT /*+ RULE */ t.pcl_evaluation_id ,t.pcl_budget_event_id ,t.sd_ss_tx_cycle_visit_id ,t.arm_no ,(t.pcl_evaluation_id || ':' || NVL(t.pcl_budget_event_id, 0) || ':' || t.sd_ss_tx_cycle_visit_id || ':' || NVL(t.arm_no, 0)) KEY ,SUM(t.invoice_cost * t.pk_count) visit_direct ,SUM(t.invoice_cost * t.pk_count * DECODE(fp.budget_only_indirect, 'Y', fp.net_rate_without_indirect, DECODE(t.indirect_flag, 'Y', fp.net_rate_with_indirect, fp.net_rate_without_indirect))) visit_total FROM sv_pcl_evaluation_visit_costs t ,sv_fin_parameters fp ,sv_evaluation_pass_thru_visits pt WHERE fp.protocol_id = t.protocol_id AND t.invoice_cost > 0 AND t.pcl_evaluation_id = pt.pcl_evaluation_id AND NVL(t.pcl_budget_event_id, 0) = NVL(pt.pcl_budget_event_id, 0) AND ( pt.visit_id IS NULL OR ( t.sd_ss_tx_cycle_visit_id = pt.visit_id AND NVL(t.arm_no, 0) = NVL(pt.arm_no, 0) ) ) GROUP BY t.pcl_evaluation_id ,t.pcl_budget_event_id ,t.study_spec_id ,t.sd_ss_tx_cycle_visit_id ,t.arm_no ) ptec ON ptec.KEY = a.pcl_evaluation_id || ':' || NVL(a.pcl_budget_event_id, 0) || ':' || a.sd_ss_tx_cycle_visit_id || ':' || NVL(a.arm_no, 0) WHERE ( a.na_for_invoice IS NULL OR a.na_for_invoice = 0 ) UNION ALL SELECT st1.type ,st1.protocol_id ,st1.item ,st1.milestone ,st1.sequence_number ,st1.initials ,st1.visit_status ,st1.occurred_date ,st1.direct ,st1.total_cost ,st1.withhold ,st1.withholding_pct ,st1.sponsor_name ,st1.indirect_flag ,st1.trigger_type ,st1.reoccurring ,NULL exclusion ,st1.comments ,st1.STATUS ,st1.unplanned_visit ,vv.additional ,vv.eval_notes FROM ( SELECT 'Visit Variation' type ,a.protocol_id ,LISTAGG(( CASE WHEN evaluation_detail IS NOT NULL THEN evaluation2 || ': ' || evaluation_detail ELSE evaluation2 END ), '; ') within GROUP ( ORDER BY eval_id ) item ,( CASE WHEN arm_code IS NOT NULL THEN 'Arm ' || arm_code || ': ' || visit_string ELSE visit_string END ) milestone ,a.sequence_number ,a.initials ,CASE WHEN a.missed_visit = 'Y' THEN 'Missed' WHEN a.na_visit = 'Y' THEN 'N/A' END AS visit_status ,nvl(a.visit_date, ( CASE WHEN missed_visit = 'Y' THEN 'Missed' WHEN na_visit = 'Y' THEN 'N/A' END )) occurred_date ,sum(CASE WHEN ( nvl(missed_visit, 'N') = 'Y' OR nvl(na_visit, 'N') = 'Y' OR billed_date IS NULL ) THEN ( CASE WHEN nvl(is_soc, 0) = 1 OR nvl(is_pass_thru, 0) = 1 THEN 0 ELSE nvl(nvl(eval_cost, non_scheduled_eval_cost), 0) * eval_count_sum END ) * ( CASE WHEN nvl(unplanned_visit, 'N') = 'Y' THEN 1 ELSE - 1 END ) ELSE ( CASE WHEN NOT ( nvl(additional, 0) = 1 AND nvl(soc_flag, 'N') = 'Y' ) THEN ( CASE WHEN nvl(additional, 0) = 1 THEN nvl(nvl(eval_cost, non_scheduled_eval_cost), 0) WHEN missed_count IS NOT NULL AND missed_count > 0 THEN nvl(nvl(eval_cost, non_scheduled_eval_cost), 0) * missed_count * - 1 WHEN nvl(is_soc, 0) = 1 AND nvl(soc_flag, 'N') = 'N' THEN nvl(nvl(eval_cost, non_scheduled_eval_cost), 0) ELSE nvl(nvl(eval_cost, non_scheduled_eval_cost), 0) * - 1 END ) ELSE 0 END ) END) direct ,sum(CASE WHEN ( nvl(missed_visit, 'N') = 'Y' OR nvl(na_visit, 'N') = 'Y' OR billed_date IS NULL ) THEN ( CASE WHEN nvl(is_soc, 0) = 1 OR nvl(is_pass_thru, 0) = 1 THEN 0 ELSE nvl(nvl(eval_due, non_scheduled_eval_due), 0) * eval_count_sum END ) * ( CASE WHEN nvl(unplanned_visit, 'N') = 'Y' THEN 1 ELSE - 1 END ) ELSE ( CASE WHEN NOT ( nvl(additional, 0) = 1 AND nvl(soc_flag, 'N') = 'Y' ) THEN ( CASE WHEN nvl(additional, 0) = 1 THEN nvl(nvl(eval_due, non_scheduled_eval_due), 0) WHEN missed_count IS NOT NULL AND missed_count > 0 THEN nvl(nvl(eval_due, non_scheduled_eval_due), 0) * missed_count * - 1 WHEN nvl(is_soc, 0) = 1 AND nvl(soc_flag, 'N') = 'N' THEN nvl(nvl(eval_due, non_scheduled_eval_due), 0) ELSE nvl(nvl(eval_due, non_scheduled_eval_due), 0) * - 1 END ) ELSE 0 END ) END) total_cost ,'N' withhold ,0 withholding_pct ,( SELECT sponsor_name FROM smrs_pcl_sponsor sps ,smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.eval_sponsor AND sps.sponsor = ss.sponsor ) sponsor_name ,'N' indirect_flag ,NULL trigger_type ,NULL reoccurring ,NULL comments ,NULL STATUS ,a.unplanned_visit ,a.evaluation_ ,a.protocol_subject_id FROM --original query for visit variations tab of financials console ( SELECT row_number() OVER ( ORDER BY sv.visit_date ) row_num ,sp.protocol_id protocol_id ,upper(SUBSTR(ss.subject_first_name, 1, 1) || SUBSTR(ss.subject_middle_name, 1, 1) || SUBSTR(ss.subject_last_name, 1, 1)) initials ,sv.sd_pcs_tracking_id ,NVL(sv.missed_flag, 'N') missed_visit ,NVL(sv.na_flag, 'N') na_visit ,sv.billed_date ,DECODE(vs.description, 'UnPlanned', 'Y', 'N') unplanned_visit ,( SELECT DISTINCT 1 FROM sv_invoice_reversals WHERE protocol_id = sp.protocol_id AND visit_id = sv.sd_pcs_tracking_id ) invoiced_uncertified ,DECODE(ve.additional, 'Y', 1, 0) additional ,ve.sd_pcs_tracking_evaluation_id evaluation_ ,get_evaluation_name(NULL, ve.sd_pcs_tracking_evaluation_id) evaluation2 ,DECODE(ed.visit_eval_id, NULL, NULL, ( SELECT be.budget_event_description FROM onc_pcl_budget_event pbe ,onc_budget_event be WHERE be.budget_event_id = pbe.budget_event_id AND pbe.pcl_budget_event_id = ed.pcl_budget_event_id )) evaluation_detail ,TO_CHAR(ve.evaluation_date, 'MM/DD/YYYY') evaluation_date ,ve.evaluation eval_id ,DECODE(ph.code, 'ONT', sv.arm_no, '') arm_no ,DECODE(ed.visit_eval_id, NULL, ve.soc_flag, DECODE(ve.alternate_event_id, NULL, ed.soc_flag, ve.soc_flag)) soc_flag ,DECODE(ed.visit_eval_id, NULL, ve.missed_flag, ed.missed_flag) missed_flag ,DECODE(ed.visit_eval_id, NULL, ve.not_applicable, ed.not_applicable) not_applicable ,DECODE(vs.description, 'UnPlanned', sv.visit_description, NVL(( SELECT revised_visit_string FROM sd_ss_tx_cycle_visit WHERE sd_ss_tx_cycle_visit_id = sv.sd_ss_tx_cycle_visit_id ), sv.visit_string)) visit_string ,DECODE(ed.visit_eval_id, NULL, ve.missed_count, ed.missed_count) missed_count ,( SELECT 1 FROM dual WHERE EXISTS ( SELECT 1 FROM sv_evaluation_soc_visits so WHERE so.sd_study_spec_eval_id = ve.evaluation AND NVL(ve.additional, 'N') = 'N' AND so.budget_version_no = sv.budget_version_no AND ( so.visit_id IS NULL OR ( NVL(so.arm_no, 0) = NVL(sv.arm_no, 0) AND so.visit_id = sv.sd_ss_tx_cycle_visit_id ) ) AND ( ed.visit_eval_id IS NULL OR so.pcl_budget_event_id = ed.pcl_budget_event_id AND ed.pcl_budget_event_item_id IS NULL ) ) ) is_soc ,( SELECT 1 FROM dual WHERE EXISTS ( SELECT /*+ RULE */ 1 FROM sv_evaluation_pass_thru_visits so WHERE so.sd_study_spec_eval_id = ve.evaluation AND NVL(ve.additional, 'N') = 'N' AND so.budget_version_no = sv.budget_version_no AND ( so.visit_id IS NULL OR ( NVL(so.arm_no, 0) = NVL(sv.arm_no, 0) AND so.visit_id = sv.sd_ss_tx_cycle_visit_id ) ) AND ( ed.visit_eval_id IS NULL OR so.pcl_budget_event_id = ed.pcl_budget_event_id AND ed.pcl_budget_event_item_id IS NULL ) ) ) is_pass_thru ,ve.sd_pcs_tracking_evaluation_id ,ed.pcl_budget_event_id ,get_visit_eval_variation_notes(ve.sd_pcs_tracking_evaluation_id, ed.visit_eval_detail_id) eval_notes ,CASE WHEN sv.missed_flag = 'Y' OR sv.na_flag = 'Y' THEN sv.verified_flag ELSE DECODE(ed.visit_eval_id, NULL, ve.verified_flag, ed.verified_flag) END verified_flag ,CASE WHEN sv.missed_flag = 'Y' OR sv.na_flag = 'Y' THEN sv.invoice_flag ELSE DECODE(ed.visit_eval_id, NULL, ve.invoice_flag, ed.invoice_flag) END invoice_flag ,CASE WHEN ( sv.missed_flag = 'Y' OR sv.na_flag = 'Y' ) AND ( SELECT COUNT(*) FROM sd_pcs_visit_evaluation eval ,sd_pcs_visit_eval_detail det WHERE eval.sd_pcs_tracking_id = sv.sd_pcs_tracking_id AND eval.sd_pcs_tracking_evaluation_id = det.visit_eval_id(+) AND DECODE(det.visit_eval_id, NULL, eval.invoice_flag, det.invoice_flag) = 'Y' ) > 0 THEN 'Y' ELSE 'N' END partial_invoice_flag ,CASE WHEN ( ve.additional = 'Y' AND ve.evaluation IS NULL ) THEN 0 ELSE ( SELECT SUM(NVL(visit_cost, 0)) FROM sv_pcl_evaluation_visit_costs ec WHERE ec.study_spec_id = sv.sd_study_spec_id AND ec.sd_study_spec_eval_id = ve.evaluation AND ec.budget_version_no = sv.budget_version_no AND ec.sd_ss_tx_cycle_visit_id = sv.sd_ss_tx_cycle_visit_id AND NVL(ec.arm_no, 0) = NVL(sv.arm_no, 0) AND NVL(ec.pcl_budget_event_id, 0) = NVL(DECODE(ve.alternate_event_id, ed.pcl_budget_event_id, NULL, ed.pcl_budget_event_id), 0) ) END eval_cost ,get_subj_visit_eval_pcl_cost(ve.sd_pcs_tracking_evaluation_id, ed.pcl_budget_event_id) non_scheduled_eval_cost ,get_subj_visit_eval_pcl_due(sp.protocol_id, ve.sd_pcs_tracking_evaluation_id, ed.pcl_budget_event_id) non_scheduled_eval_due ,CASE WHEN ( ve.additional = 'Y' AND ve.evaluation IS NULL ) THEN 0 ELSE ( SELECT SUM(NVL(ec.visit_cost, 0) * DECODE(ec.indirect_flag, 'Y', par.net_rate_with_indirect, par.net_rate_without_indirect)) FROM sv_pcl_evaluation_visit_costs ec WHERE ec.study_spec_id = sv.sd_study_spec_id AND ec.sd_study_spec_eval_id = ve.evaluation AND ec.budget_version_no = sv.budget_version_no AND ec.sd_ss_tx_cycle_visit_id = sv.sd_ss_tx_cycle_visit_id AND NVL(ec.arm_no, 0) = NVL(sv.arm_no, 0) AND NVL(ec.pcl_budget_event_id, 0) = NVL(DECODE(ve.alternate_event_id, ed.pcl_budget_event_id, NULL, ed.pcl_budget_event_id), 0) ) END eval_due ,NVL(( SELECT MIN(pk_count) FROM sv_ss_eval_visits WHERE sd_study_spec_eval_id = ve.evaluation AND NVL(ve.additional, 'N') = 'N' AND sd_ss_tx_cycle_visit_id = sv.sd_ss_tx_cycle_visit_id AND study_spec_id = sv.sd_study_spec_id ), 1) eval_count_sum ,( SELECT sponsor FROM onc_pcl_evaluation WHERE sd_study_spec_eval_id = ve.evaluation AND budget_version_no = sv.budget_version_no ) eval_sponsor ,( SELECT arm_code FROM smrs_pcl_step_arm a WHERE protocol_id = sp.protocol_id AND arm_no = sv.arm_no ) arm_code ,ps.sequence_number ,TO_CHAR(sv.visit_date, 'MM/DD/YYYY') visit_date ,ps.protocol_subject_id ,ph.description phase FROM smrs_protocol sp ,smrs_subject ss ,smrs_pcl_cent_subject ps ,sd_pcs_tracking sv ,sd_pcs_visit_evaluation ve ,pf_code ph ,pf_code vs ,( SELECT * FROM sd_pcs_visit_eval_detail WHERE pcl_budget_event_id IS NOT NULL AND pcl_budget_event_item_id IS NULL ) ed ,( SELECT protocol_id ,net_rate_with_indirect ,net_rate_without_indirect FROM sv_fin_parameters ) par WHERE sp.protocol_id = par.protocol_id AND ps.protocol_id = sp.protocol_id AND ps.subject_no = ss.subject_no AND sv.protocol_subject_id = ps.protocol_subject_id AND ve.sd_pcs_tracking_id = sv.sd_pcs_tracking_id AND ve.sd_pcs_tracking_evaluation_id = ed.visit_eval_id(+) AND ph.code_id = sv.phase AND sv.visit_status = vs.code_id AND vs.category = 'VISIT_STATUS' AND vs.description IN ( 'Acknowledged' ,'UnPlanned' ) AND ( ( DECODE(ed.visit_eval_id, NULL, ve.soc_flag, DECODE(ve.alternate_event_id, NULL, ed.soc_flag, ve.soc_flag)) = 'N' AND NVL(ve.additional, 'N') = 'N' AND EXISTS ( SELECT 1 FROM sv_evaluation_soc_visits so WHERE so.sd_study_spec_eval_id = ve.evaluation AND so.budget_version_no = sv.budget_version_no AND ( ed.visit_eval_id IS NULL OR so.pcl_budget_event_id = ed.pcl_budget_event_id ) AND ( so.visit_id IS NULL OR ( NVL(so.arm_no, 0) = NVL(sv.arm_no, 0) AND so.visit_id = sv.sd_ss_tx_cycle_visit_id ) ) ) ) OR ( DECODE(ed.visit_eval_id, NULL, ve.soc_flag, DECODE(ve.alternate_event_id, NULL, ed.soc_flag, ve.soc_flag)) = 'Y' AND NVL(ve.additional, 'N') = 'N' AND NOT EXISTS ( SELECT 1 FROM sv_evaluation_soc_visits so WHERE so.sd_study_spec_eval_id = ve.evaluation AND so.budget_version_no = sv.budget_version_no AND ( ed.visit_eval_id IS NULL OR so.pcl_budget_event_id = ed.pcl_budget_event_id ) AND ( so.visit_id IS NULL OR ( NVL(so.arm_no, 0) = NVL(sv.arm_no, 0) AND so.visit_id = sv.sd_ss_tx_cycle_visit_id ) ) ) ) OR ( ed.visit_eval_id IS NULL AND ( NVL(ve.missed_flag, 'N') = 'Y' OR NVL(ve.not_applicable, 'N') = 'Y' OR NVL(ve.missed_count, 0) > 0 ) ) OR ( ed.visit_eval_id IS NOT NULL AND ( NVL(ed.missed_flag, 'N') = 'Y' OR NVL(ed.not_applicable, 'N') = 'Y' OR NVL(ed.missed_count, 0) > 0 ) ) OR NVL(ve.additional, 'N') = 'Y' OR NVL(sv.missed_flag, 'N') = 'Y' OR NVL(sv.na_flag, 'N') = 'Y' OR vs.description = 'UnPlanned' OR sv.billed_date IS NULL ) --exclusions for items that can be invoiced AND nvl(decode(ed.visit_eval_id, NULL, ve.invoice_flag, ed.invoice_flag), 'N') = 'N' AND nvl(sv.invoice_flag, 'N') = 'N' AND ( nvl(ve.additional, 'N') = 'N' OR ( ve.additional = 'Y' AND nvl(ve.soc_flag, 'N') = 'N' AND nvl(ve.missed_flag, 'N') = 'N' ) ) AND sv.billed_date IS NOT NULL AND (ve.additional = 'Y') ORDER BY initials ,sv.visit_date ,sv.sd_pcs_tracking_id ,evaluation ,ve.sd_pcs_tracking_evaluation_id ,evaluation_detail ) a GROUP BY a.protocol_id ,a.initials ,a.visit_date ,a.sd_pcs_tracking_id , --only group rows that are missed, na, or not billed yet, otherwise use the row_num as a unique identifier to split out all other rows ( CASE WHEN ( nvl(missed_visit, 'N') != 'N' OR nvl(na_visit, 'N') != 'N' OR billed_date IS NULL ) THEN - 1 ELSE row_num END ) ,additional ,unplanned_visit ,invoice_flag ,missed_visit ,na_visit ,a.sequence_number ,a.eval_sponsor ,( CASE WHEN arm_code IS NOT NULL THEN 'Arm ' || arm_code || ': ' || visit_string ELSE visit_string END ) ,a.evaluation_ ,a.protocol_subject_id ) st1 LEFT OUTER JOIN sv_fin_visit_variations vv ON st1.protocol_id = vv.protocol_id AND st1.protocol_subject_id = vv.protocol_subject_id AND st1.evaluation_ = vv.sd_pcs_tracking_evaluation_id ) event_data left outer join jrv_inexclude i on event_data.exclusion = i.description left outer join jrv_visitstat v on event_data.visit_status = v.description left outer join jrv_yesno y on event_data.unplanned_visit = y.description --protocol info LEFT JOIN ( SELECT pcl.protocol_id ,pcl.protocol_no ,pcl_dep.departments ,pcl_mg.management_groups ,pcl_og.oncology_groups ,pcl_primary_sponsor.primary_sponsor ,pcl_secondary_sponsors.secondary_sponsors ,pcl_budget_parameters.indirect_cumulative ,pcl_budget_parameters.protocol_indirect ,pcl_budget_parameters.subject_indirect ,pcl_budget_parameters.milestone_indirect ,(1 + pcl_budget_parameters.overhead_cost_rate / 100) not_indirect_multiplier ,( CASE WHEN pcl_budget_parameters.indirect_cumulative != 'Y' THEN (1 + (pcl_budget_parameters.overhead_cost_rate + pcl_budget_parameters.indirect_cost_rate) / 100) ELSE ((1 + pcl_budget_parameters.overhead_cost_rate / 100) * (1 + pcl_budget_parameters.indirect_cost_rate / 100)) END ) indirect_multiplier FROM smrs_protocol pcl --get the protocol departments LEFT JOIN ( SELECT protocol_id ,department_name departments FROM sv_pcl_details order by protocol_id ) pcl_dep ON pcl.protocol_id = pcl_dep.protocol_id --get the management groups LEFT JOIN ( SELECT protocol_id ,LISTAGG(( CASE WHEN primary_flag = 'Y' THEN mg.name || ' (P)' ELSE mg.name END ), '; ') within GROUP ( ORDER BY primary_flag DESC ,name ) management_groups FROM onc_pcl_management_group pmg ,onc_org_unit_management_group rmg ,onc_management_group mg WHERE pmg.RESEARCH_MANAGEMENT_GROUP_ID = rmg.onc_ou_mgmt_group_id AND rmg.onc_management_group_id = mg.onc_management_group_id GROUP BY protocol_id ) pcl_mg ON pcl.protocol_id = pcl_mg.protocol_id --get the oncology groups LEFT JOIN ( SELECT protocol_id ,LISTAGG(( CASE WHEN pd.value = 'Y' THEN description || ' (P)' ELSE description END ), '; ') within GROUP ( ORDER BY pd.value DESC ,description ) oncology_groups FROM smrs_pcl_detail pd ,pf_code sc WHERE pd.detail = sc.code_id AND sc.category = 'DOWG' GROUP BY protocol_id ) pcl_og ON pcl.protocol_id = pcl_og.protocol_id LEFT JOIN ( SELECT sps.protocol_id ,ss.sponsor_name primary_sponsor FROM smrs_pcl_sponsor sps ,smrs_sponsor ss WHERE sps.sponsor = ss.sponsor AND principal_sponsor = 'Y' ) pcl_primary_sponsor ON pcl_primary_sponsor.protocol_id = pcl.protocol_id LEFT JOIN ( SELECT sps.protocol_id ,LISTAGG(ss.sponsor_name, '; ') within GROUP ( ORDER BY sponsor_name ) secondary_sponsors FROM smrs_pcl_sponsor sps ,smrs_sponsor ss WHERE sps.sponsor = ss.sponsor AND principal_sponsor = 'N' GROUP BY sps.protocol_id ) pcl_secondary_sponsors ON pcl_secondary_sponsors.protocol_id = pcl.protocol_id LEFT JOIN ( SELECT pr.protocol_id ,( SELECT description FROM pf_code WHERE code_id = bp.rate_base ) rate_base ,bp.rate_base rate_base_id ,NVL(bp.overhead_cost_pct, 0) overhead_cost_rate ,NVL(bp.indirect_cumulative, 'N') indirect_cumulative ,NVL(bp.indirect_cost_pct, 0) indirect_cost_rate ,bp.protocol_indirect ,bp.subject_indirect ,bp.milestone_indirect ,NVL(bp.budget_only_indirect, 'N') budget_only_indirect FROM smrs_protocol pr ,onc_pcl_budget_params bp WHERE pr.protocol_id = bp.protocol_id ) pcl_budget_parameters ON pcl_budget_parameters.protocol_id = pcl.protocol_id ) pcl_info ON event_data.protocol_id = pcl_info.protocol_id --protocol privileges LEFT JOIN ( SELECT protocol_id ,( CASE WHEN EXISTS ( SELECT 1 FROM sv_user_pcl_permission WHERE function_name = 'INVOICEABLE_ITEMS' AND protocol_id = pcl.protocol_id AND contact_id = $P{userContactId} ) THEN 'Y' ELSE 'N' END ) has_invoiceable_items ,( CASE WHEN EXISTS ( SELECT 1 FROM sv_user_pcl_permission WHERE function_name = 'VISIT_VARIATIONS' AND protocol_id = pcl.protocol_id AND contact_id = $P{userContactId} ) THEN 'Y' ELSE 'N' END ) has_visit_variations ,( CASE WHEN EXISTS ( SELECT 1 FROM sv_user_pcl_permission WHERE function_name IN ( 'SUBJECT-IDENTIFICATION' ,'SUBJECT-INITIALS-IDENTIFICATION' ) AND protocol_id = pcl.protocol_id AND contact_id = $P{userContactId} ) THEN 'Y' ELSE 'N' END ) has_subject_identification FROM smrs_protocol pcl ) pcl_privileges ON event_data.protocol_id = pcl_privileges.protocol_id --search parameters WHERE ( occurred_date IS NULL OR TO_DATE(occurred_date, 'MM/DD/YYYY') BETWEEN $P{fromDate} AND $P{thruDate} ) AND ( EXISTS ( SELECT 1 FROM sv_pcl_mgmt_mgmtgroup WHERE protocol_id = sv_pcl_mgmt_mgmtgroup.protocol_id AND $X{IN ,mgmt_group_description ,mgmtGroup} ) OR ( $X{IN ,'ONLY_TRUE_IF_NO_MGNT_GROUPS_SPECIFIED' ,mgmtGroup} AND NOT EXISTS ( SELECT 1 FROM sv_pcl_mgmt_mgmtgroup WHERE protocol_id = sv_pcl_mgmt_mgmtgroup.protocol_id ) ) ) AND ( pcl_info.oncology_groups like '%$P!{oncologyGroup}%' ) AND ( $X{IN ,sv_pcl_details.department_name ,department} ) AND ( $X{IN ,smrs_protocol.protocol_no ,protocol} ) AND ( Type = 'Protocol Related' AND ($P{ProtocolInclude} is null or $P{ProtocolInclude} = event_data.exclusion ) ) AND ( Type = 'Protocol Related' AND ( $X{IN ,sv_pcl_status.STATUS ,ProtocolStatus} ) ) AND ( Type = 'Visit Variation' AND ($P{Unplanned} is null or $P{Unplanned} = event_data.unplanned_visit ) ) AND ( Type = 'Visit Variation' AND ( $X{IN ,sv_fin_visit_variations.additional ,Additional} ) ) AND ( Type = 'Visit Variation' AND ($P{VisitStatus} is null or $P{VisitStatus} = event_data.visit_status ) )]]> </queryString> <field name="TYPE" class="java.lang.String"/> <field name="PROTOCOL_ID" class="java.math.BigDecimal"/> <field name="ITEM" class="java.lang.String"/> <field name="MILESTONE" class="java.lang.String"/> <field name="SEQUENCE_NUMBER" class="java.lang.String"/> <field name="INITIALS" class="java.lang.String"/> <field name="VISIT_STATUS" class="java.lang.String"/> <field name="OCCURRED_DATE" class="java.lang.String"/> <field name="DIRECT" class="java.math.BigDecimal"/> <field name="TOTAL_COST" class="java.math.BigDecimal"/> <field name="WITHHOLD" class="java.lang.String"/> <field name="WITHHOLDING_PCT" class="java.math.BigDecimal"/> <field name="SPONSOR_NAME" class="java.lang.String"/> <field name="INDIRECT_FLAG" class="java.lang.String"/> <field name="TRIGGER_TYPE" class="java.lang.String"/> <field name="REOCCURRING" class="java.lang.String"/> <field name="EXCLUSION" class="java.lang.String"/> <field name="COMMENTS" class="java.lang.String"/> <field name="STATUS" class="java.lang.String"/> <field name="UNPLANNED_VISIT" class="java.lang.String"/> <field name="ADDITIONAL" class="java.lang.String"/> <field name="EVAL_NOTES" class="java.lang.String"/> <field name="PROTOCOL_NO" class="java.lang.String"/> <field name="DEPARTMENTS" class="java.lang.String"/> <field name="MANAGEMENT_GROUPS" class="java.lang.String"/> <field name="ONCOLOGY_GROUPS" class="java.lang.String"/> <field name="PRIMARY_SPONSOR" class="java.lang.String"/> <field name="SECONDARY_SPONSORS" class="java.lang.String"/> <field name="INDIRECT_CUMULATIVE" class="java.lang.String"/> <field name="PROTOCOL_INDIRECT" class="java.lang.String"/> <field name="SUBJECT_INDIRECT" class="java.lang.String"/> <field name="MILESTONE_INDIRECT" class="java.lang.String"/> <field name="NOT_INDIRECT_MULTIPLIER" class="java.math.BigDecimal"/> <field name="INDIRECT_MULTIPLIER" class="java.math.BigDecimal"/> <field name="HAS_INVOICEABLE_ITEMS" class="java.lang.String"/> <field name="HAS_VISIT_VARIATIONS" class="java.lang.String"/> <field name="HAS_SUBJECT_IDENTIFICATION" class="java.lang.String"/> <field name="MORE_INFO" class="java.lang.String"/> <variable name="total_due" class="java.lang.String"> <variableExpression><![CDATA[new DecimalFormat("¤#,##0.00").format( $F{TOTAL_COST} == null ? ("Y".equals($F{INDIRECT_FLAG}) ? $F{DIRECT}.multiply($F{INDIRECT_MULTIPLIER}) : $F{DIRECT}) : $F{TOTAL_COST} )]]></variableExpression> </variable> <variable name="initials_filtered" class="java.lang.String"> <variableExpression><![CDATA[$F{INITIALS} == null ? "" : ("Y".equals($F{HAS_SUBJECT_IDENTIFICATION}) ? $F{INITIALS} : "**")]]></variableExpression> </variable> <variable name="More_Info" class="java.lang.String"> <variableExpression><![CDATA[$F{COMMENTS}+$F{EVAL_NOTES}]]></variableExpression> </variable> <group name="Group1"/> <group name="Group2"/> <group name="Group3"/> <group name="Group4"/> <title> <band height="102"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <frame> <reportElement mode="Opaque" x="0" y="0" width="1434" height="72" backcolor="#006699" uuid="2f9e325f-5726-45f7-980d-45df3aefc4ea"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <staticText> <reportElement x="0" y="0" width="946" height="72" forecolor="#FFFFFF" uuid="c8271c97-a3f6-4dc1-83e0-f40231bdab3f"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textElement verticalAlignment="Middle"> <font size="34" isBold="true"/> </textElement> <text><![CDATA[invoiceable Items]]></text> </staticText> <staticText> <reportElement x="1065" y="0" width="365" height="72" forecolor="#FFFFFF" uuid="c89de18d-0f2f-4fe9-a2d2-6cbee0c1c1e7"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Right" verticalAlignment="Middle"> <font size="14" isBold="false"/> </textElement> <text><![CDATA[All protocols, by item type]]></text> </staticText> </frame> <frame> <reportElement stretchType="RelativeToTallestObject" mode="Opaque" x="0" y="72" width="1434" height="30" backcolor="#006699" uuid="a9a52697-082e-42e1-89ec-68dd0b049098"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <staticText> <reportElement stretchType="RelativeToTallestObject" x="0" y="0" width="88" height="30" forecolor="#FFFFFF" uuid="3afa32d5-2f7d-4eb8-a131-28fdda4bc83b"/> <textElement textAlignment="Right"/> <text><![CDATA[start Date:]]></text> </staticText> <staticText> <reportElement stretchType="RelativeToTallestObject" x="167" y="0" width="106" height="30" forecolor="#FFFFFF" uuid="2d71ca4b-6729-46eb-a0a4-ab7decdab5c6"/> <textElement textAlignment="Right"/> <text><![CDATA[End Date:]]></text> </staticText> <staticText> <reportElement stretchType="RelativeToTallestObject" x="345" y="0" width="79" height="30" forecolor="#FFFFFF" uuid="88137e82-ed30-486c-90f6-8cc42aa0aae8"/> <textElement textAlignment="Right"/> <text><![CDATA[Management Group:]]></text> </staticText> <staticText> <reportElement stretchType="RelativeToTallestObject" x="489" y="0" width="85" height="30" forecolor="#FFFFFF" uuid="93aec992-5a53-422e-9d64-77415d1aca57"/> <textElement textAlignment="Right"/> <text><![CDATA[Oncology Group:]]></text> </staticText> <staticText> <reportElement stretchType="RelativeToTallestObject" x="658" y="0" width="77" height="30" forecolor="#FFFFFF" uuid="dbbde861-1ed7-4b0b-b0da-a0414790be83"/> <textElement textAlignment="Right"/> <text><![CDATA[Department:]]></text> </staticText> <textField pattern="MM/dd/yyyy"> <reportElement stretchType="RelativeToTallestObject" x="88" y="0" width="79" height="30" forecolor="#FFFFFF" uuid="55296cab-b55c-4bfa-95de-7c268febe10e"/> <textFieldExpression><![CDATA[$P{fromDate}]]></textFieldExpression> </textField> <textField pattern="MM/dd/yyyy"> <reportElement stretchType="RelativeToTallestObject" x="273" y="0" width="72" height="30" forecolor="#FFFFFF" uuid="c091402d-3826-4336-a1f3-95cdc021eaaa"/> <textFieldExpression><![CDATA[$P{thruDate}]]></textFieldExpression> </textField> <textField isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" x="424" y="0" width="65" height="30" forecolor="#FFFFFF" uuid="d52ae49d-59a9-48b8-a32e-5e266c957752"/> <textFieldExpression><![CDATA[$P{mgmtGroup}]]></textFieldExpression> </textField> <textField isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" x="574" y="0" width="84" height="30" forecolor="#FFFFFF" uuid="a35aeb70-4c24-40fa-94e0-b0080aa8b983"/> <textFieldExpression><![CDATA[$P{oncologyGroup}]]></textFieldExpression> </textField> <textField isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" x="736" y="0" width="100" height="30" forecolor="#FFFFFF" uuid="d735e804-c8ec-4e05-b8d8-e98f1ab80fb3"/> <textFieldExpression><![CDATA[$P{department}]]></textFieldExpression> </textField> </frame> </band> </title> <columnHeader> <band height="22" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement mode="Opaque" x="0" y="0" width="89" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="b1dcfa37-fe41-4dd3-ba8c-1132cca71ed2"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Type]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="89" y="0" width="78" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="1928f95a-28f0-4497-995c-e008308bff8c"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Mgmt. Groups]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="167" y="0" width="106" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="9e8369e1-45fe-4a67-845f-0a4fe441169e"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Working Groups]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="273" y="0" width="72" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="fa7b5671-3185-4328-ba28-fa3f57d38c95"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Departments]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="345" y="0" width="83" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="8448a272-89ca-4966-8527-f2c38d4d6fa0"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Protocol No.]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="936" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="84e996e6-c9a7-427e-85da-56127c21d313"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Milestone]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="680" y="0" width="78" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5c8d0443-f637-4202-9259-38a4122fc2b7"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[item]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1036" y="0" width="59" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="7939812e-20b2-4f33-ba4a-8ea25a774a7b"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[seq. No.]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1095" y="0" width="51" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5031c035-52c7-43ab-91f3-68148a2b3ba5"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[initials]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1146" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="f340c609-345e-4ae2-840c-96de64b1a3e2"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Occurred Date]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1246" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5fa29d59-5626-41fa-a7d6-e28fb34032ff"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Negotiated Cost]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1346" y="0" width="88" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="a8adf438-22fa-4ba1-956c-8e9df0c11458"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Total Due]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="428" y="0" width="83" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5e7fd45d-792f-499e-83b9-e2075c938224"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Principal Sponsor]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="511" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="4a6ebe9b-a072-4f48-bf3b-a8347dc047b9"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Other Sponsors]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="597" y="0" width="83" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="c591bc04-dc74-41d8-94d7-e2aab81e21fc"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[item Sponsor]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="858" y="0" width="78" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="6822b1bc-3d29-4e50-a96a-0be70ea18855"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Visit Status]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="758" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="2c3fed66-2675-4f75-96f9-d67194495e1d"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Comments/Eval Notes]]></text> </staticText> </band> </columnHeader> <detail> <band height="27" splitType="Prevent"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" x="0" y="0" width="89" height="27" uuid="3f984aa3-f917-4330-85e8-5a07cc66650a"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{TYPE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="89" y="0" width="78" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="da3929a7-d752-4ba9-830c-fe93ef1cdc22"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{MANAGEMENT_GROUPS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="167" y="0" width="106" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="1257da62-9027-4101-b32c-504e606b701a"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{ONCOLOGY_GROUPS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="273" y="0" width="72" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="dea22672-42b8-4c4e-bca6-d0eebfcd4181"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{DEPARTMENTS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="345" y="0" width="83" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="e5b65c06-b8c8-47fb-b1fa-a1052e4a2111"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{PROTOCOL_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="680" y="0" width="78" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="613bbd4e-6774-4cce-ad88-7e7fa8b3077d"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{ITEM}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1036" y="0" width="59" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="e1767861-73f9-47ca-ab3f-f59b839220d5"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{SEQUENCE_NUMBER}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="936" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="17078554-b751-42c3-9b9d-7cf920281e08"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{MILESTONE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1146" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="7019a529-e225-4ebd-9182-a1fde914daf1"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{OCCURRED_DATE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1246" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="54f419e4-ad0d-484c-9902-eb4f5a76cd30"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[new DecimalFormat("¤#,##0.00").format($F{DIRECT})]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1346" y="0" width="88" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="8b8c19d6-ea68-4c05-8f6e-61ae94369082"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$V{total_due}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="428" y="0" width="83" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="9a905988-f100-45d0-9b3b-0fac589372a3"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <textFieldExpression><![CDATA[$F{PRIMARY_SPONSOR}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="511" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="ccf720d2-779b-4bc4-b1b4-3fa253629c5d"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <textFieldExpression><![CDATA[$F{SECONDARY_SPONSORS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="597" y="0" width="83" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="14dcbcda-6498-412f-b21e-a3fcb5018502"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{SPONSOR_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1095" y="0" width="51" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="e3d0affb-afff-4270-8bc0-3172f3b15fb5"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <textFieldExpression><![CDATA[$V{initials_filtered}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="858" y="0" width="78" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="a7830fc3-d9b8-4790-85d8-7810a9591f0d"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{VISIT_STATUS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="758" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="c9073fec-8b08-4620-bf9d-82060c93f0e4"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{MORE_INFO}]]></textFieldExpression> </textField> </band> </detail> <pageFooter> <band height="25" splitType="Prevent"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement mode="Opaque" x="0" y="0" width="89" height="25" backcolor="#E6E6E6" uuid="00b71e79-08b5-44a3-af35-e114fc18433b"> <property name="local_mesure_unity" value="pixel"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textElement textAlignment="Right" verticalAlignment="Middle"/> <text><![CDATA[Report Date:]]></text> </staticText> <textField pattern="MM/dd/yyyy"> <reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="89" y="0" width="1155" height="25" backcolor="#E6E6E6" uuid="4306bff6-d9b7-498f-8124-8c583de632a9"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Left" verticalAlignment="Middle"> <paragraph firstLineIndent="20"/> </textElement> <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression> </textField> <textField isBlankWhenNull="false"> <reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="1244" y="0" width="100" height="25" forecolor="#000000" backcolor="#E6E6E6" uuid="16866726-e0e9-4b0b-86b7-17528b27e5e7"> <property name="com.jaspersoft.studio.unit.x" value="px"/> </reportElement> <textElement textAlignment="Right" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of "]]></textFieldExpression> </textField> <textField evaluationTime="Report" isBlankWhenNull="false"> <reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="1344" y="0" width="90" height="25" forecolor="#000000" backcolor="#E6E6E6" uuid="43c13917-328f-49cc-84e0-fd0ca42186ba"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box leftPadding="2"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression> </textField> </band> </pageFooter> <noData> <band height="20"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement x="0" y="0" width="1434" height="20" uuid="29dde353-6dcd-4817-a15e-599cff469095"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <text><![CDATA[No invoiceable items found for the parameters entered and user's access.]]></text> </staticText> </band> </noData> </jasperReport>

I have the following expression in the setup of a parameter: <defaultValueExpression><![CDATA["Visit".equals($P{TypeDate}) ? "vd.actual_visit_date" : "vd.visit_created_date"]]></defaultValueExpression> This works perfectly. But I've noticed issues with users not entering the parameter value exactly as displayed (i.e., entering "visit" instead of "Visit"), and so I'd like to add a command that will allow them to enter any upper/lower combination of letters that make up the word Visit. I tried the following: <defaultValueExpression><![CDATA["visit".equals(toLowerCase($P{TypeDate})) ? "vd.actual_visit_date" : "vd.visit_created_date"]]></defaultValueExpression> But I'm getting an error that "The method toLowerCase(String) is undefined for the type..." Suggestions?

I think I finally got it! Thank you for your help, C-Box!

I'm sure I'm the one misunderstanding. When I take out the variable, I'm still getting the following error: net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: 4CRU_ProcBill. at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:550) at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$18(ReportController.java:525) at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:443) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: 4CRU_ProcBill. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:357) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1257) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:726) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:457) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:578) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135) at java.lang.Thread.run(Thread.java:748) Caused by: java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:312) ... 6 more For this code: <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 6.9.0.final using JasperReports Library version 6.9.0-cb8f9004be492ccc537180b49c026951f4220bf3 --> <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="4CRU_ProcBill" pageWidth="1349" pageHeight="802" columnWidth="1349" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isSummaryWithPageHeaderAndFooter="true" isIgnorePagination="true" uuid="f1a362a1-9fdc-49a5-8b3a-e7554a779578"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="OnCore Test2"/> <parameter name="TypeDate" class="java.lang.String"/> <parameter name="Choose" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["Visit".equals($P{TypeDate}) ? "SV_SUB_CALENDAR.VISIT_DATE Between $P{StartDate} and $P{EndDate}" : "SV_FIN_INVOICE_OCCURRED_DATE Between $P{StartDate} and $P{EndDate}"]]></defaultValueExpression> </parameter> <parameter name="StartDate" class="java.util.Date"/> <parameter name="EndDate" class="java.util.Date"/> <queryString> <![CDATA[sELECT DISTINCT SV_SUB_ONSTUDY.PROTOCOL_NO AS SV_SUB_ONSTUDY_PROTOCOL_NO, SV_SUB_CALENDAR.VISIT_DATE AS SV_SUB_CALENDAR_VISIT_DATE, SV_FIN_INVOICE_ITEMS.OCCURRED_DATE AS SV_FIN_INVOICE_OCCURRED_DATE, SV_PCL_MGMT_STAFF.CONTACT_ID AS SV_PCL_MGMT_STAFF_CONTACT_ID, SV_PCL_MGMT_STAFF.LAST_NAME AS SV_PCL_MGMT_STAFF_LAST_NAME, SV_PCL_MGMT_STAFF.FIRST_NAME AS SV_PCL_MGMT_STAFF_FIRST_NAME, SV_SUB_ONSTUDY.SEQUENCE_NUMBER AS SV_SUB_ONSTUDY_SEQUENCE_NUMBER, SV_FIN_SUB_EVENTS.EVENT_CODE AS EVENT_CODE, SV_SS_EVAL_SCHED.PK_NUMBER AS SV_SS_EVAL_SCHED_PK_NUMBER, SV_SUB_CALENDAR.VISIT_NAME AS SV_SUB_CALENDAR_VISIT_NAME, SV_PCL_MGMT_DTL.GCRC_NO AS SV_PCL_MGMT_DTL_GCRC_NO, SV_SUB_CALENDAR.SD_PCS_TRACKING_ID AS SV_SUB_CALENDAR_SD_PCS_TRACKIN, SV_FIN_SUB_EVENT_COST_CENTERS.COST_CENTER_CODE AS SV_FIN_SUB_EVENT_COST_CENTERS_, SV_FIN_SUB_EVENTS.AS_NEEDED_PROCEDURE AS AS_NEEDED_PROCEDURE, SV_PCS_VISIT_EVALUATIONS.MISSED_FLAG AS SV_PCS_VISIT_MISSED_FLAG, SV_PCS_VISIT_EVALUATIONS.MISSED_COUNT AS SV_PCS_VISIT_MISSEDCOUNT, SV_PCS_VISIT_EVALUATIONS.NOT_APPLICABLE AS SV_PCS_VISIT_NA_FLAG FROM SV_SUB_CALENDAR INNER JOIN SV_SUB_ONSTUDY ON SV_SUB_CALENDAR.PROTOCOL_SUBJECT_ID = SV_SUB_ONSTUDY.PROTOCOL_SUBJECT_ID INNER JOIN SV_PCL_MGMT_DTL ON SV_SUB_ONSTUDY.PROTOCOL_ID = SV_PCL_MGMT_DTL.PROTOCOL_ID INNER JOIN SV_PCL_MGMT_STAFF ON SV_SUB_ONSTUDY.PROTOCOL_NO = SV_PCL_MGMT_STAFF.PROTOCOL_NO INNER JOIN SV_PCS_VISIT_EVALUATIONS ON SV_SUB_CALENDAR.SD_PCS_TRACKING_ID = SV_PCS_VISIT_EVALUATIONS.SD_PCS_TRACKING_ID LEFT OUTER JOIN SV_FIN_SUB_EVENTS ON SV_PCS_VISIT_EVALUATIONS.PCL_BUDGET_EVENT_ID = SV_FIN_SUB_EVENTS.PCL_BUDGET_EVENT_ID LEFT OUTER JOIN SV_FIN_SUB_EVENT_COST_CENTERS ON SV_PCS_VISIT_EVALUATIONS.PCL_BUDGET_EVENT_ID = SV_FIN_SUB_EVENT_COST_CENTERS.PCL_BUDGET_EVENT_ID INNER JOIN SV_SS_EVAL_SCHED ON SV_PCS_VISIT_EVALUATIONS.SD_STUDY_SPEC_EVAL_ID = SV_SS_EVAL_SCHED.SD_STUDY_SPEC_EVAL_ID INNER JOIN SD_SS_TX_CYCLE_VISIT_SCHED ON SV_PCS_VISIT_EVALUATIONS.SD_SS_TX_CYCLE_VISIT_ID = SD_SS_TX_CYCLE_VISIT_SCHED.SD_SS_TX_CYCLE_VISIT_ID AND SD_SS_TX_CYCLE_VISIT_SCHED.SD_SS_TX_SCHED_ID = SV_SS_EVAL_SCHED.STUDY_SPEC_SCHEDULE_ID LEFT OUTER JOIN SV_FIN_INVOICE_ITEMS ON SV_PCS_VISIT_EVALUATIONS.PROTOCOL_SUBJECT_ID = SV_FIN_INVOICE_ITEMS.PROTOCOL_SUBJECT_ID AND SV_PCS_VISIT_EVALUATIONS.SD_PCS_TRACKING_ID = SV_FIN_INVOICE_ITEMS.SD_PCS_TRACKING_ID WHERE $P!{Choose} Between $P{StartDate} and $P{EndDate} AND SV_PCL_MGMT_DTL.GCRC_APPROVAL = 'Yes' AND SV_PCL_MGMT_STAFF.STAFF_ROLE = 'Principal Investigator' AND SV_PCL_MGMT_STAFF.STOP_DATE IS null AND SV_SUB_CALENDAR.VISIT_STATUS = 'Acknowledged' AND SV_SUB_CALENDAR.MISSED_FLAG = 'N' AND SV_SUB_CALENDAR.NA_FLAG = 'N' AND SV_FIN_SUB_EVENT_COST_CENTERS.COST_CENTER_CODE = '6' ORDER BY SV_SUB_ONSTUDY.PROTOCOL_NO ASC, SV_SUB_CALENDAR.VISIT_DATE ASC, SV_SUB_CALENDAR.SD_PCS_TRACKING_ID ASC]]> </queryString> <field name="SV_SUB_ONSTUDY_PROTOCOL_NO" class="java.lang.String"/> <field name="SV_SUB_CALENDAR_VISIT_DATE" class="java.sql.Timestamp"/> <field name="SV_FIN_INVOICE_OCCURRED_DATE" class="java.sql.Timestamp"/> <field name="SV_PCL_MGMT_STAFF_CONTACT_ID" class="java.math.BigDecimal"/> <field name="SV_PCL_MGMT_STAFF_LAST_NAME" class="java.lang.String"/> <field name="SV_PCL_MGMT_STAFF_FIRST_NAME" class="java.lang.String"/> <field name="SV_SUB_ONSTUDY_SEQUENCE_NUMBER" class="java.lang.String"/> <field name="EVENT_CODE" class="java.lang.String"/> <field name="SV_SS_EVAL_SCHED_PK_NUMBER" class="java.math.BigDecimal"/> <field name="SV_SUB_CALENDAR_VISIT_NAME" class="java.lang.String"/> <field name="SV_PCL_MGMT_DTL_GCRC_NO" class="java.lang.String"/> <field name="SV_SUB_CALENDAR_SD_PCS_TRACKIN" class="java.math.BigDecimal"/> <field name="SV_FIN_SUB_EVENT_COST_CENTERS_" class="java.lang.String"/> <field name="AS_NEEDED_PROCEDURE" class="java.lang.String"/> <field name="SV_PCS_VISIT_MISSED_FLAG" class="java.lang.String"/> <field name="SV_PCS_VISIT_MISSEDCOUNT" class="java.math.BigDecimal"/> <field name="SV_PCS_VISIT_NA_FLAG" class="java.lang.String"/> <background> <band/> </background> <columnHeader> <band height="26"> <staticText> <reportElement mode="Opaque" x="0" y="0" width="89" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="7508dd54-3f29-42a8-ae6b-8a5a713f4f3b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PROTOCOL]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="89" y="0" width="43" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="b749554f-019f-4d04-97df-41873789595f"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PI_ID]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="132" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="d7b75036-4f24-44e0-afa5-faba76d34fe5"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[LAST_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="219" y="0" width="78" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="c6892498-32ff-4f36-b6bd-3ac390880317"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[FIRST_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="297" y="0" width="65" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="0b605991-09f4-4e69-8007-92a899ddbb5e"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[VISIT_DATE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="428" y="0" width="107" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="1f5bc066-46d2-4338-a7bc-3cd7f92acf8b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[sEQUENCE_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="535" y="0" width="94" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="b3740ae2-afe7-4521-b208-707a838081f0"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[EVENT_CODE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="629" y="0" width="46" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="c518a85d-5ff9-4dce-94d2-84b4f09e7764"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PK_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="675" y="0" width="172" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="99f1fb94-5787-4e0f-8a72-69b3fbbe708f"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[VISIT_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="847" y="0" width="63" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="32908272-a042-449c-ba69-ed3442089c7b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[CCTS_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="910" y="0" width="57" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="685667c6-c5d4-4299-a15c-591807e97f77"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[TRACK_ID]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="967" y="0" width="64" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="f850847d-f9f3-4b58-a2d1-0e3bcf512d39"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[CC_CODE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1031" y="0" width="64" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="09e48d12-14fd-4ed0-b422-35841d97b889"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[AN_PROC]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1095" y="0" width="81" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="4db0df45-141b-4503-860c-0209a1b690bb"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[MISSED_FLAG]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1176" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="fd900ad6-6cde-476a-ba3b-a6bf929bbd7d"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[MISSEDCOUNT]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1263" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="f0e00962-ab0a-4d6c-95f9-026f1f4b3706"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[NA_FLAG]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="363" y="0" width="65" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="12d78fc1-7473-4ee9-8e45-eac36cd80095"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[OCCURRED_DATE]]></text> </staticText> </band> </columnHeader> <detail> <band height="25"> <textField isStretchWithOverflow="true"> <reportElement x="0" y="0" width="89" height="20" uuid="e1eac73e-0817-4bbe-95fe-ddb865d413cb"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_ONSTUDY_PROTOCOL_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="89" y="0" width="43" height="20" uuid="cb7d64fa-ef17-4d75-8599-c10cdfa558df"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_CONTACT_ID}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="132" y="0" width="87" height="20" uuid="3a13bf53-dbc7-4d45-b29c-989a72a46bf4"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_LAST_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="219" y="0" width="78" height="20" uuid="1b28cd17-9ad3-4af7-b284-630cede8358c"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_FIRST_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy"> <reportElement x="297" y="0" width="65" height="20" uuid="13a6e1b5-f2f9-4145-8fda-9ca56daf0fc6"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_VISIT_DATE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="428" y="0" width="107" height="20" uuid="1de985a5-cfb1-4cc6-8d14-d19b147c7836"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_ONSTUDY_SEQUENCE_NUMBER}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="535" y="0" width="94" height="20" uuid="4f5d9e7d-ebe0-4f5c-98b0-0f6aa6b09b40"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{EVENT_CODE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="629" y="0" width="46" height="20" uuid="1305d21a-b083-4b26-aac8-946a3eaf0aff"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SS_EVAL_SCHED_PK_NUMBER}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="675" y="0" width="172" height="20" uuid="0cf412bf-4be6-4623-8c5d-2c140a7d7508"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_VISIT_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="847" y="0" width="63" height="20" uuid="e0340399-9749-46e0-bd6e-1477bfb7c3af"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_DTL_GCRC_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="910" y="0" width="57" height="20" uuid="0445b27b-9ed7-4de8-bd3e-9af2bb10d3f9"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_SD_PCS_TRACKIN}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="967" y="0" width="64" height="20" uuid="7ca595ce-b63e-47b2-af06-13091d49fd6d"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_FIN_SUB_EVENT_COST_CENTERS_}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1031" y="0" width="64" height="20" uuid="8d88059c-656a-4ab0-bad2-147b31e301a7"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{AS_NEEDED_PROCEDURE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1095" y="0" width="81" height="20" uuid="791a0d80-11b2-45a0-acc4-59bf40d1fbaf"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_MISSED_FLAG}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1176" y="0" width="87" height="20" uuid="b49d9a0f-c552-4404-aac4-023a46707bbc"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_MISSEDCOUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1263" y="0" width="87" height="20" uuid="ab9fba32-fab3-4098-be25-303117e8e45d"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_NA_FLAG}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy"> <reportElement x="363" y="0" width="65" height="20" uuid="45d696b2-729c-484d-8739-967767e67b05"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_FIN_INVOICE_OCCURRED_DATE}]]></textFieldExpression> </textField> </band> </detail> <summary> <band/> </summary> </jasperReport>

I've continued playing with the code based on the errors I'm getting, and now I don't know what else to do here...the new error I'm getting is: net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: 4CRU_ProcBill. at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:550) at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$18(ReportController.java:525) at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:443) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: 4CRU_ProcBill. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:357) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1257) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:726) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:457) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:578) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135) at java.lang.Thread.run(Thread.java:748) Caused by: java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:312) ... 6 more Here's the code: <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 6.9.0.final using JasperReports Library version 6.9.0-cb8f9004be492ccc537180b49c026951f4220bf3 --> <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="4CRU_ProcBill" pageWidth="1349" pageHeight="802" columnWidth="1349" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isSummaryWithPageHeaderAndFooter="true" isIgnorePagination="true" uuid="f1a362a1-9fdc-49a5-8b3a-e7554a779578"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="OnCore Test2"/> <parameter name="TypeDate" class="java.lang.String"/> <parameter name="Choose" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["Visit".equals($P{TypeDate}) ? "SV_SUB_CALENDAR.VISIT_DATE Between $P{StartDate} and $P{EndDate}" : "SV_FIN_INVOICE_OCCURRED_DATE Between $P{StartDate} and $P{EndDate}"]]></defaultValueExpression> </parameter> <parameter name="StartDate" class="java.util.Date"/> <parameter name="EndDate" class="java.util.Date"/> <queryString> <![CDATA[sELECT DISTINCT SV_SUB_ONSTUDY.PROTOCOL_NO AS SV_SUB_ONSTUDY_PROTOCOL_NO, SV_SUB_CALENDAR.VISIT_DATE AS SV_SUB_CALENDAR_VISIT_DATE, SV_FIN_INVOICE_ITEMS.OCCURRED_DATE AS SV_FIN_INVOICE_OCCURRED_DATE, SV_PCL_MGMT_STAFF.CONTACT_ID AS SV_PCL_MGMT_STAFF_CONTACT_ID, SV_PCL_MGMT_STAFF.LAST_NAME AS SV_PCL_MGMT_STAFF_LAST_NAME, SV_PCL_MGMT_STAFF.FIRST_NAME AS SV_PCL_MGMT_STAFF_FIRST_NAME, SV_SUB_ONSTUDY.SEQUENCE_NUMBER AS SV_SUB_ONSTUDY_SEQUENCE_NUMBER, SV_FIN_SUB_EVENTS.EVENT_CODE AS EVENT_CODE, SV_SS_EVAL_SCHED.PK_NUMBER AS SV_SS_EVAL_SCHED_PK_NUMBER, SV_SUB_CALENDAR.VISIT_NAME AS SV_SUB_CALENDAR_VISIT_NAME, SV_PCL_MGMT_DTL.GCRC_NO AS SV_PCL_MGMT_DTL_GCRC_NO, SV_SUB_CALENDAR.SD_PCS_TRACKING_ID AS SV_SUB_CALENDAR_SD_PCS_TRACKIN, SV_FIN_SUB_EVENT_COST_CENTERS.COST_CENTER_CODE AS SV_FIN_SUB_EVENT_COST_CENTERS_, SV_FIN_SUB_EVENTS.AS_NEEDED_PROCEDURE AS AS_NEEDED_PROCEDURE, SV_PCS_VISIT_EVALUATIONS.MISSED_FLAG AS SV_PCS_VISIT_MISSED_FLAG, SV_PCS_VISIT_EVALUATIONS.MISSED_COUNT AS SV_PCS_VISIT_MISSEDCOUNT, SV_PCS_VISIT_EVALUATIONS.NOT_APPLICABLE AS SV_PCS_VISIT_NA_FLAG FROM SV_SUB_CALENDAR INNER JOIN SV_SUB_ONSTUDY ON SV_SUB_CALENDAR.PROTOCOL_SUBJECT_ID = SV_SUB_ONSTUDY.PROTOCOL_SUBJECT_ID INNER JOIN SV_PCL_MGMT_DTL ON SV_SUB_ONSTUDY.PROTOCOL_ID = SV_PCL_MGMT_DTL.PROTOCOL_ID INNER JOIN SV_PCL_MGMT_STAFF ON SV_SUB_ONSTUDY.PROTOCOL_NO = SV_PCL_MGMT_STAFF.PROTOCOL_NO INNER JOIN SV_PCS_VISIT_EVALUATIONS ON SV_SUB_CALENDAR.SD_PCS_TRACKING_ID = SV_PCS_VISIT_EVALUATIONS.SD_PCS_TRACKING_ID LEFT OUTER JOIN SV_FIN_SUB_EVENTS ON SV_PCS_VISIT_EVALUATIONS.PCL_BUDGET_EVENT_ID = SV_FIN_SUB_EVENTS.PCL_BUDGET_EVENT_ID LEFT OUTER JOIN SV_FIN_SUB_EVENT_COST_CENTERS ON SV_PCS_VISIT_EVALUATIONS.PCL_BUDGET_EVENT_ID = SV_FIN_SUB_EVENT_COST_CENTERS.PCL_BUDGET_EVENT_ID INNER JOIN SV_SS_EVAL_SCHED ON SV_PCS_VISIT_EVALUATIONS.SD_STUDY_SPEC_EVAL_ID = SV_SS_EVAL_SCHED.SD_STUDY_SPEC_EVAL_ID INNER JOIN SD_SS_TX_CYCLE_VISIT_SCHED ON SV_PCS_VISIT_EVALUATIONS.SD_SS_TX_CYCLE_VISIT_ID = SD_SS_TX_CYCLE_VISIT_SCHED.SD_SS_TX_CYCLE_VISIT_ID AND SD_SS_TX_CYCLE_VISIT_SCHED.SD_SS_TX_SCHED_ID = SV_SS_EVAL_SCHED.STUDY_SPEC_SCHEDULE_ID LEFT OUTER JOIN SV_FIN_INVOICE_ITEMS ON SV_PCS_VISIT_EVALUATIONS.PROTOCOL_SUBJECT_ID = SV_FIN_INVOICE_ITEMS.PROTOCOL_SUBJECT_ID AND SV_PCS_VISIT_EVALUATIONS.SD_PCS_TRACKING_ID = SV_FIN_INVOICE_ITEMS.SD_PCS_TRACKING_ID WHERE $P!{Choose} Between $P{StartDate} and $P{EndDate} AND SV_PCL_MGMT_DTL.GCRC_APPROVAL = 'Yes' AND SV_PCL_MGMT_STAFF.STAFF_ROLE = 'Principal Investigator' AND SV_PCL_MGMT_STAFF.STOP_DATE IS null AND SV_SUB_CALENDAR.VISIT_STATUS = 'Acknowledged' AND SV_SUB_CALENDAR.MISSED_FLAG = 'N' AND SV_SUB_CALENDAR.NA_FLAG = 'N' AND SV_FIN_SUB_EVENT_COST_CENTERS.COST_CENTER_CODE = '6' ORDER BY SV_SUB_ONSTUDY.PROTOCOL_NO ASC, SV_SUB_CALENDAR.VISIT_DATE ASC, SV_SUB_CALENDAR.SD_PCS_TRACKING_ID ASC]]> </queryString> <field name="SV_SUB_ONSTUDY_PROTOCOL_NO" class="java.lang.String"/> <field name="SV_SUB_CALENDAR_VISIT_DATE" class="java.sql.Timestamp"/> <field name="SV_FIN_INVOICE_OCCURRED_DATE" class="java.sql.Timestamp"/> <field name="SV_PCL_MGMT_STAFF_CONTACT_ID" class="java.math.BigDecimal"/> <field name="SV_PCL_MGMT_STAFF_LAST_NAME" class="java.lang.String"/> <field name="SV_PCL_MGMT_STAFF_FIRST_NAME" class="java.lang.String"/> <field name="SV_SUB_ONSTUDY_SEQUENCE_NUMBER" class="java.lang.String"/> <field name="EVENT_CODE" class="java.lang.String"/> <field name="SV_SS_EVAL_SCHED_PK_NUMBER" class="java.math.BigDecimal"/> <field name="SV_SUB_CALENDAR_VISIT_NAME" class="java.lang.String"/> <field name="SV_PCL_MGMT_DTL_GCRC_NO" class="java.lang.String"/> <field name="SV_SUB_CALENDAR_SD_PCS_TRACKIN" class="java.math.BigDecimal"/> <field name="SV_FIN_SUB_EVENT_COST_CENTERS_" class="java.lang.String"/> <field name="AS_NEEDED_PROCEDURE" class="java.lang.String"/> <field name="SV_PCS_VISIT_MISSED_FLAG" class="java.lang.String"/> <field name="SV_PCS_VISIT_MISSEDCOUNT" class="java.math.BigDecimal"/> <field name="SV_PCS_VISIT_NA_FLAG" class="java.lang.String"/> <variable name="Date Variable" class="java.lang.String"> <variableExpression><![CDATA[iF($P{TypeDate}=="Visit", $F{SV_SUB_CALENDAR_VISIT_DATE}, $F{SV_FIN_INVOICE_OCCURRED_DATE})]]></variableExpression> </variable> <background> <band/> </background> <columnHeader> <band height="26"> <staticText> <reportElement mode="Opaque" x="0" y="0" width="89" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="7508dd54-3f29-42a8-ae6b-8a5a713f4f3b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PROTOCOL]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="89" y="0" width="43" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="b749554f-019f-4d04-97df-41873789595f"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PI_ID]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="132" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="d7b75036-4f24-44e0-afa5-faba76d34fe5"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[LAST_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="219" y="0" width="78" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="c6892498-32ff-4f36-b6bd-3ac390880317"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[FIRST_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="297" y="0" width="65" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="0b605991-09f4-4e69-8007-92a899ddbb5e"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[VISIT_DATE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="428" y="0" width="107" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="1f5bc066-46d2-4338-a7bc-3cd7f92acf8b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[sEQUENCE_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="535" y="0" width="94" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="b3740ae2-afe7-4521-b208-707a838081f0"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[EVENT_CODE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="629" y="0" width="46" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="c518a85d-5ff9-4dce-94d2-84b4f09e7764"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PK_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="675" y="0" width="172" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="99f1fb94-5787-4e0f-8a72-69b3fbbe708f"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[VISIT_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="847" y="0" width="63" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="32908272-a042-449c-ba69-ed3442089c7b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[CCTS_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="910" y="0" width="57" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="685667c6-c5d4-4299-a15c-591807e97f77"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[TRACK_ID]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="967" y="0" width="64" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="f850847d-f9f3-4b58-a2d1-0e3bcf512d39"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[CC_CODE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1031" y="0" width="64" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="09e48d12-14fd-4ed0-b422-35841d97b889"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[AN_PROC]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1095" y="0" width="81" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="4db0df45-141b-4503-860c-0209a1b690bb"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[MISSED_FLAG]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1176" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="fd900ad6-6cde-476a-ba3b-a6bf929bbd7d"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[MISSEDCOUNT]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1263" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="f0e00962-ab0a-4d6c-95f9-026f1f4b3706"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[NA_FLAG]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="363" y="0" width="65" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="12d78fc1-7473-4ee9-8e45-eac36cd80095"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[OCCURRED_DATE]]></text> </staticText> </band> </columnHeader> <detail> <band height="25"> <textField isStretchWithOverflow="true"> <reportElement x="0" y="0" width="89" height="20" uuid="e1eac73e-0817-4bbe-95fe-ddb865d413cb"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_ONSTUDY_PROTOCOL_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="89" y="0" width="43" height="20" uuid="cb7d64fa-ef17-4d75-8599-c10cdfa558df"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_CONTACT_ID}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="132" y="0" width="87" height="20" uuid="3a13bf53-dbc7-4d45-b29c-989a72a46bf4"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_LAST_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="219" y="0" width="78" height="20" uuid="1b28cd17-9ad3-4af7-b284-630cede8358c"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_FIRST_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy"> <reportElement x="297" y="0" width="65" height="20" uuid="13a6e1b5-f2f9-4145-8fda-9ca56daf0fc6"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_VISIT_DATE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="428" y="0" width="107" height="20" uuid="1de985a5-cfb1-4cc6-8d14-d19b147c7836"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_ONSTUDY_SEQUENCE_NUMBER}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="535" y="0" width="94" height="20" uuid="4f5d9e7d-ebe0-4f5c-98b0-0f6aa6b09b40"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{EVENT_CODE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="629" y="0" width="46" height="20" uuid="1305d21a-b083-4b26-aac8-946a3eaf0aff"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SS_EVAL_SCHED_PK_NUMBER}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="675" y="0" width="172" height="20" uuid="0cf412bf-4be6-4623-8c5d-2c140a7d7508"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_VISIT_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="847" y="0" width="63" height="20" uuid="e0340399-9749-46e0-bd6e-1477bfb7c3af"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_DTL_GCRC_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="910" y="0" width="57" height="20" uuid="0445b27b-9ed7-4de8-bd3e-9af2bb10d3f9"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_SD_PCS_TRACKIN}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="967" y="0" width="64" height="20" uuid="7ca595ce-b63e-47b2-af06-13091d49fd6d"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_FIN_SUB_EVENT_COST_CENTERS_}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1031" y="0" width="64" height="20" uuid="8d88059c-656a-4ab0-bad2-147b31e301a7"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{AS_NEEDED_PROCEDURE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1095" y="0" width="81" height="20" uuid="791a0d80-11b2-45a0-acc4-59bf40d1fbaf"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_MISSED_FLAG}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1176" y="0" width="87" height="20" uuid="b49d9a0f-c552-4404-aac4-023a46707bbc"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_MISSEDCOUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1263" y="0" width="87" height="20" uuid="ab9fba32-fab3-4098-be25-303117e8e45d"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_NA_FLAG}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy"> <reportElement x="363" y="0" width="65" height="20" uuid="45d696b2-729c-484d-8739-967767e67b05"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_FIN_INVOICE_OCCURRED_DATE}]]></textFieldExpression> </textField> </band> </detail> <summary> <band/> </summary> </jasperReport>

Ok - thank you for this suggestion - I've added the following to the list of Parameters, using "Select" as the name for this new parameter you suggested: <parameter name="Date" class="java.lang.String"/> <parameter name="Select" class="java.lang.String"> <defaultValueExpression><![CDATA["Date Variable".equals($P{Date}) ? "SV_SUB_CALENDAR.VISIT_DATE Between $P{Start Date} and $P{End Date}" : "SV_FIN_INVOICE_OCCURRED_DATE Between $P{Start Date} and $P{End Date}"]]></defaultValueExpression> </parameter> I've added this to the where clause: $P!{Select} Between $P{Start Date} and $P{End Date} And I've added the Variable I mentioned: <variable name="Date Variable" class="java.lang.String"> <variableExpression><![CDATA[iF($P{Date}="Visit", $F{SV_SUB_CALENDAR_VISIT_DATE}, $F{SV_FIN_INVOICE_OCCURRED_DATE})]]></variableExpression> </variable> And now I'm getting the following errors. net.sf.jasperreports.engine.JRException: Errors were encountered when compiling report expressions class file: 1. The left-hand side of an assignment must be a variable value = IF(((java.lang.String)parameter_Date.getValue())="Visit", ((java.sql.Timestamp)field_SV_SUB_CALENDAR_VISIT_DATE.getValue()), //$JR_EXPR_ID=9$ <-------------------------------------------> 2. The left-hand side of an assignment must be a variable value = IF(((java.lang.String)parameter_Date.getValue())="Visit", ((java.sql.Timestamp)field_SV_SUB_CALENDAR_VISIT_DATE.getOldValue()), //$JR_EXPR_ID=9$ <-------------------------------------------> 3. The left-hand side of an assignment must be a variable value = IF(((java.lang.String)parameter_Date.getValue())="Visit", ((java.sql.Timestamp)field_SV_SUB_CALENDAR_VISIT_DATE.getValue()), //$JR_EXPR_ID=9$ <-------------------------------------------> 3 errors

The report below works, but I've been tasked with finding a way to display data according to the Start Date and End Date for *either* the SV_SUB_CALENDAR_VISIT_DATE field or the SV_FIN_INVOICE_OCCURRED_DATE field. I originally thought that maybe I could create a Parameter and require users to enter either "Visit" or "Occurred"...and then I created a variable that says IF($P{Date}="Visit", $F{SV_SUB_CALENDAR_VISIT_DATE}, $F{SV_FIN_INVOICE_OCCURRED_DATE}). The issue is, I don't think there's a way to make the report filter dates based on this variable. Suggestions? <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 6.9.0.final using JasperReports Library version 6.9.0-cb8f9004be492ccc537180b49c026951f4220bf3 --> <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="4CRU_ProcBill" pageWidth="1349" pageHeight="802" columnWidth="1349" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isSummaryWithPageHeaderAndFooter="true" isIgnorePagination="true" uuid="f1a362a1-9fdc-49a5-8b3a-e7554a779578"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <parameter name="Start Date" class="java.util.Date"/> <parameter name="End Date" class="java.util.Date"/> <queryString> <![CDATA[sELECT DISTINCT SV_SUB_ONSTUDY.PROTOCOL_NO AS SV_SUB_ONSTUDY_PROTOCOL_NO, SV_SUB_CALENDAR.VISIT_DATE AS SV_SUB_CALENDAR_VISIT_DATE, SV_FIN_INVOICE_ITEMS.OCCURRED_DATE AS SV_FIN_INVOICE_OCCURRED_DATE, SV_PCL_MGMT_STAFF.CONTACT_ID AS SV_PCL_MGMT_STAFF_CONTACT_ID, SV_PCL_MGMT_STAFF.LAST_NAME AS SV_PCL_MGMT_STAFF_LAST_NAME, SV_PCL_MGMT_STAFF.FIRST_NAME AS SV_PCL_MGMT_STAFF_FIRST_NAME, SV_SUB_ONSTUDY.SEQUENCE_NUMBER AS SV_SUB_ONSTUDY_SEQUENCE_NUMBER, SV_FIN_SUB_EVENTS.EVENT_CODE AS EVENT_CODE, SV_SS_EVAL_SCHED.PK_NUMBER AS SV_SS_EVAL_SCHED_PK_NUMBER, SV_SUB_CALENDAR.VISIT_NAME AS SV_SUB_CALENDAR_VISIT_NAME, SV_PCL_MGMT_DTL.GCRC_NO AS SV_PCL_MGMT_DTL_GCRC_NO, SV_SUB_CALENDAR.SD_PCS_TRACKING_ID AS SV_SUB_CALENDAR_SD_PCS_TRACKIN, SV_FIN_SUB_EVENT_COST_CENTERS.COST_CENTER_CODE AS SV_FIN_SUB_EVENT_COST_CENTERS_, SV_FIN_SUB_EVENTS.AS_NEEDED_PROCEDURE AS AS_NEEDED_PROCEDURE, SV_PCS_VISIT_EVALUATIONS.MISSED_FLAG AS SV_PCS_VISIT_MISSED_FLAG, SV_PCS_VISIT_EVALUATIONS.MISSED_COUNT AS SV_PCS_VISIT_MISSEDCOUNT, SV_PCS_VISIT_EVALUATIONS.NOT_APPLICABLE AS SV_PCS_VISIT_NA_FLAG FROM SV_SUB_CALENDAR INNER JOIN SV_SUB_ONSTUDY ON SV_SUB_CALENDAR.PROTOCOL_SUBJECT_ID = SV_SUB_ONSTUDY.PROTOCOL_SUBJECT_ID INNER JOIN SV_PCL_MGMT_DTL ON SV_SUB_ONSTUDY.PROTOCOL_ID = SV_PCL_MGMT_DTL.PROTOCOL_ID INNER JOIN SV_PCL_MGMT_STAFF ON SV_SUB_ONSTUDY.PROTOCOL_NO = SV_PCL_MGMT_STAFF.PROTOCOL_NO INNER JOIN SV_PCS_VISIT_EVALUATIONS ON SV_SUB_CALENDAR.SD_PCS_TRACKING_ID = SV_PCS_VISIT_EVALUATIONS.SD_PCS_TRACKING_ID LEFT OUTER JOIN SV_FIN_SUB_EVENTS ON SV_PCS_VISIT_EVALUATIONS.PCL_BUDGET_EVENT_ID = SV_FIN_SUB_EVENTS.PCL_BUDGET_EVENT_ID LEFT OUTER JOIN SV_FIN_SUB_EVENT_COST_CENTERS ON SV_PCS_VISIT_EVALUATIONS.PCL_BUDGET_EVENT_ID = SV_FIN_SUB_EVENT_COST_CENTERS.PCL_BUDGET_EVENT_ID INNER JOIN SV_SS_EVAL_SCHED ON SV_PCS_VISIT_EVALUATIONS.SD_STUDY_SPEC_EVAL_ID = SV_SS_EVAL_SCHED.SD_STUDY_SPEC_EVAL_ID INNER JOIN SD_SS_TX_CYCLE_VISIT_SCHED ON SV_PCS_VISIT_EVALUATIONS.SD_SS_TX_CYCLE_VISIT_ID = SD_SS_TX_CYCLE_VISIT_SCHED.SD_SS_TX_CYCLE_VISIT_ID AND SD_SS_TX_CYCLE_VISIT_SCHED.SD_SS_TX_SCHED_ID = SV_SS_EVAL_SCHED.STUDY_SPEC_SCHEDULE_ID LEFT OUTER JOIN SV_FIN_INVOICE_ITEMS ON SV_PCS_VISIT_EVALUATIONS.PROTOCOL_SUBJECT_ID = SV_FIN_INVOICE_ITEMS.PROTOCOL_SUBJECT_ID AND SV_PCS_VISIT_EVALUATIONS.SD_PCS_TRACKING_ID = SV_FIN_INVOICE_ITEMS.SD_PCS_TRACKING_ID WHERE SV_SUB_CALENDAR.VISIT_DATE Between $P{Start Date} and $P{End Date} AND SV_PCL_MGMT_DTL.GCRC_APPROVAL = 'Yes' AND SV_PCL_MGMT_STAFF.STAFF_ROLE = 'Principal Investigator' AND SV_PCL_MGMT_STAFF.STOP_DATE IS null AND SV_SUB_CALENDAR.VISIT_STATUS = 'Acknowledged' AND SV_SUB_CALENDAR.MISSED_FLAG = 'N' AND SV_SUB_CALENDAR.NA_FLAG = 'N' AND SV_FIN_SUB_EVENT_COST_CENTERS.COST_CENTER_CODE = '6' ORDER BY SV_SUB_ONSTUDY.PROTOCOL_NO ASC, SV_SUB_CALENDAR.VISIT_DATE ASC, SV_SUB_CALENDAR.SD_PCS_TRACKING_ID ASC]]> </queryString> <field name="SV_SUB_ONSTUDY_PROTOCOL_NO" class="java.lang.String"/> <field name="SV_SUB_CALENDAR_VISIT_DATE" class="java.sql.Timestamp"/> <field name="SV_FIN_INVOICE_OCCURRED_DATE" class="java.sql.Timestamp"/> <field name="SV_PCL_MGMT_STAFF_CONTACT_ID" class="java.math.BigDecimal"/> <field name="SV_PCL_MGMT_STAFF_LAST_NAME" class="java.lang.String"/> <field name="SV_PCL_MGMT_STAFF_FIRST_NAME" class="java.lang.String"/> <field name="SV_SUB_ONSTUDY_SEQUENCE_NUMBER" class="java.lang.String"/> <field name="EVENT_CODE" class="java.lang.String"/> <field name="SV_SS_EVAL_SCHED_PK_NUMBER" class="java.math.BigDecimal"/> <field name="SV_SUB_CALENDAR_VISIT_NAME" class="java.lang.String"/> <field name="SV_PCL_MGMT_DTL_GCRC_NO" class="java.lang.String"/> <field name="SV_SUB_CALENDAR_SD_PCS_TRACKIN" class="java.math.BigDecimal"/> <field name="SV_FIN_SUB_EVENT_COST_CENTERS_" class="java.lang.String"/> <field name="AS_NEEDED_PROCEDURE" class="java.lang.String"/> <field name="SV_PCS_VISIT_MISSED_FLAG" class="java.lang.String"/> <field name="SV_PCS_VISIT_MISSEDCOUNT" class="java.math.BigDecimal"/> <field name="SV_PCS_VISIT_NA_FLAG" class="java.lang.String"/> $F{SV_FIN_INVOICE_OCCURRED_DATE})]]></variableExpression> </variable> <background> <band/> </background> <columnHeader> <band height="26"> <staticText> <reportElement mode="Opaque" x="0" y="0" width="89" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="7508dd54-3f29-42a8-ae6b-8a5a713f4f3b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PROTOCOL]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="89" y="0" width="43" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="b749554f-019f-4d04-97df-41873789595f"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PI_ID]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="132" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="d7b75036-4f24-44e0-afa5-faba76d34fe5"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[LAST_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="219" y="0" width="78" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="c6892498-32ff-4f36-b6bd-3ac390880317"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[FIRST_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="297" y="0" width="65" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="0b605991-09f4-4e69-8007-92a899ddbb5e"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[VISIT_DATE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="428" y="0" width="107" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="1f5bc066-46d2-4338-a7bc-3cd7f92acf8b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[sEQUENCE_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="535" y="0" width="94" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="b3740ae2-afe7-4521-b208-707a838081f0"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[EVENT_CODE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="629" y="0" width="46" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="c518a85d-5ff9-4dce-94d2-84b4f09e7764"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PK_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="675" y="0" width="172" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="99f1fb94-5787-4e0f-8a72-69b3fbbe708f"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[VISIT_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="847" y="0" width="63" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="32908272-a042-449c-ba69-ed3442089c7b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[CCTS_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="910" y="0" width="57" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="685667c6-c5d4-4299-a15c-591807e97f77"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[TRACK_ID]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="967" y="0" width="64" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="f850847d-f9f3-4b58-a2d1-0e3bcf512d39"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[CC_CODE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1031" y="0" width="64" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="09e48d12-14fd-4ed0-b422-35841d97b889"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[AN_PROC]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1095" y="0" width="81" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="4db0df45-141b-4503-860c-0209a1b690bb"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[MISSED_FLAG]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1176" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="fd900ad6-6cde-476a-ba3b-a6bf929bbd7d"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[MISSEDCOUNT]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1263" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="f0e00962-ab0a-4d6c-95f9-026f1f4b3706"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[NA_FLAG]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="363" y="0" width="65" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="12d78fc1-7473-4ee9-8e45-eac36cd80095"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[OCCURRED_DATE]]></text> </staticText> </band> </columnHeader> <detail> <band height="25"> <textField isStretchWithOverflow="true"> <reportElement x="0" y="0" width="89" height="20" uuid="e1eac73e-0817-4bbe-95fe-ddb865d413cb"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_ONSTUDY_PROTOCOL_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="89" y="0" width="43" height="20" uuid="cb7d64fa-ef17-4d75-8599-c10cdfa558df"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_CONTACT_ID}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="132" y="0" width="87" height="20" uuid="3a13bf53-dbc7-4d45-b29c-989a72a46bf4"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_LAST_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="219" y="0" width="78" height="20" uuid="1b28cd17-9ad3-4af7-b284-630cede8358c"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_FIRST_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy"> <reportElement x="297" y="0" width="65" height="20" uuid="13a6e1b5-f2f9-4145-8fda-9ca56daf0fc6"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_VISIT_DATE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="428" y="0" width="107" height="20" uuid="1de985a5-cfb1-4cc6-8d14-d19b147c7836"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_ONSTUDY_SEQUENCE_NUMBER}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="535" y="0" width="94" height="20" uuid="4f5d9e7d-ebe0-4f5c-98b0-0f6aa6b09b40"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{EVENT_CODE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="629" y="0" width="46" height="20" uuid="1305d21a-b083-4b26-aac8-946a3eaf0aff"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SS_EVAL_SCHED_PK_NUMBER}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="675" y="0" width="172" height="20" uuid="0cf412bf-4be6-4623-8c5d-2c140a7d7508"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_VISIT_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="847" y="0" width="63" height="20" uuid="e0340399-9749-46e0-bd6e-1477bfb7c3af"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_DTL_GCRC_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="910" y="0" width="57" height="20" uuid="0445b27b-9ed7-4de8-bd3e-9af2bb10d3f9"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_SD_PCS_TRACKIN}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="967" y="0" width="64" height="20" uuid="7ca595ce-b63e-47b2-af06-13091d49fd6d"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_FIN_SUB_EVENT_COST_CENTERS_}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1031" y="0" width="64" height="20" uuid="8d88059c-656a-4ab0-bad2-147b31e301a7"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{AS_NEEDED_PROCEDURE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1095" y="0" width="81" height="20" uuid="791a0d80-11b2-45a0-acc4-59bf40d1fbaf"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_MISSED_FLAG}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1176" y="0" width="87" height="20" uuid="b49d9a0f-c552-4404-aac4-023a46707bbc"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_MISSEDCOUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1263" y="0" width="87" height="20" uuid="ab9fba32-fab3-4098-be25-303117e8e45d"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_NA_FLAG}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy"> <reportElement x="363" y="0" width="65" height="20" uuid="45d696b2-729c-484d-8739-967767e67b05"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_FIN_INVOICE_OCCURRED_DATE}]]></textFieldExpression> </textField> </band> </detail> <summary> <band/> </summary> </jasperReport>

I'm sorry - I don't know what my anonymized JDBC URL is...but my JRXML code is below. Maybe there's something really simple that I'm overlooking... <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 6.9.0.final using JasperReports Library version 6.9.0-cb8f9004be492ccc537180b49c026951f4220bf3 --> <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="invoiceable_items" pageWidth="1234" pageHeight="954" orientation="Landscape" columnWidth="1234" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" whenResourceMissingType="Empty" uuid="7f369dea-094c-458f-97fb-6f6d351dea7f"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <property name="net.sf.jasperreports.print.keep.full.text" value="true"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="OnCore Test2"/> <parameter name="fromDate" class="java.util.Date"/> <parameter name="thruDate" class="java.util.Date"/> <parameter name="mgmtGroup" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="oncologyGroup" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="department" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="protocol" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="ProtocolInclude" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="ProtocolStatus" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="Unplanned" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="Additional" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="VisitStatus" class="java.util.Collection"> <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression> </parameter> <parameter name="userContactId" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[]]></parameterDescription> </parameter> <queryString> <![CDATA[select event_data.type, event_data.protocol_id, event_data.item, event_data.milestone, event_data.sequence_number, event_data.initials, event_data.visit_status, event_data.occurred_date, event_data.direct, event_data.total_cost, event_data.withhold, event_data.withholding_pct, event_data.sponsor_name, event_data.indirect_flag, event_data.trigger_type, event_data.reoccurring, event_data.exclusion, event_data.comments, NVL(event_data.comments, event_data.eval_notes) as more_info, event_data.status, event_data.unplanned_visit, event_data.additional, event_data.eval_notes, pcl_info.protocol_no, pcl_info.departments, pcl_info.management_groups, pcl_info.oncology_groups, pcl_info.primary_sponsor, pcl_info.secondary_sponsors, pcl_info.indirect_cumulative, pcl_info.protocol_indirect, pcl_info.subject_indirect, pcl_info.milestone_indirect, pcl_info.not_indirect_multiplier, pcl_info.indirect_multiplier, pcl_privileges.has_invoiceable_items, pcl_privileges.has_visit_variations, pcl_privileges.has_subject_identification from ( select type, protocol_id, item, NULL milestone, NULL sequence_number, NULL initials, NULL visit_status, occurred_date, direct, total_cost, withhold, withholding_pct, sponsor_name, indirect_flag, trigger_type, reoccurring, case when type = 'Protocol Related' and trigger_type is not null and reoccurring = 'Y' then 'Include' when type = 'Protocol Related' and (reoccurring is null or reoccurring = 'N') then 'Include' when type = 'Protocol Related' and trigger_type is null and reoccurring = 'Y' then 'Exclude' end as exclusion, comments, status, NULL unplanned_visit, NULL additional, NULL eval_notes from ( SELECT 'Protocol Related' type, a.protocol_id, a.budget_event_description item, a.occurred_date, NVL(a.sponsor_cost,0) direct, null total_cost, a.withhold, nvl(a.withholding_pct,0) withholding_pct, ( SELECT sponsor_name FROM smrs_pcl_sponsor sps, smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.sponsor AND sps.sponsor = ss.sponsor ) sponsor_name, a.indirect_flag, a.trigger_type, b.reoccurring, b.comments, c.status, row_number () over (partition by b.pcl_budget_event_id order by c.status_date desc, c.created_date desc) rn FROM sv_pcl_invoicables a left outer join sv_fin_pcl_events b on a.protocol_id = b.protocol_id and a.budget_event_id = b.budget_event_id and a.pcl_budget_event_id = b.pcl_budget_event_id left outer join sv_pcl_status c on a.protocol_id = c.protocol_id where 1=1 and a.na_for_invoice is null or a.na_for_invoice = 0 ) where (rn = 1) UNION ALL SELECT 'Subject Milestone' type, protocol_id, NULL item, (visit_description) milestone, sequence_number, initials, visit_status, TO_CHAR(visit_date, 'MM/DD/YYYY') occurred_date, NVL(sponsor_cost, 0) direct, NULL total_cost, withhold, NVL(withholding_pct, 0) withholding_pct, sponsor_name, 'N' indirect_flag, NULL trigger_type, NULL reoccurring, NULL exclusion, NULL comments, NULL status, NULL unplanned_visit, NULL additional, NULL eval_notes FROM ( SELECT protocol_id, pcl_milestone_id, ( CASE WHEN visit_description IS NULL THEN NULL WHEN ( SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no) IS NOT NULL THEN 'Arm ' || ( SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no ) || ': ' || visit_description ELSE visit_description END ) visit_description, na_for_invoice, withhold, withholding_pct, visit_status, visit_date, cost, sponsor, sponsor_cost, ( SELECT sponsor_name FROM smrs_pcl_sponsor sps, smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.sponsor AND sps.sponsor = ss.sponsor ) sponsor_name, protocol_subject_id, sequence_number, initials, TO_CHAR(date_submitted, 'MM/DD/YYYY') submitted_date FROM ( SELECT vst.protocol_id, vst.pcl_milestone_id, vst.arm_no, vst.visit_id, vst.visit_description, case when cal.missed_flag = 'Y' then 'Missed' when vst.visit_date is null then 'Missed' when cal.na_flag = 'Y' then 'NA' when cal.visit_status = 'Planned' then 'Planned' when vst.visit_date is not null then 'Occurred' end as visit_status, vst.visit_date, vst.cost, vst.sd_study_spec_id, vst.budget_version_no, vst.na_for_invoice, vst.withhold, vst.withholding_pct, vst.sponsor, vst.sponsor_cost, NULL sponsor_due, vst.protocol_subject_id, vst.sequence_number, vst.initials, vst.uncertified_item, vst.date_completed, vst.date_monitored, vst.date_submitted FROM sv_pcs_visit_invoicables vst left outer join sv_sub_calendar cal on vst.visit_id = cal.sd_pcs_tracking_id and vst.protocol_subject_id = cal.protocol_subject_id UNION SELECT ext.protocol_id, ext.pcl_milestone_id, ext.arm_no, ext.visit_id, ext.visit_string visit_description, case when cal.missed_flag = 'Y' then 'Missed' when ext.visit_date is null then 'Missed' when cal.na_flag = 'Y' then 'NA' when cal.visit_status = 'Planned' then 'Planned' when ext.visit_date is not null then 'Occurred' end as visit_status, ext.visit_date, ext.cost, ext.sd_study_spec_id, ext.budget_version_no, ext.na_for_invoice, ext.withhold, ext.withholding_pct, ext.sponsor, ext.sponsor_cost, NULL sponsor_due, ext.protocol_subject_id, ext.sequence_number, get_subject_name(ext.protocol_subject_id, 'Y') initials, ext.uncertified_item, ext.date_completed, ext.date_monitored, ext.date_submitted FROM sv_pcs_extended_invoicables ext left outer join sv_sub_calendar cal on ext.visit_id = cal.sd_pcs_tracking_id and ext.protocol_subject_id = cal.protocol_subject_id UNION SELECT ext.protocol_id, ext.pcl_milestone_id, ext.arm_no, ext.visit_id, ext.visit_string visit_description, case when cal.missed_flag = 'Y' then 'Missed' when ext.visit_date is null then 'Missed' when cal.na_flag = 'Y' then 'NA' when cal.visit_status = 'Planned' then 'Planned' when ext.visit_date is not null then 'Occurred' end as visit_status, ext.visit_date, ext.cost, ext.sd_study_spec_id, ext.budget_version_no, ext.na_for_invoice, ext.withhold, ext.withholding_pct, ext.sponsor, ext.sponsor_cost, NULL sponsor_due, ext.protocol_subject_id, ext.sequence_number, get_subject_name(ext.protocol_subject_id, 'Y') initials, ext.uncertified_item, ext.date_completed, ext.date_monitored, ext.date_submitted FROM sv_pcs_oem_invoicables ext left outer join sv_sub_calendar cal on ext.visit_id = cal.sd_pcs_tracking_id and ext.protocol_subject_id = cal.protocol_subject_id UNION SELECT rev.protocol_id, rev.milestone_id, rev.arm_no, rev.visit_id, rev.visit_description, case when cal.missed_flag = 'Y' then 'Missed' when rev.visit_date is null then 'Missed' when cal.na_flag = 'Y' then 'NA' when cal.visit_status = 'Planned' then 'Planned' when rev.visit_date is not null then 'Occurred' end as visit_status, rev.visit_date, rev.cost, rev.sd_study_spec_id, rev.budget_version_no, rev.na_for_invoice, rev.withhold, rev.withholding_pct, rev.sponsor, rev.sponsor_cost, rev.sponsor_due, rev.protocol_subject_id, rev.sequence_number, rev.initials, rev.uncertified_item, rev.date_completed, rev.date_monitored, rev.date_submitted FROM sv_invoice_reversals rev left outer join sv_sub_calendar cal on rev.visit_id = cal.sd_pcs_tracking_id and rev.protocol_subject_id = cal.protocol_subject_id WHERE milestone_id IS NOT NULL ) a, ( SELECT NULL spon, NULL milestone_id, 'Y' include_na FROM dual ) b WHERE 1 = 1 AND (b.spon IS NULL OR NVL(a.sponsor, b.spon) = b.spon) AND (b.milestone_id IS NULL OR a.pcl_milestone_id IN ( SELECT pcl_milestone_id FROM sv_pcs_milestone_ref WHERE milestone_id_ref = b.milestone_id )) AND (NVL(b.include_na,'N') = 'Y' OR NVL(a.na_for_invoice, 0) = 0) AND (NVL(visit_description,' ') != 'Screen Failed') UNION ALL SELECT protocol_id, pcl_milestone_id, visit_description, na_for_invoice, withhold, withholding_pct, visit_status, visit_date, cost, sponsor, sponsor_cost, ( SELECT sponsor_name FROM smrs_pcl_sponsor sps, smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.sponsor AND sps.sponsor = ss.sponsor ) sponsor_name, protocol_subject_id, sequence_number, initials, submitted_date FROM ( SELECT evt.protocol_id, pcl_milestone_id, visit_description, case when cal.missed_flag = 'Y' then 'Missed' when evt.visit_date is null then 'Missed' when cal.na_flag = 'Y' then 'NA' when cal.visit_status = 'Planned' then 'Planned' when evt.visit_date is not null then 'Occurred' end as visit_status, evt.visit_date, NULL sae_no, NULL osr_id, cost, na_for_invoice, withhold, withholding_pct, sponsor, sponsor_cost, evt.protocol_subject_id, sequence_number, initials, NULL submitted_date FROM sv_pcs_event_invoicables evt left outer join sv_sub_calendar cal on evt.protocol_subject_id = cal.protocol_subject_id and evt.visit_date = cal.visit_date WHERE evt.visit_description != 'Screen Failed' AND TRUNC(evt.billed_date) < TRUNC(NVL( ( SELECT MIN(start_date) FROM onc_pcl_invoicing_rule rl WHERE rl.protocol_id = evt.protocol_id AND rl.rule_type = 'NB' AND (rl.sponsor IS NULL OR rl.sponsor = evt.sponsor) ) , evt.billed_date + 1)) UNION SELECT evt.protocol_id, pcl_milestone_id, visit_description, case when cal.missed_flag = 'Y' then 'Missed' when evt.visit_date is null then 'Missed' when cal.na_flag = 'Y' then 'NA' when cal.visit_status = 'Planned' then 'Planned' when evt.visit_date is not null then 'Occurred' end as visit_status, evt.visit_date, NULL sae_no, NULL osr_id, cost, na_for_invoice, withhold, withholding_pct, sponsor, sponsor_cost, evt.protocol_subject_id, sequence_number, get_subject_name(evt.protocol_subject_id, 'Y') initials, NULL submitted_date FROM sv_invoicable_screen_failures evt left outer join sv_sub_calendar cal on evt.protocol_subject_id = cal.protocol_subject_id and evt.visit_date = cal.visit_date WHERE TRUNC(evt.billed_date) < TRUNC(NVL( ( SELECT MIN(start_date) FROM onc_pcl_invoicing_rule rl WHERE rl.protocol_id = evt.protocol_id AND rl.rule_type = 'NB' AND (rl.sponsor IS NULL OR rl.sponsor = evt.sponsor) ) , evt.billed_date + 1)) UNION SELECT protocol_id, pcl_milestone_id, visit_description, case when cal.missed_flag = 'Y' then 'Missed' when saes.visit_date is null then 'Missed' when cal.na_flag = 'Y' then 'NA' when cal.visit_status = 'Planned' then 'Planned' when saes.visit_date is not null then 'Occurred' end as visit_status, saes.visit_date, sae_no, NULL osr_id, cost, na_for_invoice, withhold, withholding_pct, sponsor, sponsor_cost, saes.protocol_subject_id, sequence_number, initials, NULL submitted_date FROM sv_invoicable_saes saes left outer join sv_sub_calendar cal on saes.protocol_subject_id = cal.protocol_subject_id and saes.visit_date = cal.visit_date UNION SELECT protocol_id, pcl_milestone_id, milestone, case when cal.missed_flag = 'Y' then 'Missed' when osrs.visit_date is null then 'Missed' when cal.na_flag = 'Y' then 'NA' when cal.visit_status = 'Planned' then 'Planned' when osrs.visit_date is not null then 'Occurred' end as visit_status, osrs.visit_date, NULL sae_no, osr_id, cost, na_for_invoice, withhold, withholding_pct, sponsor, sponsor_cost, osrs.protocol_subject_id, sequence_number, initials, NULL submitted_date FROM sv_invoicable_osrs osrs left outer join sv_sub_calendar cal on osrs.protocol_subject_id = cal.protocol_subject_id and osrs.visit_date = cal.visit_date ) a where nvl(na_for_invoice, 0) = 0 ) UNION ALL SELECT 'Pass Through' type, a.protocol_id protocol_id, budget_event_description item, ( CASE WHEN ( SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no) IS NOT NULL THEN 'Arm '|| ( SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no ) || ': ' || visit_description ELSE visit_description END) milestone, sequence_number, initials, NULL visit_status, tracking_date occurred_date, NVL(ptec.visit_direct, 0) direct, NVL(ptec.visit_total, 0) total_cost, --sponsor,--id of the sponsor withhold, NVL(withholding_pct, 0) withholding_pct, --na_for_invoice, ( SELECT sponsor_name FROM smrs_pcl_sponsor sps, smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.sponsor AND sps.sponsor = ss.sponsor ) sponsor_name, 'N' indirect_flag, NULL trigger_type, NULL reoccurring, NULL exclusion, NULL comments, NULL status, NULL unplanned_visit, NULL additional, NULL eval_notes FROM ( SELECT DISTINCT pcl_evaluation_id, pcl_budget_event_id, protocol_id, tracking_date eval_date, TO_CHAR(tracking_date, 'MM/DD/YYYY') tracking_date, tracking_id, a.sd_ss_tx_cycle_visit_id, visit_eval_id, a.arm_no, arm_code arm, na_for_invoice, a.sd_study_spec_id, a.budget_version_no, ( SELECT version_no FROM sd_study_spec WHERE sd_study_spec_id = a.sd_study_spec_id ) calendar_version_no, ( SELECT TO_CHAR(version_date, 'MM/DD/YYYY') FROM onc_pcl_budget_version WHERE sd_study_spec_id = a.sd_study_spec_id AND budget_version_no = a.budget_version_no ) budget_version_date, NULL sponsor_cost, NULL sponsor_due, sponsor, withhold, withholding_pct, ( SELECT sponsor_name FROM smrs_pcl_sponsor sps, smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.sponsor AND sps.sponsor = ss.sponsor ) sponsor_name, NVL(budget_event_description, clinical_procedure) budget_event_description, visit_description, protocol_subject_id, sequence_number, get_subject_name(a.protocol_subject_id, 'Y') initials, to_number(NULL) uncertified_item FROM sv_pcs_procedure_invoicables a UNION ALL SELECT DISTINCT a.pcl_evaluation_id, a.pcl_budget_event_id, a.protocol_id, visit_date eval_date, TO_CHAR(a.visit_date, 'MM/DD/YYYY') tracking_date, a.visit_id tracking_id, NULL sd_ss_tx_cycle_visit_id, visit_eval_id, a.arm_no, ( SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no ) arm, na_for_invoice, a.sd_study_spec_id, a.budget_version_no, ( SELECT version_no FROM sd_study_spec WHERE sd_study_spec_id = a.sd_study_spec_id ) calendar_version_no, ( SELECT TO_CHAR(version_date, 'MM/DD/YYYY') FROM onc_pcl_budget_version WHERE sd_study_spec_id = a.sd_study_spec_id AND budget_version_no = a.budget_version_no ) budget_version_date, a.sponsor_cost, a.sponsor_due, a.sponsor, a.withhold, a.withholding_pct, ( SELECT sponsor_name FROM smrs_pcl_sponsor sps, smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.sponsor AND sps.sponsor = ss.sponsor ) sponsor_name, DECODE(a.pcl_budget_event_id, NULL, NVL(c.clinical_procedure, d.event_description), ( SELECT budget_event_description FROM onc_budget_event WHERE budget_event_id = e.budget_event_id ) ) budget_event_description, a.visit_description, a.protocol_subject_id, a.sequence_number, a.initials, uncertified_item FROM sv_invoice_reversals a, onc_pcl_evaluation b, sd_study_spec_evaluation c, pf_lab_category d, onc_pcl_budget_event e WHERE a.milestone_id IS NULL AND a.pcl_evaluation_id = b.pcl_evaluation_id AND b.sd_study_spec_eval_id = c.sd_study_spec_eval_id AND c.lab_category_id = d.lab_category_id(+) AND a.pcl_budget_event_id = e.pcl_budget_event_id(+) ) a LEFT JOIN ( SELECT /*+ RULE */ t.pcl_evaluation_id, t.pcl_budget_event_id, t.sd_ss_tx_cycle_visit_id, t.arm_no, (t.pcl_evaluation_id || ':' || NVL(t.pcl_budget_event_id, 0) || ':' || t.sd_ss_tx_cycle_visit_id || ':' || NVL( t.arm_no, 0)) KEY, SUM(t.invoice_cost * t.pk_count) visit_direct, SUM(t.invoice_cost * t.pk_count * DECODE(fp.budget_only_indirect, 'Y', fp.net_rate_without_indirect, DECODE( t.indirect_flag, 'Y', fp.net_rate_with_indirect, fp.net_rate_without_indirect))) visit_total FROM sv_pcl_evaluation_visit_costs t, sv_fin_parameters fp, sv_evaluation_pass_thru_visits pt WHERE fp.protocol_id = t.protocol_id AND t.invoice_cost > 0 AND t.pcl_evaluation_id = pt.pcl_evaluation_id AND NVL(t.pcl_budget_event_id, 0) = NVL(pt.pcl_budget_event_id, 0) AND (pt.visit_id IS NULL OR (t.sd_ss_tx_cycle_visit_id = pt.visit_id AND NVL(t.arm_no, 0) = NVL(pt.arm_no, 0))) GROUP BY t.pcl_evaluation_id, t.pcl_budget_event_id, t.study_spec_id, t.sd_ss_tx_cycle_visit_id, t.arm_no ) ptec ON ptec.key = a.pcl_evaluation_id || ':' || NVL(a.pcl_budget_event_id, 0) || ':' || a.sd_ss_tx_cycle_visit_id || ':' || NVL(a.arm_no, 0) WHERE (a.na_for_invoice IS NULL OR a.na_for_invoice = 0) UNION ALL select st1.type, st1.protocol_id, st1.item, st1.milestone, st1.sequence_number, st1.initials, st1.visit_status, st1.occurred_date, st1.direct, st1.total_cost, st1.withhold, st1.withholding_pct, st1.sponsor_name, st1.indirect_flag, st1.trigger_type, st1.reoccurring, NULL exclusion, st1.comments, st1.status, st1.unplanned_visit, vv.additional, vv.eval_notes from ( SELECT 'Visit Variation' type, a.protocol_id, LISTAGG((case when evaluation_detail is not null then evaluation2 || ': ' || evaluation_detail else evaluation2 end), '; ') within group (order by eval_id) item, (case when arm_code is not null then 'Arm ' || arm_code || ': ' || visit_string else visit_string end) milestone, a.sequence_number, a.initials, case when a.missed_visit = 'Y' then 'Missed' when a.na_visit = 'Y' then 'NA' end as visit_status, nvl(a.visit_date, (case when missed_visit = 'Y' then 'Missed' when na_visit = 'Y' then 'NA' end)) occurred_date, sum( case when (nvl(missed_visit, 'N') = 'Y' or nvl(na_visit, 'N') = 'Y' or billed_date is null) then (case when nvl(is_soc, 0) = 1 or nvl(is_pass_thru, 0) = 1 then 0 else nvl(nvl(eval_cost, non_scheduled_eval_cost),0) * eval_count_sum end) * (case when nvl(unplanned_visit, 'N') = 'Y' then 1 else -1 end) else (case when not (nvl(additional, 0) = 1 and nvl(soc_flag, 'N') = 'Y') then ( case when nvl(additional, 0) = 1 then nvl(nvl(eval_cost, non_scheduled_eval_cost),0) when missed_count is not null and missed_count > 0 then nvl(nvl(eval_cost, non_scheduled_eval_cost),0) * missed_count * -1 when nvl(is_soc, 0) = 1 and nvl(soc_flag, 'N') = 'N' then nvl(nvl(eval_cost, non_scheduled_eval_cost),0) else nvl(nvl(eval_cost, non_scheduled_eval_cost),0) * -1 end ) else 0 end) end ) direct, sum( case when (nvl(missed_visit, 'N') = 'Y' or nvl(na_visit, 'N') = 'Y' or billed_date is null) then (case when nvl(is_soc, 0) = 1 or nvl(is_pass_thru, 0) = 1 then 0 else nvl(nvl(eval_due, non_scheduled_eval_due),0) * eval_count_sum end) * (case when nvl(unplanned_visit, 'N') = 'Y' then 1 else -1 end) else (case when not (nvl(additional, 0) = 1 and nvl(soc_flag, 'N') = 'Y') then ( case when nvl(additional, 0) = 1 then nvl(nvl(eval_due, non_scheduled_eval_due),0) when missed_count is not null and missed_count > 0 then nvl(nvl(eval_due, non_scheduled_eval_due),0) * missed_count * -1 when nvl(is_soc, 0) = 1 and nvl(soc_flag, 'N') = 'N' then nvl(nvl(eval_due, non_scheduled_eval_due),0) else nvl(nvl(eval_due, non_scheduled_eval_due),0) * -1 end ) else 0 end) end ) total_cost, 'N' withhold, 0 withholding_pct, ( SELECT sponsor_name FROM smrs_pcl_sponsor sps, smrs_sponsor ss WHERE sps.pcl_sponsor_id = a.eval_sponsor AND sps.sponsor = ss.sponsor ) sponsor_name, 'N' indirect_flag, null trigger_type, NULL reoccurring, NULL comments, NULL status, a.unplanned_visit, a.evaluation_, a.protocol_subject_id FROM --original query for visit variations tab of financials console ( SELECT row_number() over (ORDER BY sv.visit_date) row_num, sp.protocol_id protocol_id, upper(SUBSTR(ss.subject_first_name,1,1) || SUBSTR(ss.subject_middle_name,1,1) || SUBSTR(ss.subject_last_name,1,1)) initials, sv.sd_pcs_tracking_id, NVL(sv.missed_flag,'N') missed_visit, NVL(sv.na_flag,'N') na_visit, sv.billed_date, DECODE(vs.description, 'UnPlanned', 'Y', 'N') unplanned_visit, ( SELECT DISTINCT 1 FROM sv_invoice_reversals WHERE protocol_id = sp.protocol_id AND visit_id = sv.sd_pcs_tracking_id ) invoiced_uncertified, DECODE(ve.additional, 'Y', 1, 0) additional, ve.sd_pcs_tracking_evaluation_id evaluation_, get_evaluation_name(NULL, ve.sd_pcs_tracking_evaluation_id) evaluation2, DECODE(ed.visit_eval_id, NULL, NULL, ( SELECT be.budget_event_description FROM onc_pcl_budget_event pbe, onc_budget_event be WHERE be.budget_event_id = pbe.budget_event_id AND pbe.pcl_budget_event_id = ed.pcl_budget_event_id ) ) evaluation_detail, TO_CHAR(ve.evaluation_date,'MM/DD/YYYY') evaluation_date, ve.evaluation eval_id, DECODE(ph.code,'ONT',sv.arm_no,'') arm_no, DECODE(ed.visit_eval_id, NULL, ve.soc_flag, DECODE(ve.alternate_event_id, NULL, ed.soc_flag, ve.soc_flag)) soc_flag, DECODE(ed.visit_eval_id, NULL, ve.missed_flag, ed.missed_flag) missed_flag, DECODE(ed.visit_eval_id, NULL, ve.not_applicable, ed.not_applicable) not_applicable, DECODE(vs.description, 'UnPlanned', sv.visit_description, NVL( ( SELECT revised_visit_string FROM sd_ss_tx_cycle_visit WHERE sd_ss_tx_cycle_visit_id = sv.sd_ss_tx_cycle_visit_id ) , sv.visit_string)) visit_string, DECODE(ed.visit_eval_id, NULL, ve.missed_count, ed.missed_count) missed_count, ( SELECT 1 FROM dual WHERE EXISTS ( SELECT 1 FROM sv_evaluation_soc_visits so WHERE so.sd_study_spec_eval_id = ve.evaluation AND NVL(ve.additional, 'N') = 'N' AND so.budget_version_no = sv.budget_version_no AND (so.visit_id IS NULL OR (NVL(so.arm_no,0) = NVL(sv.arm_no,0) AND so.visit_id = sv.sd_ss_tx_cycle_visit_id)) AND (ed.visit_eval_id IS NULL OR so.pcl_budget_event_id = ed.pcl_budget_event_id AND ed.pcl_budget_event_item_id IS NULL) ) ) is_soc, ( SELECT 1 FROM dual WHERE EXISTS ( SELECT /*+ RULE */ 1 FROM sv_evaluation_pass_thru_visits so WHERE so.sd_study_spec_eval_id = ve.evaluation AND NVL(ve.additional, 'N') = 'N' AND so.budget_version_no = sv.budget_version_no AND (so.visit_id IS NULL OR (NVL(so.arm_no,0) = NVL(sv.arm_no,0) AND so.visit_id = sv.sd_ss_tx_cycle_visit_id)) AND (ed.visit_eval_id IS NULL OR so.pcl_budget_event_id = ed.pcl_budget_event_id AND ed.pcl_budget_event_item_id IS NULL) ) ) is_pass_thru, ve.sd_pcs_tracking_evaluation_id, ed.pcl_budget_event_id, get_visit_eval_variation_notes(ve.sd_pcs_tracking_evaluation_id, ed.visit_eval_detail_id) eval_notes, CASE WHEN sv.missed_flag = 'Y' OR sv.na_flag = 'Y' THEN sv.verified_flag ELSE DECODE(ed.visit_eval_id, NULL, ve.verified_flag, ed.verified_flag) END verified_flag, CASE WHEN sv.missed_flag = 'Y' OR sv.na_flag = 'Y' THEN sv.invoice_flag ELSE DECODE(ed.visit_eval_id, NULL, ve.invoice_flag, ed.invoice_flag) END invoice_flag, CASE WHEN (sv.missed_flag = 'Y' OR sv.na_flag = 'Y') AND ( SELECT COUNT(*) FROM sd_pcs_visit_evaluation eval, sd_pcs_visit_eval_detail det WHERE eval.sd_pcs_tracking_id = sv.sd_pcs_tracking_id AND eval.sd_pcs_tracking_evaluation_id = det.visit_eval_id(+) AND DECODE(det.visit_eval_id, NULL, eval.invoice_flag, det.invoice_flag) = 'Y' ) > 0 THEN 'Y' ELSE 'N' END partial_invoice_flag, CASE WHEN (ve.additional = 'Y' AND ve.evaluation IS NULL) THEN 0 ELSE ( SELECT SUM (NVL (visit_cost, 0)) FROM sv_pcl_evaluation_visit_costs ec WHERE ec.study_spec_id = sv.sd_study_spec_id AND ec.sd_study_spec_eval_id = ve.evaluation AND ec.budget_version_no = sv.budget_version_no AND ec.sd_ss_tx_cycle_visit_id = sv.sd_ss_tx_cycle_visit_id AND NVL (ec.arm_no, 0) = NVL (sv.arm_no, 0) AND NVL(ec.pcl_budget_event_id, 0) = NVL(DECODE(ve.alternate_event_id, ed.pcl_budget_event_id, NULL, ed.pcl_budget_event_id), 0) ) END eval_cost, get_subj_visit_eval_pcl_cost(ve.sd_pcs_tracking_evaluation_id, ed.pcl_budget_event_id) non_scheduled_eval_cost, get_subj_visit_eval_pcl_due(sp.protocol_id, ve.sd_pcs_tracking_evaluation_id, ed.pcl_budget_event_id) non_scheduled_eval_due, CASE WHEN (ve.additional = 'Y' AND ve.evaluation IS NULL) THEN 0 ELSE ( SELECT SUM (NVL (ec.visit_cost, 0) * DECODE(ec.indirect_flag, 'Y', par.net_rate_with_indirect, par.net_rate_without_indirect)) FROM sv_pcl_evaluation_visit_costs ec WHERE ec.study_spec_id = sv.sd_study_spec_id AND ec.sd_study_spec_eval_id = ve.evaluation AND ec.budget_version_no = sv.budget_version_no AND ec.sd_ss_tx_cycle_visit_id = sv.sd_ss_tx_cycle_visit_id AND NVL (ec.arm_no, 0) = NVL (sv.arm_no, 0) AND NVL(ec.pcl_budget_event_id, 0) = NVL(DECODE(ve.alternate_event_id, ed.pcl_budget_event_id, NULL, ed.pcl_budget_event_id), 0) ) END eval_due, NVL( ( SELECT MIN(pk_count) FROM sv_ss_eval_visits WHERE sd_study_spec_eval_id = ve.evaluation AND NVL(ve.additional, 'N') = 'N' AND sd_ss_tx_cycle_visit_id = sv.sd_ss_tx_cycle_visit_id AND study_spec_id = sv.sd_study_spec_id ) , 1) eval_count_sum, ( SELECT sponsor FROM onc_pcl_evaluation WHERE sd_study_spec_eval_id = ve.evaluation AND budget_version_no = sv.budget_version_no ) eval_sponsor, ( SELECT arm_code FROM smrs_pcl_step_arm a WHERE protocol_id = sp.protocol_id AND arm_no = sv.arm_no ) arm_code, ps.sequence_number, TO_CHAR(sv.visit_date,'MM/DD/YYYY') visit_date, ps.protocol_subject_id, ph.description phase FROM smrs_protocol sp, smrs_subject ss, smrs_pcl_cent_subject ps, sd_pcs_tracking sv, sd_pcs_visit_evaluation ve, pf_code ph, pf_code vs, ( SELECT * FROM sd_pcs_visit_eval_detail WHERE pcl_budget_event_id IS NOT NULL AND pcl_budget_event_item_id IS NULL ) ed, ( SELECT protocol_id, net_rate_with_indirect, net_rate_without_indirect FROM sv_fin_parameters ) par WHERE sp.protocol_id = par.protocol_id AND ps.protocol_id = sp.protocol_id AND ps.subject_no = ss.subject_no AND sv.protocol_subject_id = ps.protocol_subject_id AND ve.sd_pcs_tracking_id = sv.sd_pcs_tracking_id AND ve.sd_pcs_tracking_evaluation_id = ed.visit_eval_id(+) AND ph.code_id = sv.phase AND sv.visit_status = vs.code_id AND vs.category = 'VISIT_STATUS' AND vs.description IN ('Acknowledged','UnPlanned') AND ( (DECODE(ed.visit_eval_id, NULL, ve.soc_flag, DECODE(ve.alternate_event_id, NULL, ed.soc_flag, ve.soc_flag)) = 'N' AND NVL(ve.additional, 'N') = 'N' AND EXISTS ( SELECT 1 FROM sv_evaluation_soc_visits so WHERE so.sd_study_spec_eval_id = ve.evaluation AND so.budget_version_no = sv.budget_version_no AND (ed.visit_eval_id IS NULL OR so.pcl_budget_event_id = ed.pcl_budget_event_id) AND (so.visit_id IS NULL OR (NVL(so.arm_no,0) = NVL(sv.arm_no,0) AND so.visit_id = sv.sd_ss_tx_cycle_visit_id)) ) ) OR (DECODE(ed.visit_eval_id, NULL, ve.soc_flag, DECODE(ve.alternate_event_id, NULL, ed.soc_flag, ve.soc_flag)) = 'Y' AND NVL(ve.additional, 'N') = 'N' AND NOT EXISTS ( SELECT 1 FROM sv_evaluation_soc_visits so WHERE so.sd_study_spec_eval_id = ve.evaluation AND so.budget_version_no = sv.budget_version_no AND (ed.visit_eval_id IS NULL OR so.pcl_budget_event_id = ed.pcl_budget_event_id) AND (so.visit_id IS NULL OR (NVL(so.arm_no,0) = NVL(sv.arm_no,0) AND so.visit_id = sv.sd_ss_tx_cycle_visit_id)) ) ) OR (ed.visit_eval_id IS NULL AND (NVL(ve.missed_flag,'N') = 'Y' OR NVL(ve.not_applicable,'N') = 'Y' OR NVL(ve.missed_count,0) > 0)) OR (ed.visit_eval_id IS NOT NULL AND (NVL(ed.missed_flag,'N') = 'Y' OR NVL(ed.not_applicable,'N') = 'Y' OR NVL(ed.missed_count,0) > 0)) OR NVL(ve.additional,'N') = 'Y' OR NVL(sv.missed_flag,'N') = 'Y' OR NVL(sv.na_flag,'N') = 'Y' OR vs.description = 'UnPlanned' OR sv.billed_date IS NULL ) --exclusions for items that can be invoiced and nvl(decode(ed.visit_eval_id, null, ve.invoice_flag, ed.invoice_flag), 'N') = 'N' and nvl(sv.invoice_flag,'N') = 'N' and (nvl(ve.additional,'N') = 'N' or (ve.additional = 'Y' and nvl(ve.soc_flag,'N') = 'N' and nvl(ve.missed_flag,'N') = 'N')) and sv.billed_date is not null and (ve.additional = 'Y') ORDER BY initials, sv.visit_date, sv.sd_pcs_tracking_id, evaluation, ve.sd_pcs_tracking_evaluation_id, evaluation_detail ) a group by a.protocol_id, a.initials, a.visit_date, a.sd_pcs_tracking_id, --only group rows that are missed, na, or not billed yet, otherwise use the row_num as a unique identifier to split out all other rows (case when (nvl(missed_visit,'N') != 'N' or nvl(na_visit, 'N') != 'N' or billed_date is null) then -1 else row_num end), additional, unplanned_visit, invoice_flag, missed_visit, na_visit, a.sequence_number, a.eval_sponsor, ( CASE WHEN arm_code IS NOT NULL THEN 'Arm ' || arm_code || ': ' || visit_string ELSE visit_string END), a.evaluation_, a.protocol_subject_id ) st1 left outer join sv_fin_visit_variations vv on st1.protocol_id = vv.protocol_id and st1.protocol_subject_id = vv.protocol_subject_id and st1.evaluation_ = vv.sd_pcs_tracking_evaluation_id ) event_data --protocol info left join ( select pcl.protocol_id, pcl.protocol_no, pcl_dep.departments, pcl_mg.management_groups, pcl_og.oncology_groups, pcl_primary_sponsor.primary_sponsor, pcl_secondary_sponsors.secondary_sponsors, pcl_budget_parameters.indirect_cumulative, pcl_budget_parameters.protocol_indirect, pcl_budget_parameters.subject_indirect, pcl_budget_parameters.milestone_indirect, (1 + pcl_budget_parameters.overhead_cost_rate/100) not_indirect_multiplier, (case when pcl_budget_parameters.indirect_cumulative != 'Y' then (1 + (pcl_budget_parameters.overhead_cost_rate + pcl_budget_parameters.indirect_cost_rate) / 100) else ((1 + pcl_budget_parameters.overhead_cost_rate/100) * (1 + pcl_budget_parameters.indirect_cost_rate/100)) end) indirect_multiplier from smrs_protocol pcl --get the protocol departments left join ( SELECT a.protocol_id, LISTAGG((case when a.primary_flag = 'Y' then b.name || ' (P)' else b.name end), '; ') within GROUP (ORDER BY a.primary_flag DESC, b.name) departments FROM smrs_pcl_department a LEFT JOIN onc_department b ON a.department_id = b.onc_department_id GROUP BY a.protocol_id ) pcl_dep on pcl.protocol_id = pcl_dep.protocol_id --get the management groups left join ( SELECT protocol_id, LISTAGG((case when primary_flag='Y' then mg.name || ' (P)' else mg.name end), '; ') within group (order by primary_flag DESC, name) management_groups FROM onc_pcl_management_group pmg, onc_org_unit_management_group rmg, onc_management_group mg WHERE pmg.RESEARCH_MANAGEMENT_GROUP_ID = rmg.onc_ou_mgmt_group_id AND rmg.onc_management_group_id = mg.onc_management_group_id group by protocol_id ) pcl_mg on pcl.protocol_id = pcl_mg.protocol_id --get the oncology groups left join ( SELECT protocol_id, LISTAGG((case when pd.value = 'Y' then description || ' (P)' else description end), '; ') within group (order by pd.value DESC, description) oncology_groups FROM smrs_pcl_detail pd, pf_code sc WHERE pd.detail = sc.code_id AND sc.category = 'DOWG' group by protocol_id ) pcl_og on pcl.protocol_id = pcl_og.protocol_id left join ( SELECT sps.protocol_id, ss.sponsor_name primary_sponsor FROM smrs_pcl_sponsor sps, smrs_sponsor ss WHERE sps.sponsor = ss.sponsor and principal_sponsor = 'Y' ) pcl_primary_sponsor on pcl_primary_sponsor.protocol_id = pcl.protocol_id left join ( SELECT sps.protocol_id, LISTAGG(ss.sponsor_name, '; ') within group (order by sponsor_name) secondary_sponsors FROM smrs_pcl_sponsor sps, smrs_sponsor ss WHERE sps.sponsor = ss.sponsor and principal_sponsor = 'N' group by sps.protocol_id ) pcl_secondary_sponsors on pcl_secondary_sponsors.protocol_id = pcl.protocol_id left join ( SELECT pr.protocol_id, ( SELECT description FROM pf_code WHERE code_id = bp.rate_base ) rate_base, bp.rate_base rate_base_id, NVL(bp.overhead_cost_pct, 0) overhead_cost_rate, NVL(bp.indirect_cumulative, 'N') indirect_cumulative, NVL(bp.indirect_cost_pct, 0) indirect_cost_rate, bp.protocol_indirect, bp.subject_indirect, bp.milestone_indirect, NVL(bp.budget_only_indirect, 'N') budget_only_indirect FROM smrs_protocol pr, onc_pcl_budget_params bp WHERE pr.protocol_id = bp.protocol_id ) pcl_budget_parameters on pcl_budget_parameters.protocol_id = pcl.protocol_id ) pcl_info on event_data.protocol_id = pcl_info.protocol_id --protocol privileges left join ( select protocol_id, (case when exists (select 1 from sv_user_pcl_permission where function_name = 'INVOICEABLE_ITEMS' and protocol_id = pcl.protocol_id and contact_id = $P{userContactId}) then 'Y' else 'N' end) has_invoiceable_items, (case when exists (select 1 from sv_user_pcl_permission where function_name = 'VISIT_VARIATIONS' and protocol_id = pcl.protocol_id and contact_id = $P{userContactId}) then 'Y' else 'N' end) has_visit_variations, ( case when exists ( select 1 from sv_user_pcl_permission where function_name in ('SUBJECT-IDENTIFICATION', 'SUBJECT-INITIALS-IDENTIFICATION') and protocol_id = pcl.protocol_id and contact_id = $P{userContactId} ) then 'Y' else 'N' end ) has_subject_identification from smrs_protocol pcl ) pcl_privileges on event_data.protocol_id = pcl_privileges.protocol_id --search parameters where ( occurred_date is null or TO_DATE(occurred_date, 'MM/DD/YYYY') between $P{fromDate} and $P{thruDate} ) and ( exists ( select 1 from sv_pcl_mgmt_mgmtgroup where protocol_id = sv_pcl_mgmt_mgmtgroup.protocol_id and $X{IN, mgmt_group_description, mgmtGroup} ) or ( $X{IN, 'ONLY_TRUE_IF_NO_MGNT_GROUPS_SPECIFIED', mgmtGroup} and not exists ( select 1 from sv_pcl_mgmt_mgmtgroup where protocol_id = sv_pcl_mgmt_mgmtgroup.protocol_id ) ) ) and ( smrs_pcl_detail.detail = pf_code.code_id and pf_code.category = 'DOWG' and $X{IN, pf_code.description, oncologyGroup} ) and ( smrs_pcl_department.department_id = onc_department.onc_department_id and $X{IN, onc_department.name, department} ) and ( $X{IN, smrs_protocol.protocol_no, protocol} ) and ( Type = 'Protocol Related' and ($X{IN, Exclusion, ProtocolInclude}) ) and ( Type = 'Protocol Related' and ($X{IN, sv_pcl_status.status, ProtocolStatus}) ) and ( Type = 'Visit Variation' and ($X{IN, Unplanned_Visit, Unplanned}) ) and ( Type = 'Visit Variation' and ($X{IN, sv_fin_visit_variations.additional, Additional}) ) and ( Type = 'Visit Variation' and ($X{IN, Visit_Status, VisitStatus}) )]]> </queryString> <field name="TYPE" class="java.lang.String"/> <field name="PROTOCOL_ID" class="java.math.BigDecimal"/> <field name="ITEM" class="java.lang.String"/> <field name="MILESTONE" class="java.lang.String"/> <field name="SEQUENCE_NUMBER" class="java.lang.String"/> <field name="INITIALS" class="java.lang.String"/> <field name="VISIT_STATUS" class="java.lang.String"/> <field name="OCCURRED_DATE" class="java.lang.String"/> <field name="DIRECT" class="java.math.BigDecimal"/> <field name="TOTAL_COST" class="java.math.BigDecimal"/> <field name="WITHHOLD" class="java.lang.String"/> <field name="WITHHOLDING_PCT" class="java.math.BigDecimal"/> <field name="SPONSOR_NAME" class="java.lang.String"/> <field name="INDIRECT_FLAG" class="java.lang.String"/> <field name="TRIGGER_TYPE" class="java.lang.String"/> <field name="REOCCURRING" class="java.lang.String"/> <field name="EXCLUSION" class="java.lang.String"/> <field name="COMMENTS" class="java.lang.String"/> <field name="STATUS" class="java.lang.String"/> <field name="UNPLANNED_VISIT" class="java.lang.String"/> <field name="ADDITIONAL" class="java.lang.String"/> <field name="EVAL_NOTES" class="java.lang.String"/> <field name="PROTOCOL_NO" class="java.lang.String"/> <field name="DEPARTMENTS" class="java.lang.String"/> <field name="MANAGEMENT_GROUPS" class="java.lang.String"/> <field name="ONCOLOGY_GROUPS" class="java.lang.String"/> <field name="PRIMARY_SPONSOR" class="java.lang.String"/> <field name="SECONDARY_SPONSORS" class="java.lang.String"/> <field name="INDIRECT_CUMULATIVE" class="java.lang.String"/> <field name="PROTOCOL_INDIRECT" class="java.lang.String"/> <field name="SUBJECT_INDIRECT" class="java.lang.String"/> <field name="MILESTONE_INDIRECT" class="java.lang.String"/> <field name="NOT_INDIRECT_MULTIPLIER" class="java.math.BigDecimal"/> <field name="INDIRECT_MULTIPLIER" class="java.math.BigDecimal"/> <field name="HAS_INVOICEABLE_ITEMS" class="java.lang.String"/> <field name="HAS_VISIT_VARIATIONS" class="java.lang.String"/> <field name="HAS_SUBJECT_IDENTIFICATION" class="java.lang.String"/> <field name="MORE_INFO" class="java.lang.String"/> <variable name="total_due" class="java.lang.String"> <variableExpression><![CDATA[new DecimalFormat("¤#,##0.00").format( $F{TOTAL_COST} == null ? ("Y".equals($F{INDIRECT_FLAG}) ? $F{DIRECT}.multiply($F{INDIRECT_MULTIPLIER}) : $F{DIRECT}) : $F{TOTAL_COST} )]]></variableExpression> </variable> <variable name="initials_filtered" class="java.lang.String"> <variableExpression><![CDATA[$F{INITIALS} == null ? "" : ("Y".equals($F{HAS_SUBJECT_IDENTIFICATION}) ? $F{INITIALS} : "**")]]></variableExpression> </variable> <variable name="More_Info" class="java.lang.String"> <variableExpression><![CDATA[$F{COMMENTS}+$F{EVAL_NOTES}]]></variableExpression> </variable> <group name="Group1"/> <group name="Group2"/> <group name="Group3"/> <group name="Group4"/> <title> <band height="102"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <frame> <reportElement mode="Opaque" x="0" y="0" width="1434" height="72" backcolor="#006699" uuid="2f9e325f-5726-45f7-980d-45df3aefc4ea"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <staticText> <reportElement x="0" y="0" width="946" height="72" forecolor="#FFFFFF" uuid="c8271c97-a3f6-4dc1-83e0-f40231bdab3f"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textElement verticalAlignment="Middle"> <font size="34" isBold="true"/> </textElement> <text><![CDATA[invoiceable Items]]></text> </staticText> <staticText> <reportElement x="1065" y="0" width="365" height="72" forecolor="#FFFFFF" uuid="c89de18d-0f2f-4fe9-a2d2-6cbee0c1c1e7"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Right" verticalAlignment="Middle"> <font size="14" isBold="false"/> </textElement> <text><![CDATA[All protocols, by item type]]></text> </staticText> </frame> <frame> <reportElement stretchType="RelativeToTallestObject" mode="Opaque" x="0" y="72" width="1434" height="30" backcolor="#006699" uuid="a9a52697-082e-42e1-89ec-68dd0b049098"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <staticText> <reportElement stretchType="RelativeToTallestObject" x="0" y="0" width="88" height="30" forecolor="#FFFFFF" uuid="3afa32d5-2f7d-4eb8-a131-28fdda4bc83b"/> <textElement textAlignment="Right"/> <text><![CDATA[start Date:]]></text> </staticText> <staticText> <reportElement stretchType="RelativeToTallestObject" x="167" y="0" width="106" height="30" forecolor="#FFFFFF" uuid="2d71ca4b-6729-46eb-a0a4-ab7decdab5c6"/> <textElement textAlignment="Right"/> <text><![CDATA[End Date:]]></text> </staticText> <staticText> <reportElement stretchType="RelativeToTallestObject" x="345" y="0" width="79" height="30" forecolor="#FFFFFF" uuid="88137e82-ed30-486c-90f6-8cc42aa0aae8"/> <textElement textAlignment="Right"/> <text><![CDATA[Management Group:]]></text> </staticText> <staticText> <reportElement stretchType="RelativeToTallestObject" x="489" y="0" width="85" height="30" forecolor="#FFFFFF" uuid="93aec992-5a53-422e-9d64-77415d1aca57"/> <textElement textAlignment="Right"/> <text><![CDATA[Oncology Group:]]></text> </staticText> <staticText> <reportElement stretchType="RelativeToTallestObject" x="658" y="0" width="77" height="30" forecolor="#FFFFFF" uuid="dbbde861-1ed7-4b0b-b0da-a0414790be83"/> <textElement textAlignment="Right"/> <text><![CDATA[Department:]]></text> </staticText> <textField pattern="MM/dd/yyyy"> <reportElement stretchType="RelativeToTallestObject" x="88" y="0" width="79" height="30" forecolor="#FFFFFF" uuid="55296cab-b55c-4bfa-95de-7c268febe10e"/> <textFieldExpression><![CDATA[$P{fromDate}]]></textFieldExpression> </textField> <textField pattern="MM/dd/yyyy"> <reportElement stretchType="RelativeToTallestObject" x="273" y="0" width="72" height="30" forecolor="#FFFFFF" uuid="c091402d-3826-4336-a1f3-95cdc021eaaa"/> <textFieldExpression><![CDATA[$P{thruDate}]]></textFieldExpression> </textField> <textField isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" x="424" y="0" width="65" height="30" forecolor="#FFFFFF" uuid="d52ae49d-59a9-48b8-a32e-5e266c957752"/> <textFieldExpression><![CDATA[$P{mgmtGroup}]]></textFieldExpression> </textField> <textField isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" x="574" y="0" width="84" height="30" forecolor="#FFFFFF" uuid="a35aeb70-4c24-40fa-94e0-b0080aa8b983"/> <textFieldExpression><![CDATA[$P{oncologyGroup}]]></textFieldExpression> </textField> <textField isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" x="736" y="0" width="100" height="30" forecolor="#FFFFFF" uuid="d735e804-c8ec-4e05-b8d8-e98f1ab80fb3"/> <textFieldExpression><![CDATA[$P{department}]]></textFieldExpression> </textField> </frame> </band> </title> <columnHeader> <band height="27" splitType="Stretch"> <staticText> <reportElement mode="Opaque" x="0" y="0" width="89" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="b1dcfa37-fe41-4dd3-ba8c-1132cca71ed2"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Type]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="89" y="0" width="78" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="1928f95a-28f0-4497-995c-e008308bff8c"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Mgmt. Groups]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="167" y="0" width="106" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="9e8369e1-45fe-4a67-845f-0a4fe441169e"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Working Groups]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="273" y="0" width="72" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="fa7b5671-3185-4328-ba28-fa3f57d38c95"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Departments]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="345" y="0" width="83" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="8448a272-89ca-4966-8527-f2c38d4d6fa0"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Protocol No.]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="936" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="84e996e6-c9a7-427e-85da-56127c21d313"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Milestone]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="680" y="0" width="78" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5c8d0443-f637-4202-9259-38a4122fc2b7"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[item]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1036" y="0" width="59" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="7939812e-20b2-4f33-ba4a-8ea25a774a7b"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[seq. No.]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1095" y="0" width="51" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5031c035-52c7-43ab-91f3-68148a2b3ba5"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[initials]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1146" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="f340c609-345e-4ae2-840c-96de64b1a3e2"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Occurred Date]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1246" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5fa29d59-5626-41fa-a7d6-e28fb34032ff"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Negotiated Cost]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1346" y="0" width="88" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="a8adf438-22fa-4ba1-956c-8e9df0c11458"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Total Due]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="428" y="0" width="83" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5e7fd45d-792f-499e-83b9-e2075c938224"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Principal Sponsor]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="511" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="4a6ebe9b-a072-4f48-bf3b-a8347dc047b9"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Other Sponsors]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="597" y="0" width="83" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="c591bc04-dc74-41d8-94d7-e2aab81e21fc"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[item Sponsor]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="858" y="0" width="78" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="6822b1bc-3d29-4e50-a96a-0be70ea18855"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Visit Status]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="758" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="2c3fed66-2675-4f75-96f9-d67194495e1d"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <text><![CDATA[Comments/Eval Notes]]></text> </staticText> </band> </columnHeader> <detail> <band height="32" splitType="Prevent"> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" x="0" y="0" width="89" height="27" uuid="3f984aa3-f917-4330-85e8-5a07cc66650a"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{TYPE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="89" y="0" width="78" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="da3929a7-d752-4ba9-830c-fe93ef1cdc22"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{MANAGEMENT_GROUPS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="167" y="0" width="106" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="1257da62-9027-4101-b32c-504e606b701a"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{ONCOLOGY_GROUPS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="273" y="0" width="72" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="dea22672-42b8-4c4e-bca6-d0eebfcd4181"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{DEPARTMENTS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="345" y="0" width="83" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="e5b65c06-b8c8-47fb-b1fa-a1052e4a2111"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{PROTOCOL_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="680" y="0" width="78" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="613bbd4e-6774-4cce-ad88-7e7fa8b3077d"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{ITEM}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1036" y="0" width="59" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="e1767861-73f9-47ca-ab3f-f59b839220d5"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{SEQUENCE_NUMBER}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="936" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="17078554-b751-42c3-9b9d-7cf920281e08"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{MILESTONE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1146" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="7019a529-e225-4ebd-9182-a1fde914daf1"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{OCCURRED_DATE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1246" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="54f419e4-ad0d-484c-9902-eb4f5a76cd30"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[new DecimalFormat("¤#,##0.00").format($F{DIRECT})]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1346" y="0" width="88" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="8b8c19d6-ea68-4c05-8f6e-61ae94369082"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$V{total_due}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="428" y="0" width="83" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="9a905988-f100-45d0-9b3b-0fac589372a3"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <textFieldExpression><![CDATA[$F{PRIMARY_SPONSOR}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="511" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="ccf720d2-779b-4bc4-b1b4-3fa253629c5d"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <textFieldExpression><![CDATA[$F{SECONDARY_SPONSORS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="597" y="0" width="83" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="14dcbcda-6498-412f-b21e-a3fcb5018502"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{SPONSOR_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1095" y="0" width="51" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="e3d0affb-afff-4270-8bc0-3172f3b15fb5"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <textFieldExpression><![CDATA[$V{initials_filtered}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="858" y="0" width="78" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="a7830fc3-d9b8-4790-85d8-7810a9591f0d"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{VISIT_STATUS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="758" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="c9073fec-8b08-4620-bf9d-82060c93f0e4"/> <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$F{MORE_INFO}]]></textFieldExpression> </textField> </band> </detail> <pageFooter> <band height="25" splitType="Prevent"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement mode="Opaque" x="0" y="0" width="89" height="25" backcolor="#E6E6E6" uuid="00b71e79-08b5-44a3-af35-e114fc18433b"> <property name="local_mesure_unity" value="pixel"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textElement textAlignment="Right" verticalAlignment="Middle"/> <text><![CDATA[Report Date:]]></text> </staticText> <textField pattern="MM/dd/yyyy"> <reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="89" y="0" width="1155" height="25" backcolor="#E6E6E6" uuid="4306bff6-d9b7-498f-8124-8c583de632a9"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Left" verticalAlignment="Middle"> <paragraph firstLineIndent="20"/> </textElement> <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression> </textField> <textField isBlankWhenNull="false"> <reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="1244" y="0" width="100" height="25" forecolor="#000000" backcolor="#E6E6E6" uuid="16866726-e0e9-4b0b-86b7-17528b27e5e7"> <property name="com.jaspersoft.studio.unit.x" value="px"/> </reportElement> <textElement textAlignment="Right" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of "]]></textFieldExpression> </textField> <textField evaluationTime="Report" isBlankWhenNull="false"> <reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="1344" y="0" width="90" height="25" forecolor="#000000" backcolor="#E6E6E6" uuid="43c13917-328f-49cc-84e0-fd0ca42186ba"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box leftPadding="2"/> <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression> </textField> </band> </pageFooter> </jasperReport>

I'm new to Jaspersoft reports, but I'm using Jaspersoft Studio 6.9.0, and modifying a report to add some additional Parameters, per a usser's request. I can't preview the report, because I'm getting the following message "Error generating report." with the errors below. If just run the SQL with the lines including parameters commented out, the code runs fine. If I delete anything related to parameters in the JRXML file, the report will complete. I've deleted parameters one by one to see if I could figure out what the cause of the error was, but that hasn't been helpful. Do these errors mean anything to anyone? net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: java.sql.SQLRecoverableException: IO Error: Connection reset by peer: socket write error at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:550) at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$18(ReportController.java:525) at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:443) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: net.sf.jasperreports.engine.JRException: java.sql.SQLRecoverableException: IO Error: Connection reset by peer: socket write error at net.sf.jasperreports.data.jdbc.JdbcDataAdapterService.contributeParameters(JdbcDataAdapterService.java:133) at net.sf.jasperreports.engine.fill.JRFillDataset.contributeParameters(JRFillDataset.java:1153) at net.sf.jasperreports.engine.fill.JRFillDataset.setParameterValues(JRFillDataset.java:665) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:455) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:578) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135) at java.lang.Thread.run(Thread.java:748) Caused by: java.sql.SQLRecoverableException: IO Error: Connection reset by peer: socket write error at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:421) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:531) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503) at net.sf.jasperreports.data.jdbc.JdbcDataAdapterService.getConnection(JdbcDataAdapterService.java:171) at net.sf.jasperreports.data.jdbc.JdbcDataAdapterService.contributeParameters(JdbcDataAdapterService.java:131) ... 6 more Caused by: java.net.SocketException: Connection reset by peer: socket write error at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111) at java.net.SocketOutputStream.write(SocketOutputStream.java:155) at oracle.net.ns.DataPacket.send(DataPacket.java:199) at oracle.net.ns.NetOutputStream.flush(NetOutputStream.java:211) at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:227) at oracle.net.ns.NetInputStream.read(NetInputStream.java:175) at oracle.net.ns.NetInputStream.read(NetInputStream.java:100) at oracle.net.ns.NetInputStream.read(NetInputStream.java:85) at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:122) at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:78) at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1179) at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:366) at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:752) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:359) ... 12 more