Highlighted

Print 0 when null value is returned

Explorer ,
Apr 27, 2015

Copy link to clipboard

Copied

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?

TOPICS
Reporting

Views

473

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Print 0 when null value is returned

Explorer ,
Apr 27, 2015

Copy link to clipboard

Copied

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?

TOPICS
Reporting

Views

474

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 27, 2015 0
Advocate ,
Apr 27, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 27, 2015 0
Explorer ,
Apr 28, 2015

Copy link to clipboard

Copied

​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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 28, 2015 0
Advocate ,
Apr 28, 2015

Copy link to clipboard

Copied

Please show your query.

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 28, 2015 0
Explorer ,
Apr 28, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 28, 2015 0
Advocate ,
Apr 28, 2015

Copy link to clipboard

Copied

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

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 28, 2015 0
Cozmo2 LATEST
Explorer ,
Apr 28, 2015

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 28, 2015 0