Summary report using SQL 'Distinct' and 'Sum()'

[nkamir]

I'm trying to design a report using 'Print When Group Changes' option IReport 3.0.0

Heres what I want to acheive.
I want to display sums of certain fields of my database for each date of given date range.
Lets say, the date range is 01-01-2009 to 01-31-2009, now there are more than one records for each date.
For example sum of parking, tolls, gas, tax of all the records of date '01-01-2009'
and sum of parking, tolls, gas, tax of all the records of date '01-02-2009'
and so on.

I do not want to show all the records of this date, just the sum of certain fields.

Heres what I have done so far.
1- I grouped the report by 'date',
2- created a variable (lets say varTotalParking) with
- Expression to sum the database 'parkingAmount' field.
- Reset type 'Group',
- Evaluation type 'Group',
- 'print repeated values' disabled
- Print when group changes

what happens that the variable is printed everytime when its sum changes. I started this solution with something in mind that I'ld just print the sum of the variable at the end of the each Group, and delete the group elements (which prints all the records  of that date, hence I'ld have just the total of that databse field on the report.

If I can make that variable print ONLY once at the end of the group, it will work fine, I hope.

Post Edited by Nasir Amir at 02/04/09 14:23

[nkamir]

ok. I was able to make this report using a subreport.

First, I created a main report with an SQL statement "SELECT DISTINCT myDate FROM myTable"
which gave me unique dates from myTable eliminating the duplicate entries

Second, I created a parameter in the subreport and I passed the date from main report to the subreport.
Then, in subreport, I created an SQL statement like "

SELECT
     SUM(parking) AS var_parking,
     SUM(tolls) AS var_tolls,
     SUM(andSoOn) AS var_andSoOn
FROM
     myTable
WHERE
     myDate = $P{subPar_myDate}   // the date passed from main report
 

attachment shows the result (I formatted the subreport of course).