What value is passed in if no parameter is specified when running a report?

Support
#1

I’m trying to create a report that has multiple input fields, all of which can be left blank. However, using SQl, I can’t figure out how to run the report efficiently if even one field is left blank. here’s a sample query. How do I catch the situation where no parameter is specified?


DECLARE @username VARCHAR(MAX)
SET @username = ISNULL(null, ${user_name})
select  ag.name as "accessgroupname", su.name as "username", eaa.account as "acNumber", su.position_ as "position"
from dbo.ebank_user_access_group uag
  join dbo.sec_user su on uag.user_id = su.id
  join dbo.E_ACCESS_GROUP ag on uag.access_group_id = ag.id
  join dbo.e_a_access eaa on eaa.access_group_id = ag.id
where su.name like '%' + COALESCE(@username, su.name) + '%'

inputWithNoParameter

#2

Is the problem due to using SQL for the queries in these reports?

#3

Hi Ned,

JPQL/SQL have a feature “Preprocess query as a Groovy Template”.
Query is processed using GStringTemplateEngine (Template engines)
Report input parameters are passed to the template engine.
You can change query depends on the parameter value.

For example:


select e.create_ts, e.id, e.vin from ref_car e 
where 
e.create_ts >= \${createTs1} 
and 
<% if (createTs2 != null) {%>e.create_Ts < \${createTs2} <%} else {%> e.create_Ts < current_timestamp <%}%>

If input parameter ‘createTs’ is null, the query is transformed into:


select e.create_ts, e.id, e.vin from ref_car e 
where 
e.create_ts >= ${createTs1} 
and 
e.create_Ts < current_timestamp

Thanks,
Subbotin Andrey

sql dataset

1 Like
#4

Perfect! Thank you.