confutatis

Hello to all, I've been working to convert some personal finance reports from an old NextReports 9.0 report writer using SQLITE to Jaspersoft Studio reports. I've been noticing that when one of my query's field names is identified using an alias table, such as in the following example (not complete code, just paraphrasing): select tb_collect.payee from (select * from payee_master) tb_collect The field name will not display when rendered by Jaspersoft Server, but will display in Jaspersoft Studio. To correct the issue up to this point, I've just been adding as "as" to the field declaration as in "tb_collect.payee as payee". In a previous post, I mentioned this issue, but I never followed up on a real solution because I found this work around. Somehow I feel there must be a config setting that might allow JS Server to recognize the field name when declared with a table alias. Anyone have any idea? It's not a huge issue, because I simply just add the "as" extension, but it would save me a little time if I didn't have to change all of my queries. Thanks in advance, David in OH

Well, I spoke too soon. Same issue. The payee name appears on the JS Studio preview, but when I publish it to JS Server, no payee name. I've included the jrxml for your studying pleasure: ======================================================================================================================================================================== <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 6.20.5.final using JasperReports Library version 6.20.5-3efcf2e67f959db3888d79f73dde2dbd7acb4f8e --> <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="Payee Activity - Month Summaary" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="1f4a2102-a5c6-48bb-a861-6a810deb7f74"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="despec"/> <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver/"/> <property name="ireport.jasperserver.user" value="jasperadmin"/> <property name="ireport.jasperserver.report.resource" value="/Money_Manager_EX/Payee_Activity___Month_Summaary_files/main_jrxml"/> <property name="ireport.jasperserver.reportUnit" value="/Money_Manager_EX/Payee_Activity___Month_Summaary"/> <style name="Detail_LIne"> <conditionalStyle> <conditionExpression><![CDATA[($V{REPORT_COUNT} % 2) == 0]]></conditionExpression> <style mode="Opaque" backcolor="#DCE2E0" fontName="Calibri" fontSize="10"/> </conditionalStyle> </style> <parameter name="userRequestedMonthYear" class="java.lang.String"> <property name="com.jaspersoft.studio.js.ic.ds" value="/datasources/despec"/> <property name="com.jaspersoft.studio.js.ic.path" value="/Input_Controls/userRequestedMonthYear"/> <property name="com.jaspersoft.studio.js.ic.type" value="VALUE"/> <property name="com.jaspersoft.studio.js.ic.label" value=""/> <defaultValueExpression><![CDATA[DATEFORMAT(NOW( ),"MMyyyy")]]></defaultValueExpression> </parameter> <parameter name="userRequestedPayeeID" class="java.lang.Integer"> <property name="com.jaspersoft.studio.js.ic.path" value="/Input_Controls/userRequestedPayeeID"/> <property name="com.jaspersoft.studio.js.ic.ds" value="/datasources/despec"/> <property name="com.jaspersoft.studio.js.ic.type" value="SINGLE_QUERY"/> <defaultValueExpression><![CDATA[-100]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[/* CMO Payee Activity - Month Summary */ select 'CMO' as report_total_group, pye.payeename as payee_name, tb_collect.curOrPast_month_year as curOrPast_month_year, sum(tb_collect.transamount) as act_amount from (select dmc_vw.transdate, strftime('%m%Y',dmc_vw.transdate) as curOrPast_month_year, (case when dmc_vw.payeeid = -1 and lower(dmc_vw.transcode) = 'transfer' then (case when (lower(act1.accounttype) = 'credit card' or lower(act1.accessinfo = 'sav')) then act1.accountpayeeid else (case when strftime('%Y', dmc_vw.transdate) = strftime('%Y', 'now') then act1.accountpayeeid else (select trp.payeeid from transfer_payeeid trp where trp.accountid = dmc_vw.toaccountid and dmc_vw.transdate between trp.active_from and ifnull(trp.active_to, date())) end) end) else dmc_vw.payeeid end) as payee_id, dmc_vw.transamount from dmc_vw left join accountlist_v1 act on act.accountid = dmc_vw.accountid left join accountlist_v1 act1 on act1.accountid = dmc_vw.toaccountid where strftime('%m%Y', dmc_vw.transdate) = $P{userRequestedMonthYear}) tb_collect left join payee_v1 pye on pye.payeeid = tb_collect.payee_id where ($P{userRequestedPayeeID} = tb_collect.payee_id or $P{userRequestedPayeeID} = -100) group by 'CMO', pye.payeename, tb_collect.curOrPast_month_year /* order by case ${amountNameSortOrder} when 1 then act_amount end desc, case ${amountNameSortOrder} when 2 then pye.payeename end; */]]> </queryString> <field name="report_total_group" class="java.lang.Object"> <property name="com.jaspersoft.studio.field.name" value="report_total_group"/> <property name="com.jaspersoft.studio.field.label" value="report_total_group"/> </field> <field name="payee_name" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="payee_name"/> <property name="com.jaspersoft.studio.field.label" value="payee_name"/> <property name="com.jaspersoft.studio.field.tree.path" value="PAYEE_V1"/> </field> <field name="curOrPast_month_year" class="java.lang.Object"> <property name="com.jaspersoft.studio.field.name" value="curOrPast_month_year"/> <property name="com.jaspersoft.studio.field.label" value="curOrPast_month_year"/> </field> <field name="act_amount" class="java.lang.Float"> <property name="com.jaspersoft.studio.field.name" value="act_amount"/> <property name="com.jaspersoft.studio.field.label" value="act_amount"/> </field> <background> <band splitType="Stretch"/> </background> <pageHeader> <band height="151" splitType="Stretch"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.FreeLayout"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement positionType="Float" mode="Opaque" x="0" y="0" width="555" height="35" backcolor="#A4BFDA" uuid="c25766bc-170a-42cb-93fe-ba2cc7bcbcf7"> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <box topPadding="2" bottomPadding="2"> <pen lineWidth="1.5" lineStyle="Double"/> <topPen lineWidth="1.75" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.75" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.75" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.75" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"> <font fontName="Calibri" size="18"/> </textElement> <text><![CDATA[Current Month Reports]]></text> </staticText> <staticText> <reportElement positionType="Float" mode="Opaque" x="0" y="44" width="555" height="35" backcolor="#A4BFDA" uuid="0b00ff1e-2b29-458e-a148-421510982a0a"/> <box> <topPen lineWidth="1.75" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.75" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.25" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.75" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"> <font fontName="Calibri" size="16"/> </textElement> <text><![CDATA[Payee Activity - Month Summary]]></text> </staticText> <textField> <reportElement positionType="Float" mode="Opaque" x="0" y="81" width="555" height="35" backcolor="#A4BFDA" uuid="8e121e27-c5c4-4c78-8060-ea9e8484161a"/> <box> <leftPen lineWidth="1.75"/> <bottomPen lineWidth="1.75"/> <rightPen lineWidth="1.75"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"> <font size="14" isItalic="true"/> </textElement> <textFieldExpression><![CDATA[( $P{userRequestedPayeeID}== -100 ? "ALL PAYEES" : $F{payee_name} )]]></textFieldExpression> </textField> <textField> <reportElement positionType="Float" mode="Opaque" x="0" y="116" width="555" height="35" backcolor="#A4BFDA" uuid="eeac040e-2ace-4dba-810e-385779d15604"> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <box> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.75" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.25" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.75" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"> <font fontName="Calibri" size="14" isItalic="true"/> </textElement> <textFieldExpression><![CDATA[CONCATENATE(LEFT($P{userRequestedMonthYear},2),"/",RIGHT($P{userRequestedMonthYear},4))]]></textFieldExpression> </textField> </band> </pageHeader> <columnHeader> <band height="51" splitType="Stretch"> <frame> <reportElement mode="Opaque" x="27" y="11" width="500" height="30" backcolor="#DADBBD" uuid="ec51e74c-b9c4-46b2-829b-f62665abd2c3"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.FreeLayout"/> </reportElement> <box> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <staticText> <reportElement x="0" y="0" width="389" height="30" uuid="ecadd2c1-e4e7-4ae8-b592-642e2f0815ff"/> <box leftPadding="5"/> <textElement verticalAlignment="Middle"> <font fontName="Calibri" size="12" isBold="true"/> </textElement> <text><![CDATA[PAYEE]]></text> </staticText> <staticText> <reportElement x="390" y="0" width="110" height="30" uuid="f0f07244-0975-4da0-931f-77b41910a00e"/> <box rightPadding="5"/> <textElement textAlignment="Right" verticalAlignment="Middle"> <font fontName="Calibri" size="12" isBold="true"/> </textElement> <text><![CDATA[AMOUNT]]></text> </staticText> </frame> </band> </columnHeader> <detail> <band height="23" splitType="Stretch"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.FreeLayout"/> <frame> <reportElement style="Detail_LIne" x="27" y="3" width="500" height="20" uuid="cec9ae39-2d7e-4ab8-a039-6a1c937c51fe"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <box topPadding="1"/> <textField> <reportElement x="-1" y="3" width="389" height="15" uuid="a4e8c88e-ef8e-4e8c-a3b3-69f953479bd0"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="a5a44dab-f3ae-4788-b0f4-053e9eb36b09"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <box leftPadding="5"/> <textFieldExpression><![CDATA[$F{payee_name}]]></textFieldExpression> </textField> <textField> <reportElement x="390" y="3" width="110" height="15" uuid="bc345d90-b640-4a3d-a4b3-093d241d6544"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="26baf1ef-6edd-48d7-a2c0-c92381d517cd"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <box rightPadding="5"/> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$F{act_amount}]]></textFieldExpression> </textField> </frame> </band> </detail> <columnFooter> <band height="45" splitType="Stretch"/> </columnFooter> <pageFooter> <band height="46" splitType="Stretch"> <frame> <reportElement style="Detail_LIne" x="3" y="0" width="552" height="41" uuid="ca9b2955-f36b-4663-adf5-006f62270dfd"> <property name="com.jaspersoft.studio.unit.height" value="pixel"/> </reportElement> <frame> <reportElement x="0" y="0" width="552" height="41" uuid="6ad5a384-6ef1-4ad4-b1c4-32a9146788e2"/> <box topPadding="2" leftPadding="2" bottomPadding="0" rightPadding="2"> <topPen lineWidth="1.75"/> </box> <textField> <reportElement x="348" y="0" width="100" height="41" uuid="27adac8e-53bb-4bcb-9ed6-5a9b16ad469c"/> <textElement textAlignment="Right"> <font fontName="Calibri"/> </textElement> <textFieldExpression><![CDATA["Page " + $V{PAGE_NUMBER}]]></textFieldExpression> </textField> <textField evaluationTime="Report"> <reportElement x="448" y="0" width="100" height="41" uuid="57f36724-194f-4aa8-9e88-ebb41c51c150"/> <textElement textAlignment="Left"> <font fontName="Calibri"/> </textElement> <textFieldExpression><![CDATA[" of " + $V{PAGE_NUMBER}]]></textFieldExpression> </textField> <textField pattern="MMMMM dd, yyyy"> <reportElement x="0" y="0" width="100" height="41" uuid="1fa22342-5d0d-4026-899b-49b2bc442737"/> <textElement> <font fontName="Calibri"/> </textElement> <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression> </textField> </frame> </frame> </band> </pageFooter> </jasperReport> ======================================================================================================================================================================== Do any of you see anything I don't that may be the cause of my issues? Again, my appreciation for the help. David in OH

