jburczyk Posted September 26, 2019 Share Posted September 26, 2019 The report below works, but I've been tasked with finding a way to display data according to the Start Date and End Date for *either* the SV_SUB_CALENDAR_VISIT_DATE field or the SV_FIN_INVOICE_OCCURRED_DATE field. I originally thought that maybe I could create a Parameter and require users to enter either "Visit" or "Occurred"...and then I created a variable that says IF($P{Date}="Visit", $F{SV_SUB_CALENDAR_VISIT_DATE},$F{SV_FIN_INVOICE_OCCURRED_DATE}). The issue is, I don't think there's a way to make the report filter dates based on this variable. Suggestions? <?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.9.0.final using JasperReports Library version 6.9.0-cb8f9004be492ccc537180b49c026951f4220bf3 --><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="4CRU_ProcBill" pageWidth="1349" pageHeight="802" columnWidth="1349" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isSummaryWithPageHeaderAndFooter="true" isIgnorePagination="true" uuid="f1a362a1-9fdc-49a5-8b3a-e7554a779578"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <parameter name="Start Date" class="java.util.Date"/> <parameter name="End Date" class="java.util.Date"/> <queryString> <![CDATA[sELECT DISTINCT SV_SUB_ONSTUDY.PROTOCOL_NO AS SV_SUB_ONSTUDY_PROTOCOL_NO, SV_SUB_CALENDAR.VISIT_DATE AS SV_SUB_CALENDAR_VISIT_DATE, SV_FIN_INVOICE_ITEMS.OCCURRED_DATE AS SV_FIN_INVOICE_OCCURRED_DATE, SV_PCL_MGMT_STAFF.CONTACT_ID AS SV_PCL_MGMT_STAFF_CONTACT_ID, SV_PCL_MGMT_STAFF.LAST_NAME AS SV_PCL_MGMT_STAFF_LAST_NAME, SV_PCL_MGMT_STAFF.FIRST_NAME AS SV_PCL_MGMT_STAFF_FIRST_NAME, SV_SUB_ONSTUDY.SEQUENCE_NUMBER AS SV_SUB_ONSTUDY_SEQUENCE_NUMBER, SV_FIN_SUB_EVENTS.EVENT_CODE AS EVENT_CODE, SV_SS_EVAL_SCHED.PK_NUMBER AS SV_SS_EVAL_SCHED_PK_NUMBER, SV_SUB_CALENDAR.VISIT_NAME AS SV_SUB_CALENDAR_VISIT_NAME, SV_PCL_MGMT_DTL.GCRC_NO AS SV_PCL_MGMT_DTL_GCRC_NO, SV_SUB_CALENDAR.SD_PCS_TRACKING_ID AS SV_SUB_CALENDAR_SD_PCS_TRACKIN, SV_FIN_SUB_EVENT_COST_CENTERS.COST_CENTER_CODE AS SV_FIN_SUB_EVENT_COST_CENTERS_, SV_FIN_SUB_EVENTS.AS_NEEDED_PROCEDURE AS AS_NEEDED_PROCEDURE, SV_PCS_VISIT_EVALUATIONS.MISSED_FLAG AS SV_PCS_VISIT_MISSED_FLAG, SV_PCS_VISIT_EVALUATIONS.MISSED_COUNT AS SV_PCS_VISIT_MISSEDCOUNT, SV_PCS_VISIT_EVALUATIONS.NOT_APPLICABLE AS SV_PCS_VISIT_NA_FLAGFROM 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_IDWHERE SV_SUB_CALENDAR.VISIT_DATE Between $P{Start Date} and $P{End Date} AND SV_PCL_MGMT_DTL.GCRC_APPROVAL = 'Yes' AND SV_PCL_MGMT_STAFF.STAFF_ROLE = 'Principal Investigator' AND SV_PCL_MGMT_STAFF.STOP_DATE IS null AND SV_SUB_CALENDAR.VISIT_STATUS = 'Acknowledged' AND SV_SUB_CALENDAR.MISSED_FLAG = 'N' AND SV_SUB_CALENDAR.NA_FLAG = 'N' AND SV_FIN_SUB_EVENT_COST_CENTERS.COST_CENTER_CODE = '6'ORDER BY SV_SUB_ONSTUDY.PROTOCOL_NO ASC, SV_SUB_CALENDAR.VISIT_DATE ASC, SV_SUB_CALENDAR.SD_PCS_TRACKING_ID ASC]]> </queryString> <field name="SV_SUB_ONSTUDY_PROTOCOL_NO" class="java.lang.String"/> <field name="SV_SUB_CALENDAR_VISIT_DATE" class="java.sql.Timestamp"/> <field name="SV_FIN_INVOICE_OCCURRED_DATE" class="java.sql.Timestamp"/> <field name="SV_PCL_MGMT_STAFF_CONTACT_ID" class="java.math.BigDecimal"/> <field name="SV_PCL_MGMT_STAFF_LAST_NAME" class="java.lang.String"/> <field name="SV_PCL_MGMT_STAFF_FIRST_NAME" class="java.lang.String"/> <field name="SV_SUB_ONSTUDY_SEQUENCE_NUMBER" class="java.lang.String"/> <field name="EVENT_CODE" class="java.lang.String"/> <field name="SV_SS_EVAL_SCHED_PK_NUMBER" class="java.math.BigDecimal"/> <field name="SV_SUB_CALENDAR_VISIT_NAME" class="java.lang.String"/> <field name="SV_PCL_MGMT_DTL_GCRC_NO" class="java.lang.String"/> <field name="SV_SUB_CALENDAR_SD_PCS_TRACKIN" class="java.math.BigDecimal"/> <field name="SV_FIN_SUB_EVENT_COST_CENTERS_" class="java.lang.String"/> <field name="AS_NEEDED_PROCEDURE" class="java.lang.String"/> <field name="SV_PCS_VISIT_MISSED_FLAG" class="java.lang.String"/> <field name="SV_PCS_VISIT_MISSEDCOUNT" class="java.math.BigDecimal"/> <field name="SV_PCS_VISIT_NA_FLAG" class="java.lang.String"/> $F{SV_FIN_INVOICE_OCCURRED_DATE})]]></variableExpression> </variable> <background> <band/> </background> <columnHeader> <band height="26"> <staticText> <reportElement mode="Opaque" x="0" y="0" width="89" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="7508dd54-3f29-42a8-ae6b-8a5a713f4f3b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PROTOCOL]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="89" y="0" width="43" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="b749554f-019f-4d04-97df-41873789595f"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PI_ID]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="132" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="d7b75036-4f24-44e0-afa5-faba76d34fe5"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[LAST_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="219" y="0" width="78" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="c6892498-32ff-4f36-b6bd-3ac390880317"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[FIRST_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="297" y="0" width="65" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="0b605991-09f4-4e69-8007-92a899ddbb5e"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[VISIT_DATE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="428" y="0" width="107" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="1f5bc066-46d2-4338-a7bc-3cd7f92acf8b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[sEQUENCE_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="535" y="0" width="94" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="b3740ae2-afe7-4521-b208-707a838081f0"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[EVENT_CODE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="629" y="0" width="46" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="c518a85d-5ff9-4dce-94d2-84b4f09e7764"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PK_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="675" y="0" width="172" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="99f1fb94-5787-4e0f-8a72-69b3fbbe708f"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[VISIT_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="847" y="0" width="63" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="32908272-a042-449c-ba69-ed3442089c7b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[CCTS_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="910" y="0" width="57" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="685667c6-c5d4-4299-a15c-591807e97f77"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[TRACK_ID]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="967" y="0" width="64" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="f850847d-f9f3-4b58-a2d1-0e3bcf512d39"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[CC_CODE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1031" y="0" width="64" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="09e48d12-14fd-4ed0-b422-35841d97b889"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[AN_PROC]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1095" y="0" width="81" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="4db0df45-141b-4503-860c-0209a1b690bb"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[MISSED_FLAG]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1176" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="fd900ad6-6cde-476a-ba3b-a6bf929bbd7d"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[MISSEDCOUNT]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1263" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="f0e00962-ab0a-4d6c-95f9-026f1f4b3706"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[NA_FLAG]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="363" y="0" width="65" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="12d78fc1-7473-4ee9-8e45-eac36cd80095"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[OCCURRED_DATE]]></text> </staticText> </band> </columnHeader> <detail> <band height="25"> <textField isStretchWithOverflow="true"> <reportElement x="0" y="0" width="89" height="20" uuid="e1eac73e-0817-4bbe-95fe-ddb865d413cb"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_ONSTUDY_PROTOCOL_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="89" y="0" width="43" height="20" uuid="cb7d64fa-ef17-4d75-8599-c10cdfa558df"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_CONTACT_ID}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="132" y="0" width="87" height="20" uuid="3a13bf53-dbc7-4d45-b29c-989a72a46bf4"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_LAST_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="219" y="0" width="78" height="20" uuid="1b28cd17-9ad3-4af7-b284-630cede8358c"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_FIRST_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy"> <reportElement x="297" y="0" width="65" height="20" uuid="13a6e1b5-f2f9-4145-8fda-9ca56daf0fc6"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_VISIT_DATE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="428" y="0" width="107" height="20" uuid="1de985a5-cfb1-4cc6-8d14-d19b147c7836"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_ONSTUDY_SEQUENCE_NUMBER}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="535" y="0" width="94" height="20" uuid="4f5d9e7d-ebe0-4f5c-98b0-0f6aa6b09b40"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{EVENT_CODE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="629" y="0" width="46" height="20" uuid="1305d21a-b083-4b26-aac8-946a3eaf0aff"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SS_EVAL_SCHED_PK_NUMBER}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="675" y="0" width="172" height="20" uuid="0cf412bf-4be6-4623-8c5d-2c140a7d7508"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_VISIT_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="847" y="0" width="63" height="20" uuid="e0340399-9749-46e0-bd6e-1477bfb7c3af"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_DTL_GCRC_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="910" y="0" width="57" height="20" uuid="0445b27b-9ed7-4de8-bd3e-9af2bb10d3f9"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_SD_PCS_TRACKIN}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="967" y="0" width="64" height="20" uuid="7ca595ce-b63e-47b2-af06-13091d49fd6d"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_FIN_SUB_EVENT_COST_CENTERS_}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1031" y="0" width="64" height="20" uuid="8d88059c-656a-4ab0-bad2-147b31e301a7"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{AS_NEEDED_PROCEDURE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1095" y="0" width="81" height="20" uuid="791a0d80-11b2-45a0-acc4-59bf40d1fbaf"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_MISSED_FLAG}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1176" y="0" width="87" height="20" uuid="b49d9a0f-c552-4404-aac4-023a46707bbc"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_MISSEDCOUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1263" y="0" width="87" height="20" uuid="ab9fba32-fab3-4098-be25-303117e8e45d"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_NA_FLAG}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy"> <reportElement x="363" y="0" width="65" height="20" uuid="45d696b2-729c-484d-8739-967767e67b05"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_FIN_INVOICE_OCCURRED_DATE}]]></textFieldExpression> </textField> </band> </detail> <summary> <band/> </summary></jasperReport> Link to comment Share on other sites More sharing options...
C-Box Posted September 27, 2019 Share Posted September 27, 2019 Just create a (hidden - not for input) parameter $P{JustDateParam4SQL} for your dynamic date query depending on your input choice param $P{Date}:In this "calced" Parameter you use as Default Expression an IfThenElse Syntax:"Visit".equals($P{Date}) ? "SV_SUB_CALENDAR.VISIT_DATE Between $P{Start Date} and $P{End Date}" : "SV_FIN_INVOICE_OCCURRED_DATE Between $P{Start Date} and $P{End Date}"in your query you could then use the calculated Parameter:...WHERE $!P{JustDateParam4SQL) ....Not 100% sure where exactly the exlamation mark was placed $!P or $P! ... but just give it a try. Should work (I've built some years something similiar in a customer project).hth + regardsC-Box Link to comment Share on other sites More sharing options...
jburczyk Posted September 27, 2019 Author Share Posted September 27, 2019 Ok - thank you for this suggestion - I've added the following to the list of Parameters, using "Select" as the name for this new parameter you suggested: <parameter name="Date" class="java.lang.String"/> <parameter name="Select" class="java.lang.String"> <defaultValueExpression><![CDATA["Date Variable".equals($P{Date}) ? "SV_SUB_CALENDAR.VISIT_DATE Between $P{Start Date} and $P{End Date}" : "SV_FIN_INVOICE_OCCURRED_DATE Between $P{Start Date} and $P{End Date}"]]></defaultValueExpression> </parameter>I've added this to the where clause: $P!{Select} Between $P{Start Date} and $P{End Date} And I've added the Variable I mentioned: <variable name="Date Variable" class="java.lang.String"> <variableExpression><![CDATA[iF($P{Date}="Visit", $F{SV_SUB_CALENDAR_VISIT_DATE},$F{SV_FIN_INVOICE_OCCURRED_DATE})]]></variableExpression> </variable> And now I'm getting the following errors. net.sf.jasperreports.engine.JRException: Errors were encountered when compiling report expressions class file:1. The left-hand side of an assignment must be a variable value = IF(((java.lang.String)parameter_Date.getValue())="Visit", ((java.sql.Timestamp)field_SV_SUB_CALENDAR_VISIT_DATE.getValue()), //$JR_EXPR_ID=9$ <------------------------------------------->2. The left-hand side of an assignment must be a variable value = IF(((java.lang.String)parameter_Date.getValue())="Visit", ((java.sql.Timestamp)field_SV_SUB_CALENDAR_VISIT_DATE.getOldValue()), //$JR_EXPR_ID=9$ <------------------------------------------->3. The left-hand side of an assignment must be a variable value = IF(((java.lang.String)parameter_Date.getValue())="Visit", ((java.sql.Timestamp)field_SV_SUB_CALENDAR_VISIT_DATE.getValue()), //$JR_EXPR_ID=9$ <------------------------------------------->3 errors Link to comment Share on other sites More sharing options...
jburczyk Posted September 27, 2019 Author Share Posted September 27, 2019 I've continued playing with the code based on the errors I'm getting, and now I don't know what else to do here...the new error I'm getting is:net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: 4CRU_ProcBill. at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:550) at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$18(ReportController.java:525) at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:443) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: 4CRU_ProcBill. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:357) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1257) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:726) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:457) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:578) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135) at java.lang.Thread.run(Thread.java:748)Caused by: java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:312) ... 6 moreHere's the code:<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.9.0.final using JasperReports Library version 6.9.0-cb8f9004be492ccc537180b49c026951f4220bf3 --><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="4CRU_ProcBill" pageWidth="1349" pageHeight="802" columnWidth="1349" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isSummaryWithPageHeaderAndFooter="true" isIgnorePagination="true" uuid="f1a362a1-9fdc-49a5-8b3a-e7554a779578"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="OnCore Test2"/> <parameter name="TypeDate" class="java.lang.String"/> <parameter name="Choose" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["Visit".equals($P{TypeDate}) ? "SV_SUB_CALENDAR.VISIT_DATE Between $P{StartDate} and $P{EndDate}" : "SV_FIN_INVOICE_OCCURRED_DATE Between $P{StartDate} and $P{EndDate}"]]></defaultValueExpression> </parameter> <parameter name="StartDate" class="java.util.Date"/> <parameter name="EndDate" class="java.util.Date"/> <queryString> <![CDATA[sELECT DISTINCT SV_SUB_ONSTUDY.PROTOCOL_NO AS SV_SUB_ONSTUDY_PROTOCOL_NO, SV_SUB_CALENDAR.VISIT_DATE AS SV_SUB_CALENDAR_VISIT_DATE, SV_FIN_INVOICE_ITEMS.OCCURRED_DATE AS SV_FIN_INVOICE_OCCURRED_DATE, SV_PCL_MGMT_STAFF.CONTACT_ID AS SV_PCL_MGMT_STAFF_CONTACT_ID, SV_PCL_MGMT_STAFF.LAST_NAME AS SV_PCL_MGMT_STAFF_LAST_NAME, SV_PCL_MGMT_STAFF.FIRST_NAME AS SV_PCL_MGMT_STAFF_FIRST_NAME, SV_SUB_ONSTUDY.SEQUENCE_NUMBER AS SV_SUB_ONSTUDY_SEQUENCE_NUMBER, SV_FIN_SUB_EVENTS.EVENT_CODE AS EVENT_CODE, SV_SS_EVAL_SCHED.PK_NUMBER AS SV_SS_EVAL_SCHED_PK_NUMBER, SV_SUB_CALENDAR.VISIT_NAME AS SV_SUB_CALENDAR_VISIT_NAME, SV_PCL_MGMT_DTL.GCRC_NO AS SV_PCL_MGMT_DTL_GCRC_NO, SV_SUB_CALENDAR.SD_PCS_TRACKING_ID AS SV_SUB_CALENDAR_SD_PCS_TRACKIN, SV_FIN_SUB_EVENT_COST_CENTERS.COST_CENTER_CODE AS SV_FIN_SUB_EVENT_COST_CENTERS_, SV_FIN_SUB_EVENTS.AS_NEEDED_PROCEDURE AS AS_NEEDED_PROCEDURE, SV_PCS_VISIT_EVALUATIONS.MISSED_FLAG AS SV_PCS_VISIT_MISSED_FLAG, SV_PCS_VISIT_EVALUATIONS.MISSED_COUNT AS SV_PCS_VISIT_MISSEDCOUNT, SV_PCS_VISIT_EVALUATIONS.NOT_APPLICABLE AS SV_PCS_VISIT_NA_FLAGFROM 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_IDWHERE $P!{Choose} Between $P{StartDate} and $P{EndDate} AND SV_PCL_MGMT_DTL.GCRC_APPROVAL = 'Yes' AND SV_PCL_MGMT_STAFF.STAFF_ROLE = 'Principal Investigator' AND SV_PCL_MGMT_STAFF.STOP_DATE IS null AND SV_SUB_CALENDAR.VISIT_STATUS = 'Acknowledged' AND SV_SUB_CALENDAR.MISSED_FLAG = 'N' AND SV_SUB_CALENDAR.NA_FLAG = 'N' AND SV_FIN_SUB_EVENT_COST_CENTERS.COST_CENTER_CODE = '6'ORDER BY SV_SUB_ONSTUDY.PROTOCOL_NO ASC, SV_SUB_CALENDAR.VISIT_DATE ASC, SV_SUB_CALENDAR.SD_PCS_TRACKING_ID ASC]]> </queryString> <field name="SV_SUB_ONSTUDY_PROTOCOL_NO" class="java.lang.String"/> <field name="SV_SUB_CALENDAR_VISIT_DATE" class="java.sql.Timestamp"/> <field name="SV_FIN_INVOICE_OCCURRED_DATE" class="java.sql.Timestamp"/> <field name="SV_PCL_MGMT_STAFF_CONTACT_ID" class="java.math.BigDecimal"/> <field name="SV_PCL_MGMT_STAFF_LAST_NAME" class="java.lang.String"/> <field name="SV_PCL_MGMT_STAFF_FIRST_NAME" class="java.lang.String"/> <field name="SV_SUB_ONSTUDY_SEQUENCE_NUMBER" class="java.lang.String"/> <field name="EVENT_CODE" class="java.lang.String"/> <field name="SV_SS_EVAL_SCHED_PK_NUMBER" class="java.math.BigDecimal"/> <field name="SV_SUB_CALENDAR_VISIT_NAME" class="java.lang.String"/> <field name="SV_PCL_MGMT_DTL_GCRC_NO" class="java.lang.String"/> <field name="SV_SUB_CALENDAR_SD_PCS_TRACKIN" class="java.math.BigDecimal"/> <field name="SV_FIN_SUB_EVENT_COST_CENTERS_" class="java.lang.String"/> <field name="AS_NEEDED_PROCEDURE" class="java.lang.String"/> <field name="SV_PCS_VISIT_MISSED_FLAG" class="java.lang.String"/> <field name="SV_PCS_VISIT_MISSEDCOUNT" class="java.math.BigDecimal"/> <field name="SV_PCS_VISIT_NA_FLAG" class="java.lang.String"/> <variable name="Date Variable" class="java.lang.String"> <variableExpression><![CDATA[iF($P{TypeDate}=="Visit", $F{SV_SUB_CALENDAR_VISIT_DATE},$F{SV_FIN_INVOICE_OCCURRED_DATE})]]></variableExpression> </variable> <background> <band/> </background> <columnHeader> <band height="26"> <staticText> <reportElement mode="Opaque" x="0" y="0" width="89" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="7508dd54-3f29-42a8-ae6b-8a5a713f4f3b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PROTOCOL]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="89" y="0" width="43" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="b749554f-019f-4d04-97df-41873789595f"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PI_ID]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="132" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="d7b75036-4f24-44e0-afa5-faba76d34fe5"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[LAST_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="219" y="0" width="78" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="c6892498-32ff-4f36-b6bd-3ac390880317"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[FIRST_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="297" y="0" width="65" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="0b605991-09f4-4e69-8007-92a899ddbb5e"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[VISIT_DATE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="428" y="0" width="107" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="1f5bc066-46d2-4338-a7bc-3cd7f92acf8b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[sEQUENCE_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="535" y="0" width="94" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="b3740ae2-afe7-4521-b208-707a838081f0"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[EVENT_CODE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="629" y="0" width="46" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="c518a85d-5ff9-4dce-94d2-84b4f09e7764"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[PK_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="675" y="0" width="172" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="99f1fb94-5787-4e0f-8a72-69b3fbbe708f"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[VISIT_NAME]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="847" y="0" width="63" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="32908272-a042-449c-ba69-ed3442089c7b"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[CCTS_NO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="910" y="0" width="57" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="685667c6-c5d4-4299-a15c-591807e97f77"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[TRACK_ID]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="967" y="0" width="64" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="f850847d-f9f3-4b58-a2d1-0e3bcf512d39"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[CC_CODE]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1031" y="0" width="64" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="09e48d12-14fd-4ed0-b422-35841d97b889"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[AN_PROC]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1095" y="0" width="81" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="4db0df45-141b-4503-860c-0209a1b690bb"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[MISSED_FLAG]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1176" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="fd900ad6-6cde-476a-ba3b-a6bf929bbd7d"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[MISSEDCOUNT]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="1263" y="0" width="87" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="f0e00962-ab0a-4d6c-95f9-026f1f4b3706"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[NA_FLAG]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="363" y="0" width="65" height="21" forecolor="#006699" backcolor="#E6E6E6" uuid="12d78fc1-7473-4ee9-8e45-eac36cd80095"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Center"> <font size="10" isBold="true"/> </textElement> <text><![CDATA[OCCURRED_DATE]]></text> </staticText> </band> </columnHeader> <detail> <band height="25"> <textField isStretchWithOverflow="true"> <reportElement x="0" y="0" width="89" height="20" uuid="e1eac73e-0817-4bbe-95fe-ddb865d413cb"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_ONSTUDY_PROTOCOL_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="89" y="0" width="43" height="20" uuid="cb7d64fa-ef17-4d75-8599-c10cdfa558df"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_CONTACT_ID}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="132" y="0" width="87" height="20" uuid="3a13bf53-dbc7-4d45-b29c-989a72a46bf4"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_LAST_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="219" y="0" width="78" height="20" uuid="1b28cd17-9ad3-4af7-b284-630cede8358c"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_STAFF_FIRST_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy"> <reportElement x="297" y="0" width="65" height="20" uuid="13a6e1b5-f2f9-4145-8fda-9ca56daf0fc6"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_VISIT_DATE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="428" y="0" width="107" height="20" uuid="1de985a5-cfb1-4cc6-8d14-d19b147c7836"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_ONSTUDY_SEQUENCE_NUMBER}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="535" y="0" width="94" height="20" uuid="4f5d9e7d-ebe0-4f5c-98b0-0f6aa6b09b40"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{EVENT_CODE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="629" y="0" width="46" height="20" uuid="1305d21a-b083-4b26-aac8-946a3eaf0aff"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SS_EVAL_SCHED_PK_NUMBER}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="675" y="0" width="172" height="20" uuid="0cf412bf-4be6-4623-8c5d-2c140a7d7508"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_VISIT_NAME}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="847" y="0" width="63" height="20" uuid="e0340399-9749-46e0-bd6e-1477bfb7c3af"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCL_MGMT_DTL_GCRC_NO}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="910" y="0" width="57" height="20" uuid="0445b27b-9ed7-4de8-bd3e-9af2bb10d3f9"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_SUB_CALENDAR_SD_PCS_TRACKIN}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="967" y="0" width="64" height="20" uuid="7ca595ce-b63e-47b2-af06-13091d49fd6d"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_FIN_SUB_EVENT_COST_CENTERS_}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1031" y="0" width="64" height="20" uuid="8d88059c-656a-4ab0-bad2-147b31e301a7"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{AS_NEEDED_PROCEDURE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1095" y="0" width="81" height="20" uuid="791a0d80-11b2-45a0-acc4-59bf40d1fbaf"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_MISSED_FLAG}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1176" y="0" width="87" height="20" uuid="b49d9a0f-c552-4404-aac4-023a46707bbc"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_MISSEDCOUNT}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true"> <reportElement x="1263" y="0" width="87" height="20" uuid="ab9fba32-fab3-4098-be25-303117e8e45d"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_PCS_VISIT_NA_FLAG}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy"> <reportElement x="363" y="0" width="65" height="20" uuid="45d696b2-729c-484d-8739-967767e67b05"/> <box leftPadding="3"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{SV_FIN_INVOICE_OCCURRED_DATE}]]></textFieldExpression> </textField> </band> </detail> <summary> <band/> </summary></jasperReport> Link to comment Share on other sites More sharing options...
C-Box Posted September 27, 2019 Share Posted September 27, 2019 No you don't need any VARIABLE just to filter dynamically based at the date-parameter that is also given from "outside". What I suggested was just at PARAMTER level.So that your (new) "hidden" select parameter internally just differs beetwen the two fields in databaseSV_SUB_CALENDAR_VISIT_DATE or SV_FIN_INVOICE_OCCURRED_DATEAlso you already have included the " BETWENN ..." in your default expression, so that you won't add to the query again.otherwise I missunderstood your problem, sorry!regardsC-Box Link to comment Share on other sites More sharing options...
jburczyk Posted September 27, 2019 Author Share Posted September 27, 2019 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_FLAGFROM 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_IDWHERE $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> Link to comment Share on other sites More sharing options...
jburczyk Posted September 27, 2019 Author Share Posted September 27, 2019 I think I finally got it! Thank you for your help, C-Box! Link to comment Share on other sites More sharing options...
jburczyk Posted October 10, 2019 Author Share Posted October 10, 2019 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_idPrimary 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;...ManagementGroupnPrimary_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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now