Variable (Count) SQL Query

[jcordingley]

 Hi all, im new to ireport and have tried creating a report for some test results we have stored in a MSSQL db, i have created the report fine and have this working through jasperserver as i uploaded my report to the repository.

However i would like to show a  text field on the report for the amount of results returned from the database.

I presume i can setup a variable named fieldcount and have this calculate the number of results using the calculation COUNT, but i cannot get this working, surely this cannot be that hard?

Any help or ideas from people would be greatly appreciated.

Thanks in advance

Jamie

[jcordingley]

 Surely someone has done this?! 

Anyone?!

[cbarlow3]

Yes, this is very simple in iReport.  Let's say that I have two fields called employeeId and salary that I'm outputting in the detail band.  In the summary band, I want to output a new variable called totalCount that has the number of detail lines (one per employeeId) and another new variable called totalSalaryAmount that outputs the sum of the salary fields I output.  Here's how you do it:

In the Report Inspector Pane, right click "Variables" and select "Add Variable".  A name is automatically assigned ("variable1" or some such).  In the "Properties" pane, change the name of the variable to totalCount.  Change the Variable class from java.lang.String to java.math.BigDecimal (or some other suitable numeric type).  Change the Calculation from "Nothing" to "Count".  Increment is automatically defaulting to "Report", which is correct.  Click the three dots to the right of "Variable Expression" and make the variable expression that is being counted be $F{employeeId}.  Now drag that variable from the report inspector into the summary band of your design view pane.

Similarly, you can add a variable for totalSalaryAmount.  Set the name and the variable class.  Change the Calculation from "Nothing" to "Sum".  For the expression to be summed, use $F{salary}.  Drag the new variable into the summary band.  Make sure you have that field selected in the summary band, and in the Properties pane, change the pattern to a currency pattern and change the horizontal alignment to Right, for appearance purposes.

By the way, depending on what you're trying to count, you may find that the system-defined variable REPORT_COUNT already does what you need!  It keeps a count of how many times your definition of a detail band has been output.  Not only can you potentially use the final value for a total count, you can also use the value in each detail to create a "banded table" effect, where the detail lines have an alternating background of white and green, for example.  Here's that technique gratis:

In the Report Inspector, right click "Styles" and choose "Add | Style".  A style is automatically created called "style1".  In the Properties pane, change the name to "BandedReport".  Back in the Report Inspect pane, right click the "BandedReport" style and choose "Add conditional style".  It adds one with the words "<No condition set>".  In the Properties pane, set the Condition Expression to $V{REPORT_COUNT}%2==1  and set the Backcolor to some color other than white that you think will make a good alternating background color and still let you see your data. 

Now that you've defined a style (which you can copy from one report and paste into another, by the way), here's how you use it: In the design pane or in the Detail section of the Report Inspector, select all the fields that you want to have this banded effect (probably all of them, and it will look better if all  the boxes for the fields are touching each other).   In the Properties pane, the section marked "Style" should now have a dropdown where you can choose "BandedTable" as a style.  Do so, and click the "Opaque" checkbox right above it.  Now preview your report.  Voila!  You should be seeing every other detail has your custom background color.

Carl