How do I report a text field as NA if it is NULL?

[Brandy.gaydos]

I have a text field on my report that will not always have data.  When this field is blank (NULL) the report will not generate.  I want to specify that if the field in the database is NULL that it should report as "N/A", thus allowing the report to generate.

The field in question is "dbo.actionplan.actionplanid" and is being called from a joined table in the database per the following query:

SELECT dbo.incident.incidentid,
    dbo.incident.incidentdesc,
    dbo.incident.incidenttype,
    dbo.incident.incidentstatus,
    dbo.incident.incidentdt,
    dbo.incident.reportedby,
    dbo.incident.rootcause,
    dbo.incident.triage,
    dbo.incident.departmentid,
    dbo.incidentfind.incidentfinddesc,
    dbo.incidentfind.investigationlog,
    dbo.incidentfind.u_response,
    dbo.incidentfind.u_findinggroup,
    (select refdisplayvalue from refvalue where reftypeid= 'Findinggroup' and refvalueid=incidentfind.u_findinggroup) reftypedisplay,
    dbo.incident.explanation,
    dbo.incident.standardreference,
    dbo.incident.riskdone,
    dbo.incident.risknotdone,
    dbo.incident.locationid,
      dbo.LV_Query_GetLocPathLabel(incident.locationid,' / ',null) as LocPathLabel,
    dbo.incident.expirationdt,
    dbo.incident.u_timepoint,
    dbo.incident.incidentauditby,
    dbo.incident.incidentauditdt,
    dbo.incident.incidentauditreference,
    dbo.incident.severity,
    dbo.incident.notes,
    dbo.actionplan.actionplanid
FROM dbo.incidentfind
    INNER JOIN dbo.incident ON 
     dbo.incident.incidentid = dbo.incidentfind.incidentid 
     INNER JOIN dbo.actionplan ON 
     dbo.actionplan.incidentid = dbo.incident.incidentid 
WHERE 
     dbo.incident.incidentid = $P{incidentid}

[anish.rai]

 

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

 

[jgust]

If "dbo.actionplan.actionplanid" is allowed to be null then you must change the join condition on "dbo.actionplan" to be an OUTER JOIN.  It is the INNER JOIN to that table that is causing nothing to be returned when there is no "dbo.actionplan.actionplanid".

SELECT dbo.incident.incidentid     , dbo.incident.incidentdesc     , dbo.incident.incidenttype     , dbo.incident.incidentstatus     , dbo.incident.incidentdt     , dbo.incident.reportedby     , dbo.incident.rootcause     , dbo.incident.triage     , dbo.incident.departmentid     , dbo.incidentfind.incidentfinddesc     , dbo.incidentfind.investigationlog     , dbo.incidentfind.u_response     , dbo.incidentfind.u_findinggroup     , (select refdisplayvalue from refvalue where reftypeid= 'Findinggroup' and refvalueid=incidentfind.u_findinggroup) as reftypedisplay     , dbo.incident.explanation     , dbo.incident.standardreference     , dbo.incident.riskdone     , dbo.incident.risknotdone     , dbo.incident.locationid     , dbo.LV_Query_GetLocPathLabel(incident.locationid,' / ',null) as LocPathLabel     , dbo.incident.expirationdt     , dbo.incident.u_timepoint     , dbo.incident.incidentauditby     , dbo.incident.incidentauditdt     , dbo.incident.incidentauditreference     , dbo.incident.severity     , dbo.incident.notes     , dbo.actionplan.actionplanid  FROM dbo.incidentfind       INNER JOIN dbo.incident ON dbo.incident.incidentid = dbo.incidentfind.incidentid       LEFT OUTER JOIN dbo.actionplan ON dbo.actionplan.incidentid = dbo.incident.incidentidWHERE     dbo.incident.incidentid = $P{incidentid}[/code]

If you still would like for actionplanid to return "NA" when null then I suggest looking up the ANSI SQL standard for COALESCE().