Count All Records as well as Selected Records

[dianaj59]

I am using iReports 3.0.0 and jasperReports 3.1.2.

I need to count all the records for the report as well as count a selected number of records.

I am able to use the REPORT_COUNT variable for the count of all the records, no problem.

However, I don't know how to use this variable to find the records between Date_Start and Date_End.

My report looks like:

 

NAME	maxDATE
jon doe	07/01/2010
jane doe	09/01/2010

The variable REPORT_COUNT will count the Names just fine and displays the total count(int) in the header as desired.

I want to county only the dates between say, 08/01/2010 and 10/01/2010 and display this count(int) in the header as well.

I can't seem to find how to do this in the sql statement OR the Report_count variable.

Thanks for any solutions.

[cbarlow3]

Let's assume every record has a value for maxDate (no nulls) and that you have defined a parameter called $P{StartDate} and another called $P{EndDate}, which when you run the report you enter 8/1/2010 and 10/1/2010 respectively (or you can hardcode them or calculate them based on something else, naturally...I don't know where you get these dates from).

Then in your SQL command, you can actually create a new row that doesn't come from your database directly but which will be in the result set and therefore be a new field in iReport.  Let's call it inDateRange.  If your original SQL statement was something like this:

SELECT t.NAME AS NAME, t.maxDate AS maxDate FROM mySchema.Table as t

You could change that to read:

SELECT t.NAME AS NAME, t.maxDate AS maxDate,

   case when maxDate between $P{StartDate} and $P{EndDate} then 1

              else 0

   end AS inDateRange

FROM mySchema.Table as t

Now you should have a new field in your result set that is always set to 1 or 0 appropriately, and you can define a new variable that is the sum of the values in this field over the entire report.  Good luck!

 

Carl

[dianaj59]

Thank you.  You understand exactly what I need.

I was on the right track, however I can not seem to see where the "case" piece fits into my sql.

I'm attaching my sql statement.  If I put it in the select statement with it's own FROM, I get this error

"FROM keyword not found where expected".

Where should this case piece be placed given my sql as it is right now?

 

Code:

SELECT     CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME" AS NAME,     max(IMMUN_FACT."VACCINATION_DATE") AS MaxDateFROM     "MASTER"."SCHOOL_REPORT_LIST" SCHOOL_REPORT_LIST LEFT OUTER JOIN    "MASTER"."SCHOOL_REPORT_LIST_CLIENT" SCHOOL_REPORT_LIST_CLIENT ON SCHOOL_REPORT_LIST."SCHOOL_REPORT_LIST_ID" = SCHOOL_REPORT_LIST_CLIENT."SCHOOL_REPORT_LIST_ID"     LEFT OUTER JOIN "DM"."CLIENT_DEMOGRAPHIC" CLIENT_DEMOGRAPHIC ON SCHOOL_REPORT_LIST_CLIENT."CLIENT_ID" = CLIENT_DEMOGRAPHIC."CLIENT_ID"     LEFT OUTER JOIN "DM"."IMMUN_FACT" IMMUN_FACT ON CLIENT_DEMOGRAPHIC."CLIENT_ID" = IMMUN_FACT."CLIENT_ID"     LEFT OUTER JOIN "DM"."VACCINE" VACCINE_A ON IMMUN_FACT."VACCINE_ID" = VACCINE_A."VACCINE_ID"WHERE     SCHOOL_REPORT_LIST_NAME = 'schoolNameTest'	   AND VACCINE_ID IN (21,23,79,127,130)GROUP BY      CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME"ORDER BY      Name,MaxDate

[dianaj59]

ALSO...

The code below is what I tried and got the "FROM keyword not where expected" error.

I also tried to put a comma after      max(IMMUN_FACT."VACCINATION_DATE") AS MaxDate

and then got an error of "SQL not properly ended.

My sql knowledge has been very basic to this point.

Thanks...

Code:

