Generate a Unique Document number

[ajith.devendra]

Dear Expert,

 

I need to generate a squence number eash time I generate the report .. Basically when we run the report , it should generate the number and print .. next time generating the same report with diffrent details , it should generate the next sequence number and print on the report .. It should be the document reference number for the report ..

I would apprecaite if you can help me on above query .. 

 

rgds

Ajith 

 

[reportdev]

if you are using a database,

1. try creating a sequence in your database
2. call that sequence using a plsql block from the dataset
3. display that sequence.nextval from the dataset in your report.

documentation for Oracle sequences: https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6014.htm

documentation for SQL server sequences: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql

documentation for postgres sequences: https://www.postgresql.org/docs/9.5/static/sql-createsequence.html

[hozawa]

Create a scriplet which access a database table that keeps track of the sequence number.

http://jasperreports.sourceforge.net/sample.reference/scriptlet/

[reportdev]

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.4.2.final using JasperReports Library version 6.4.1  --><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="jasperSequenceGenerator" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="04443ff2-e94a-4d12-8600-dd4d5686346f">    <queryString language="plsql">        <![CDATA[{call get_nextval($P{ORACLE_REF_CURSOR}) }/*CREATE SEQUENCE test_sequence START WITH     1 INCREMENT BY   1 NOCACHE NOCYCLE;create or replace procedure get_nextval(val_cur out sys_refcursor) isbeginopen val_cur forselect test_sequence.nextval from dual;end;*/]]>    </queryString>    <field name="NEXTVAL" class="java.math.BigDecimal"/>    <background>        <band splitType="Stretch"/>    </background>    <title>        <band height="20">            <textField>                <reportElement x="0" y="0" width="555" height="20" uuid="aeff4ffd-9dc2-46c3-a360-e1d394a002e0"/>                <textElement>                    <font isBold="true"/>                </textElement>                <textFieldExpression><![CDATA["Current Value for the Unique Sequence is " + $F{NEXTVAL}]]></textFieldExpression>            </textField>        </band>    </title>    <columnHeader>        <band height="20" splitType="Stretch">            <staticText>                <reportElement x="0" y="0" width="100" height="20" uuid="faf2370f-082b-4132-8a88-0da19082e770"/>                <text><![CDATA[Column Header]]></text>            </staticText>            <staticText>                <reportElement x="100" y="0" width="100" height="20" uuid="ce121043-65ab-485e-8856-731f957806e5"/>                <text><![CDATA[Column Header]]></text>            </staticText>            <staticText>                <reportElement x="210" y="0" width="100" height="20" uuid="67be1f25-a9a1-4510-88ff-1405da399d07"/>                <text><![CDATA[Column Header]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="20" splitType="Stretch">            <textField>                <reportElement x="0" y="0" width="100" height="20" uuid="10fba0fa-c210-4129-b9fa-c95c37f9250b"/>                <textFieldExpression><![CDATA["DUMMY"]]></textFieldExpression>            </textField>            <textField>                <reportElement x="100" y="0" width="100" height="20" uuid="64609422-7400-48a6-81a6-45f6e6a65388"/>                <textFieldExpression><![CDATA["DUMMY"]]></textFieldExpression>            </textField>            <textField>                <reportElement x="210" y="0" width="100" height="20" uuid="29d5df8a-fdce-4d0d-829c-1fa90a543e3c"/>                <textFieldExpression><![CDATA["DUMMY"]]></textFieldExpression>            </textField>        </band>    </detail></jasperReport>[/code]

If you are still wondering, how to implement this jrxml would help you out if you are using Oracle. 

Create the sequence using

CREATE SEQUENCE test_sequence 

START WITH     1 

INCREMENT BY   1 

NOCACHE 

NOCYCLE;

then Create a Stored Proc using

create or replace procedure get_nextval(val_cur out sys_refcursor) is

begin

open val_cur for

select test_sequence.nextval from dual;

end;

Switch to plsql evaluation on the dataset and click read fields with {call get_nextval($P{ORACLE_REF_CURSOR}) }

[ajith.devendra]

Thanks Hozawa/Report Dev.

Tried both the options and working fine ..

Appreciated your advice

Rgds

Ajith