vspn Posted July 14, 2015 Share Posted July 14, 2015 I want to display same sequence number for a group as shown in below example. Please help with SQL query Ex: I have data as below in the table. Facility Store Stop 620592 S0722 1 620578 S0308 2 620578 S0631 3 620590 S0678 4 620590 S0444 5 620590 S0237 6 I need Sequence number for these record as Facility Stop Sequence 620592 1 1 620578 2 2 620578 3 2 620590 4 3 620590 5 3 620590 6 3 The sequence number is same for a group of Facility and ordered by minimum of stop. Is it possible to generate sequence number as shown above in iReport designer? Link to comment Share on other sites More sharing options...
hozawa Posted July 14, 2015 Share Posted July 14, 2015 If you have a question with your sql, it's probably better to ask your question at some database project or vendor's forum instead of here. Link to comment Share on other sites More sharing options...
vspn Posted July 14, 2015 Author Share Posted July 14, 2015 My doubt is whether is it possible to generate sequence number in iReport? Link to comment Share on other sites More sharing options...
Tom C Posted July 14, 2015 Share Posted July 14, 2015 You can create a report group for Facility, then use a variable to count number of groups based on distinct Facility value and display this variable in your report. <variable name="Sequence" class="java.lang.Integer" incrementType="Group" incrementGroup="groupFacility" calculation="Count"> <variableExpression><![CDATA[$F{facility}]]></variableExpression> </variable> <group name="groupFacility"> <groupExpression><![CDATA[$F{facility}]]></groupExpression> </group>[/code] Link to comment Share on other sites More sharing options...
Solution Tom C Posted July 14, 2015 Solution Share Posted July 14, 2015 Here is the sample report design template to demo the approach. You can test run this report using any PostgreSQL data source. It was created using iReport 5.6 but it should work with Jasper Studio as well.<?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="demo for vspn" language="groovy" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="5fa71407-8d68-4ece-8349-60dded252ccc"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <queryString> <![CDATA[select '620592' as Facility, 'S0722' as Store, 1 as Stopunion allselect '620578' as Facility, 'S0308' as Store, 2 as Stopunion allselect '620578' as Facility, 'S0631' as Store, 3 as Stopunion allselect '620590' as Facility, 'S0678' as Store, 4 as Stopunion allselect '620590' as Facility, 'S0444' as Store, 5 as Stopunion allselect '620590' as Facility, 'S0237' as Store, 6]]> </queryString> <field name="facility" class="java.lang.String"/> <field name="store" class="java.lang.String"/> <field name="stop" class="java.lang.Integer"/> <variable name="Sequence" class="java.lang.Integer" incrementType="Group" incrementGroup="groupFacility" calculation="Count"> <variableExpression><![CDATA[$F{facility}]]></variableExpression> </variable> <group name="groupFacility"> <groupExpression><![CDATA[$F{facility}]]></groupExpression> </group> <background> <band splitType="Stretch"/> </background> <title> <band splitType="Stretch"/> </title> <pageHeader> <band splitType="Stretch"/> </pageHeader> <columnHeader> <band height="25" splitType="Stretch"> <staticText> <reportElement x="0" y="0" width="100" height="20" uuid="c371c0da-2c48-484d-99f1-efddf3d7eabc"/> <text><![CDATA[Facility]]></text> </staticText> <staticText> <reportElement x="100" y="0" width="100" height="20" uuid="08071704-3eed-41d2-bf72-2757aa1a69db"/> <text><![CDATA[stop]]></text> </staticText> <staticText> <reportElement x="200" y="0" width="100" height="20" uuid="5c55f9e4-4d41-46d6-b941-c0cb0c81f946"/> <text><![CDATA[sequence]]></text> </staticText> </band> </columnHeader> <detail> <band height="24" splitType="Stretch"> <textField> <reportElement x="0" y="0" width="100" height="20" uuid="43d664a7-471e-4cb8-889b-31a4cda9f589"/> <textFieldExpression><![CDATA[$F{facility}]]></textFieldExpression> </textField> <textField> <reportElement x="100" y="0" width="100" height="20" uuid="0e67a151-1a70-4b93-b78c-4773c767b89c"/> <textFieldExpression><![CDATA[$F{stop}]]></textFieldExpression> </textField> <textField> <reportElement x="200" y="0" width="100" height="20" uuid="55968736-2d84-4c71-a85f-d99674f06b70"/> <textFieldExpression><![CDATA[$V{Sequence}]]></textFieldExpression> </textField> </band> </detail> <columnFooter> <band splitType="Stretch"/> </columnFooter> <pageFooter> <band splitType="Stretch"/> </pageFooter> <summary> <band height="25" splitType="Stretch"> <line> <reportElement x="0" y="0" width="300" height="1" uuid="57ba23ee-04c1-42af-a231-e17158e5eeee"/> </line> <staticText> <reportElement x="100" y="5" width="100" height="20" uuid="0d4e0c10-b06c-4014-8e1c-43a30eabe5e2"/> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[TOTAL RECORDS:]]></text> </staticText> <textField> <reportElement x="200" y="5" width="100" height="20" uuid="f53e8c67-193a-41d7-b0de-e66ad3770eb1"/> <textElement> <font isBold="true"/> </textElement> <textFieldExpression><![CDATA[$V{REPORT_COUNT}]]></textFieldExpression> </textField> </band> </summary></jasperReport>Here is the screen shot of the report output: Link to comment Share on other sites More sharing options...
vspn Posted July 15, 2015 Author Share Posted July 15, 2015 Thank you. I am able to fix with the suggestions provided Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now