Getting duplicate records

[vs00500773]

Here is the SQL query resultset.

Project Name, Amount, Cost Type, Month Dept

A                    1000        Capital       Jan       A101

A                    2000        Operating   Jan       A101

A                     3000       Captial       Jan       A102

A                     4000      Operating   Jan        A102

 

Here is the expected resport outpt:

Project A               Jan

                        Captial    Operating

A101              1000         2000

A102             3000          4000

I have created Group Header for Project, Dept, Month, Cost Type but the records are getting duplicated.  Example.  I get FOUR records in the detailed section  I even, tried in the header section to display the values

 

Any help is greated appreciated

 

Thanks

 

[riodavid]

Not sure if it will work, but try footer section.

[Ankur Gupta]

Hey, 

You should use the crosstab element to produce the desired output.

Where you need to define following-

Dataset - Main Report Dataset/ Defined Dataset(Whichever produces the data)

Column - Month, Cost Type

Row - Dept

Measures - Amount (Select No Calculation)

Creating this element would definitely give you the desired result.

Also remember not to put the crosstab in the detail section it should reside in Summary Section.

Hope that this helps

Thanks 
Ankur

[vs00500773]

Thanks Ankur for the prompt response.  I have 12 month columns and i get the below error message when I select 'Fit columns to crosstab element'

 

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: Not enough space to render the crosstab.
    at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:548)
    at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:523)
    at com.jaspersoft.studio.editor.preview.view.control.ReportControler$5.run(ReportControler.java:404)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)
Caused by: net.sf.jasperreports.engine.JRRuntimeException: Not enough space to render the crosstab.
    at net.sf.jasperreports.engine.fill.JRFillCrosstab$CrosstabFiller.fillVerticalCrosstab(JRFillCrosstab.java:1527)
    at net.sf.jasperreports.engine.fill.JRFillCrosstab$CrosstabFiller.fill(JRFillCrosstab.java:1470)
    at net.sf.jasperreports.engine.fill.JRFillCrosstab.prepare(JRFillCrosstab.java:805)
    at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:537)
    at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:411)
    at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:386)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummaryNoLastFooterSamePage(JRVerticalFiller.java:1067)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummary(JRVerticalFiller.java:1026)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportEnd(JRVerticalFiller.java:287)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:122)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:586)
    at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
    at java.lang.Thread.run(Thread.java:745)