Actually, I found the issue. If you look at the SQL code, notice that I use "dot" reference for one of the fields which is created in a inner query called "tb_collect". The field referenced is " tb_collect.curOrPast_month_year". Once I added an "as", that is " tb_collect.curOrPast_month_year as curOrPast_month_year", the field appeared in the report. Is there anything I can do to get around having to add the "as", because I do a lot of this in my report code. Regards, David in OH

Hello again, folks, I developed a report in JS Studio. All the fields display on the report (they are all simple string fields) when I do the preview in Studio. When I publish the report to the server and run it, one of the fields in the detail line is showing NULL instead of the value. Here is the query I am using (SQLITE): /* CMO Payee Activity - Month Summary */ select 'CMO' as report_total_group, pye.payeename as payee_name, tb_collect.curOrPast_month_year, sum(tb_collect.transamount) as act_amount from (select dmc_vw.transdate, strftime('%m%Y',dmc_vw.transdate) as curOrPast_month_year, (case when dmc_vw.payeeid = -1 and lower(dmc_vw.transcode) = 'transfer' then (case when (lower(act1.accounttype) = 'credit card' or lower(act1.accessinfo = 'sav')) then act1.accountpayeeid else (case when strftime('%Y', dmc_vw.transdate) = strftime('%Y', 'now') then act1.accountpayeeid else (select trp.payeeid from transfer_payeeid trp where trp.accountid = dmc_vw.toaccountid and dmc_vw.transdate between trp.active_from and ifnull(trp.active_to, date())) end) end) else dmc_vw.payeeid end) as payee_id, dmc_vw.transamount from dmc_vw left join accountlist_v1 act on act.accountid = dmc_vw.accountid left join accountlist_v1 act1 on act1.accountid = dmc_vw.toaccountid where strftime('%m%Y', dmc_vw.transdate) = $P{userRequestedMonthYear}) tb_collect left join payee_v1 pye on pye.payeeid = tb_collect.payee_id -------------------------------------------------------------------------------------------------------------------------- The field that displays fine in Studio is "tb_collect.curOrPast_month_year", but appears as a NULL display on the JS Server. Any ideas? Regards, David in OH

Hello everyone. I am using a Sqlite DB with a JDBC data source configured on the server. When I try to test the connection, the log file displays this error. What do I need to do to allow Sqlite to work properly with the Jaspersoft Server? Thanks in advance for any advice, David in Ohio