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?
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
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>
Copy link to clipboard
Copied
Please show your query.
Cheers
Eddie
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
Copy link to clipboard
Copied
Where is the "award" field that you are saying is sometimes NULL?
Cheers
Eddie
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>