Print 0 when null value is returned

Report · Apr 27, 2015

When I run my query and the award is not found a null value is returned, I'd like to show that as a 0 value on my excel report.Is there a way to do this?

Report · Apr 27, 2015

You can do it in your SQL query, for example in MS SQL Server you can use the IsNull() function or you can use the expression builder in Report Builder to show a 0 when the field value is an empty string.

Cheers

Eddie

Report · Apr 28, 2015

I don't think the query will work because a record is not returned. I

tried checking for a length of zero and still blanks are being returned.

I'm using a sub report and when it does not find the record it returns

blanks. Not sure what I'm doing wrong. Please help.

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Mon, Apr 27, 2015 at 3:34 PM, EddieLotter <forums_noreply@adobe.com>

Report · Apr 28, 2015

Please show your query.

Cheers

Eddie

Report · Apr 28, 2015

Here it is:

SELECT distinct name.soc_sec, name.level_, transact.awd_id, transact.semester, MAX(nmcrs.sch_yr) AS Enrolled_Yr,

SUM(distinct (case when name.level_ < 3 AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS FRSO_Enroll,

SUM(distinct (case when name.level_ = 3 AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS JR_Enroll,

SUM(distinct (case when name.level_ > 3 AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS SRplus_Enroll,

SUM(distinct (case when name.level_ < 3 AND nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS FRSO_Offer,

SUM(distinct (case when name.level_ = 3 AND nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS JR_Offer,

SUM(distinct (case when name.level_ > 3 AND nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS SRPlus_Offer

FROM name, transact, prospect, nmprg, nmcrs

WHERE name.soc_sec = transact.soc_sec

AND name.soc_sec = prospect.soc_sec

AND name.soc_sec = nmprg.soc_sec

AND name.soc_sec = nmcrs.soc_sec

AND transact.accept <> 'v'

AND transact.tcodes = 'AW'

AND nmprg.active = '1'

AND nmprg.prg_cod <> 'UNGRDTR'

AND name.last_name not like '%Teste%'

AND prospect.term_int NOT BETWEEN '#param.Term1#' AND '#param.Term2#'

AND '#param.AwdID#' = transact.awd_id

AND '#param.SchYr#' = transact.sch_yr

GROUP BY name.soc_sec, transact.awd_id, transact.semester, name.level_

UNION

SELECT distinct name.soc_sec, name.level_, transact.awd_id, 0, MAX(nmcrs.sch_yr) AS Enrolled_Yr,

SUM(distinct (case when name.level_ < 3 AND nmcrs.sch_yr = transact.sch_yr then 1 ELSE 0 END)) AS FRSO_Enroll,

SUM(distinct (case when name.level_ = 3 AND nmcrs.sch_yr = transact.sch_yr then 1 ELSE 0 END)) AS JR_Enroll,

SUM(distinct (case when name.level_ > 3 AND nmcrs.sch_yr = transact.sch_yr then 1 ELSE 0 END)) AS SRplus_Enroll,

SUM(distinct (case when name.level_ < 3 AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS FRSO_Offer,

SUM(distinct (case when name.level_ = 3 AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS FRSO_Offer,

SUM(distinct (case when name.level_ > 3 AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS FRSO_Offer

FROM name, transact, prospect, nmprg, nmcrs

WHERE name.soc_sec = transact.soc_sec

AND name.soc_sec = prospect.soc_sec

AND name.soc_sec = nmprg.soc_sec

AND name.soc_sec = nmcrs.soc_sec

AND transact.accept <> 'v'

AND transact.tcodes = 'AW'

AND nmprg.active = '1'

AND nmprg.prg_cod <> 'UNGRDTR'

AND name.last_name not like '%Teste%'

AND prospect.term_int NOT BETWEEN '#param.Term1#' AND '#param.Term2#'

AND '#param.AwdID#' = transact.awd_id

AND '#param.SchYr#' = transact.sch_yr

GROUP BY name.soc_sec, transact.awd_id, transact.semester, name.level_

ORDER BY transact.awd_id

Report · Apr 28, 2015

Where is the "award" field that you are saying is sometimes NULL?

Cheers

Eddie

Report · Apr 28, 2015

Actually it is the whole record that is null. I think by combining these

reports I should be able to solve this problem.

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Tue, Apr 28, 2015 at 1:06 PM, EddieLotter <forums_noreply@adobe.com>

Adobe Community

Print 0 when null value is returned