I am using Apex 20.1.
I have chart report where I have added 3 filters. I only need to include values that are not null in the "group by" clause.
The filters are: :p6010_system, :p6010_year, :p6010_month
In my example below, if :p6010_system is null, it does not need to be part of group by clause. The same for :P6010_YEAR and :P6010_MONTH. If either one is null, they do not need to be part of the group by clause.
Any ideas on how to do this? Open to alternative solutions.
Query:
select
system_name, review_period_year, review_period_month,
health,
count(health)
from cap_mgmt_systems_rep_per_v
where (:P6010_YEAR is null or review_period_year = :P6010_YEAR)
and (:P6010_MONTH is null or review_period_month = :P6010_MONTH)
and (:p6010_system is null or system_name = :p6010_system)
and system_name not like '%Test%'
and health = 'Green'
group by system_name, review_period_year, review_period_month,health