Jump to content

jburczyk

Members
  • Posts

    20
  • Joined

  • Last visited

jburczyk's Achievements

Explorer

Explorer (4/14)

  • Week One Done
  • One Month Later
  • One Year In
  • First Post Rare
  • Collaborator Rare

Recent Badges

0

Reputation

  1. 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.
  2. 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.
  3. 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.
  4. 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
  5. 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?
  6. 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>
  7. 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.
  8. 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.
  9. 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.
  10. 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.
  11. 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?
  12. 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>
  13. 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?
  14. 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>
×
×
  • Create New...