SELECT     CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME" AS NAME,     max(IMMUN_FACT."VACCINATION_DATE") AS MaxDate     	case	   		WHEN maxDate BETWEEN $P{DATE_START} AND $P{DATE_END}	   		THEN 1 else 0	   end AS inDateRange FROM "IR_DM_OWNER"."IMMUN_FACT" as tFROM     "MASTER"."SCHOOL_REPORT_LIST" SCHOOL_REPORT_LIST LEFT OUTER JOIN "MASTER"."SCHOOL_REPORT_LIST_CLIENT" SCHOOL_REPORT_LIST_CLIENT ON SCHOOL_REPORT_LIST."SCHOOL_REPORT_LIST_ID" = SCHOOL_REPORT_LIST_CLIENT."SCHOOL_REPORT_LIST_ID"     LEFT OUTER JOIN "DM"."CLIENT_DEMOGRAPHIC" CLIENT_DEMOGRAPHIC ON SCHOOL_REPORT_LIST_CLIENT."CLIENT_ID" = CLIENT_DEMOGRAPHIC."CLIENT_ID"     LEFT OUTER JOIN "DM"."IMMUN_FACT" IMMUN_FACT ON CLIENT_DEMOGRAPHIC."CLIENT_ID" = IMMUN_FACT."CLIENT_ID"     LEFT OUTER JOIN "DM"."VACCINE" VACCINE_A ON IMMUN_FACT."VACCINE_ID" = VACCINE_A."VACCINE_ID"WHERE     SCHOOL_REPORT_LIST_NAME = 'schoolNameTest'	   AND VACCINE_ID IN (21,23,79,127,130)GROUP BY      CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME"ORDER BY      Name,MaxDate

[cbarlow3]

When I took your original SQL command that doesn't attempt to use the CASE statement to count how many dates are within a range, I got an error in iReport's parser, so make sure that your first SQL statement is working the way you expect before we "enhance" it.  As for your second SQL statement, I think you have the CASE in exactly the right place (I misspoke in my explanation--you're creating a new COLUMN in your result set called "inDateRange", not a new ROW--sorry for any confusion).  The two problems I see with your second SQL statement are:

1. As you guessed, you need a comma after "As MaxDate", because you always need a comma between two column specifications in your SELECT clause...this is just a long way of writing SELECT name, maxDate, inDateRange FROM...

2. Where did the extra "FROM" clause come from after the CASE statement?  You don't want two FROM clauses here.  The CASE statement (including the alias "AS inDateRange") is just another column in the SELECT clause...you want to leave the FROM clause the way it was before...and only have one!

Again, I don't know if the SQL statement was correct before, but I think that once you've verified that it is, adding a comma and a CASE statement to the end of your SELECT clause should get you the extra column you need.  I'd also say that if you're going to be doing a fair number of reports, I find it easier to build/test the SQL SELECT command in another program besides iReport.  Maybe there's a way in iReport to actually view the result set itself without dragging the fields into a detail band, but I haven't seen it yet.  In my case, I'm getting data from a DB2 database on an IBM AIX server, so I use IBM Data Studio, but any SQL client that's compatible with your database should have a place where you can enter a SELECT command and it will return rows or parsing errors.  I like to have confidence that my SQL command is doing most of the work correctly before I start making my report rely on it.  Having said that, the query designer in iReport is pretty good for quickly seeing the fields available in the various records and the relationships between the records that is defined by the foreign and primary keys.  If they had/have a window where you could see a raw result set, I wouldn't be tempted to use two tools.

Carl

[dianaj59]

My sql statement works without the case statement. 

It brings back a column of names and maximum dates.

I am using PLSQL to test the sql statements outside of iReports.

When I put the "case" piece into the select statement I get an error of...

"ORA-00904:"MAXDATE": Invalid Identifier

It seems to want a string instead of a data.

Please look at the SELECT statement below and see if that is what you'd expect it to look like.

I have the dates in there for PLSQL instead of the iReport field names, but either way it works without the case statement.

Code:

SELECT     CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME" AS NAME,     max(IMMUN_FACT."VACCINATION_DATE") AS MaxDate       case WHEN MaxDate BETWEEN to_date('2010/08/01','yyyy/mm/dd')AND to_date('2010/10/20','yyyy/mm/dd')             THEN 1 else 0       end AS inDateRangeFROM.....ETC.

[dianaj59]

WE GOT IT....

This is what was needed.  See code below:

THANK YOU...so much for the guidance!

Code:

SELECT     CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME" AS NAME,     max(IMMUN_FACT."VACCINATION_DATE") AS MaxDate,       case WHEN max(IMMUN_FACT."VACCINATION_DATE") BETWEEN to_date('2010/08/01','yyyy/mm/dd')AND to_date('2010/10/20','yyyy/mm/dd')             THEN 1 else 0       end AS inDateRange

[cbarlow3]

Needed that comma, didn't you? Computers are so picky.

[dianaj59]

Yep, and it would not accept MaxDate.  I had to use max(IMMUN_FACT."VACCINATION_DATE") in the CASE statement.

It is working in PLsql, I am sure I can get it to work in iReports now....Thanks.