CODEWITHSUNDEEP
jasper-reports
JasperNewBie
JasperNewBie

Reputation: 31

Displaying multiple records of one column in a single jasperreport

I am using jasper report 5.0.0 and creating a report with a scenario as described below: In my ireport I need to display a students name, class, id, goal, proficiencyForGoal. For each student there are multiple goals and for each goal there is progress. My sql query is as following : select stud.name, stud.class, stud.id, prog.goal_id, prog.proficiency from 2 or 3 tables which are joined and group by prog.goal_id. This query gives multiple rows per student. And the ireport creates multiple PDFs(using PDF Preview) per row. But I want to display all goals and the goals proficiency along with student details like name, class, id in a single PDF. That is I want one PDF per student with all the details. So if there are 500 students then 500 pdfs.

Suppose there are 10 goals per student then right now what I am getting is 10 PDFs for one student with name, class, id repeating and for 500 students I am getting 500*10=5000 pdfs WHICH I DONT WANT.

Appreciate your quick input or ideas on how to achieve this requirement.

Thanks

Upvotes: 2

Views: 7166

Answers (4)

Mike Noland
Mike Noland

Reputation: 505

a Simpler way assuming your query is orderd by student. in Ireport add a group (in the report inspector right click on the root click "add report group") you most likely will not need headers or footers lay down all your fields ( name, class, id, goal_id, proficiency ) you may want UN-click the "print repeated values" property on Name, class, id select your Group (in the report inspector) select "start on new page" ( you may also want to reset page # and reprint header) also "Keep together"

here is an example, you will need to put your Query in for it to work at all 

<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="report9" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="5ac8f93f-0f1f-4f1c-8c7c-41a15b76ef65">
<?xml version="1.0" encoding="UTF-8"?>
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <queryString>
        <![CDATA[select sysdate from dual ]]>
    </queryString>
    <field name="name" class="java.lang.String"/>
    <field name="class" class="java.lang.String"/>
    <field name="id" class="java.lang.String"/>
    <field name="goal_id" class="java.lang.String"/>
    <field name="proficiency" class="java.lang.String"/>
    <group name="Student Name" isStartNewPage="true" isResetPageNumber="true" isReprintHeaderOnEachPage="true" keepTogether="true">
        <groupExpression><![CDATA[$F{name}]]></groupExpression>
    </group>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch"/>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement uuid="b78d9294-5efc-4d3a-a08b-749493ecf242" x="0" y="0" width="111" height="20"/>
                <textElement/>
                <text><![CDATA[name]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="8eeacd33-15c4-416e-981e-f8aac2586443" x="111" y="0" width="111" height="20"/>
                <textElement/>
                <text><![CDATA[class]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="dc878516-ecc7-405d-a288-ead78229e564" x="222" y="0" width="111" height="20"/>
                <textElement/>
                <text><![CDATA[id]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="0eb5c805-4358-4d85-b567-d513a318a54d" x="333" y="0" width="111" height="20"/>
                <textElement/>
                <text><![CDATA[goal_id]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="b9675b06-87ac-4441-a207-0cbd58dbf925" x="444" y="0" width="111" height="20"/>
                <textElement/>
                <text><![CDATA[proficiency]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement uuid="b284aaa5-475e-4b54-a1bd-ce4772cf79a2" isPrintRepeatedValues="false" x="0" y="0" width="111" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{name}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="80f38abc-1cb3-41c0-a9c6-2473e84a8c6d" isPrintRepeatedValues="false" x="111" y="0" width="111" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{class}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="30a371f8-4d08-4125-8e85-497b2697e9d3" x="222" y="0" width="111" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{id}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="53757283-0c03-4248-9848-1eba9ee168b7" x="333" y="0" width="111" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{goal_id}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="3d2bbbc0-0fe1-4442-809f-acee606db5ac" x="444" y="0" width="111" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{proficiency}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>

Upvotes: 0

Sharad
Sharad

Reputation: 3548

In query you can add parametrized condition for student_id

Like:  where student_id=$P{p_student_id}

and keep everything such as Text Field and Static Text in summary band.

Upvotes: 0

Mike Noland
Mike Noland

Reputation: 505

Not sure how to add this as a comment so i put it here.

To pass values to your data set, it works alot like sub reports but the Parameters option is not in the properties section of Ireport.
Just like in a subreport you first need to add a Parameter to your dataset (student_id)

then to get to parameters screen you need to :

Right click on your list select "edit list datasource" under the parameters tab click "Add" in to top pull you will have your dataset Parameter (student_id) [you will only get a list of valid parameters from your data set] in the value expression you will have your master report $F{student_id}

this will pass the student_id from your main report into your dataset (used for list's, table's and crosstab's)

and of then of cource you need to add to your where clause of your dataset

where $P{student_id} = student_id

Upvotes: 0

acostache
acostache

Reputation: 2275

After you have already added the sql query in your report, i think just placing the fields (name, class etc) on the report should at least; also if there are multiple details per student, you can use subreports for each student in the report.

Upvotes: 1

Related Questions