using database views in jasper reports

[patricia.williams]

The xml is very simple and no complaints from dataset and Query Dialog wizard and I can preview my list:

SELECT DISTINCT filer_ident, filer_name_organization, filer_name_last, filer_name_first, filer_name_middle, 

       filer_persent_type_cd, primary_business, 

       lobby_reporting_interval_cd, addr_1, addr_2, city, state_cd, 

       region, postal_code, country_cd, phone_number

 FROM tecadhoc.list_lobby_clients_2016

 

The tecadhoc.list_lobby_clients_2016 is a database view, not a database table. Depending on a type code, either filer_name_organization or filer_name_last is null.  I want to combine filer_name_organization and filer_name_last so that I can order by name.  So I change the xml to

 

SELECT DISTINCT filer_ident, concat(filer_name_organization, filer_name_last) as name, filer_name_first, filer_name_middle, 

       filer_persent_type_cd, primary_business, 

       lobby_reporting_interval_cd, addr_1, addr_2, city, state_cd, 

       region, postal_code, country_cd, phone_number

 FROM tecadhoc.list_lobby_clients_2016

 order by name

 

The database wizard doesn't complain about syntax.  But  preview gives the error 

Field not found: filer_name_organization --- net.sf.jasperreports.engine.design.JRDesignExpression@7528da35  (and same error for filer_name_last)

 

Why is that?  I also tried coaelse of the two as name and it complains.  I know it lets me do this if it is a table, why not a view?

 

 

[javier.ggi90]

Does the same error appear if you try to "ORDER BY filer_name_organization, filer_name_last"?

Sometimes I have issues while grouping or ordering by an alias.

[patricia.williams]

No error if I call out the names,  but if I do that then I get all the name_last alphabetized first followed by all the name_organization alphabetized at the end.  I want to alphabetize them regardless of type.

[szaharia]

Is your report using scriptlets? Usually this error message is raised when a scriptlet tries to access a missing field.

[patricia.williams]

I am not using any scriplets.

[szaharia]

Could you post the JRXML file, in this case? Maybe we'll be able to notice some other possible clues there.

Thanks,

Sanda

[patricia.williams]

Here's the jrxml without the concat of the two fields

<?xml version="1.0" encoding="UTF-8"?>

<!-- Created with Jaspersoft Studio version 6.1.0.final using JasperReports Library version 6.1.0 -->

<!-- 2016-04-04T15:13:53 -->

<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="RegisterdLobbyistsByYear" language="groovy" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="7a5c9b49-9dc5-422a-ac69-e92cf61ef530">

<property name="com.jaspersoft.studio.data.defaultdataadapter" value="TECInternal"/>

<property name="ireport.jasperserver.url" value="http://10.10.10.32:8082/jasperserver-pro/"/>

<property name="ireport.jasperserver.user" value="jasperadmin"/>

<property name="ireport.jasperserver.report.resource" value="/public/StaticLists/RegisterdLobbyistsByYear_files/main_jrxml"/>

<property name="ireport.jasperserver.reportUnit" value="/public/StaticLists/RegisterdLobbyistsByYear"/>

<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="net.sf.jasperreports.export.xls.ignore.cell.background" value="true"/>

<property name="net.sf.jasperreports.export.xlsx.exclude.origin.band.1" value="title"/>

<property name="net.sf.jasperreports.export.xlsx.exclude.origin.band.2" value="pageHeader"/>

<property name="net.sf.jasperreports.export.xlsx.exclude.origin.band.3" value="pageFooter"/>

<property name="net.sf.jasperreports.export.xls.show.gridlines" value="true"/>

<property name="net.sf.jasperreports.export.xls.ignore.graphics" value="true"/>

<style name="Title" forecolor="#FFFFFF" fontName="Times New Roman" fontSize="50" isBold="false"/>

<style name="SubTitle" forecolor="#CCCCCC" fontName="Times New Roman" fontSize="18" isBold="false"/>

<style name="Column header" forecolor="#666666" fontName="Times New Roman" fontSize="14" isBold="true"/>

<style name="Detail" mode="Transparent" fontName="Times New Roman"/>

<style name="Row" mode="Transparent" fontName="Times New Roman" pdfFontName="Times-Roman">

<conditionalStyle>

<conditionExpression><![CDATA[$V{REPORT_COUNT}%2 == 0]]></conditionExpression>

<style mode="Opaque" backcolor="#F0EFEF"/>

</conditionalStyle>

</style>

<parameter name="Year" class="java.lang.String"/>

<queryString>

<![CDATA[SELECT DISTINCT

filer_ident,

filer_name_last, filer_name_first, filer_name_middle, filer_name_organization,

filer_persent_type_cd, primary_business, lobby_reporting_interval_cd,

addr_1, addr_2, city, state_cd, region, postal_code, country_cd, phone_number

from tecadhoc.lobby_client_list($P{Year}) order by filer_ident]]>

