How to add Ad Hoc database content to Textfield

Question

I have an input parameter which is an ID.

I want to use a lookup table in the database to expand that ID into a long name for use in the report title.

How could I just add text into a textfield from a SQL query without needing to worry about rows of tabular data?

Answer 1

You can use the List component in the Title band. This List (or Table component) will be associated with another additional (non main) datasource for showing information (the Name by Id passed via Parameter in your case).

The main datasource will be used by Detail band (or another Table component with the its own datasource) for showing data filtered by parameter's value (Id in your case).

With help of textField's property isStretchWithOverflow (with true value) we can garantee that all text will be drawing with the textField.

The sample

In this sample I've used the DB distributed with the Jaspersoft Studio. The parameter addrId was used for filtering data by the id field of address table. This parameter was mapped to the List's datasource parameter with the same name (addrId). Yes, I've used the List component placed on Title band for showing information about the value of our external parameter (the city and the street of address in this sample). At the Detail band we are showing the information about documents (table document) related to our address (defined by addrId)

The report's template

<?xml version="1.0" encoding="UTF-8"?>
<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="growing_text" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="77f0cb04-7f4b-43dc-af12-89c25fa7c58c">
    <subDataset name="dsAddrTitle" uuid="0eb7cd0c-f4f1-408d-be13-dc484fda80d5">
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Sample DB"/>
        <parameter name="addrId" class="java.lang.Integer"/>
        <queryString>
            <![CDATA[SELECT city + ', ' + street AS name FROM address WHERE id=$P{addrId}]]>
        </queryString>
        <field name="NAME" class="java.lang.String"/>
    </subDataset>
    <parameter name="addrId" class="java.lang.Integer">
        <defaultValueExpression><![CDATA[33]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT ID, ADDRESSID, TOTAL FROM DOCUMENT WHERE  ADDRESSID=$P{addrId} ORDER BY ADDRESSID]]>
    </queryString>
    <field name="ID" class="java.lang.Integer"/>
    <field name="ADDRESSID" class="java.lang.Integer"/>
    <field name="TOTAL" class="java.math.BigDecimal"/>
    <title>
        <band height="10" splitType="Stretch">
            <componentElement>
                <reportElement x="160" y="0" width="40" height="10" uuid="f4cb4e5c-e2d7-4927-b143-4cfcd7d99b76"/>
                <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" ignoreWidth="true">
                    <datasetRun subDataset="dsAddrTitle" uuid="4bf3eb57-f752-4856-ac3a-fd7e3a33f434">
                        <parametersMapExpression><![CDATA[$P{REPORT_PARAMETERS_MAP}]]></parametersMapExpression>
                        <datasetParameter name="addrId">
                            <datasetParameterExpression><![CDATA[$P{addrId}]]></datasetParameterExpression>
                        </datasetParameter>
                        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    </datasetRun>
                    <jr:listContents height="30" width="100">
                        <textField isStretchWithOverflow="true">
                            <reportElement x="0" y="0" width="40" height="10" uuid="1b533c30-7868-450b-a5b9-59d5130dcb67"/>
                            <textFieldExpression><![CDATA[$F{NAME}]]></textFieldExpression>
                        </textField>
                    </jr:listContents>
                </jr:list>
            </componentElement>
        </band>
    </title>
    <columnHeader>
        <band height="30">
            <staticText>
                <reportElement x="0" y="0" width="185" height="30" uuid="100faa3b-790d-4dc6-b86c-8911a8762207"/>
                <text><![CDATA[ID]]></text>
            </staticText>
            <staticText>
                <reportElement x="185" y="0" width="185" height="30" uuid="aef6af65-f7b5-42e9-a102-aeb272f99103"/>
                <text><![CDATA[ADDRESSID]]></text>
            </staticText>
            <staticText>
                <reportElement x="370" y="0" width="185" height="30" uuid="2c176a21-6387-4505-836e-7e250751755f"/>
                <text><![CDATA[TOTAL]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="30" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="185" height="30" uuid="22cbe96d-5322-40e3-bd96-d2aa9bf35dd2"/>
                <textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="185" y="0" width="185" height="30" uuid="3adedcc9-f60e-4664-bbe8-6b7d7b8e13a4"/>
                <textFieldExpression><![CDATA[$F{ADDRESSID}]]></textFieldExpression>
            </textField>
            <textField pattern="#,##0.00#">
                <reportElement x="370" y="0" width="185" height="30" uuid="48041fd6-1375-4819-8ebb-ffd4aef84889"/>
                <textFieldExpression><![CDATA[$F{TOTAL}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

The output result