Convert Reporting Services expression to SQL

Question

I'm trying to modify a report and I need to exclude a number of rows based on the data in one column the expression for that column is:

=iif(sum(iif(left(Fields!crs_group.Value,1) = "G",1,0)
     ,"GrpCourse")=0,"n/a",
     sum(iif(Fields!crs_group.Value="Enrolled on Course",1,0))
     - sum(iif(left(Fields!crs_group.Value,1) = "G",1,0)))

I think if I can convert it to SQL I can filter out those rows in the query. My code so far is:

CASE WHEN SUM(CASE WHEN LEFT(sub.crs_group, 1) = 'G' 
THEN 1 ELSE 0 END)=0 THEN 999999999
ELSE SUM(CASE WHEN sub.crs_group = 'Enrolled on Course' 
THEN 1 ELSE 0 END) - SUM(CASE WHEN LEFT(sub.crs_group,1) = 'G' 
THEN 1 ELSE 0 END)
END AS NumberNotInGroups

However this doesn't give matching results to when the report is run. I've change "n/a" to 999999999 because I get a conversion from VARCHAR to INT error. I've tried casting to VARCHAR with no success. Any help would be much appreciated, spent all afternoon yesterday trying to figure it out.

EDIT

Here is the full query:

    SELECT        
sub.course, 
sub.crs_group,
m.m_reference, 
me.e_status, 
me.e_id,
s.s_studentreference,
p.p_forenames,
p.p_surname,
pcd.p_surname + ',' + pcd.p_forenames as course_dir,
CASE WHEN SUM(CASE WHEN LEFT(sub.crs_group, 1) = 'G' THEN 1 ELSE 0 END)=0 THEN 999999999
     ELSE SUM(CASE WHEN sub.crs_group = 'Enrolled on Course' THEN 1 ELSE 0 END) - SUM(CASE WHEN LEFT(sub.crs_group,1) = 'G' THEN 1 ELSE 0 END)
END AS NumberNotInGroups



FROM            
msql.unitesnapshot.dbo.capd_moduleenrolment AS me 
INNER JOIN msql.unitesnapshot.dbo.capd_module AS m ON me.e_module = m.m_id
LEFT JOIN msql.unitesnapshot.dbo.capd_staff scd on m.m_modulesupervisor = scd.s_id
LEFT JOIN msql.unitesnapshot.dbo.capd_person pcd on scd.s_id = pcd.p_id
INNER JOIN msql.unitesnapshot.dbo.capd_student s on me.e_student = s.s_id
INNER JOIN msql.unitesnapshot.dbo.capd_person p on s.s_id = p.p_id
INNER JOIN (SELECT

            m.m_id,
            CASE WHEN m.m_reference not like '%G_' THEN m.m_reference ELSE LEFT(m.m_reference, charindex('G', m.m_reference) - 1) END AS course, 
            CASE WHEN m.m_reference not like '%G_' THEN 'Enrolled on Course' ELSE RIGHT(m.m_reference, 2) END AS crs_group

            FROM

            unitesnapshot.dbo.capd_module m) sub ON sub.m_id = me.e_module

WHERE        
me.e_status = 'A' AND 
LEFT(m.m_reference, 2) = '12' AND 
SUBSTRING(m.m_reference, 7, 2) in ('VF','AB','FB')

GROUP BY

sub.course, 
sub.crs_group,
m.m_reference, 
me.e_status, 
me.e_id,
s.s_studentreference,
p.p_forenames,
p.p_surname,
pcd.p_surname + ',' + pcd.p_forenames

ORDER BY 
p.p_surname

Here is the correct result in SSRS:

And here is the incorrect result in sql-server, it should show 1 not 99999999:

Answer 1

You probably need to explain the question better and show more details of your queries. It looks like you're trying to show those courses which have people enrolled who aren't in groups. However, you use the same field, crs_Group, to show whether they are simply enrolled or whether they are in a group. Now, if crs_Group equals "Enrolled on Course" but changes to something beginning with G when they join a group, then your equation should give a negative number when the course has more people in groups than not.

So maybe it would be better to calculate everyone enrolled in a course less those people in groups to end up with the people on the course but not in groups:

SELECT CourseName, 
  COUNT(*) - SUM(CASE WHEN LEFT(crs_group, 1) = 'G' THEN 1 END) AS NumberNotInGroups
FROM Courses
GROUP BY CourseName
HAVING COUNT(*) - SUM(CASE WHEN LEFT(crs_group, 1) = 'G' THEN 1 ELSE 0 END) <> 0

This query filters out courses where everyone is in a group.