</queryString>

<field name="filer_ident" class="java.lang.String"/>

<field name="filer_name_last" class="java.lang.String"/>

<field name="filer_name_first" class="java.lang.String"/>

<field name="filer_name_middle" class="java.lang.String"/>

<field name="filer_name_organization" class="java.lang.String"/>

<field name="filer_persent_type_cd" class="java.lang.String"/>

<field name="primary_business" class="java.lang.String"/>

<field name="lobby_reporting_interval_cd" class="java.lang.String"/>

<field name="addr_1" class="java.lang.String"/>

<field name="addr_2" class="java.lang.String"/>

<field name="city" class="java.lang.String"/>

<field name="state_cd" class="java.lang.String"/>

<field name="region" class="java.lang.String"/>

<field name="postal_code" class="java.lang.String"/>

<field name="country_cd" class="java.lang.String"/>

<field name="phone_number" class="java.lang.String"/>

<group name="Group1">

<groupExpression><![CDATA[(int)($V{REPORT_COUNT}/15)]]></groupExpression>

</group>

<group name="Group2">

<groupExpression><![CDATA[(int)($V{REPORT_COUNT}/5)]]></groupExpression>

</group>

<background>

<band splitType="Stretch"/>

</background>

<title>

<band height="102" splitType="Stretch">

<image onErrorType="Blank">

<reportElement x="725" y="0" width="77" height="102" uuid="651ccc4b-e999-40fe-bcbf-1f25bcfc98af"/>

<imageExpression><![CDATA["repo:/public/publicData/image/WhiteCapitolDome_BlueBackground"]]></imageExpression>

</image>

<frame>

<reportElement mode="Opaque" x="0" y="0" width="721" height="66" backcolor="#000000" uuid="d8d9e644-3a32-491d-97cb-a277aff17133"/>

<staticText>

<reportElement x="0" y="0" width="721" height="30" forecolor="#FFFFFF" uuid="13d40517-c3ed-41c3-b3bd-81699b9c30ef"/>

<textElement textAlignment="Center" verticalAlignment="Middle">

<font size="22" isBold="false"/>

</textElement>

<text><![CDATA[Texas Ethics Commission ]]></text>

</staticText>

<textField>

<reportElement x="0" y="32" width="721" height="30" forecolor="#FFFFFF" uuid="9e9caab2-4e23-495a-91d1-fe40b600efee"/>

<textElement textAlignment="Center" verticalAlignment="Middle">

<font size="20"/>

</textElement>

<textFieldExpression><![CDATA[$P{Year}+" LIST OF REGISTERED LOBBYISTS"]]></textFieldExpression>

</textField>

</frame>

<frame>

<reportElement mode="Opaque" x="0" y="67" width="721" height="32" forecolor="#000000" backcolor="#7CCBFC" uuid="b0c895f6-6dbd-477d-9858-9ad66af087e1"/>

<textField pattern="MMMMM dd, yyyy">

<reportElement x="0" y="0" width="721" height="32" forecolor="#FFFFFF" uuid="a33fce3b-20c1-4696-873a-fe134d1e7d88"/>

<textElement textAlignment="Center" verticalAlignment="Middle">

<font size="12"/>

</textElement>

<textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>

</textField>

</frame>

</band>

</title>

<pageHeader>

<band splitType="Stretch"/>

</pageHeader>

<detail>

<band height="20" splitType="Stretch">

<property name="com.jaspersoft.studio.unit.height" value="pixel"/>

<textField isStretchWithOverflow="true" isBlankWhenNull="true">

<reportElement x="30" y="0" width="120" height="20" uuid="dc57d609-c89f-444c-815f-5bad95e2d1e9"/>

<textElement verticalAlignment="Middle">

<font size="10" isBold="true"/>

</textElement>

<textFieldExpression><![CDATA[$F{filer_persent_type_cd}.equals("ENTITY") ? $F{filer_name_organization} : $F{filer_name_last}+", "+$F{filer_name_first}]]></textFieldExpression>

</textField>

<textField>

<reportElement x="216" y="0" width="150" height="20" uuid="81ccf5bf-1a02-42dc-8c9d-f307e75320fc">

<property name="com.jaspersoft.studio.unit.height" value="pixel"/>

</reportElement>

<textElement verticalAlignment="Middle">

<font size="9"/>

</textElement>

<textFieldExpression><![CDATA[$F{addr_1}]]></textFieldExpression>

</textField>

<textField isBlankWhenNull="true">

<reportElement x="366" y="0" width="70" height="20" uuid="4a8bd66a-fa16-4fbf-ad4b-04fd691ef3b1"/>

<textElement verticalAlignment="Middle">

<font size="9"/>

</textElement>

<textFieldExpression><![CDATA[$F{addr_2}]]></textFieldExpression>

</textField>

<textField>

<reportElement x="436" y="0" width="71" height="20" uuid="0f545c95-f34b-44da-98e7-7b577cb9096b"/>

