soldout6000 - Sunday, December 24, 2017 8:56 AM

Create table main
(name varchar,
member_type_id integer)

Following are the values in main table.
Name                member_type_id
Sara                        11
Jack                        11
Jill                            99
Mary                        99
Anna                        99

SELECT CASE
WHEN MEMBER_TYPE_ID = 11 THEN COUNT(MEMBER_TYPE_ID) OVER (PARTITION BY MEMBER_TYPE_ID) 
END emp_count,
CASE
WHEN MEMBER_TYPE_ID = 99 THEN COUNT(MEMBER_TYPE_ID) OVER (PARTITION BY MEMBER_TYPE_ID) 
END sp_count
FROM main 
When I run the above query in sql server I get following result
emp_count        
2                                           
2                                            
sp_count                                            
3
3
3
However, when I run this query in crystal report, I get the result of only emp_count i.e 2. For sp_count nothing shows. I think the issue is with how crystal is reading the case statement. I don't know if this is the right place for me to ask this question. However, any help will be greatly appreciated. Thanks in advance.