Jasper Report: วิธีการตรวจสอบ $P{date} is null ในคิวรี่
โดยปกติการคิวรี่ข้อมูลจากวันที่ เราสามารถเขียน SQL ใส่เงื่อนไขจากวันที่ได้ตรงๆ อยู่แล้ว เช่น
select * from tasks where due_date = $P{date}-- orselect * from tasks where due_date between $P{start_date} and $P{end_date}
แต่ถ้าต้องการเพิ่มเงื่อนไขว่าถ้า $P{date}
มีค่าเท่ากับ null ให้แสดงค่าทั้งหมดออกมา เราจะไม่สามารถใช้ $P{date} is null
ได้เหมือนกับพารามิเตอร์แบบ String
select * from tasks where $P{date} is null or due_date = $P{date} Error filling print... Error executing SQL statement for : sample-report net.sf.jasperreports.engine.JRException: Error executing SQL statement for : sample-report
หมายเหตุ ถ้า $P{date} มีค่าเป็น null จะสามารถทำงานได้ แต่ถ้ามีค่าจะไม่สามารถทำงานได้
วิธีการที่ถูกต้อง คือ ต้องใส่ $X{}
expression มาช่วยในการตรวจสอบค่า null ดังนี้
select * from tasks where $X{EQUAL, NULL, date} or due_date = $P{date}
นอกจากนี้ยังสามารถใช้ $X{}
expression กับ Date ได้อีก ดังนี้
$X{LESS, column, date}
คือcolumn value < $P{date}
$X{LESS], column, date}
คือcolumn value<= $P{date}
$X{GREATER, column, date}
คือcolumn value> $P{date}
$X{[GREATER, column, date}
คือcolumn value>= $P{date}
$X{BETWEEN, column, start_date, end_date}
คือcolumn value >= $P{start_date} AND column value <= $P{end_date}
หมายเหตุ ส่วนตัวลอง $X{BETWEEN, column, start_date, end_date}
แล้วไม่ได้ผล เพราะมันดันเอาเวลาไปใช้ด้วย
ตัวอย่าง
ถ้าต้องสร้างรายงานแสดงรายการงาน พร้อมแสดงวันที่ส่งงาน โดยสามารถค้นหาได้จากช่วงวันที่ หรือแสดงทั้งหมดเมื่อวันที่เป็น null ตามนี้


Step 1: เตรียมฐานข้อมูล
ในตัวอย่างนี้ ผมได้เตรียมข้อมูลเอาไว้แล้วเป็น PostgreSQL ซึ่งสามารถรันได้จาก Docker ตามนี้
docker container run --rm -p 5433:5432 somprasongd/pg10-sample-db
แต่ถ้าต้องการสร้างตารางเองก็สามารถใช้คำสั่งด้านล่างนี้ได้
CREATE TABLE public.tasks (id serial NOT NULL,name varchar(100) NOT NULL,due_date date NOT NULL,created_at timestamptz NOT NULL DEFAULT now(),CONSTRAINT tasks_pk PRIMARY KEY (id));CREATE INDEX tasks_due_date_idx ON tasks USING btree (due_date);INSERT INTO public.tasks (name, due_date) VALUES('Task #1', now()), ('Task #2', now() + interval '2 day'), ('Task #3', now() + interval '4 day'), ('Task #4', now() + interval '6 day'), ('Task #5', now() + interval '8 day'), ('Task #6', now() + interval '10 day'), ('Task #7', now() + interval '20 day'), ('Task #8', now() + interval '22 day'), ('Task #9', now() + interval '30 day'), ('Task #10', now() + interval '60 day');-- ข้อมูลจะเริ่มจากวันที่ที่นำข้อมูลเข้าครั้งแรก เช่น วันนี้วันที่ 11 Oct 2018 Task #1 จะมี due_date เป็นวันที่ 11 Oct 2018
Step 2: เชื่อมต่อฐานข้อมูลกับโปรแกรม iReport
เนื่องจากผมใช้ฐานข้อมูลจาก docker โดยกำหนด
- port: 5433
- database name: sample-db
- username & password: postgres

Step 3: สร้างไฟล์ sample-report.jrxml
เมื่อสร้างไฟล์ใหม่จะได้ออกมาแบบนี้

เริ่มจากสร้าง parameter 2 ตัว คือ
– $P{start_date} เป็น java.util.Date ค่าเริ่มต้นเป็น null
– $P{end_date} เป็น java.util.Date ค่าเริ่มต้นเป็น null
เสร็จแล้วเขียน SQL ตามนี้
SELECT name, due_date
FROM tasks
WHERE ($X{EQUAL, NULL, start_date} or $X{EQUAL, NULL, end_date} or
due_date between $P{start_date} and $P{end_date})
ORDER BY due_date
จัดหน้าจอตามตัวอย่าง

? “Show All”
: ($P{start_date} != null ? “From: “ + $P{start_date} + “ “: “”)
+ ($P{end_date} != null ? “To: “ + $P{end_date} : “”)
หรือถ้าขี้เกียจ ให้เลือกที่ XML แล้ว copy โค้ดด้านล่างนี้ไปวางทับทั้งหมด
Final Step: ทดสอบ Preview
กดที่ปุ่ม Preview จะมีหน้าต่าง Parameter prompt ขึ้นมาให้เลือกวันที่ start_date และ end_date

ถ้าเลือก start_date และ end_date เป็น Use default

รายงานก็จะแสดงข้อมูลทั้งหมดออกมาแทน

ที่มา ; https://medium.com/@somprasongd