<textElement textAlignment="Justified" verticalAlignment="Middle">

<font size="9"/>

</textElement>

<textFieldExpression><![CDATA[$F{city}]]></textFieldExpression>

</textField>

<textField>

<reportElement x="507" y="0" width="40" height="20" uuid="5135a86f-c2a9-4caf-a4d3-39b3705f29e5"/>

<textElement verticalAlignment="Middle">

<font size="9"/>

</textElement>

<textFieldExpression><![CDATA[$F{state_cd}]]></textFieldExpression>

</textField>

<textField>

<reportElement x="547" y="0" width="81" height="20" uuid="352e2ee6-cd1c-4c44-9a7a-dd64c54ad118"/>

<textElement verticalAlignment="Middle">

<font size="9"/>

</textElement>

<textFieldExpression><![CDATA[$F{postal_code}]]></textFieldExpression>

</textField>

<textField pattern="" isBlankWhenNull="true">

<reportElement x="628" y="0" width="100" height="20" uuid="a65e53e5-dd53-428e-aa54-732fd8a68457"/>

<textElement verticalAlignment="Middle">

<font size="9"/>

</textElement>

<textFieldExpression><![CDATA["("+$F{phone_number}.substring(0,3)+")"+$F{phone_number}.substring(3,6)+"-"+$F{phone_number}.substring(6)]]></textFieldExpression>

</textField>

<textField isBlankWhenNull="false">

<reportElement x="150" y="0" width="66" height="20" uuid="86e8f2fc-a82b-4dbd-85c0-c81f9ef4c8fe">

<property name="com.jaspersoft.studio.unit.height" value="pixel"/>

</reportElement>

<textElement verticalAlignment="Middle">

<font isBold="true"/>

</textElement>

<textFieldExpression><![CDATA[$F{filer_ident}]]></textFieldExpression>

</textField>

<textField>

<reportElement x="2" y="0" width="28" height="20" uuid="34adc2ef-6a90-4e4a-a0d0-1b8a14d3cbf2">

<property name="com.jaspersoft.studio.unit.height" value="pixel"/>

</reportElement>

<textElement verticalAlignment="Middle">

<font isBold="true"/>

</textElement>

<textFieldExpression><![CDATA[$V{REPORT_COUNT}]]></textFieldExpression>

</textField>

</band>

</detail>

<pageFooter>

<band height="25" splitType="Stretch">

<frame>

<reportElement mode="Opaque" x="0" y="0" width="802" height="25" forecolor="#D0B48E" backcolor="#000000" uuid="ffb10ec8-0371-4545-8eaa-96d62ad3cec0"/>

<textField evaluationTime="Report">

<reportElement style="Column header" x="757" y="3" width="40" height="20" forecolor="#FFFFFF" uuid="b5521ea6-5f36-4864-856f-58d0bcd011f2"/>

<textElement verticalAlignment="Middle">

<font size="10" isBold="false"/>

</textElement>

<textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>

</textField>

<textField>

<reportElement style="Column header" x="677" y="3" width="80" height="20" forecolor="#FFFFFF" uuid="e625ea3c-1741-4cfe-81cf-fbbdd00162c8"/>

<textElement textAlignment="Right" verticalAlignment="Middle">

<font size="10" isBold="false"/>

</textElement>

<textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>

</textField>

<textField pattern="EEEEE dd MMMMM yyyy">

<reportElement style="Column header" x="2" y="3" width="197" height="20" forecolor="#FFFFFF" uuid="0c9f7e91-d3f5-47f9-82b7-0b2b3a43a5d6"/>

<textElement verticalAlignment="Middle">

<font size="10" isBold="false"/>

</textElement>

<textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>

</textField>

</frame>

</band>

</pageFooter>

<summary>

<band splitType="Stretch"/>

</summary>

</jasperReport>

[patricia.williams]

After I closed the application this weekend and logged back in today, preview no longer complains about an unknown column.  I really don't understand why this app flags errors and after a close/ reopen the app no longer complains!

Nor why I can close the application and my workspace directory shows no red X over my project explorer directory or jrmlx file afer successful compiles and previews.  But when I reopen, it goes back to having the red X before you even open it again.

Strange behavior to say the least (and frustrating!!)

[szaharia]

The report design looks ok, but it doesn't contain info about the modified structure when using the concat() function.

So, in the report that uses concat() make sure that the following modifications were done:

• <field name="filer_name_last" class="java.lang.String"/> and <field name="filer_name_organization" class="java.lang.String"/> field settings were removed

• <field name="name" class="java.lang.String"/> field setting was added instead

• the $F{filer_persent_type_cd}.equals("ENTITY") ? $F{filer_name_organization} : $F{filer_name_last}+", "+$F{filer_name_first} expression was replaced by $F{name}+", "+$F{filer_name_first}

I hope this helps,

Sanda